How to test a text-to-SQL agent in production
A text-to-SQL agent that passed a demo can still be wrong most of the time on your data, and you will not notice unless you test it. Testing a text-to-SQL agent means running it against questions whose correct answers you already know, comparing the results, and doing that continuously so accuracy cannot quietly drop. This guide is the hands-on version, covering how to build the test set, what to measure, how to grade when there is no clean answer, and how to catch drift in production.
We have a conceptual guide to evaluating an analytics agent. This one is the tactical, text-to-SQL-specific companion.
Start with execution accuracy, not string matching
The first instinct is to compare the agent’s SQL to a reference query. Do not. Two very different queries can return the same correct answer, and two similar-looking queries can return different ones. What matters is the result.
Execution accuracy means running both the agent’s query and the known-correct query, then checking whether the result sets match. This is the metric the research world settled on. The Spider benchmark’s test-suite evaluation does exactly this, comparing the returned rows rather than the SQL text. Use the same idea on your own questions.
Build a test set from real questions
An eval is only as good as its questions. Build a set from the questions your team actually asks, each paired with a result a human has verified.
Cover the range. Simple lookups, questions that need a join, questions about defined metrics, and the ambiguous ones where the correct behavior is to ask for clarification. Include the questions that have produced wrong answers before, because those are your regression tests. Fifty good, verified questions beat a thousand generated ones.
Public benchmarks are a useful reference for how hard the general problem is. On the enterprise-grade Spider 2.0 benchmark, even strong models solve only a fraction of real-world tasks, a sharp drop from the near-solved original Spider. That gap is exactly why your own test set matters more than a leaderboard.
Grade when there is no clean answer
Some questions do not have one correct result, or you want to judge the explanation and not just the number. Two approaches help.
- LLM as a judge. Use a second model to score the answer against a rubric. This works, but only after you have checked that the judge agrees with a human on a sample. An unvalidated judge is just another model you are trusting blindly.
- Consistency checks. Ask the same question a few ways and see if the answers agree. Wide variance is a signal the agent is guessing, even when you cannot label a single right answer.
Monitor in production, not just at build time
The trap is treating evals as a one-time gate. Accuracy drifts. A model update, a prompt change, a new table, or a shift in the data can all move it, and because text-to-SQL fails silently, nobody notices until a decision is made on a wrong number.
Run the eval suite as a regression test on every change. In production, sample real questions, log the queries and results, and re-check a portion against known answers on a schedule. Track the trend, not just a single score. A slow decline is the thing you are trying to catch.
The short version
- Measure execution accuracy by comparing results, not SQL text.
- Build the test set from real, human-verified questions, including past failures.
- Use a validated LLM judge and consistency checks where there is no single answer.
- Run it as a regression test and keep sampling in production to catch drift.
None of this is exotic. It is software testing applied to an agent. Some tools build it in. Open-source agents like nao ship a test command so you can run your question set against the agent before you deploy and keep running it after. An agent you have not tested is an agent you cannot trust, no matter how good the demo looked.
How do you measure text-to-SQL accuracy?
Use execution accuracy. Run both the agent’s query and a known-correct query, then compare the result sets rather than the SQL text, because different queries can produce the same right answer. This is the method behind the Spider benchmark’s test-suite evaluation, and it catches silent failures that reading the SQL would miss.
How do you evaluate a text-to-SQL agent without ground truth?
When a question has no single correct result, use an LLM as a judge scored against a rubric, but only after confirming the judge agrees with a human on a sample. Consistency checks also help. Ask the same question several ways and treat wide variance in the answers as a sign the agent is guessing.
How often should you test a text-to-SQL agent?
Run the full eval suite as a regression test on every change to the model, prompt, or context. In production, keep sampling real questions and re-checking a portion against known answers on a schedule, and track the trend, because text-to-SQL accuracy drifts silently and a slow decline is easy to miss.