Quick summary:
Text-to-SQL hallucinations happen when a language model writes valid, executable SQL that answers the wrong question, usually because business rules such as “active accounts only” or “net of returns” never appear in the schema. Our team at ScriptsHub Technologies diagnosed this in a natural language SQL tool built for a distribution company’s executives, where revenue figures ran cleanly but did not reconcile with finance. The fix combined a governed semantic layer, schema-aware text-to-SQL RAG, pre-execution SQL validation, and answers that show their query. Silent wrong numbers fell from 23% of sampled queries to 1.5%. This article covers causes, validation code, results, and five production checks.
If your chat-with-your-data tool returns revenue figures that don’t match the finance report, runs every query without an error, and looked flawless in the demo, you are almost certainly looking at text-to-SQL hallucinations. These are the exact symptoms our team at ScriptsHub Technologies encountered when a distribution company asked us to explain why its new analytics assistant was quietly feeding executives the wrong numbers.
Why does a text-to-SQL tool return wrong numbers without errors?
A text-to-SQL tool returns wrong numbers without errors because syntactically valid SQL can still encode the wrong business logic, and the database has no way to know which logic you meant.
The company had deployed a natural language layer over a warehouse of several hundred tables so leaders could ask plain-English questions such as “what was net revenue by region last quarter?” without waiting on the BI team. The demos were impressive, and leadership rolled it out to more than a hundred business users.
Within weeks, a regional manager spotted a quarterly revenue figure materially higher than the finance report. Nothing had failed, and the manager had nearly pasted the number into a board deck. It was caught only because that manager happened to know the real figure. When we traced the generated query, it had dropped the “active accounts only” filter and double-counted revenue through a one-to-many join. The SQL was valid, and the answer was wrong.
What causes text-to-SQL hallucinations in enterprise data warehouses?
Text-to-SQL hallucinations in enterprise warehouses come from generation without grounding: the model picks tables and infers business rules from column names instead of from governed definitions.
Our week-long audit found the model, warehouse connection, and embeddings sound. The failure sat in mapping business questions onto a messy schema. In this company, “revenue” meant net revenue after returns, on active accounts only, on a non-standard fiscal calendar. None of that lived in the schema, so the model guessed.
Four failure modes accounted for nearly every wrong answer we traced.
Table 1: The four text-to-SQL failure modes and how to catch each one
Underneath all four sat over-generalization: the model falls back on training priors from public SQL, such as calendar quarters or a single revenue column, whenever nothing in its context overrides them. Users only saw the final number, so a confident fabrication looked like a grounded answer.
Figure 1: Text-to-SQL pipeline, governed vs. ungrounded generation. The governed path resolves business terms and validates SQL before execution; the ungrounded path guesses and returns whatever runs.
We covered a similar silent failure in LLM data reconciliation for silent data drift.
Why do text-to-SQL LLM benchmarks overstate production accuracy?
Benchmarks overstate production accuracy because they either use small, clean schemas or supply curated metadata and documentation, while most enterprise warehouses have thousands of columns and undocumented business rules.
On the original Spider 1.0 benchmark, GPT-4o scores 86.6%. On the Spider 2.0 enterprise benchmark, spanning BigQuery, Snowflake, and schemas above 3,000 columns, the same model solved only 10.1% of tasks at publication, and o1-preview 17.1%. The leaderboard linked from the official Spider 2.0 repository has since climbed sharply, with top agent systems above 90% on the Snowflake track, but they typically wrap the model in schema exploration and self-verification and work from well-prepared metadata and documentation that most production warehouses lack.
The BIRD benchmark paper found a similar gap on real-world values and business knowledge, with ChatGPT at 40.08% execution accuracy against a 92.96% human baseline. Top systems on the BIRD leaderboard, linked from the official BIRD repository, have improved sharply but still trail that human figure, and they receive a per-question evidence hint your production users will never provide. Given the business rule, models do far better. Forced to infer it, they fail.
How do you prevent text-to-SQL hallucinations with a semantic layer?
You prevent text-to-SQL hallucinations by giving the model governed metric definitions to resolve against, so “revenue” maps to one approved calculation instead of whatever the model infers.
In weeks one and two, we replaced raw schema guessing with a semantic layer. Net revenue, active account, and the fiscal calendar were encoded once as approved business logic, so a revenue question resolved to the governed metric. Encoding business definitions this way is the foundation of data readiness for AI. Power BI semantic models are a familiar example of the pattern, so Power BI teams should audit those measures, as covered in DAX measure optimization, before exposing them to a model. The same pattern works with semantic layers teams already run, such as the dbt Semantic Layer, LookML, or Cube, so nothing needs rebuilding.
We paired this with schema-aware retrieval, a text-to-SQL RAG pattern that pulls only the relevant tables and approved join paths into context for each question. That step alone removed the fan-out joins behind the original double-counting. Retrieval carries its own risks, covered in our guide to RAG pipeline failure modes. On a held-out set of 200 real executive questions from the previous quarter, grounded generation fixed 88% of the cases where the original tool had failed silently.
How do you validate LLM-generated SQL before running it?
Validate each text-to-SQL conversion by parsing the generated SQL in the target dialect, allowing only a single read-only SELECT, checking every table and column against a governed catalog, confirming required business filters are present, and dry-running the plan before execution.
In weeks three and four, we added a validation layer so every natural language SQL query was checked between generation and execution. Here is a simplified core check using the open-source sqlglot SQL parser and SQLite.
Why this works: The failing SQL parsed and ran, so no database error would ever flag it. When we fed it the revenue query that dropped the active-account filter, it returned
missing governed filter: is_activebefore a single row was read, and a hallucinated column name is rejected the same way. Write and stacked statements are refused. Still, run generated SQL under a read-only role.How to verify it worked: Replay your historical failures through the validator: every known wrong-number query should be blocked, and every analyst-approved query should pass. Keep those pairs as a versioned golden dataset and rerun it in CI whenever the schema, prompt, or model changes. In production, use your warehouse’s native planner in place of SQLite, such as PostgreSQL EXPLAIN plans or BigQuery dry-run queries. Avoid EXPLAIN ANALYZE, which executes the query.
Failed validation triggered one corrected attempt. If governed metrics still could not answer the question, the assistant replied: “I can’t answer this confidently from your governed data. Here is the closest query I can run. Please review it.” That fallback follows the principles of error handling for AI features. Every answer also shipped with the SQL it ran and a reconciliation against a known control total, so drift from issues like late-arriving data surfaced as a flagged mismatch instead of a quiet error.
If you can’t say how often your analytics assistant returns a silently wrong number, our engineers can measure it. Talk to ScriptsHub Technologies about a text-to-SQL accuracy audit.
Did grounding and validation fix the wrong numbers?
Yes. Sixty days after ScriptsHub Technologies implemented grounding and validation, analyst-verified accuracy on answered questions rose from 41% to 92%, and silent text-to-SQL hallucinations fell from 23% of sampled queries to 1.5%.
Figure 2: Text-to-SQL BI assistant before vs. after (60 days). Analyst-verified sample of 200 executive queries. Accuracy is measured on answered questions; lower is better for the wrong-number rate.
The 11% refusal rate is the metric to watch. Those are questions the tool now flags for human review instead of answering with a guess.
How to make a text-to-SQL tool production-ready: five checks
Before trusting a text-to-SQL tool with executive decisions, confirm it uses governed definitions, shows its SQL, refuses gracefully, validates before execution, and is monitored for reconciliation drift.
First, ask whether the tool answers from governed metric definitions, backed by real AI data governance, or guesses what “revenue” means. Unencoded logic gets inferred, often wrongly.
Second, check that every answer shows the exact SQL it ran so reviewers can audit it.
Third, test what happens when the schema cannot answer a question. A tool that always returns something must be fabricating some answers. Route high-risk questions, like board-deck figures, to human-in-the-loop review.
Fourth, confirm every text-to-SQL conversion is validated before results are trusted. Governed-filter checks catch silent errors. Catalog checks and a dry run catch the rest before execution.
Fifth, ask who would notice a silently wrong number. If the answer is “only the one person who knows the real figure,” add reconciliation monitoring against control totals.
What should teams deploying natural language SQL tools do next?
Treat semantic grounding and SQL validation with the same engineering discipline as the rest of your data platform, because the model will answer whether or not it understands your business.
Text-to-SQL hallucinations are a grounding and validation problem, not a language-model problem. Grounding plus validation turned the same model into a tool executives could rely on.
Without reconciliation monitoring, you cannot tell whether leadership is acting on correct answers or confident fabrications. ScriptsHub Technologies offers a complimentary AI Production Readiness Assessment, a 90-minute diagnostic of your tool’s silent error rate with a prioritized fix roadmap. Book your AI Production Readiness Assessment or email info@scriptshub.net.
Frequently asked questions
Q. How to convert text to SQL query?
Use a text-to-SQL LLM to convert the question into a SQL query, passing it the relevant schema, join paths, and metric definitions. Then validate the SQL against a governed catalog and dry-run it before execution.
Q. How to improve text-to-SQL accuracy?
Use text-to-SQL RAG to retrieve only relevant tables and approved joins, a semantic layer for business metrics, pre-execution validation, and a golden dataset rerun whenever the schema, prompt, or model changes.
Q. Does AI still hallucinate in 2026?
Yes. AI models still hallucinate in 2026 because they predict plausible text rather than verify facts. In analytics, text-to-SQL hallucinations persist unless answers are grounded in governed data and validated before execution.
Q. Does ChatGPT still hallucinate?
Yes. ChatGPT’s own interface warns that it can make mistakes. When Spider 2.0 launched, GPT-4o, then ChatGPT’s default model, solved only 10.1% of its enterprise tasks, versus 86.6% on Spider 1.0.
Q. Which LLM has the least hallucinations?
No single LLM has the least hallucinations on every task, and rankings shift with each release. For text-to-SQL, the BIRD benchmark shows that supplying business context sharply improves accuracy across the models it evaluated.
