Text-to-SQL fails on your warehouse, and it is not the model's fault
Benchmarks are run on tidy schemas with clean names. Your warehouse has thirteen years of history in it. Here is where the accuracy actually goes, and what to do instead.
Text-to-SQL benchmarks look encouraging. Then somebody runs the same model against a real warehouse and accuracy falls off a cliff, and the conclusion drawn is that the model is not ready.
The model is fine. The benchmark schema is not your schema.
What benchmarks assume that you do not have#
Public text-to-SQL evaluations run against databases that are, by construction, tidy. A few dozen tables. Names that mean what they say. One table per concept. No deprecated columns, no market-specific quirks, no _v2 suffixes, no columns that meant one thing before 2022 and another after.
Your warehouse has all of those, because it has been alive for years and a business happened to it.
The specific things that break it:
Ambiguous entities. There are four things called customer. One is the CRM record, one is the borrower on a contract, one is a deduplicated identity and one is a legacy view somebody is afraid to drop. The model picks one. It picks confidently.
Undocumented status codes. A column holds integers. Three of them mean active for one product and two of them mean active for another. Nothing in the schema says so.
Implicit filters. Every human who queries this table knows to exclude test accounts and internal staff. It is not written anywhere. The model includes them.
Date semantics. Booking date, value date, processing date and the date the row landed in the warehouse are four different columns and four different answers. "Last month" resolves differently against each.
Grain mistakes. A join that fans out and silently doubles a sum is the single most common wrong answer we see, and it is the hardest for a non-technical user to spot, because the number is the right order of magnitude.
None of these are reasoning failures. They are missing-information failures. The model is doing inference because inference is all you gave it.
The accuracy number that should worry you#
In our experience, unaided text-to-SQL against a mature warehouse lands somewhere in the region of two-thirds correct on real business questions. Point estimates vary enormously by schema, so treat that as a shape rather than a statistic.
Two-thirds is the worst possible number.
At forty percent, nobody trusts it and no harm is done. At ninety-nine percent, you can build a process around it. At two-thirds, it works often enough that people stop checking, and the third that is wrong flows into a board pack.
The failure mode is not that the assistant is wrong. It is that it is wrong in a way that looks exactly like being right.
What to do instead#
The fix is not a better prompt, and it is not fine-tuning on your schema. It is to narrow what the model has to decide.
Stop asking it to compose SQL. Ask it to select a metric. If approval_rate exists as a defined metric with dimensions for market, product and month, then "what was approval in Spain last month" becomes a parameter-filling problem rather than a query-authoring problem. Parameter filling is something these models are extremely good at.
In practice that means the dbt Semantic Layer, or an equivalent metric API, exposed as tools — increasingly through MCP so any assistant can reach it without seeing your schema at all.
Where you genuinely need open SQL, constrain the surface. Expose a small set of wide, denormalised, well-named marts rather than the whole warehouse. Fewer joins is fewer chances to fan out. Put the implicit filters into the mart so they stop being implicit.
Make the descriptions carry the business logic. Every table, column and metric gets a sentence that says what it excludes. This is the cheapest accuracy improvement available and almost nobody does it, because it looks like documentation.
Return the query with the answer. Always. An analyst can spot a bad join in three seconds. Hiding the SQL to make the product feel magical removes the only cheap check you have.
Give it a way to refuse. An assistant that says "I do not have a defined metric for that" is more valuable than one that always produces something. Refusal needs to be an explicit, rewarded behaviour or the model will guess, because guessing looks more helpful.
Score it against real questions. Fifty questions your business actually asks, with known-correct answers, run on every change to the prompt, the model or the data model. Without that you are not managing accuracy, you are hoping.
What good looks like#
A working setup, in our experience, has this shape:
| Layer | What it does | Why it matters |
|---|---|---|
| Metric layer | Defined, tested metrics with dimensions | Removes the guessing |
| Tool interface | Assistant selects a metric and fills parameters | Removes the SQL authoring |
| Row-level security | Carried through from the semantic model | Stops it becoming a data leak |
| Grounding check | Answer verified against what was retrieved | Stops unsupported claims shipping |
| Golden set | Real questions, scored on every change | Tells you when it breaks |
| Trace | Question, tools called, parameters, result | Lets you explain it later |
None of that is about the model. You could swap the model tomorrow and the whole thing would still work, probably slightly better. That is the point — and it is the test of whether you have built a system or a demo.
The one-line version#
If your assistant has to reconstruct your business logic from table names, it will. Confidently, and about a third of the time, wrongly.
Give it the logic instead.
Is this the shape of your problem?
We build agentic AI and analytics pods — analytics engineers, AI engineers and decision scientists, with a lead who has done this for thirteen years. Start with a two-week Readiness Check.