Text to SQL in production: why the query is the easy part

A demo schema hides the problem

Every text-to-SQL demo looks good because the demo schema was built to be read. Tables are called orders and customers, there are few of them, and every column means what its name says. An enterprise warehouse has thousands of tables, three that plausibly hold revenue, and an amount column whose meaning depends on a status code nobody documented.

Spider 2.0, a benchmark built from real enterprise workflows, shows the size of that gap. Its authors report that the agent they tested solved 21.3 percent of its tasks, against 91.2 percent for the same agent framework on the earlier and simpler Spider 1.0. Nothing got worse about the model between those two results. The tasks got closer to real work.

I’d start by sorting the failures by where they come from, because each kind needs a different fix.

Five ways a correct-looking query is wrong

Most production failures run without an error and return a number. Nothing in the output says to look again.

• Wrong tableThe model picks a staging copy or a retired table over the one finance reconciles to. Both exist, both have an amount column, and only one is right.
• Join fan-outJoining orders to line items and then summing the order total counts each order once per line. The total comes back inflated, and it looks plausible.
• Unstated conventionRevenue here excludes test accounts and returns, and the fiscal year starts in February. None of that is in the schema, so a model cannot infer it.
• Literal mismatchThe question says Northeast and the column stores NE. The filter matches nothing, and an empty result gets reported as zero.
• Wrong population for this personA query run under a shared service account returns rows the person asking is not allowed to see. The arithmetic is correct and the answer should never have been shown.

Model proposes, code decides

A production design separates what the model is good at from what must be guaranteed. The model reads context and proposes a query. Everything that has to hold every time is ordinary code that doesn’t depend on the model behaving well.

Generated SQL deserves the treatment you give a form post from the internet, since that’s what it is. Parse it, check it against rules, and only then let it near a database.

Give the model context it can search

Pasting a whole schema into a prompt stops working at a few hundred tables. Retrieval works better, and what you retrieve matters more than the retriever. Column descriptions help, but the highest-value context is the kind a schema never contains: which joins analysts use, which values a column holds, and what your organization means by a word.

Query history is the best source most teams ignore. It records which tables people trust and how they join them. Mine it with care, because a person’s past query can contain literals they were allowed to see and the next reader isn’t. Keep the shape of the query, and be strict about keeping values.

column:      orders.net_amount
meaning:     Invoiced amount after discounts, before tax
currency:    Column orders.currency_code; do not sum across currencies
safe joins:  customers on customer_id (many to one)
unsafe:      order_lines on order_id (fans out; aggregate lines first)
excludes:    Test accounts, where customers.is_internal is true

Check every query in code

Parse the generated SQL into a tree with a real parser, then check the tree. String matching on keywords loses to the first comment or quoted identifier, and I wouldn’t ship it. The essential rules are small: statements must be read-only, tables must be in scope, and aggregates over a join must not cross a one-to-many boundary without pre-aggregation.

Run these checks before execution and return the failure to the model as a message it can act on. Most fan-out mistakes correct themselves within one retry when the feedback names the join. A database’s own read-only role is still needed underneath, since a parser is one layer and defense has to hold when a layer is wrong. The OWASP guidance on LLM applications treats unchecked model output as an injection risk for this reason.

Run it as the person asking

A shared service account is the most common shortcut in this category and the most expensive one. It forces you to rebuild every row policy, mask and sharing rule in the application layer, and they will drift from the source. Running as the user keeps one authority: Snowflake row access policies or Salesforce sharing decide what comes back, as they do everywhere else.

Buyers should expect a consequence. Two people can ask the same question and get different numbers, and they should. Any cached answer has to respect that too, so a cache key that omits identity is a data leak waiting for its second user.

Verify the answer, then measure the system

A query that passes every rule can still answer the wrong question. An independent second derivation catches much of this: work the answer out another way and compare. When the two disagree, say so to the reader. A wrong number nobody would have noticed becomes a visible warning, and the disagreement rate becomes one of the most useful signals you have.

Evaluate on your own questions, and grade on returned results, never on matching SQL text. Published benchmarks help with orientation and make weak proof. A 2026 audit of BIRD and Spider 2.0 reference answers found error rates above 50 percent in the subsets it examined, enough to change how systems ranked. Include questions the system should decline, and report how often it declines one it should have answered, or a system can score well by refusing.

How we apply this

We built Nexatron around this separation. The model explores through tools and proposes a query. Deterministic checks decide whether it runs, it runs with the person’s own sign-in to Snowflake or Salesforce, and an independent check compares the result. Each answer carries a trust label for where its definition came from, from Approved through Interpreted, plus the generated query and what was left out, so the reader can judge it.

For a build of your own, I’d begin with the query checks and identity. You can’t add them later without rewriting, and they’ll stay useful after you change models next year.