Progressive schema discovery: finding the right table without reading them all

Putting the whole schema in the prompt costs tokens, confuses the model and shows it tables it should never query. Our text-to-SQL agent discovers the schema in three steps instead, and stops as soon as it knows enough to answer.

Đorđe SpasojevićAI EngineerPublished 5 November 2026 · 2 min read · Life Sciences · Python · LangGraph · Postgres

01

The problem with the whole schema in the prompt

—

Size.500+ tables, most of them junction tables, so almost every question needs several joins. In the fixed workflow, working out the joins alone took 20 to 30 calls.

—

Confusion.Similar names sit side by side, such as country, country_code and market, and the model picks the wrong one.

—

Exposure.Every table in the prompt is a table the model may try to query.

02

Three steps, each a tool

01

List.Names and one line on each view. Cheap, and always the first call.

02

Describe.Columns, types and allowed values, only for the views the agent plans to use.

03

Sample.Three real rows, so the model sees how values are actually written.

python
CATALOG = {
    "reimbursement_decisions": {
        "summary": "One row per reimbursement decision, by product, country and date.",
        "columns": {
            "product_name": "text",
            "country_code": "ISO 3166-1 alpha-2, e.g. DE, FR",
            "decision": "one of: reimbursed, restricted, not reimbursed",
            "decision_date": "date the payer published the decision",
        },
    },
    # ... six more views
}

@tool
def list_views() -> str:
    """Step 1. Names and one-line summaries of the views. Call this first."""
    return "\n".join(f"{name}: {v['summary']}" for name, v in CATALOG.items())

@tool
def describe_view(name: str) -> str:
    """Step 2. Columns and allowed values for one view you plan to query."""
    if name not in CATALOG:
        return f"Unknown view '{name}'. Call list_views first."
    return "\n".join(f"{col}: {desc}" for col, desc in CATALOG[name]["columns"].items())

@tool
def sample_rows(name: str) -> str:
    """Step 3. Three real rows from one view, to see how values are written."""
    if name not in CATALOG:
        return f"Unknown view '{name}'. Call list_views first."
    return db.fetch(f'SELECT * FROM "{name}" LIMIT 3')  # name checked against CATALOG above

The view and column names here are examples; the real ones stay internal. The descriptions matter more than the code: they are what the model reads when it chooses.

03

Checking the SQL against what it may touch

Discovery tells the model what exists. A guard makes sure it uses nothing else. Its error messages are written for the model, so it can correct the query and try again.

python
import sqlglot
from sqlglot import exp

def check(query: str) -> str:
    tree = sqlglot.parse_one(query, read="postgres")
    if not isinstance(tree, exp.Select):
        raise ValueError("Only one SELECT statement is allowed.")
    used = {t.name for t in tree.find_all(exp.Table)}
    unknown = used - CATALOG.keys()
    if unknown:
        raise ValueError(f"Not an allowed view: {', '.join(sorted(unknown))}. Call list_views.")
    return tree.sql(dialect="postgres")

This version rejects CTEs and UNIONs, which is stricter than needed. The production guard parses them with sqlglot too, and checks every table and column they touch against the catalog, so a column the model made up never reaches the database.

04

What it changed

—

Calls to work out the joins:from 20 to 30 in the fixed workflow to 2 or 3.

—

Choosing columns:the fixed workflow rarely got that far. Now the agent reads the column descriptions in one call, and the guard rejects any column that does not exist before the query runs.

05

When not to do this

With a small schema of clearly named tables, put it in the prompt and skip the extra calls. Discovery pays when the schema is large, the names are close, or some tables must stay out of reach.

Build Notes · for engineersMore in Insights →

Bring the system you’re trying to make work.

Schedule a call with a partnerFree until 11 December