Text-to-SQL Hallucination Patterns on Production Schemas

Text-to-SQL systems that look nearly perfect in the lab fall apart on real company databases, and this piece is about why that happens and what actually fixes it. On Spider 2.0, a benchmark built from real-world enterprise schemas that average over a thousand columns, o1-preview's accuracy drops from 91.2% to 21.3%. What changed is the schema: it stopped looking like a tidy research dataset and started looking like an actual production warehouse.

BEAVER makes the same point even more starkly. Sourced from real enterprise data warehouses, it drops GPT-4o's end-to-end accuracy to close to 0%. A floor near zero.

What makes this worse than a simple failure rate is how the failures show up. The queries that fall into this gap don't throw errors. They run cleanly, they come back with rows shaped like a correct answer, but they're wrong. Nothing in the output flags the problem. A downstream system checking for valid SQL syntax sees nothing wrong. If a business user looks at a clean table of numbers, they see nothing wrong either.

The instinct is to blame the model. That instinct is misplaced. LLM-powered analytics is bottlenecked on the availability of structured business context, not on language understanding. Swapping in a smarter model doesn't fix a problem that was never about reasoning ability. The real lever is what the model is given to work with: how the schema is organized, labeled, and explained before a single query ever reaches it.

The three hallucination classes that account for production failures

Production text-to-SQL failures aren't one undifferentiated mess. They sort into three distinct types, and mixing them up leads teams to apply the wrong fix to the wrong problem.

Schema-based hallucinations happen when the model invents tables or columns that don't exist. Logic-based hallucinations are different: the SQL is syntactically correct and points at real tables and real columns, but the logic inside it doesn't match what the user actually asked for. Content-based hallucinations are a third category again: the query's structure is fine, but the values it filters on, like a string in a WHERE clause, are fabricated or don't match what's actually in the data.

Of the three, logic-based hallucinations do the most damage when they happen. If a query is syntactically valid but semantically wrong, it slides misinformation straight into a business decision with no warning sign anywhere in the output.

But in terms of sheer volume, schema-based errors dominate. If you test four state-of-the-art systems on Spider and BIRD, schema errors make up over 80% of execution failures. So the class that happens most often isn't the class that does the most damage per occurrence. That distinction matters: a team chasing the highest-frequency bug and a team chasing the highest-stakes bug are solving two different problems, and you need to solve both.

Schema grounding failure in wide, abbreviated, opaque production tables

Schema grounding is the highest-volume failure class, and it fails for a structural reason, not an intelligence reason. The model is working with context that simply doesn't contain the information it needs.

A business user asks for "revenue." The warehouse stores it as amt_net_rev_lcl. The user has no way to know that churn data lives in a lifecycle table three joins away, or that a familiar dashboard metric quietly excludes certain accounts by default. None of that is written down anywhere the model can see it. Left without explicit schema information, LLMs fall back on patterns learned during training, and those patterns rarely match a specific company's naming conventions. The model invents plausible-sounding column names that don't exist in that warehouse.

Uber ran into exactly this building QueryGPT. Some individual tables there have 200+ columns, and stuffing all of that into a prompt caused token-overflow failures. The fix wasn't a bigger context window or a different model. It was a dedicated Column Prune Agent, built specifically to manage which columns even make it into context on very wide tables.

The research backs up what Uber found operationally. Schema-First Retrieval's results on BIRD show that retrieved catalog context roughly halves SQL execution errors compared to dumping the full schema into the prompt. Same models, same questions, different context strategy. What goes into the prompt determines grounding quality more than which model reads it.

Ambiguous column names across tables causing logic errors that execute cleanly

Schema grounding failures and column-ambiguity failures both involve the schema, but they break differently. When grounding fails, the model usually reaches for something that isn't there. When ambiguity hits, the model finds something that exists in three different places, picks the wrong one, and tells no one.

