Materialization

Materialization turns recurring, validated queries into reusable views in an agent-owned schema. Later requests can read the same view and use consistent query logic.

The pattern

A recurring question is a candidate for a view. The agent promotes the validated query to an agent-managed schema and answers future requests from that view.

1. A question repeats, or a query gets validated as correct.
2. The agent proposes the view and a human approves the write.
3. The Engineer builds the view in its own schema (for example dash), never in public.
4. The new object's schema and an example query go into knowledge, so later runs can discover it.
5. The next request reads the view directly and uses the same query logic across users.

uv pip install "agno[openai,psycopg,sql]"

Set OPENAI_API_KEY in the environment before running the Python code. Replace the warehouse URLs with your own PostgreSQL connection strings and create the database roles, schemas, and grants described on this page first. The host warehouse, database analytics, and roles such as readonly and dash_writer are placeholders.

Examples using PostgresDb or PgVector also need a separate writable application database. The sample URL assumes a PostgreSQL service at localhost:5532 with database/user/password ai; knowledge examples need the pgvector extension. See PgVector setup. Keep this application's storage credentials separate from the restricted warehouse role.

from agno.agent import Agent
from agno.models.openai import OpenAIResponses
from agno.tools.sql import SQLTools

engineer = Agent(
    name="Engineer",
    model=OpenAIResponses(id="gpt-5.5"),
    tools=[
        SQLTools(
            db_url="postgresql+psycopg://dash_writer@warehouse/analytics",
            requires_confirmation_tools=["run_sql_query"],
        ),
    ],
    instructions=(
        "When the user requests or approves a reusable view, introspect the "
        "schema and create it in dash. Never create objects outside dash."
    ),
)

Configure dash_writer as a non-owner role with read access to source data and write access only to dash. requires_confirmation_tools pauses every run_sql_query call. After the client continues the run with approval, SQLTools executes the statement. The role's grants must enforce the schema boundary because SQLTools does not restrict SQL by schema.

After creation, add the object's columns, purpose, and example queries to the agent's knowledge so later runs can discover the view. Attach the knowledge store from grounding to the Engineer and set update_knowledge=True. That gives the agent an add_to_knowledge tool, which writes the description back into the same store it searches.

Why an agent-owned schema

Give generated views a dedicated schema. This keeps them separate from the tables your product depends on.

Treat the agent-owned schema as rebuildable. Review and drop incorrect views, then generate them again.

Materialize from validated queries

Validated queries from grounding are good materialization candidates. A frequently requested query that analysts already trust can become a view. Review the generated DDL before approving the write.

A regular view evaluates its query when read. Summary tables and materialized views need explicit refresh logic; this example does not schedule that work.

Reuse validated work

Reusable views reduce repeated query generation:

• Captured corrections inform later runs (self-correction).
• Validated query shapes sit in the knowledge stores (grounding).
• The agent-owned schema stores reusable views for recurring questions.

Each approved view gives later runs a consistent query path for the same question.

Next steps

Developer Resources

• Dash: reusable views in the dash schema
• Serve and embed