Text-to-SQL in Production: Why “Chat With Your Data” Breaks Quietly, and What Makes It Reliable
Last updated: July 2026
The demo is always the same, and it always works. Someone types “show me our top ten customers by revenue last quarter, excluding trial accounts,” and a clean, syntactically perfect SQL query appears in a second, runs, and returns a tidy table. The room nods. The data bottleneck is over. Anyone can ask anything now.
Three weeks later a stakeholder asks a slightly different question, gets a confident answer, and drops the number into a board deck. It is wrong. Not obviously wrong, because the query ran without error and the figure looked reasonable, but wrong in the way that only surfaces when finance reconciles the quarter and the two numbers do not match. Nobody misread a chart. The system did exactly what it was asked. The question was just ambiguous in a way the model resolved incorrectly and silently. This is the failure mode that makes text-to-SQL dangerous in production, and it is the opposite of the one the demo prepares you for.
This guide is for teams being asked to ship “ask your data anything” on top of a real warehouse. It covers why the gap between demo and production is so wide, the specific ways these systems break, what the benchmark numbers actually mean, and the architecture that separates a party trick from something a data team can put its name on.
The demo-to-production gap
Every major platform is selling this now. Snowflake Cortex Analyst, Microsoft Fabric Copilot, Databricks Genie, and a long list of startups all promise natural language that becomes correct SQL, and the marketing routinely cites accuracy in the 85 to 95 percent range. Those numbers are real. They just come from the wrong test.
Most of the impressive figures trace back to Spider 1.0, an academic benchmark of clean schemas with ten to twenty tables and simple business logic. When the same task is measured against enterprise conditions, the floor falls out. Spider 2.0, built from 632 real workflow problems on databases that often exceed a thousand columns across Snowflake and BigQuery, dropped a leading model to 21.3 percent execution accuracy, against 91.2 percent on Spider 1.0 and 73 percent on the harder BIRD benchmark. Some enterprise evaluations put real-world accuracy in the single digits to mid-teens once production schemas are involved. The takeaway is not that the models are bad. It is that a high benchmark score measures the easy part and says almost nothing about whether a system will survive contact with your warehouse.
Why it breaks
The reasons are structural, not cosmetic, and they show up in the same places on almost every real deployment.
Metric ambiguity. Words like revenue, active customer, and margin mean different things to different teams, and the warehouse does not tell the model which definition to use. Faced with three columns that could plausibly be “revenue,” the model picks one and commits, with no signal that a choice was even made.
Hidden joins and schema traps. Production schemas are full of bridge tables, soft deletes, effective-dated dimensions, and multi-hop relationships that plain schema introspection never exposes. The model sees column names, not the tribal knowledge that a status of 7 means refunded or that you must filter out internal test accounts before counting anything.
Business rule exceptions. Every mature company has rules that live in people’s heads and a few dbt models, not in the table definitions. An LLM reading raw schema has no way to know them and will confidently produce answers that ignore them.
Schema evolution. Even a system that works can quietly stop working. When upstream tables are added, split, or merged, execution accuracy can drop by as much as 24 points, and the same question starts returning different answers on the changed schema without anyone touching the AI layer.
Underneath all of these sits one property that makes the whole category treacherous. Because most systems generate fresh SQL against the schema on every question, there is nothing to check the answer against. The query executes, the number looks plausible, and the only way to know whether it is right is for someone who can read SQL to trace the logic by hand, which is exactly the work the tool was supposed to eliminate.
What the benchmarks do not test
Execution accuracy, the standard metric, counts a query correct when its output matches a single gold answer. That framing bakes in an assumption that does not hold in the real world: that each question has one correct SQL answer. Enterprise questions are frequently ambiguous, and two defensible interpretations produce two different queries, neither of which is a bug.
Public benchmarks also do not test adversarial input, cost, or drift. A model can top a leaderboard and still be wide open to a prompt that coaxes it into dumping a table it should never touch. It can be accurate and ruinously expensive at the same time. And it can pass on the day you measure it, then degrade as your schema changes. Treating a Spider score as a readiness signal is like judging a car by its performance on an empty test track. The problem, as several practitioners have put it, is an architecture problem rather than a modeling problem. A better model helps at the margin. It does not close the gap on its own.
The cost and security dimensions people forget
Two failure modes rarely make it into the demo and routinely make it into the incident review.
The first is cost. Correct is not the same as efficient. A natural language interface will happily generate a query that scans a year of raw events across a wide table, and in a consumption-priced warehouse a single careless query can cost hundreds of dollars. Multiply that by a few hundred curious users and you have a line item nobody budgeted for.
The second is security. Wiring plain English directly to SQL execution creates a path from an untrusted prompt to your data, which means prompt injection and data exfiltration stop being theoretical. This is part of why the data engineer’s job is changing. The role is shifting from writing queries toward designing the semantic layer, monitoring generated query patterns, and governing against injection and exfiltration. The person who used to answer the questions now builds and polices the system that answers them.
What actually makes it reliable
None of this means text-to-SQL is a dead end. It means the naive version, pointing a model at raw tables and hoping, is the version that fails. The deployments that reach usable reliability share a common shape.
Start by refusing to expose the whole warehouse. The systems that reach the 70 to 85 percent range tend to surface only five to ten curated views and force the model to query predefined metrics rather than write free SQL against raw tables. Shrinking the surface removes most of the ambiguity before the model ever sees the question.
Route through governed definitions. This is where a semantic layer earns its place, because it makes answers deterministic: the same question maps to the same curated dataset and returns the same query and the same number every time, which also gives you something to verify against. The alternative, regenerating SQL from scratch on every ask, is what makes verification impossible.
Add a check on the generation itself. A common pattern is a generator and a critic, where one model writes the query and a second reviews it, sometimes alongside multiple candidate queries scored for consistency. Salesforce’s production agent reportedly climbed from roughly 50 percent to around 80 percent by generating ten candidate queries per prompt and scoring them, though the same source is candid that majority voting has real limits and is not a substitute for governed context.
Then evaluate on your own data. A leaderboard tells you nothing about your schema. The teams that succeed build a test set of real questions from their own business, measure against it continuously, and treat that suite as the actual bar for shipping.
The last piece is verifying the answer, not just the SQL. Even a correct query does not tell you whether the pattern it surfaces is meaningful or noise, and a plausible wrong number is the whole problem. One durable approach is to separate computation from interpretation: run the statistics with purpose-built algorithms first, then hand the model only the validated findings to explain. QuantumLayers builds its insights engine around this split, computing significance and effect sizes deterministically so every figure the model reports can be reproduced rather than generated and hoped over. It is the difference between an answer that sounds right and one you can check.
The role shift this forces
Put together, these practices change what a data team spends its time on. Less time writing individual queries, more time curating the semantic surface that the AI is allowed to see, defining metrics once so they cannot be interpreted three ways, governing access and cost, and maintaining the evaluation suite that decides whether the system is trustworthy this week. Text-to-SQL does not remove the data team from the loop. It moves the team upstream, from answering questions to owning the machine that answers them.
The bottom line
Text-to-SQL is genuinely useful and worth building. The trap is believing the demo, which tests the clean 20 percent of the problem and hides the 80 percent that is ambiguity, hidden business logic, cost, security, and drift. The benchmark gap between the academic track and the enterprise warehouse is not a rounding error. It is the whole story.
The teams that make this work stop treating it as a model you plug in and start treating it as an architecture you own. Curate the surface, route every question through governed definitions, constrain and check the generation, evaluate on your own schema, and verify the answer rather than the syntax. Do that, and “ask your data anything” becomes something you can put in front of an executive. Skip it, and the number in the board deck will look reasonable right up until the day it does not.
Lurika is an independent publication covering data analytics. We are not owned by any analytics vendor.
