agent's SQL referenced a column that doesn't exist: schema grounding

TL;DR

Agents invent column names that sound right but dont exist. Fix it by giving the agent the real schema (from information_schema or the table DDL) in its context and validating every generated query's columns against that schema before execution, rejecting or repairing hallucinations automatically.

agent's SQL referenced a column that doesn't exist: schema grounding

Use this when

• Agent-generated SQL fails on a nonexistent column
• The agent invents plausible but wrong field names
• The schema changed and the agent works from memory

Not for this skill when

• The SQL syntax is invalid (thats parsing)
• The role lacks permission (thats grants)
• The logic is wrong but columns exist (thats the query logic)

Steps

1. Pull the real schema and put it in the agent's context:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'orders';

Expected output: the authoritative column list. This is ground truth; the agent's memory of the schema is not.

2. Instruct the agent to use only listed columns:

You may only reference these columns: [id, customer_id, total, created_at].
If you need another column, STOP and ask instead of guessing.

Expected output: the agent stops inventing names. Explicit permission to stop beats a confident hallucination.

3. Validate generated SQL against the schema before running it:

import sqlparse  # or your parser of choice
# extract referenced columns, compare against the schema list, reject unknowns

Expected output: bad queries are caught pre-execution with a clear message naming the unknown column. The validator is cheap; the failed query against prod is not.

4. Refresh the schema snapshot on a schedule or on failure:

# on "column does not exist", refresh the schema cache and retry once

Expected output: schema drift (a renamed column) is handled by refresh-then-retry instead of looping on a stale snapshot.

Variant phrasings

agent uses the right column name but wrong table

Same grounding problem at the table level. Provide the table list too, not just columns.

works for one database, fails on another

Schemas differ between environments. Ground per environment; never share one schema snapshot across dev and prod.

Why it happens

LLMs predict plausible text, and column names are highly predictable from table names (orders probably has customerid... or is it userid?). Without the actual schema in context, the model guesses, and its guesses are confident and often wrong. Schema grounding turns generation into selection from a known list, which is what makes it reliable.

Edge cases

• Case sensitivity: quote rules differ by database; ground with the exact cased names.
• Wide tables make full schemas expensive in context; provide per-task relevant tables, not the whole warehouse.
• Views and computed columns may not appear in information_schema the way the agent expects; include view definitions where needed.

Provenance

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