How to Review an Agent's Schema Guess Before It Writes SQL

A schema card is a compact, written description of the part of the database the agent needs for one question. It is not the full DDL dump. The goal is to remove every table and column that is irrelevant, and to explain every column that remains in plain language.

Annotation is not decoration. The same accuracy guide shows augmented schema passed to a model with inline comments such as a foreign key marker and a note that a status column holds the lifecycle values pending, fulfilled, refunded and cancelled. Those comments steer the model toward the right table and the right filter values text-to-SQL accuracy practices.

A usable card for a retention question looks like this:

-- Table: users (one row per registered account)
--   user_id      int primary key, unique per account
--   signup_date  date, when the account was created (use for cohorts)
--   country      text, billing country at signup

-- Table: user_events (one row per action taken in the product)
--   event_id     int primary key
--   user_id      int, foreign key -> users.user_id
--   event_date   date, when the action happened
--   event_type   text, one of: login | purchase | view
-- join key: user_events.user_id = users.user_id  (many events per user)

Four rules make the card do its job:

• Trim to the tables the question plausibly needs. Adding two extra tables "just in case" invites the agent to join them.
• Describe every column in business terms. "Signup date" is clear; a column named created_at on four different tables is not.
• Mark join keys on both sides and state the cardinality (one row per user, many events per user). The cohort walkthroughs depend on joining activity back to the signup month, so a wrong key silently changes every retained user count a SQL cohort walkthrough.
• Name the allowed values for any status or type column, so filters use real values instead of invented ones.

The core technique is a restatement step. Hand the agent the schema card and the question, and ask for no SQL yet. Ask for its understanding in a fixed shape: the tables it will use, the columns, what each column means in its own words, how the tables join, and the filters it plans to apply. The model does not need a schema to produce a sentence like this, and because it never sees the full DDL, it cannot quietly fall back on guessing.

A prompt that works in practice:

You are given a schema card and a question. Do NOT write SQL.

Return exactly these sections:

1. Tables I will use, and why each is needed.
2. Columns I will use, with a one-line meaning for each.
3. How the tables join (column = column, and cardinality).
4. Filters I plan to apply, with the exact column and value.
5. Anything in the question that is ambiguous and how I will resolve it.

Question: <the user question>
Schema card: <the trimmed card>

The ambiguity section matters. Natural language is less precise than SQL, and a model faced with an unclear term tends to answer anyway rather than ask, which produces hallucinated intent Google Cloud on text-to-SQL. A question like "best selling" has no single meaning until someone decides whether it means most orders or most revenue, and the restatement is where that decision gets surfaced. If the model states its reading and you agree, the eventual SQL is grounded. If it states a reading you did not intend, you have caught the failure before a single row was scanned.

Now compare the restatement to two references: the actual schema and the question. Work through five checks in order.

• Tables. Does every table the agent named exist, and is every table it needs present? A missing user_events table means retention cannot be computed at all, however good the query looks.
• Columns. Do the named columns exist on the named tables? This is where an agent invents a plausible column such as last_login that no one ever created.
• Types and meaning. Does the agent's stated meaning match the documented one? A date stored as a timestamp with a time zone will behave differently in date truncation than a plain date. Getting this wrong shifts events into the wrong month and changes every retention percentage.
• Relationships. Is each join a real foreign key or a documented link, with the right cardinality? A many-to-many link treated as one-to-many duplicates rows and inflates counts.
• Filters. Are the filter values drawn from the allowed set, and do they match the question's intent? "Refunded orders" should filter on the refund type, not on order status, if those differ.

A worked example makes the value clear. Suppose the question is "what share of users who signed up in January were still active three months later?" and there are 1,000 users in the January cohort. The agent restates the join as user_events.user_id = users.user_id and plans to filter on users.signup_date. That reading is correct, and the eventual query can bucket users by signup month exactly as the standard cohort pattern does a retention SQL guide. Now suppose the agent instead restates the join as user_events.event_date = users.signup_date. That runs, returns numbers, and answers a different question entirely: it only counts users active on the day they signed up. Reviewing the restatement catches this in seconds. Reviewing the finished SQL, with a join condition buried in a WITH clause, often does not.

The instinct when the restatement is wrong is to rewrite the prompt. Resist it. If the agent misread the schema, the fault is in the schema card or the data, and a prompt tweak will paper over it for this question and fail on the next one.

Use this decision rule:

Two supporting habits pay off. First, validate the underlying values, not just the column names. Data validation is the practice of checking that data is complete, unique and consistent with expectations before it is used, and running those checks on the columns in your card tells you whether the agent will see clean values or nulls and duplicates simple data validation in SQL. Second, when the agent eventually does generate SQL, run it against a read-only account with a statement timeout, so a misread join cannot lock a production table while you debug creating a read-only Postgres user. A misread schema that turns into a full scan is a performance incident on top of a wrong answer.

If you want the schema card and the restatement step wired into a working pipeline rather than hand-rolled, a specialist can scope it with you: describe the pipeline you need.

The schema review does not replace query-level checks, it precedes them. Once the restatement matches, the agent's SQL can be tested the usual ways. Use EXPLAIN ANALYZE to see whether the plan does what the query's author intended, and remember that it actually executes the statement, so wrap writes in a transaction and roll back PostgreSQL EXPLAIN. Confirm that the row count the agent assumed matches reality rather than trusting a hand-written estimate counting rows after a SELECT. None of these steps will tell you that the agent joined the wrong table on purpose. Only the schema restatement does that.

Teams that run many agent questions over the same warehouse get compounding value from the card. Each wrong restatement produces one card fix, and the card improves for every future question against those tables. That is the opposite of prompt patching, where each fix is local to one question and invisible to the next.