Text-to-SQL Isn't the Hard Part — Schema Context Is

Ask a frontier model to write a query with a window function, a timezone conversion, and a lateral join, and it will get the syntax right on the first try. Ask it whether "revenue last month" means gross_total, net_total, or settled cash in stripe_transactions, and it guesses — confidently, and often wrong.

That gap is the whole story of text-to-SQL in production. Writing correct SQL syntax is the easy 20%. Knowing which of your tables holds the truth, what each near-duplicate column actually means, and how those tables join without duplicating rows — that is the part that determines whether the answer is right. And none of it is in the model. It all has to arrive as context.

Why SQL syntax was never the bottleneck

Language models are, at this point, extremely good at SQL as a language. They have read millions of queries. They know the dialect differences, the window-function framing clauses, the date-trunc idioms. Syntax is a pattern-completion problem, and pattern completion is what they do.

That matters because of an asymmetry: syntax errors are loud and semantic errors are silent. A query with a bad keyword fails at parse time, names the offending token, and costs one retry. A query that sums the wrong revenue column runs perfectly, returns a plausible number, and gets pasted into a board deck. The failure mode you should fear is not the one that errors — it is the one that executes.

This is also why "use a bigger model" is such a tempting and such an incomplete answer. A larger model writes more fluent SQL and still cannot see your schema. Fluency is not knowledge. The model that writes a beautiful query against order_total — a column that does not exist in your warehouse — has failed in exactly the way a better autocomplete cannot fix.

What "schema context" actually means in practice

"Give the model schema context" sounds like one thing. In practice it is five distinct pieces of information, and most setups provide two of them and wonder why answers drift.

The remaining two are metric definitions ("active customer means ordered in the trailing 90 days") and operational metadata (which column is partitioned, how fresh the table is, which tables are deprecated). The metric layer is where a semantic layer earns its keep; the operational layer is what keeps a generated query cheap as well as correct, because a model told which column is partitioned will actually filter on it.

Notice the shape of this list: every item is your institutional knowledge, not model capability. Nobody else can supply it. Which is also the good news — it means the fix is documentation and plumbing your team controls, not waiting for the next model release.

A worked example: one word, five "revenues"

A growth lead asks: "What was revenue last month?" The warehouse holds all of these:

Here is what the wrong choice looks like in practice, next to the right one:

And columns are only half the ambiguity. The other half is joins: "revenue by campaign" requires joining orders to spend, and the sanctioned path runs through campaign_id on attributed line items — not a direct join on date, which fans out one order per campaign-day and inflates revenue several-fold. A model with the schema but without the relationship layer will pick the join that reads most naturally. The natural join is the dangerous one.

How to give a tool that context up front

If you take one operating procedure from this post, make it this sequence. Each step is cheap; each one removes a class of wrong answer that no later step can fully recover.

Step 4 deserves its pointer rather than a recap: retrieval, constrained context, dry-run validation and the repair loop are a solved engineering problem, and the question of where definitions should live long-term — prompt versus semantic layer — is settled in favour of the layer for anything that changes a decision. This post is the "why"; those two are the "how."

Start this week with step 2. An hour spent writing descriptions for your most-queried tables — gross_total marked "includes tax and shipping, do not use for revenue reporting," the deprecated table marked "deprecated, use orders_v2" — pays off on every question the tool answers from that day forward. It is the highest-return hour in AI analytics, and almost nobody spends it.

Frequently asked questions

Can't the model infer the right column from the names?

Sometimes, and "sometimes" is the problem. net_total versus gross_total is guessable about as often as it is not, and the failures are silent. Worse, some traps are unguessable in principle: a total column that stopped being maintained after a 2024 migration looks exactly like a live one. Inference works until it meets history, and every production warehouse has history.

How much schema context is too much?

When the model starts picking deprecated or near-duplicate tables, you have passed it. Past roughly fifty tables, unconstrained context degrades both accuracy and cost: the right table competes with dozens of wrong ones for attention, and every token of schema is billed on every question. Retrieve the relevant few per question rather than sending everything always.

Do I need to document the whole warehouse?

No. Document the twenty tables that answer 90% of questions, and within those, the columns where a wrong choice changes a number rather than erroring. Trap columns — near-duplicates with different business meanings — are the entire priority list. A raw events table nobody queries through the AI needs nothing.

Does good context replace a semantic layer?

It postpones one. Descriptions and mappings handle ambiguity while the metric count is small; once you have dozens of definitions changing across teams, you want them as version-controlled SQL rather than prose the model may or may not follow. Think of context as the treatment and the layer as the cure for the metrics that matter most.

Will a bigger model need less of this?

Less syntax repair, roughly the same context. Model scale improves reasoning over what it can see; it does not supply what it cannot see. A larger model with no schema still guesses, and guesses more fluently — which makes the wrong answer harder to spot, not less likely.

The summary

• SQL syntax was never the bottleneck. Models write fluent SQL; syntax errors are loud and cheap, semantic errors are silent and expensive.
• "Schema context" means five things: inventory, definitions, relationships, metric meanings, and operational metadata. Most setups supply the first and wonder about the rest.
• One English word routinely maps to several columns with a 20%+ spread between them. Validity is not correctness — no validator catches the wrong real column.
• Give tools context up front, in order: curated inventory, trap-column descriptions, synonym mapping, then constrain-validate-repair.
• The cheapest high-return step is an hour of column descriptions on your most-queried tables.