1h 11m read

An Agent's Bill Is Paid for Re-Reading, Not Thinking: Preventing Hallucinations in Natural-Language-to-SQL

Why text-to-SQL agents spend tokens re-reading context instead of catching wrong queries, and seven ordered controls — semantic layer, constrained output, deterministic validation, calibrated verifier, abstention, and a gold set on your own schema — for preventing LLM hallucinations in natural-language-to-SQL.

An Agent's Bill Is Paid for Re-Reading, Not Thinking: Preventing Hallucinations in Natural-Language-to-SQL

A text-to-SQL agent's invoice is not dominated by the reasoning that would catch a wrong query. It is dominated by re-sending context: schema, history, tool output, and failed attempts, turn after turn. The expensive part of the loop is the model reading the same material again. The part that would actually prevent a hallucinated query — deciding whether the SQL expresses the question that was asked — is comparatively rare and comparatively cheap.

That asymmetry explains why the usual remedies underperform. More context and more retries both buy additional re-reading. They do not supply the information the model was missing, and they do not make a wrong query look wrong. Preventing hallucination in natural-language-to-SQL is a design problem about three things: what the model is asked to infer, what it is allowed to emit, and when it must decline. This article works through that problem in order: first what the failure actually is, then why the cost structure pushes spend toward re-reading, then seven controls ordered by leverage per dollar.

A note on evidence. Benchmark results below are cited with the dataset, system, and metric they were measured on, because they do not transfer to your warehouse. Vendor pricing is cited as published rates, which change. Where an example is constructed to illustrate a mechanism rather than drawn from a published result, it is labelled as illustrative.

What a Text-to-SQL Hallucination Actually Is

A hallucinated query is not simply one that fails. It is one that answers a different question than the user asked, or references objects that do not exist, or returns a correctly computed number at the wrong grain. The controls differ by layer, so the definition has to be sharp enough to assign them.

Schema-level hallucination. The query names a table or column that does not exist, or joins two tables on a path that does not relate them as assumed. The TA-SQL work classifies text-to-SQL hallucinations into schema-based and logic-based categories, describing schema-based hallucinations as inaccurately identifying schema structures, introducing unnecessary attributes, or failing to accurately represent or interpret database values (TA-SQL, Findings of ACL 2024). A separate error study of in-context-learning pipelines documents schema hallucination producing semantically plausible but non-existent table or column names, and table-column mismatches where a column exists in the database but not in the table the query attached it to (Understanding, Detecting, and Repairing Real-World ICL-Based Text-to-SQL Errors).

Semantic-level hallucination. The SQL parses, executes, and returns rows, but encodes the wrong business meaning: a filter that omits a required status, a grain that double-counts, a metric computed by a formula nobody approved, a status code read as one thing when the business uses it as two. This is the layer that survives a green query plan.

Presentation-level mismatch. The computation is right but the result is shaped differently from what the question implied — extra columns, a missing identifier, a different sort. This is a reporting defect rather than a correctness defect, and it matters mainly because a careless evaluation metric will score it as an error and hide real ones.

The distinction that carries the rest of the article is between execution and correctness. Execution accuracy asks whether the query ran and returned the gold result; it is the standard metric on BIRD and Spider. But a query can run and be wrong. A selective-prediction study over BIRD and Spider measured the signals commonly used to guess whether a query is correct and found that executability is essentially chance as a correctness predictor, because wrong queries often run successfully and return rows (What Predicts Correctness in Text-to-SQL?).

That finding sets the target. Schema-level errors are largely decidable by code, and the practices below handle them cheaply. Semantic errors are not decidable by code alone, which is why they require supplied definitions and, where definitions run out, a verifier that reasons about intent. A dashboard showing 100% execution success tells you almost nothing about either.

Why the Bill Goes to Re-Reading

The cost structure of a multi-turn agent is straightforward once stated. Each API call is billed separately, and every turn re-sends the conversation so far as input. Prompt caching reduces the unit price of the repeated prefix but does not change the shape of the bill: OpenAI's documentation describes caching as preserving the unchanged tokens at the beginning of a prompt so the model can reuse saved state, while it still processes any new input to generate a response (OpenAI prompt caching guide). The volume of context still grows with each turn; only its price per token falls.

