Why text-to-SQL fails on enterprise databases: 8 modes
Text-to-SQL scores above 90 percent on tidy benchmarks; the best published agent solved 17 percent of real warehouse tasks. 8 failure modes and fixes.
Text-to-SQL fails on real enterprise databases because generating valid SQL is easy and resolving business semantics is hard: a model can write flawless SQL against the wrong table, the wrong join key, and the wrong definition of revenue. The resulting number looks entirely plausible, runs without error, and is indistinguishable from a correct one in a chart. The published benchmark gap is large, and it is a gap in semantics rather than syntax. Fixing it is an engineering problem of definitions and guardrails, not a prompting problem.
What is the text-to-SQL benchmark cliff?
The text-to-SQL benchmark cliff is the sharp drop in accuracy when a system moves from academic benchmark databases to real enterprise warehouses. The three most-cited benchmarks measure progressively harder conditions, and the reported numbers fall accordingly.
| Benchmark | What it tests | Reported result |
|---|---|---|
| Spider 1.0 | Small cross-domain databases, readable column names, mostly single-query answers | Leading systems above 90 percent execution accuracy |
| BIRD | Larger, dirtier databases with messy values and external knowledge | Around 70 percent when Spider 2.0 was published, higher since, still short of the human expert baseline near 93 percent |
| Spider 2.0 | Real enterprise warehouse schemas, multi-step analytical workflows | The code agent framework in the published paper solved 17 percent of tasks |
Leaderboard scores move, and top results on the harder benchmarks have risen since publication. The structural point does not move: the difficulty is not the SQL dialect, it is everything the schema does not tell you. Treat any vendor accuracy claim quoting a Spider 1.0-style number as a statement about clean schemas, not about yours.
What differs between a benchmark schema and a production warehouse?
A benchmark schema is documentation-complete and small; a production warehouse is neither. These differences, not model quality, explain most of the accuracy drop.
| Property | Benchmark database | Real enterprise warehouse |
|---|---|---|
| Column names | Readable (customer_name) |
Cryptic (cust_nm_1, flg_03, amt_b) |
| Table count | Tens | Hundreds to thousands |
| Column count | Fits in a prompt | Often over a thousand, beyond what fits usefully in context |
| Duplicate tables | None | Multiple near-identical copies from ETL history |
| Business rules | Stated in the question | Undocumented, held by analysts |
| Metric definitions | Implicit and obvious | Contested (gross vs net, which exclusions apply) |
| Correctness check | Gold query available | No gold answer exists in advance |
The last row matters most. On a benchmark, a wrong answer is detected automatically. In your company, nobody holds the gold answer, which is exactly why wrong answers survive.
What are the main failure modes of LLM SQL generation?
Text-to-SQL failures in production fall into eight repeatable categories. Naming each one is useful because each has a different fix.
- Cryptic and ambiguous schema. The model must guess that
dt_pstis posting date and thatstat_cd = 7means cancelled. Operational column names were written for the application, not for a reader. - Wrong table among near-duplicates. A warehouse accumulates
orders,orders_v2,orders_stg, and a materialised copy. All four return numbers. Only one is the reporting source of truth. - Join errors and fan-out. Joining a one-to-many relationship without aggregating first multiplies a revenue figure. The query succeeds; the total is inflated.
- Missing implicit filters. Real tables contain test accounts, internal orders, soft-deleted rows, and reversals. Every analyst knows to exclude them. The schema says so nowhere.
- Undefined business metrics. “Active customer”, “net revenue”, and “churn” have specific, negotiated definitions per company. A model asked to compute them invents a reasonable definition, which is a different thing from the correct one.
- Time and fiscal logic. “Last quarter” means one thing on a calendar year and another on an April to March financial year. Timezone handling on event timestamps adds a second layer of quiet error.
- Context window versus schema size. A schema with thousands of columns will not fit usefully in a prompt. Whatever you retrieve selectively, you can retrieve wrongly, and a model cannot ask about a table it was never shown.
- Non-determinism. The same question asked twice can produce two queries and two numbers. Sampling behaviour and model updates make exact repeatability hard to guarantee, and inconsistent numbers destroy trust faster than a single wrong one.
Why is a wrong query worse than a query that errors?
A wrong query is worse than a query that errors because wrong SQL usually still runs. A syntax error is a visible bug someone fixes in a minute. A query that omits a refund exclusion returns a revenue figure that is quietly too high, renders as a perfectly normal bar chart, and gets forwarded into a board deck.
Three properties make this worse than an ordinary software bug:
- No error signal. Nothing in the stack knows the answer was wrong, so no alert fires and no test fails.
- Plausibility. The number is in the right order of magnitude, which is precisely why nobody questions it.
- Compounding. Once a figure is quoted, later analyses anchor on it, and the correction becomes an embarrassment rather than a routine fix.
Assume that any natural-language analytics system without a correctness guarantee will produce wrong numbers nobody notices, and design as though that has already happened.
What actually fixes text-to-SQL accuracy?
What fixes text-to-SQL accuracy is reducing how much the model has to infer. Every measure below moves a decision from the model’s guess to a definition a human approved.
- A governed semantic layer. Define metrics, joins, grain, and filters once, centrally, in a model the language model selects from rather than reconstructs. This is the highest-leverage fix because it closes failure modes 2 through 6 by construction. Our explainer on natural-language analytics without SQL covers how that architecture differs from raw generation.
- Schema curation, not schema dumping. Expose a small, documented, business-facing subset with readable names and enumerated code meanings. Hide staging tables and deprecated copies entirely.
- Scoped data marts. Point the system at a purpose-built mart per domain (finance, sales, operations) instead of the whole warehouse. Fewer tables means fewer wrong tables.
- Query validation before execution. Parse the generated query and check it: allowlisted tables only, no cross joins, no unbounded scans, mandatory tenant and date predicates, read-only role. Reject rather than execute anything that fails.
- Security enforced in the query path. Row-level and column-level rules belong where the query is built, not in the chart, or a rephrased question becomes an access-control bypass.
- The generated query stays visible. Let a technical user inspect the query and the definitions behind any answer. Auditability is what lets a finance team sign off.
- Result-level sanity checks. Row counts, null rates, and period-over-period deltas that trip a warning on implausible deviation catch fan-out and missing-filter errors that pass every syntax check.
How do you measure text-to-SQL accuracy on your own data?
Measure text-to-SQL accuracy with a golden query set built from your own business questions, never from a public benchmark. The method mirrors how to evaluate RAG accuracy, applied to numbers instead of passages.
- Collect 100 to 300 real questions from the people who ask them, including awkward phrasings and questions your data cannot answer.
- Have an analyst write and approve the correct query and its result for each.
- Score execution accuracy: does the returned result set match the approved one? Exact SQL string matching is the wrong metric, because many correct queries exist.
- Score consistency: ask each question three times, plus twice reworded, and check the number does not move.
- Score permission correctness: run the same question as three roles and verify each sees only permitted rows and columns.
- Score refusal: for unanswerable questions, the correct behaviour is to say so, not to return a confident number.
A system that is 95 percent accurate on a public benchmark and 60 percent accurate on your golden set is a 60 percent system.
Where does raw text-to-SQL still belong?
Raw text-to-SQL belongs with engineers who read the query before trusting it: exploratory work, throwaway analysis on a sandbox copy, or scaffolding a first draft of a data model. It does not belong on the path between a non-technical user and a number that drives a decision.
Gist takes the other route. It answers plain-language questions with no SQL by reasoning over a governed semantic layer where metrics are defined once, enforces row-level and column-level security in the query path, and is locale-aware for Indian reporting conventions including rupees in lakh and crore and the April to March financial year. Gist connects to Postgres, ClickHouse, and DuckDB and is embeddable in your own product. If you need the semantic layer and guardrails built around an existing warehouse, the Teclops AI and workflow automation services team builds them inside your own infrastructure.
To query your data in plain language with definitions you control, see Gist or write to teclops.ai@gmail.com.
Frequently asked questions
Can a bigger model or fine-tuning fix text-to-SQL accuracy?
Not on its own. A larger or fine-tuned model writes better SQL, but it still cannot know that stat_cd = 7 means cancelled, that orders_v2 is the reporting table, or that your net revenue excludes intercompany sales, because none of that is written down in the schema. Fine-tuning helps with dialect and style; encoding definitions in a governed semantic layer is what removes the guessing.
What is a fan-out join error in generated SQL?
A fan-out join error happens when a query joins a one-to-many relationship and then aggregates, so each parent row is counted once per matching child row and the total is silently inflated. The query runs without error and the chart renders normally, which is why fan-out is one of the hardest text-to-SQL failures to catch by eye.
How many questions should a golden query set contain?
Aim for 100 to 300 real questions drawn from the people who actually ask them, covering awkward phrasings, permission-sensitive questions, and questions your data cannot answer. A set much smaller than that tends not to surface the long tail of joins, filters, and fiscal-period edge cases where natural-language analytics breaks.
Is it safe to let business users query a production warehouse in plain language?
It is safe when the system answers from a governed semantic layer with read-only access, allowlisted tables, and row- and column-level security enforced in the query path. It is not safe when a model writes raw SQL against the full production schema, because a plausible wrong number or a rephrased permission bypass will not raise any error.
Does Gist use raw text-to-SQL?
No. Gist answers plain-language questions with no SQL by reasoning over a governed semantic layer where metrics, joins, and time logic are defined once, and it enforces row- and column-level security in the query path. Gist connects to Postgres, ClickHouse, and DuckDB and is embeddable in your own product.