Twenty questions that break text-to-SQL

Every text-to-SQL demo I have sat through works. Every rollout I have watched stalls within a month. The gap is not model quality. It is that the demo asks questions with one right answer, and the business asks questions with none.

The schema every question runs against

Six tables. Small enough to fit in a prompt, which is the point.

orders

order_id
customer_id
ordered_at (utc)
status
channel
currency
subtotal
discount
tax
shipping

order_items

order_id
product_id
qty
unit_price

customers

customer_id
email
created_at
country
is_internal
is_test

products

product_id
sku
category
list_price
cost

refunds

refund_id
order_id
amount
refunded_at
reason

subscriptions

subscription_id
customer_id
plan
mrr
status
started_at
canceled_at

A schema that fits in a prompt

Six tables, forty-odd columns. Any text-to-SQL demo would handle this. That is what makes it a useful test: nothing below fails because the schema is big. It fails because the question is underspecified.

Which revenue?

Four questions where the noun in the question maps to several columns. Read the SQL, then pick the assumption it made without telling you. The SQL always runs. That is the problem.

When is last month?

Timezones, fiscal calendars, week starts and partial periods. The model picks the warehouse default every time, and the warehouse default is never what finance uses.

The join that doubles the number

Order-level measures summed at line grain. One-to-many joins treated as one-to-one. No error, no warning, just a number that is two or three times too big.

The WHERE clause everyone forgets to mention

Test accounts, staff orders, fraud, wholesale. Every analyst applies these filters by reflex. The model has never seen the reflex, only the tables.

Questions with no correct SQL

Churn, active, lifetime value, MRR. The model has to invent a definition to answer at all, and it invents one silently. These are not text-to-SQL failures. They are missing-definition failures.

Use them as an acceptance test

Filter by class, quiz yourself on the ones you skipped, and copy the checklist. Run all twenty against any chat-with-data tool before you trust it with anything.

Why the demo passes and production fails

A demo is run by someone who knows the schema. They ask “how many orders shipped to Germany in August” because they know there is a ship_country column and a shipped_at timestamp. The question and the schema were written by the same brain.

Production is a head of sales typing “what was revenue last month” into a chat box. That question has, by my count, at least four unstated decisions in it: which revenue, which month boundary, which timezone, and which orders count. The model has to pick all four. It picks the warehouse defaults, returns a number, and formats it nicely. Nothing about the answer looks wrong. It just does not match the finance deck, and once that happens twice, nobody types into the box again.

That is the whole failure mode. Not hallucinated tables, not bad joins in the syntactic sense. Confident answers to underspecified questions.

questions in the taxonomy

all over the same six tables

failure classes

metric, time, grain, filters, definition

accuracy numbers in this post

nothing here was benchmarked

The five classes, in order of how often I see them

Ambiguous metric names. Revenue, customers, margin, order value. The word in the question maps to a column, and the model takes the closest column name. Finance uses a different one. Most common, easiest to fix, because the fix is a definition, not a model.

Implicit time semantics. Timestamps are stored in UTC and truncated in UTC. Weeks start on Monday in the warehouse and Sunday in the dashboard. Q3 is calendar unless it is fiscal. “This week” is a partial week compared to a full one. Each one is off by a little, which is worse than off by a lot, because nobody notices until the reconciliation.

Grain traps. A join from orders to order_items is valid SQL, and summing an order-level column after it multiplies revenue by the number of lines. A LEFT JOIN to refunds does the same for orders with two partial refunds. These are the ones that get screenshotted in Slack with the caption “the AI says we did 3x last month”.

Hidden filters. Every analyst in the company excludes test accounts, internal orders, fraud and wholesale by reflex. The reflex lives in their heads, or in a dbt model the chat tool does not point at. The model sees six tables and no reflex.

Unanswerable without a definition. Churn, active, LTV, MRR. There is no SQL for these until someone writes the definition down. The model writes one for you, silently, and it is never the one your board deck uses. These are not text-to-SQL failures. They are organisational failures that text-to-SQL exposes.

Use the twenty as an acceptance test

This is the practical part. Before you let any chat-with-data tool near a stakeholder, run all twenty questions against it, using your own schema. Do not grade on whether the SQL runs. Grade on three things:

Did it state the assumption it made, or hide it? “Revenue here is subtotal before refunds” is a pass even if the number is not the one you wanted. A bare number is a fail.
Did it ask when it should have? The definition class should produce a clarifying question, not an answer. A tool that confidently returns a churn rate without asking what churn means has told you how it will behave on everything else.
Did it use your definitions when they exist? If you have a metric layer and the tool still summed subtotal, the integration is decorative.

Copy the checklist from the last scene above. It has a “needs a definition for” line under each question, which doubles as the list of things to write down before you re-run the test.

What to put in the metric layer first

The temptation is to define everything. Do not. Start with the definitions that the twenty questions expose, in this order:

The default population filter. One place that says: exclude is_test, exclude is_internal, exclude these statuses, exclude these channels. Every measure inherits it. This closes the entire hidden-filter class at once.
The three or four revenue-like measures, with names that are not “revenue”. Gross bookings, net revenue, recognised revenue. Once the names are distinct, the model can map a vague question to a specific one, and the answer says which it chose.
A reporting calendar. Timezone, week start, fiscal year start, and a rule for partial periods. This is boring and takes an afternoon.
Grain, declared on every measure. Order-level measures are not additive across a line-level join. If the layer knows that, it can refuse the join or pre-aggregate. If it does not, no prompt will save you.
The four words with no SQL. Active, churn, new customer, MRR. Write the definition, get finance to sign it, and put it where the tool reads it. Until then the correct answer to those questions is a question back.

Do those five and re-run the twenty. My expectation, and it is only an expectation, is that the metric, filter and time failures mostly go away, the grain failures depend on whether the tool respects the layer, and the definition failures turn into clarifying questions. That is what a working chat-with-data tool looks like. Not a better model. A better set of nouns.