The published rates make the discount concrete. Anthropic documents a cache hit at 10% of the standard input price, with the break-even at one cache read for the 5-minute cache duration and two cache reads for the 1-hour duration (Anthropic pricing). OpenAI's pricing page lists cached input at a fraction of standard input across its model tiers (OpenAI pricing). Both are published rates for specific models and cache durations; the ratio is not a universal constant, and it changes when providers revise pricing.

So caching makes re-reading cheap per token. It does not make re-reading informative. If the model's missing premise is that "active customer" means a subscription renewed within 30 days and excludes trial accounts, a second pass over the same schema cannot supply it. The model will read the same columns again and guess again, and the guess may be confidently wrong in the same way.

The evidence for where the missing information lives comes from a paired benchmark that held questions and data fixed and varied only the context. Three frontier models answered 100 questions over a retail warehouse twice: once with the schema alone, once with the schema plus a 4 KB hand-authored document of measures, conventions, and disambiguation rules. Adding the document improved accuracy by 17 to 23 percentage points across all three models. With the document the models scored 67.7–68.7% and were statistically indistinguishable; without it they scored 45.5–50.5% and were also indistinguishable. The authors attribute essentially all of the significant variance to the presence of the document, not to model choice within tier (Semantic Layers for Reliable LLM-Powered Data Analytics).

The design principle follows: spend tokens supplying semantics once, in a form that caches, rather than re-presenting schema repeatedly and hoping the model infers what was never written down.

Practice 1: Ground the Model in a Semantic Layer Before It Writes SQL

Why. The benchmark above isolates the dominant error class as business semantics the schema does not encode. Supplying them does not make the model more capable; it changes what the model is being asked to do, from inventing a definition to applying one.

How. Author a compact, versioned document that covers, at minimum: metric definitions with their formulas; the grain at which each metric is valid; canonical join paths between facts and dimensions; the meaning of status codes and enum values; and disambiguation rules for terms that appear in more than one sense. Render it into the system prompt ahead of the question. Keep it static across queries so it sits in the cached prefix rather than being rebuilt per request.

Failure mode. A team ships a schema-only agent, sees plausible SQL, and concludes the model is weak. The actual defect is that "active customer" was never defined anywhere the model could read. Swapping to a larger model will not fix a missing definition, and the paired benchmark suggests it will not even move the number much within a tier.

Illustrative example (constructed, not observed data). A user asks how many accounts have running contracts in a branch. The status column uses C for running contracts in good standing and D for running contracts where the client is in debt. Both are running. This is the disambiguation TA-SQL uses to motivate schema linking, and it is exactly the kind of mapping a schema does not carry. Without the mapping, WHERE status = 'C' is syntactically perfect and silently undercounts every account in debt. With the mapping in the semantic layer, the model has a reason to write status IN ('C','D').

Cost. The document adds input tokens. The semantic-layer authors note this is largely mitigable through prompt caching because the document is static across queries, and report that latency for the semantic-layer configuration stayed comparable to the raw configuration, often within 8 to 15 seconds (alphaXiv summary of the same benchmark). The document is a fixed cost paid once per cache window, not a per-question cost.

Practice 2: Constrain the Output Space, Not Just the Prompt

Why. Prompt instructions are advisory. A model told to return valid SQL can still emit a token sequence no parser accepts, and a grammar is enforced at each decoding step rather than hoped for at the end.

How. Constrain generation so that only syntactically admissible SQL can be produced. PICARD does this by rejecting inadmissible tokens at each decoding step using incremental parsing, and on Spider and CoSQL it turned fine-tuned T5 models with passable performance into state-of-the-art results (PICARD, EMNLP 2021). Modern serving stacks expose equivalent grammar-constrained decoding, and the OWASP AISVS output-format chapter documents the current engines and their overhead characteristics (AISVS C07-01).

Pair the grammar with a structured response schema that permits either a query or a typed abstention. The GROUND enterprise implementation constrains generation to a JSON response that is either a SQL query or one of ask_clarification, reject_undefined_metric, reject_unknown_table_or_metric, or reject_unsupported_dimension (GROUND, arXiv:2608.26157). The point of the enum is that declining becomes a representable output rather than a failure to produce one.