Picture a warehouse with three tables, and each one has a column simply named status. Nothing in the schema tells the model which one applies to the question being asked. It guesses. The guess doesn't show up anywhere in the output; the query just runs, and the number it returns is wrong.

This is the exact failure mode the Amb-Spider benchmark was built to test: how well a system holds up against lexical-schema ambiguity from homonymy, synonymy, and inconsistent naming conventions. These aren't edge cases dreamed up for a paper. You find these conditions in almost any production warehouse that has grown organically over a few years.

DexterSQL's answer is a deep schema explorator that does this analysis offline, before any user question arrives, and produces disambiguation notes that travel alongside the schema from then on.

And it isn't a problem that gets solved by throwing a bigger model at it. In a live case study, 11 tables spanned 6 business domains with deliberately opaque naming, and the LLM in question managed only 10% accuracy. The ambiguity was baked into the schema itself, and no amount of reasoning power could undo that from the outside.

Missing business semantics in the schema causing wrong metrics and aggregations

Schema grounding fails when the right object doesn't exist in context. But when ambiguity fails, the right object exists; you just can't tell it apart from its lookalikes. Missing business semantics is a third, deeper problem: the model finds the right table and the right column, and still gets the answer wrong, because the schema never told it how the business actually defines the thing being asked about.

LLMs end up inferring business logic, metric definitions, aggregation rules, join exclusions, that simply isn't written anywhere in the schema. A wrong answer and a hallucinated answer, in this case, are often the same event seen from two angles.

A benchmark from Cube put a number on how much this matters. Adding a 4 KB hand-authored markdown document, describing a dataset's measures, conventions, and disambiguation rules, improved accuracy by a substantial margin across all three frontier models tested (Claude Opus 4.7, Claude Sonnet 4.6, and GPT-5.4). The document did.

BIRD found something structurally similar years earlier. Appending a single sentence of "external knowledge evidence" to each question raised GPT-4's test accuracy from 34.88% to 54.89%. That jump is almost the same size as the gap between human experts who work without that evidence and those who work with it.

The catalog as the right retrieval target for schema context

Most of the failures described so far trace back to one habit: pasting the raw schema into the prompt and hoping the model sorts it out. Treating schema selection as a retrieval problem aimed at the catalog rather than the warehouse itself fixes this habit, which is the proximate cause of most grounding and semantic failures.

Schema-First Retrieval's approach indexes five typed catalog objects, tables, columns, metrics, relationships, and query history, each using its own text template, because each object answers a different kind of question. Tables anchor what domain a query lives in. Columns ground the specific predicates a query needs. Metrics translate business language into something queryable. Relationships protect join paths from guesswork. Query history carries the memory of what's worked before.

That last piece turns out to matter more than it might sound like it should. On SEDE, where past query history is available, using it as a retrieval signal raises table recall@5 from 52.1% to 92.3%. It's closer to a different system entirely, and you get there by treating old queries as first-class evidence rather than throwaway logs.

Cross-encoder reranking adds 11.1 points at column recall@10 over vector search alone, and it's the same multi-stage pattern document retrieval systems have used for years, now applied to schema objects instead of text passages.

One more piece belongs outside the model entirely: policy enforcement. If you ask a model to remember and respect access rules through prompting alone, people have already bypassed that in practice, so you can never rely on it as the actual security boundary.

What a semantic layer adds beyond catalog retrieval

A semantic layer doesn't make the model smarter. It changes the question the model is being asked to answer, and that shift is what suppresses the dominant class of text-to-SQL errors. So instead of inferring raw SQL against a pile of opaque tables, the model decomposes a question into governed metrics and dimensions that already carry agreed-upon definitions.

The honest limitation here deserves real weight, not a footnote. If a question falls outside what's been modeled, a semantic layer just fails silently. So the semantic layer trades coverage for reliability: strong for governed reporting metrics that get asked about repeatedly, weaker for one-off investigative questions that have never been modeled before.

