data agent hallucinated a table that does not exist: schema-ground every query

TL;DR

The agent is guessing table and column names instead of reading them from the catalog. Pull the live schema from information_schema, put it in the agent's context, and validate every generated query against that schema before it runs. Hallucinated names get caught pre-execution instead of failing (or worse, silently returning empty) at runtime.

Symptom

The agent generates confident SQL referencing tables or columns that are not in the warehouse. Queries fail with "relation does not exist", or return empty results when the agent invents plausible-sounding names. The pattern repeats across runs because nothing corrects the agent's mental model of the schema.

Steps

1. Pull the live schema from the catalog. This is ground truth; the agent must never guess names:

SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

Expected output: the real tables and columns. Save this as the allowlist every query is checked against.

2. Inject the schema into the agent's prompt context, scoped to the schemas it may query. Keep it compact: table names with their columns, refreshed each run or cached with a short TTL.

Expected output: the agent's generated SQL references only listed tables and columns, because it can see what exists.

3. Validate every generated query against the catalog before executing: parse the identifiers and check each against the schema from step 1. Reject or repair on the first unknown identifier instead of running it.

Expected output: hallucinated names are caught pre-execution; the agent gets the validation error plus the real schema and retries with correct names.

4. Log every rejected query with the hallucinated identifier. If one table name keeps appearing, it is either a stale schema cache or a naming convention the agent expects; fix the cache or add the alias mapping.

Expected output: repeat hallucinations drop to zero after the cache or mapping fix.

When to use

• Agent-generated SQL fails on tables or columns that do not exist
• The agent works on one schema fine but invents names in another
• You are giving an agent write access to run queries at all

When not to use

• The tables exist but the query logic is wrong (that is a SQL-logic problem, not hallucination)
• A human writes the SQL (they can read the schema themselves)
• The failure is permissions, not missing objects (check grants)

Variant phrasings

agent keeps using an old table name after a rename

The schema cache is stale, or the old name is baked into the prompt. Refresh the cache and grep the prompt for the old name.

agent invents columns that sound right but do not exist

Same fix. Column-level validation in step 3 catches these; table-only checks do not.

Why it happens

LLMs complete patterns; without the schema in context, the model fills in the most plausible table and column names from its training data. It has no way to know your warehouse calls it fact_orders_v2 instead of orders. Schema grounding replaces guessing with lookup.

Edge cases

• Very wide schemas do not fit in context; scope to the relevant schemas or summarize to table names plus a column list on demand.
• Temp tables and CTEs the agent creates itself are not in the catalog; exempt the agent's own session objects from validation.
• Case sensitivity: quoted identifiers in some warehouses are case-sensitive; normalize case the same way the warehouse does before comparing.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_3lymFTLd1Kb66DvNU5WA