Failure mode. Relying on "please return valid SQL" in the prompt, then spending turns on parse-error retries. Each retry re-sends the full context, which is precisely the re-reading cost the title names — and a retry loop that never converges is the worst version of it, because it pays repeatedly for the same failure.

Example. A model that emits a JSON object containing a sql field is checked against the schema before anything runs. A model that emits prose, a markdown fence, or a truncated object is rejected by the response validator at zero database cost. The difference between the two designs is not model quality; it is whether the output space was bounded.

Practice 3: Validate Deterministically Before You Trust the Model's Own Review

Why. Some hallucinations are decidable by code, and code-level checks reproduce exactly across runs while model judgments drift as providers update snapshots. Spend the cheap, exact checks first.

How. Run a schema check that every referenced table and column exists in the physical schema, resolving CTE names and their derived columns and excluding them from the check so they are not flagged as unknown. Enforce row-level-security predicates server-side rather than trusting the prompt to include them. Execute against a read-only role, and use a cost proxy such as the row counts of tables the plan full-scans to catch queries that would be expensive before they run.

The GROUND implementation reports that its enforced governance metrics — filter and row-level security — are decided by code-level validation and reproduce exactly at 0.000 violations across runs, models, and machines, while judgment-dependent metrics such as recognizing an undefined metric vary run to run and as model snapshots change. That variance structure is the useful part: the properties you can check in code are the ones you can promise.

Failure mode. Treating successful execution as validation. The error study measured what rule-based repair can do: it detects 47.0% of incorrect queries with 95.9% precision, but successfully repairs only 23.1% of them, with 5.3% mis-repair, because rules lack understanding of the error (Understanding, Detecting, and Repairing Real-World ICL-Based Text-to-SQL Errors). Deterministic checks are excellent detectors and poor fixers. Use them to reject and route, not to rewrite.

Example. A query referencing dealer_profit_summary — a table that does not exist — is rejected by the schema check before any tokens are spent on a repair loop. The rejection is exact, reproducible, and costs nothing beyond the check itself.

Practice 4: Pay for a Logic-Aware Verifier, and Calibrate It

Why. The selective-prediction study measured the black-box signals teams reach for first — string self-consistency, structural self-consistency, execution self-consistency, executability — and found them clustered in a narrow 0.61–0.68 AUROC band, with executability at chance. A frozen GPT-4o verifier reached 0.770. A two-provider ensemble of GPT-4o and Claude reached 0.822, a gain of +0.052 over GPT-4o alone with a 95% confidence interval of [+0.032, +0.073], and was well calibrated at ECE 0.031 after cross-fit logistic calibration.

How. Add a verifier that reasons about whether the SQL expresses the computation the question asked for, rather than one that checks surface features. Prefer independent providers: the study found GPT-4o and Claude scores correlated at only r = 0.43, meaning they make different errors, which is why the ensemble beats either judge alone. Calibrate the verifier's score into an abstention threshold rather than applying a raw cutoff, so the threshold corresponds to a stated risk tolerance.

Failure mode. Sampling the same model five times and calling agreement confidence. The study found that once a strong verifier is available, the sampling-agreement signal contributes little: a cross-fit combination of GPT-4o with string self-consistency scored 0.754, below GPT-4o alone at 0.770. Self-consistency is weakest exactly where the model is confidently and unanimously wrong.

Example. A verifier weaker than the generator misses subtler errors. The study observed this with GPT-4o-mini as judge over a stronger GPT-4.1-mini generator, and concluded the judge should be at least as strong as the generator when the goal is universal correctness prediction. The practical rule: if you are paying for a verifier, do not under-buy it.

Practice 5: Make Abstention a First-Class, Cheaper Outcome

Why. A wrong query that executes costs more than a declined one. It produces a confident wrong number that propagates into a decision, and the repair attempts that follow are the expensive, low-yield path the error study documents. A decline, by contrast, ends the loop and routes the question to a human who can answer it.

