Materialization

Turn validated recurring queries into reusable views in an agent-owned schema.

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]"

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.

	public (your data)	dash (agent-owned)
Who writes	Your application and pipelines	The Engineer agent only
Holds	Source tables	Generated views and summary tables
Effect of an incorrect write	Production tables may be affected	Limited to generated objects; the schema can be rebuilt

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

Task	Guide
Keep writes inside the boundary	Safe data access
Seed the validated queries	Grounding in context

Developer Resources

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