Why text-to-SQL fails on real data
Text-to-SQL works great in demos and struggles on real warehouses. The reason is not that language models are bad at SQL. They are good at it. The reason is that writing SQL is the easy part, and knowing what a question means against a specific, messy, undocumented database is the hard part. That is context the model does not have, so it guesses.
Here is what that looks like in practice, and why the failures are so easy to miss.
The failure is silent
Start with the worst property. When text-to-SQL gets it wrong, nothing breaks. The query runs. It returns a number. The number is formatted exactly like the correct one, and there is no error to warn you. Someone takes it into a meeting and makes a call on it.
This is different from most software failures, where wrong usually means broken. Benn Stancil put it well. You cannot verify an analytical answer the way you verify code, so a confident wrong answer sails straight through.
Metrics are ambiguous
“Revenue” is not a column. It is a definition, and often a contested one. Finance counts it one way, the growth team another, and neither is written in the schema. Ask an agent for revenue and it picks whichever column looks closest, which might be gross, net, booked, or recognized. It will not tell you which. This is the single biggest source of quietly wrong answers, and it is why Omni argues a governed definition is the only real fix.
Join paths are traps
Real schemas have many ways to connect two tables, and only some are correct. Join to customers through the wrong key and you double-count. Miss a filter that everyone on the data team knows to apply and your total is off. The model cannot know these unwritten rules, so it takes the path that looks reasonable and is sometimes wrong.
The schema is not enough context
Give the model just the table and column names and it is working almost blind. Names are cryptic, the same concept lives in several tables, and there is no signal for which table is canonical. This is why the teams who shipped real text-to-SQL, like Uber and Pinterest, did not just prompt a model. They built retrieval systems to feed it the right context for each question, because the schema alone did not carry enough meaning.
It shows up in the numbers
This is measurable. dbt Labs benchmarked text-to-SQL against a semantic layer and found raw text-to-SQL trailing by ten to fifteen points, not because the queries were broken but because they were plausibly wrong. On the harder, enterprise-grade Spider 2.0 benchmark, even strong models solve only a fraction of real-world tasks. The gap is not going away with a bigger model.
What actually fixes it
The fix is not a better prompt. It is context.
- A semantic layer or metric definitions, so the agent selects “revenue” instead of guessing at a column.
- Documentation and rules the agent can read, like which table is canonical, which filters always apply, and how each metric is calculated.
- An evaluation suite, so you measure accuracy on real questions instead of trusting the demo.
- A correction loop, so a failed or suspicious query gets retried and checked instead of returned.
This is the work of context engineering, and it is what separates an analytics agent you can trust from a text box that writes SQL. Open-source agents like nao are built around it, treating the context as the thing you maintain rather than hoping the model guesses right.
Text-to-SQL is not useless. It is a good tool for exploring data nobody has modeled. But for the questions that matter, on the data that matters, the raw version fails quietly, and the fix is to give the agent the meaning the schema leaves out. There is more on this in why agents give wrong answers.
Why does text-to-SQL fail on enterprise data?
Because writing SQL is easy but knowing what a question means against a messy, undocumented warehouse is hard. Metrics like “revenue” are not columns, join paths have unwritten rules, and the schema alone does not say which table is canonical. The model fills those gaps with a guess, and the query still returns a number, so the error is silent.
Is text-to-SQL accurate enough to trust?
For exploring unmodeled data, yes, with a human in the loop. For metrics people make decisions on, not on its own. Benchmarks show raw text-to-SQL trailing a governed semantic layer by ten to fifteen points, and the failures are plausible wrong answers rather than obvious errors, which is what makes them dangerous.
How do you make text-to-SQL more reliable?
Give the agent context it cannot get from the schema, meaning defined metrics or a semantic layer, documentation of which tables and filters are canonical, an evaluation suite to measure accuracy, and a correction loop to catch bad queries. This practice is called context engineering, and it is the main thing that separates a reliable agent from a demo.