How We Keep an LLM From Inventing a Column Name

Ask a language model for "total revenue by campaign last month" and, with no knowledge of your schema, it will write something confident:

SELECT campaign_name, SUM(revenue) AS total_revenue
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)
GROUP BY campaign_name;

Reasonable-looking, and wrong in four ways at once. Your table is sales_orders. There is no revenue column — there is net_total. There is no campaign_name on an order at all. And order_date is created_at, a timestamp, not a date.

This is not a model being bad. It is a model doing exactly what it was asked with information it did not have. The fix is four layers of engineering, and none of them is "use a better model."

Why it happens

A language model generates the most plausible continuation given its context. Trained on a great deal of public SQL, its prior for an orders table is order_id, customer_id, order_date, revenue, status — because that is what most published example schemas look like.

If your actual schema is not in the context window, the model does not know it is guessing. There is no internal signal distinguishing "I recall this column exists" from "this is the most likely column name for a table like this." Both produce the same confident output.

The four layers

Layer 1: Retrieval

Fetch the real schema from the warehouse and put it in context. BigQuery's table metadata gives you name, type, mode, and — critically — description:

Layer 2: Constraint

More schema is not better. Two hundred tables in context creates three problems: the relevant one competes for attention with 199 others, the cost per question rises with every token, and long contexts degrade instruction-following.

Selection strategies, in ascending order of effort:

• Curated allowlist — expose only the semantic schema, not raw tables. Crude, extremely effective, and it doubles as a semantic layer.
• Keyword and lexical matching — question mentions "campaign", so include tables whose names or columns contain campaign-ish terms.
• Embedding retrieval — embed table descriptions, retrieve the top N by similarity to the question. Scales to hundreds of tables.
• Two-pass selection — a first cheap call picks the tables from a list of names and one-line descriptions; a second call gets full schemas for only those.

The two-pass approach is usually the best cost-to-quality trade above about fifty tables: the first pass is short and cheap, and the second gets a small, highly relevant context.

Layer 3: Validation

This is the layer that turns a probabilistic system into a safe one, and it is remarkably cheap.

A dry run parses and plans the query against the real schema without executing it. It returns either an error naming the exact problem, or the byte estimate. It costs nothing and takes under a second.

POST /bigquery/v2/projects/{projectId}/queries
{ "query": "<generated sql>", "dryRun": true, "useLegacySql": false }

Two things come back that matter:

• Any schema error — Unrecognized name: revenue at [1:24]. The query never ran, nothing was billed, and you have a precise error.
• totalBytesProcessed — what it would cost. Which lets you enforce a ceiling before execution rather than discovering it on an invoice.

DataLens runs this on every generated query before execution, and passes maximumBytesBilled on the real run as a second belt: even if the estimate were somehow wrong, the query fails rather than billing beyond the cap.

Layer 4: Repair

A validation error is an unusually good correction signal, because it is specific: it names the offending identifier and its position.

Capping retries matters. A loop that retries five times burns tokens working around a question that cannot be answered from the available tables. After two failures, the honest response is "I could not find a column for X — did you mean one of these?", which is more useful than a third guess.

The failure this does not catch

Everything above ensures the SQL is valid. None of it ensures the SQL is right.

Two mitigations, and they are the ones that matter most:

1. Column descriptions that say which column not to use. gross_total described as "includes tax and shipping — use net_total for revenue reporting" moves the choice from a guess to a read.
2. A semantic layer that removes the choice entirely, by exposing net_revenue and not exposing gross_total.

Frequently asked questions

Does a bigger model fix this?

It reduces the rate and does not change the shape. A larger model with no schema in context still guesses; a smaller model with the right schema, a dry run and a repair loop is more reliable than a larger one without them. The architecture matters more than the model here.

Should I fine-tune on my schema?

Almost never worth it. Schemas change and a fine-tune goes stale immediately, whereas retrieval is always current. Fine-tuning can help with house SQL style or dialect quirks; for schema knowledge, retrieval wins on both accuracy and maintenance.

How much schema is too much?

Watch two things: token cost per question, and whether the model starts picking deprecated or near-duplicate tables. The second symptom appears before the first becomes painful, and it is the one that produces wrong answers rather than expensive ones.

What if a dry run is not available?

Most engines offer something equivalent — EXPLAIN in Postgres, EXPLAIN or a LIMIT 0 execution elsewhere. If truly nothing is available, parse the generated SQL and check every referenced identifier against your cached schema before running. Less thorough, and far better than nothing.

Does this slow things down?

A dry run is sub-second and the repair loop only fires on failure. The added latency on a successful query is negligible, and on a failing one it converts an error the user would have seen into a corrected query they never knew about.

The summary

• Hallucinated columns are a retrieval failure, not a reasoning failure. The model was asked to write SQL against a schema it could not see.
• Retrieve real schema — names, types, modes, descriptions, partition columns — from the warehouse.
• Constrain it. Too much schema causes wrong-table selection, which is subtler and worse than an invalid column.
• Validate with a dry run before executing. Free, sub-second, catches every invalid identifier, and returns the byte estimate so you can cap cost before you pay it.
• Repair with the literal error text, capped at two attempts, then ask the human.
• None of this catches a valid but wrong query. Column descriptions and a semantic layer address that; showing the SQL is what makes it catchable at all.