The two approaches aren't really competing for the same job, though. The direction most systems are heading is convergence: text-to-SQL querying the semantic layer's API. What looked like a choice between two architectures turns into a layered pipeline, where the semantic layer handles governed questions and text-to-SQL handles the open-ended edges, each doing the part it's actually suited for.

Multi-agent pipelines distributing the schema-linking problem across specialized components

No single LLM call reliably handles schema linking, query generation, and self-correction all at once on a large schema. The most reliable production systems split the work across multiple agents, with each one handling one narrow step of the pipeline.

Uber's QueryGPT is the clearest named example. It runs an Intent agent, a Table agent, and a Column Prune agent, each as a separate stage. Splitting the pruning step out into its own agent solves a problem that a single combined prompt couldn't.

That architecture didn't appear fully formed. QueryGPT took more than 20 iterations to go from a May 2023 hackathon prototype to something running in production. Upgrading the model underneath it didn't close the distance between a working demo and a system you can trust with real queries. It was closed by engineering work on schema context and validation, iteration after iteration.

DexterSQL's research offers a second, independent validation of the same decomposition principle. DexterSQL also has a database-agnostic rule creator that mines recurring model failures offline and turns them into correction rules for future queries, so systematic mistakes become explicit, reusable rules, and you no longer just hope the model stops repeating them on its own.

Why hallucination detection alone cannot serve as the production safety backstop

Catching hallucinations after a query has already been generated is useful, and it belongs in a production pipeline. It just can't be the only thing standing between a model and a wrong answer reaching a user.

Detection frameworks built on the schema/logic typology described earlier can catch 69 to 83% of hallucinations after the fact. NL2SQL-Bugs, the first benchmark built specifically to detect semantic errors in NL2SQL translation, found that even state-of-the-art LLMs only reach 75.16% detection accuracy across 9 error categories and 31 subcategories. Detection itself is still an unsolved problem; it's not a finished layer you can deploy.

The failure mode that matters most is also the one detection struggles hardest to catch: a query that runs without error, returns something that looks plausible, and quietly answers the wrong question through a bad join path, a missing filter, or a wrong aggregation.

Prompt injection through database content itself is a related threat. Better schema context and validation prevent more problems than catching them after the fact ever can.

A production-grade text-to-SQL pipeline with schema context as the primary lever

Pulling all of this together, a production text-to-SQL pipeline that doesn't quietly hand users wrong answers is built in five stages, and every one of them is a different way of giving the model structured business context it wouldn't otherwise have.

• Stage 1, catalog retrieval before generation: embed typed catalog objects, tables, columns, metrics, relationships, and query history, using object-specific templates. Apply cross-encoder reranking. Enforce access-control gates deterministically at the retrieval layer, never inside the prompt.
• Stage 2, schema disambiguation offline: analyze the data distributions of ambiguous columns before any query arrives, then produce disambiguation notes that travel with the schema context into every prompt afterward, fixing the column-collision failure class at its source.
• Stage 3, a semantic layer for governed metrics: encode metric definitions, aggregation conventions, and join exclusions explicitly, so the model's job shrinks to decomposing a question rather than inventing business logic; accept the coverage tradeoff this involves and scope it accordingly.
• Stage 4, multi-agent generation with agentic correction: split query generation across specialized agents; include at least one correction pass checked against live execution feedback; apply rule-based correction for the recurring failure patterns captured offline.
• Stage 5, validation before results reach users: parse the generated SQL, bind it to catalog metadata, apply policy rules, score query risk, and show users the generated SQL itself alongside the results. Raw output from an LLM should never reach a non-technical user without that review step in between.

Every one of these five stages comes back to the same engineering principle the research keeps landing on: LLM-powered analytics is bottlenecked on the availability of structured business context, not on model capability, and each stage above is simply a different mechanism for supplying that context before a wrong answer ever has the chance to look right.