If you've spent any time close to a data warehouse, you already know the scariest bug isn't the one that throws an error. It's the one that returns a clean, confident, wrong number that nobody questions until a decision has already been made on top of it.
We're now generating SQL with LLMs at scale, and we've handed that exact failure mode a much bigger surface area. I want to walk through why this is a real problem, not a theoretical one, and share a small tool I built to catch a specific slice of it.
The failure that doesn't look like a failure
The pitch for text-to-SQL is great: a business user asks a question in plain English, a model writes the query, and they get an answer without waiting on the data team. And a lot of the time, it genuinely works.
The problem is the tail. Sometimes the generated query runs perfectly, no syntax error, no exception, no warning, and the result is simply incorrect. A join fans out and double-counts revenue. A filter is subtly wrong and quietly drops a third of the rows. A GROUP BY sits at the wrong grain. The query executes, the dashboard fills in, the number looks reasonable, and it ships.
Anyone who has debugged a "the numbers look off this week" ticket knows how much time these cost, and how late they're usually caught. The Omni Analytics team put it well in a piece called "Why text-to-SQL fails": because each query is generated from scratch, there's nothing to verify it against. Your only real check is to read the generated SQL and reason through it by hand, which is precisely the skill the tool was supposed to remove from the loop.
That's the crux. We've automated query writing without automating query verification.
This isn't a rounding error
It's worth grounding this in numbers, because "bad data" gets hand-waved a lot.
Gartner's often-cited estimate puts the cost of poor data quality at roughly $12.9 million per year for the average organization. And there are named, public cases: in 2022, Unity Software attributed a loss of over $110 million in revenue, and a far larger hit to its market cap, to ingesting bad data from a large customer that corrupted its models' outputs.
Neither of those is specifically a text-to-SQL story. But they're the same failure class: wrong data flowing through systems and producing wrong outputs that ran clean and reached a decision before anyone noticed. LLM-generated SQL doesn't introduce this problem, it accelerates it, by producing more queries, faster, with more surface-level confidence and less human review.
The approach: differential testing for SQL
The tool I built, difftest-sql, borrows an old idea from compiler and database testing: differential testing. If you can't easily prove an answer is right, generate a second, independent answer and check whether the two agree.
Concretely: for a given question, it produces a primary query and a structurally different control query that should compute the same result a different way. It runs both, compares the results, and returns one of three verdicts, agreement, disagreement, or "not independently verifiable."
The mental model is just double-entry bookkeeping applied to queries. You don't trust one derivation; you derive the answer two independent ways and reconcile.
Here it is catching a fan-out bug in the demo:
$ difftest "What is the total revenue?" --db demo.duckdb --offline
total_revenue
-------------
850.00
DISAGREEMENT: an independent control query disagrees — this answer may be unreliable.
row 0, column "total_revenue": primary=850.00 vs control=350.00 (abs gap 500, 83.33%)
-> exit 2
The primary joined orders to line items and summed, and the join inflated the total to 850. The control computed the same figure at the correct grain and got 350. The disagreement got flagged automatically, with the specific cell and the gap, no manual SQL review involved.
How it works, for the people who'll ask
A few design decisions did the real work here.
Decorrelation has to be structural, not cosmetic. A control query that's just the primary reworded buys you nothing, it'll make the same mistake. So the control is forced onto a genuinely different execution path (different join strategy, a subquery, a different aggregation grain). The bet is that two independent derivations rarely make the same mechanical error, so a real bug shows up as a disagreement.
Independence is checked on the query plan, not the SQL text. This is the part I'd flag in a design review. Two queries can read very differently and still optimize to the same plan, in which case they'll always agree and prove nothing. So the tool runs EXPLAIN, compares the optimized logical plans, and normalizes away cosmetic noise (cardinality estimates, output-column aliases) before deciding whether two queries are actually independent. Same plan, rejected.
Comparison is a contract, not ==. Result sets get their columns aligned by position (guarded by type-category checks), both sides sorted into a canonical order so row ordering can't cause a false mismatch, and values compared under a tolerance: 0.01% relative to the average of the two values, with an absolute floor near zero. NULL equals NULL; NULL versus a real value is a disagreement.
The verdict stays honest. Three outcomes, no fabricated confidence score. If there's no genuinely distinct query shape to compare against, it says "not independently verifiable" rather than pretending.
One small war story: I built a wildcard rule for all-NULL columns, then found that DuckDB never actually reports an all-NULL column as a NULL type, it reports INTEGER, so the branch couldn't fire on that engine. Instead of deleting it or quietly leaving a dead path, I documented it as deliberate, engine-portability code. Small thing, but it's the kind of assumption that bites you later if you don't write it down.
What it deliberately does not do
I'd rather be clear about the boundary than oversell it.
This detects disagreement. It does not prove correctness.
It reliably catches mechanical errors, fan-outs, double-counting, wrong grain, cases where two independent queries diverge. It does not catch semantic errors where the model misunderstands intent the same way in both queries. If "revenue" is supposed to exclude refunds and the model forgets that consistently across both derivations, they'll agree, and the tool will report agreement, because from its vantage point they genuinely do agree.
Agreement is a confidence signal, not a guarantee. I designed the whole thing around that distinction, because a verification tool that overstates what it verifies is worse than none at all.
Where it stands
It's an early, v0.1 project, not something I've run in production. It works entirely offline with no API key (clone it and it'll catch the seeded bug in a single command), it's covered by a full test suite, and every design decision is written down.
I built it because this is a real, expensive, and oddly under-discussed problem, and I wanted a concrete, principled attempt at catching it rather than another "just review the SQL" recommendation.
I think the core idea holds up. If you work on text-to-SQL or data reliability and you see a hole in it, or a direction worth taking it, I'd genuinely like to hear it.
Top comments (0)