Blog /
How to evaluate a text-to-SQL system on your own data
Published . Updated . Written for nlqueries-core 0.3.
evaluation text-to-sql
If you are considering a natural language interface to your data, at some point somebody will show you a benchmark number. It is worth knowing exactly how much that number tells you about your own situation, which is: less than it appears, for reasons that are structural rather than anybody's fault.
This post is the evaluation we would run if we were on the buying side. It applies to NLQueries and to everything else in the category.
Why public benchmarks do not transfer
The best-known academic benchmarks for this task did real work in making the field measurable. They are also built on databases that differ from yours in ways that matter.
Benchmark schemas are small enough to fit in a prompt. Yours is not. Benchmark tables are cleanly named and were designed to be understood from the schema alone. Yours accreted over years and encodes decisions nobody documented. Benchmark questions were written by annotators looking at the schema. Your questions come from people who have never seen the schema and use vocabulary that exists only inside your company.
And the largest difference: benchmark questions have one correct answer, agreed in advance. Many of your real questions do not, because they are ambiguous in ways only a person with context can resolve.
A high leaderboard score means a system is competent at the general task. That is genuinely worth knowing, and it is a floor rather than a prediction. The number you actually need does not exist yet, because it has to be computed on your data.
Build the evaluation set from real questions
The temptation is to write test questions by looking at the schema. Resist it, because you will unconsciously write questions the schema can answer and the resulting score will be flattering and useless.
Better sources, roughly in order of quality:
- Requests your data team already received. Slack messages, tickets, emails. This is the actual distribution.
- Existing dashboards and reports. Each one answers a question that somebody cared about enough to build. Reverse them into natural language.
- Your query log. Frequently repeated query patterns are frequently asked questions.
Aim for something like fifty to a hundred questions to start. Fewer than that and you cannot distinguish signal from noise; many more and you will not finish building it.
Deliberately include the hard cases rather than a clean sample:
- Questions spanning three or more tables.
- Questions using internal jargon, with no explanation.
- Questions with a genuine ambiguity, where you want to see whether the system picks silently or surfaces the choice.
- Questions about the parts of the warehouse that are a mess, not just the well-modelled core.
- A few questions that cannot be answered from the data at all. This one matters more than it sounds.
For each question, have somebody who knows the data write the query they would consider correct, and record the result.
Score execution, not string similarity
Comparing the generated SQL to your reference SQL as text is a bad metric. There are many correct ways to write the same query, and a system that gets the right answer via a different formulation should not be penalised.
Run both queries. Compare the result sets. That is execution accuracy and it is the metric that corresponds to something you care about.
Two refinements worth the effort. First, compare results as sets rather than ordered lists unless order was part of the question, so that an irrelevant ORDER BY difference does not count as a failure. Second, track near misses separately from disasters: a query that is right except for an off-by-one date boundary is a different kind of problem from one that triple-counts revenue, and lumping them together hides the distinction that matters most.
Measure the abstentions
The questions that cannot be answered from your data are the most informative part of the set, and almost nobody includes them.
A system that answers all fifty answerable questions correctly and also confidently answers the five unanswerable ones has not scored 50/55. It has demonstrated that it will fabricate under pressure, which means every one of its answers now needs checking, which means it has saved nobody any time.
Score this explicitly. What fraction of unanswerable questions produced an honest "I cannot answer this from the available data" rather than a plausible query over the wrong tables? A system that abstains well is worth substantially more than one with a higher raw accuracy and no ability to stop.
Check the things that are not accuracy
While you have the harness set up, measure the properties that determine whether people will keep using the thing:
Is the query visible and readable? Can the person receiving the answer see how it was produced, without asking?
What does a question cost? In model spend and in warehouse compute. A system that answers well and scans a terabyte per question has a problem that will surface as an invoice.
How does it handle follow-ups? Real usage is conversational. "And by region?" needs to resolve against the previous query, and this is worth testing separately because it fails independently of single-question accuracy.
What happens at the edges? Empty results, a query that times out, a table the asker is not permitted to see. How the system behaves when it cannot succeed tells you more about the engineering than how it behaves when it can.
Can you re-run this yourself in six months? An evaluation you cannot repeat is a one-off impression. Schemas drift, models get updated, and the number you measured in May does not hold in November.
Run it against more than one thing
Finally, run the same set against a baseline. A frontier model with your schema pasted into the prompt and nothing else takes an afternoon to set up and gives you the number that matters: how much does the machinery around the model actually buy you on your data?
Sometimes the honest answer is "not much, your schema is small and well named and the plain model is fine". That is a genuinely useful thing to learn before signing anything, and any vendor who is uncomfortable with you running that comparison has told you something too.
We would rather you ran this evaluation than took our word for it. The Core repository is open, it runs on your infrastructure, and the only cost of pointing it at a database is your own model spend.