How to keep an LLM from inventing a column name (four layers, zero model upgrades)

Ask a language model for "average order value by city last quarter" without showing it your schema, and it will write something confident:

SELECT city, AVG(order_value) AS aov
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)
GROUP BY city;

Your table is actually shop_orders. There's no order_value — the field is net_amount. City isn't on the order at all; it's billing_city on customers. And order_date doesn't exist — there's a placed_at timestamp.

That isn't a bad model. It's a model doing exactly what it was asked with information it never had. The fix is four layers of plain engineering, and "upgrade the model" isn't one of them.

Why it happens

A language model writes the most plausible continuation of its context. It has seen a huge amount of public SQL, so its idea of an orders table is order_id, customer_id, order_date, order_value, status — what most tutorial schemas look like.

If your schema isn't in the context window, the model has no signal that it's guessing. "I know this column exists" and "this is the likeliest name for a column like this" feel the same from the inside, and produce the same confident output.

The four layers

1. Retrieve — put the real schema in contextPull actual table and column names, types and descriptions from the warehouse. The model can't invent what it's been handed.
2. Constrain — only what's relevantTen relevant tables beat two hundred. Too much schema hurts as much as too little, just differently.
3. Validate — dry-run before executingA dry run checks the SQL against the real schema without running it, fails on any bad column, and costs nothing.
4. Repair — feed the error back, retry, stopA validation error names the exact bad identifier. One or two retries fix most of what's left; after that, ask the person.

Layer 1: Retrieve

Get the schema from the warehouse itself. In BigQuery, INFORMATION_SCHEMA has everything you need — including the column descriptions that carry real business knowledge:

SELECT
  c.table_name,
  c.column_name,
  c.data_type,
  c.is_nullable,
  f.description,                            -- where the business knowledge lives
  c.is_partitioning_column                  -- tells the model which date to filter on
FROM `project.shop.INFORMATION_SCHEMA.COLUMNS` c
LEFT JOIN `project.shop.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS` f
  ON  f.table_name  = c.table_name
  AND f.column_name = c.column_name
ORDER BY c.table_name, c.ordinal_position;

Layer 2: Constrain

Sending all 200 tables creates three problems: the right one competes for attention with 199 others, every question costs more tokens, and long contexts make instructions easier to ignore. Ways to narrow it, from least to most effort:

Too much schema fails more subtly than too little. The query is valid and runs — against orders_old instead of shop_orders, because both were offered and nothing said which was current. A validator can't catch that. Only constraint can.

Layer 3: Validate

This is the layer that makes a probabilistic system safe, and it's almost free. A BigQuery dry run parses and plans the query without executing it:

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

You get back one of two useful things:

• A precise error — Unrecognized name: order_value at [1:18]. Nothing ran, nothing was billed.
• The bytes it would scan — so you can refuse anything over a cost ceiling before it runs. For extra safety, set maximumBytesBilled on the real run so it fails rather than overspends.

Layer 4: Repair

A validation error is an unusually good correction signal because it's specific — it names the identifier and its position, and BigQuery often suggests the nearest real column.

1. The dry run fails with a named column"Unrecognized name: order_value. Did you mean net_amount?"
2. Send the literal error plus the relevant schema backDon't paraphrase it — the exact text is the useful part.
3. Regenerate and validate againSame gate, no special cases.
4. Stop after two tries and askIf it still fails, the question is usually ambiguous, and a third guess won't fix that. "I couldn't find a column for X — did you mean one of these?" is the honest answer.

What this doesn't catch

All four layers make sure the SQL is valid. None of them make sure it's right.

Two things address this: column descriptions that say which column not to use, and curated views that expose net_revenue and simply don't expose order_total. And the SQL has to be visible, so a person can catch what's left.

Quick gut-check

Questions people ask