How. Define the abstention taxonomy before the first query, and route each type to a specific user-facing action: clarify the ambiguous term, reject an undefined metric and list the approved alternatives, reject an unknown table or metric, reject an unsupported dimension. Then measure clarification accuracy as a first-class metric alongside execution accuracy. GROUND reports clarification accuracy as a distinct measure precisely because declining correctly is a capability, not a failure — its systems scored between 0.83 and 0.94 depending on model, and the variance grew as the model weakened.

Failure mode. Optimizing only for execution accuracy. That metric rewards a system for emitting something rather than nothing, so a model that guesses at an undefined metric scores better than one that asks. The incentive is backwards, and the metric will show it.

Example. A question names "net revenue retention" but the semantic layer defines no such metric. The correct response is reject_undefined_metric with the closest defined alternatives — gross retention, expansion rate — not a plausible SUM over a column whose name resembles the term. The GROUND authors report that even their strongest model fabricated a year-over-year growth formula rather than refusing an undefined metric, reproducibly across all three runs. Abstention is a capability that has to be built and measured, not assumed.

Practice 6: Evaluate on Your Schema, Not on Spider

Why. Benchmark scores do not transfer. Spider 2.0 reports that the highest-performing traditional method, DAIL-SQL + GPT-4o, reaches 86.6% execution accuracy on Spider 1.0 and 57.4% on BIRD, but only 5.68% on Spider 2.0-lite; even the most advanced model tested achieved a 21.3% success rate on the full benchmark (Spider 2.0). The gap is not a model deficiency to be waited out. It reflects real enterprise schemas, dialects, and multi-step workflows that public benchmarks do not represent.

DAIL-SQL + GPT-4o execution accuracy across benchmarks Horizontal bars showing reported execution accuracy for one method on three benchmarks: 86.6% on Spider 1.0, 57.4% on BIRD, and 5.68% on Spider 2.0-lite. The drop reflects the added complexity of enterprise-scale workflow tasks. DAIL-SQL + GPT-4o execution accuracy across benchmarks. horizontal bar data: Spider 1.0 86.6; BIRD 57.4; Spider 2.0-lite 5.68.Source: Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows 2024. DAIL-SQL + GPT-4o executionaccuracy across benchmarks Spider 1.0 86.6 BIRD 57.4 Spider 2.0-lite 5.68 Source: Spider 2.0: Evaluating Language Models onReal-World Enterprise Text-to-SQL Workflows (2024)

Execution accuracy reported by the Spider 2.0 authors for GPT-4o-based methods: DAIL-SQL + GPT-4o scores 86.6% on Spider 1.0, 57.4% on BIRD, and 5.68% on Spider 2.0-lite. Spider 2.0 tasks are enterprise-scale workflows with real schemas, dialects, and documentation, so the comparison illustrates benchmark difficulty, not a ranking of models.

How. Build a gold set from your own questions with expected results authored independently of the generator — otherwise you are measuring the generator against itself. Score value-based accuracy at the correct grain rather than exact column-set match, and report hallucination rates per category — schema, metric, join, grain, filter, security — as separate numbers. Spider 2.0 itself flags that table-based evaluation can penalize predictions for returning extra columns when the question did not specify them, which is why the value-based notion exists.

Failure mode. Quoting a public benchmark number in a readiness review. It tells the reader what a system did on someone else's schema, which is close to no information about yours.

Example. A team scores 0.95 value-based on its own gold set but 0.79 strict. The gap is grouped queries omitting an identifier column — a presentation difference, not a computational error. The two numbers together tell the team where to invest; either alone misleads.

Practice 7: Keep the Controls From Becoming the Cost Sink

Why. Every control added — verifier calls, repair loops, re-retrieval — is itself billed. The error study found existing repair attempts deliver limited correctness improvement at high computational overhead with many mis-repairs. A control stack that is not ordered by cost per error caught becomes the cost sink it was meant to prevent.

How. Order controls by cost per error caught. Deterministic checks come first because they are near-free and exact. The cached semantic layer comes second because it is static and amortized across queries. The verifier comes third because it is one extra model call per query. Generative repair comes last because it is the most expensive and the least reliable. Instrument token cost and turn count per question as reported metrics, as GROUND does, so the cost of each control is visible rather than inferred.

