Text-to-SQL Against a Real Schema: Where the Demo Database Stops Telling the Truth
Six of twelve on the clean database. Five of twelve on the same data in legacy dress.
We built a small sales dataset (seven customers, six products, eleven orders, fourteen order lines, eleven invoices) and stored it twice. The first copy is the demo version: table and column names that say what they mean, dates in one format, statuses stored as words. The second copy holds exactly the same business data under the rules real databases accumulate over the years: abbreviated names, status columns holding undocumented integer codes, dates in three formats, a deleted order still sitting in the table, product attributes pushed into a key-value table, and one customer's credit hold written only in a notes field.
Then we asked Anthropic's Claude Sonnet 4.5 the same twelve questions against each copy. Same model, same prompt, the schema definition as its only context, one attempt per question, no retries. The questions were ordinary business questions: revenue by category, outstanding invoice money, which products come from Germany. Gold answers were written before the run, and we verified first that the gold queries return identical results on both copies, so any gap would be the schema's doing rather than the data's. A question counted as correct only when the executed result matched the gold result exactly.
The clean schema failed in exactly one way
Every easy question passed on the clean schema: counts, totals, a category lookup. The six misses, three medium and three hard, share a single cause. The model had to guess the literal values stored in the status columns, and it guessed the way a person writes: 'Completed' with a capital C, 'partially paid' where the stored value is just 'partial'. The database holds 'completed', lowercase.
Those guesses returned nothing. Not wrong numbers: empty result sets and null totals, because a filter that matches no rows produces silence, and a sum over silence is null. The joins were right. The aggregations were right. Revenue by category came back with the correct shape and zero rows inside it.
There was no consistency to lean on, either. The same model wrote 'completed' correctly in one question and 'Completed' in another. A schema definition tells a model which columns exist. It says nothing about the values inside them, and in this run the values were the whole game.
The legacy schema failed in four different ways
Four misses trace to a deleted order nobody filtered out. One order in the legacy table carries a deleted flag, and it is worth 1,400. The model ignored the flag four times: a total inflated by exactly 1,400, a count of 10 completed orders instead of 9, quarterly revenue of 5,160 instead of 3,760, and a revenue-by-category table where Electronics alone absorbed the phantom 1,400. Each answer was precisely, confidently wrong.
One miss was an attribute that was not a column. Asked which products are made in Germany, the model found the attribute table and joined it, then filtered on the attribute name 'country'. The stored name is 'origin_country'. Zero rows.
One miss was a fact that lives in a sentence. Asked for the completed-order value of customers who are not on credit hold, the model returned 4,050 instead of 3,450. On the legacy schema the credit hold exists only as text in a notes field, three words, 'CREDIT HOLD', attached to one customer. No column carries it, and the model never looked at the notes. A system reading only the schema cannot know which customer is on hold, because the schema never says. That is not a prompting problem.
One miss was not SQL at all. Asked how much invoiced money is still outstanding, the model replied with a paragraph of reasoning and a half-finished code block. No executable query. One answer in twenty-four came back unusable as an answer.
Not everything broke. The model converted epoch timestamps into a 2024 date range without trouble. Faced with two customer rows for the same company on the legacy schema, it summed across both and got the duplicate question right, a question the clean copy failed on a capital letter. The reasoning was rarely the problem. The misses were facts about the data that the schema doesn't state.
What we would do about each one
Publish the values alongside the columns. A data dictionary carrying the actual stored values of status-type columns, or a few sample rows, belongs in the model's context next to the schema definition. Six clean-schema misses say the values are the context that matters.
Make deletion a default rather than a detail. Serve the model a view with deleted rows already excluded, or pin the rule to the schema where no query can miss it. A flag every query must remember is a flag some query will forget — we watched it happen four times in twelve questions.
Treat attribute tables as first-class. The attribute names belong in the dictionary too. Given 'country' or 'origin_country' to guess, the model will guess the obvious one and get silence.
Promote notes to data. If a fact decides money, a credit hold, a do-not-ship, it needs a column. Text fields are where facts go to be missed.
Execute before you trust. Run every generated query and inspect what comes back. An empty set or a null total, on a question that should have an answer, is a detectable smell, and it would have caught the silent clean-schema failures. The inflated totals are harder: they sail through any check that only looks for emptiness, which is why the dictionary and the defaults come first.
The limits of this test
One small synthetic dataset. One model, one attempt per question, one run. The legacy transformations were ours, documented and deliberate, and probably kinder than plenty of real schemas, where codes go undocumented for a decade and three systems disagree about what a customer is. A different model would post a different score, and a second attempt with hints would post a better one. The numbers describe this rig. What transfers is the shape of the failures: they clustered where the schema stops describing the data, and production schemas stop much earlier than demo ones.
Work with us
If your team is putting natural-language questions over a database that has been accumulating decisions for a decade, the demo is the easy part. The real work is the dictionary, the defaults and the checks around the model, and it starts before the first question is asked. Tell us about the database through Trida AI's intake, and we will talk through what it takes to make its answers worth trusting.