Originally published at Trida AI.
Copy as markdown
Twelve questions, three production models, each asked three times. On the clean database the scores ran from 5 to 11 of 12. On the same data in legacy dress they ran from 6 down to 1. The failures were the same failures, model after model.
We built the test twice. The small dataset is a sales book: seven customers, six products, eleven orders. The larger one is a wholesale business: 47 active customers, 20 products, 161 live orders and four deleted ones besides, three companies filed under two customer records each, two credit holds. Each dataset is stored two ways. The first copy is the demo version: names that say what they mean, dates in one format, statuses stored as words. The second holds exactly the same business data under the rules real databases accumulate: abbreviated names, undocumented integer status codes, dates in three or four formats, deleted orders still in the table, product attributes in a key-value table, credit holds written only in a notes field.
Then we asked Anthropic's Claude Sonnet 4.5, Opus 4.5 and Haiku 4.5 the same twelve questions against each copy, three runs apiece: 432 answers in all. Same prompt, the schema definition as the only context, no retries. Gold answers were written first, and the gold queries return identical results on both copies of each dataset, so any gap is the schema's doing, not the data's. A question counted as correct only when the executed result matched the gold result exactly.
The test rig: one dataset, two schemas, the same twelve questions, scored against gold answers.
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.
Three models, two datasets, the same traps
The worked example above is Sonnet on the small dataset. The full scores (a range where the three runs disagreed): small clean schema — Sonnet 5–6, Opus 10, Haiku 9; small legacy — Sonnet 5, Opus 6, Haiku 3. On the larger dataset the clean scores held up (Sonnet 6, Opus 11, Haiku 8) and the legacy scores fell through the floor: Sonnet 2, Opus 3, Haiku 1.
The traps did not care which model walked into them. Guessing stored values sank all three; the deleted flag was the biggest killer, 84 of the 156 legacy misses across both datasets. The attribute-name guess caught Sonnet and Opus on both datasets. Haiku failed the same questions two other ways: prose on the small dataset, and on the larger one a query that went looking for Germany in the customer table. The credit-hold question beat all three models on both datasets. And 21 of the 432 answers came back as paragraphs rather than a query at all, usually on the hardest questions — all from Sonnet and Haiku; Opus always returned a query.
A bigger model helped on the clean schema. On the legacy schema it barely mattered: the best legacy score in these runs was 6, and nobody beat 3 on the larger dataset. The information the models needed was not in the prompt. It was not in the schema either.
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.
Then we applied the fixes
So we applied the fixes a schema and a prompt can carry, and ran everything again: dictionary in the prompt, deleted rows excluded by default, credit holds as a column. Another 432 answers. Small legacy: Sonnet 5 → 8, Opus 6 → 10, Haiku 3 → 10. Larger legacy: Sonnet 2 → 9, Opus 3 → 10, Haiku 1 → 10. Clean schemas: every model reached 11 or 12. Across all 432 fixed answers, not one miss fell into the four failure modes above.
What remained was different in kind. Duplicated customers still got double-counted on a signup question — nothing in these fixes merges two records for one company. One model added the gross and net amount columns together. A few answers still came back as prose, or with the right rows carrying an extra column. Smaller problems, and honest ones: identity and output discipline, not facts the schema hides.
The limits of this test
Two synthetic datasets, both ours, the larger one still small next to a real warehouse. Three models from one provider, three runs each, one prompt per pass, no tools. Exact-match scoring is strict: a right answer carrying an extra column counted as a miss, and prose counted as a miss even when a query was buried in it. 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. 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.
Top comments (0)