Failure mode. A repair loop that re-sends the full schema and history on every attempt. That converts a correctness problem into a bill that grows with failure — and failed runs are exactly the ones that take more turns and carry more accumulated context.

Example. A rule-based detector flags a suspicious empty result set and escalates to a constrained LLM repairer only when rules yield multiple plausible candidates — the MapleDoctor pattern — rather than invoking the model on every query. The model is called where judgment is needed and nowhere else.

A Production Readiness Checklist

Each item below is a pass/fail the team can check against its own system. The thresholds are yours to set against your risk tolerance; the list only fixes what should be measured.

  • Semantic layer authored and versioned. Metric definitions, grain, canonical joins, status-code meanings, and disambiguation rules live in a versioned document rendered into the system prompt.

  • Output constrained. Generation is bounded by a grammar or structured schema that permits either a query or a typed abstention, and a non-conforming response is rejected before execution.

  • Deterministic checks run server-side. Schema existence, join validity, and row-level-security predicates are enforced by code on a read-only connection, not by prompt instruction.

  • Verifier calibrated on your gold set. A logic-aware verifier from an independent provider has a measured AUROC on your own questions, and its score maps to an abstention threshold rather than a raw cutoff.

  • Abstention measured separately. Clarification accuracy is reported alongside execution accuracy, and a decline is scored as a correct outcome where one was expected.

  • Hallucination rates reported per category. Schema, metric, join, grain, filter, and security rates appear as separate numbers, not one aggregate.

  • Cost instrumented. Prompt and completion tokens, turn count, and validation-retry revisions are logged per question.

  • Gold set authored independently. Expected results were written by someone other than the generator, or generated deterministically from the semantic layer rather than from the model.

FAQ

Does prompt caching make the semantic layer free? No. It reduces the unit price of re-reading. Anthropic documents a cache hit at 10% of standard input price, with break-even at one cache read for the 5-minute duration and two for the 1-hour duration; the ratio differs by model and provider. You still pay for the first write and for every read, just less per read.

Does a bigger model remove the need for controls? Not according to the paired benchmark. Within a tier, the three models tested were statistically indistinguishable both with and without the semantic-layer document; the document, not the model, carried the variance. A larger model may help at the margin, but it does not supply a definition that was never written.

Is execution success a reasonable proxy for correctness? No. The selective-prediction study found executability essentially at chance as a correctness signal, because wrong queries often run and return rows.

Is schema linking still worth doing? The evidence is mixed and context-dependent. TA-SQL reports a 6.42-point absolute drop in execution accuracy on BIRD's development set when its schema-linking stage is removed. A pipeline that forgoes schema linking when the schema fits the context window ranked first on BIRD at 71.83% (The Death of Schema Linking?). The answer depends on schema size relative to the context window and on whether filtering risks dropping a required column.

Where should a team start? With the semantic layer and the gold set. Both are prerequisites for measuring anything else, and neither depends on which model you eventually choose.

Where This Leaves the Bill

The bill is paid for re-reading because agents re-send context each turn, and the missing information is usually semantic rather than contextual. That combination is why more context and more retries underperform: they buy additional passes over material the model has already seen, when what it lacks is a definition, a join path, or a status-code mapping that was never in the schema.

The fix follows from the diagnosis. Supply semantics once in a form that caches. Constrain what can be emitted so that invalid SQL and typed abstentions are the only representable outputs. Validate by code what code can decide, and pay a calibrated verifier only for the judgment code cannot make. Measure abstention as a capability, and evaluate on your own schema rather than on someone else's benchmark.

The honest limit is that the cited benchmarks measure specific systems on specific datasets — BIRD, Spider, a retail warehouse, a synthetic governed benchmark — and none of them is your warehouse. Your schema, question distribution, and risk tolerance set the actual thresholds. What transfers is the ordering: semantics before retries, constraints before prompts, deterministic checks before model judgments, and a verifier before a repair loop.

That ordering is also the practical way to evaluate a text-to-SQL platform. A natural-language analytics agent is only as reliable as the governed semantics underneath it and the validation path in front of it, which makes the semantic layer, the abstention behavior, and the enforcement of permissions the things to test first — before speed, and well before any benchmark headline.