How to Evaluate an Agentic Text-to-SQL System in Production

One number for two very different failures

In one of the projects I’ve worked on, an agentic text-to-SQL system answering business questions over a production ERP, the first metric I put on the evaluation was the obvious one: execution accuracy. Run the reference query, run the agent’s query, compare the two result sets. Same table, 1. Anything else, 0.

On a 17-case run it read 0.118. That was 2 cases out of 17, and one of the two compared zero rows against zero rows.

The low number wasn’t the problem. The problem was what it couldn’t see. It gave the same 0 to a query that missed 2 rows out of 302 and to a query that returned 107 rows where 2 were wanted. The first is almost right. The second has a join fanning out across a table it shouldn’t touch. And fixing that fan-out everywhere it occurred would not have moved the metric at all, because a query that returns a different number of columns fails before a single value is read.

Execution accuracy is the headline metric of public text-to-SQL benchmarks such as Spider and BIRD, and for comparing models on a leaderboard it does its job. On a system you have to improve week after week, it tells you that something is wrong, never what or where.

So I replaced it with independent families of metrics, each answering one question about one part of the system:

• Retrieval: were the tables the answer needs offered to the agent at all?
• Output: did the query return the right information, and is the data inside it right?
• Component: is the query built out of the right SQL pieces?

They are never folded into a single score. A query that returns the right rows for the wrong reason and one that was never given the tables it needed are different failures with different fixes, and one number cannot tell them apart.

The setup: a gold dataset and one case at a time

The ground truth came from the people who know the business. The functional team wrote each case: a question in natural language, a few alternative phrasings of it, and the SQL that correctly answers it on the ERP schema. No stored expected output: the gold SQL runs live, next to the agent’s, on every evaluation. That way both queries see the same data.

Each case goes through the deployed system, not through the model in isolation. The question goes to the agent through its API, exactly as a user’s would, and the system does everything it normally does: it retrieves schema, writes SQL, executes it. That’s the point of the exercise. In an agentic system the model is one component among several, and a wrong answer can start in any of them.

One rule makes the whole thing honest: leave-one-out. Every case in the dataset is also an example the retriever can serve, so asking it verbatim would let the agent retrieve its own answer and copy it. For the duration of each case, the case is hidden from retrieval, together with its twins: any other case with the same gold SQL. Without that, the evaluation measures the recall of the index, not the ability to answer.

The retriever is reached twice per case. The agent calls it inside its own answer, and the evaluation calls it directly with the same question. The direct call describes what the system would offer for that question, independently of what the agent then did with it. Keeping the two apart is what lets a bad answer be traced to bad retrieval rather than to bad generation.

Retrieval: did the agent have the tables?

The first family doesn’t look at the answer at all. It asks whether the answer was possible.

The required tables are the ones the gold SQL reads, parsed from the query itself. The offered tables are the ones whose DDL the retriever returned. I counted only the DDL on purpose: on an ERP schema, column names are opaque codes, and a table the model sees mentioned in an example query but whose definition never arrived is a table whose columns it has to guess.

recall    = |required ∩ offered| / |required|
precision = |required ∩ offered| / |offered|

A gold query that reads orders, order_lines and customers, against a retrieval that returned five tables, order_lines not among them, scores recall 2/3 = 0.667 and precision 2/5 = 0.400. The recall says the answer could not be written: without order_lines, no amount of reasoning gets there. When every other number is low, this is the one to read first.

Precision is mostly noise volume, and it largely tracks how many tables the question needed. I reported the absolute number of spurious tables per case next to it, which is easier to act on.

Output: columns and cells

This is the heart of the evaluation, and where execution accuracy gets replaced. It splits “did the query return the right data?” into two questions:

• columns: did the requested information come back, and how much noise came with it?
• cells: is the data inside that information right?

Neither can start until each candidate column is matched to the reference column it means. Names can’t do it: the model picks its own aliases, and three unaliased sums all arrive with an empty name. Position can’t do it either. So the pipeline is: pair the columns, score the columns, then score the cells inside the pairs.

Here is an example to follow through every step. The reference:

SELECT o.item, c.customer_id, SUM(o.amount) AS total
FROM orders o JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.item, c.customer_id

The candidate read the customer from the order side of the join and also grouped by site, so HELM · C002 never came back and HELM · C001 came back twice, split in two:

SELECT o.item, o.customer_id, SUM(o.amount) AS Total, o.site
FROM orders o JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.item, o.customer_id, o.site

Pairing the columns

First by origin. Parsing both queries tells where each output column comes from. The same table.column on both sides is proof, so it pairs with no threshold: orders.item with orders.item.

Then by values. What’s left is paired by the overlap of distinct values, best overlap first. customer_id came from orders on one side and customers on the other, so origin couldn’t pair it, but the values are identical: precision and recall 1.0. Total holds 100, 250, 30, 50 against 100 and 250: precision 0.5, recall 1.0. site has nothing to pair with.

This step infers rather than proves, so I put two guards on it, and on nothing else:

• The threshold is on min(P, R), not on F1. F1 averages the asymmetry away. A real pair once had recall 1.0 against precision 0.033, 30 distinct values on one side and 1 on the other, for an F1 of 0.065, which a low F1 threshold would have let through.
• A reference column needs at least two distinct values. A constant column discriminates nothing.

Measured on a 17-case run, the guards left the column metric almost still and lifted cell recall from 0.593 to 0.748: the spurious pairs had been poisoning the row comparison, not the column one.

Scoring the columns

Precision sums the pairs’ precision, recall sums their recall, and the denominators are every column on each side, never just the paired ones:

precision(columns) = Σ precision of each pair / number of candidate columns
recall(columns)    = Σ recall of each pair    / number of reference columns

Restricted to the pairs, a candidate that returned 1 of 20 requested columns perfectly would score 1.0. In the example: precision (1 + 1 + 0.5) / 4 = 0.625, recall (1 + 1 + 1) / 3 = 1.0. Everything that was asked came back, with one column nobody wanted and one inflated set of totals.

Pairing the rows

Rows have no identity. SQL’s order means nothing and the two sides can hold different numbers of rows, so before any cell can be compared, each candidate row has to find the reference row it corresponds to.

That’s the job of a row key: at most two paired columns whose values separate the reference rows. Here item + customer_id gives four distinct values over four rows, a selectivity of 1.0. Each reference row goes on the “shelf” named by its key. Each candidate row reads its key, finds its shelf, compares its cells, and empties the shelf:

Two rules do all the work. A row pays in parts: the third row is the right row with a wrong total and scores 2, not 0. Whole-row equality, which this replaced, zeroed 5 cases out of a 12-case sample. A shelf empties: the duplicate can’t collect the same reference row twice.

When the key is weaker, several reference rows share a shelf, and the candidate rows on it have to be matched to them. Matching them one at a time, each taking its best remaining option, makes the score depend on the order the database returned the rows in. So the whole shelf is paired at once, with the arrangement that maximises the matched cells: the assignment problem, solved by the Hungarian algorithm.

from scipy.optimize import linear_sum_assignment

def shelf_hits(cand_rows, ref_rows):
    """Cells matched on one shelf, with rows paired to maximise the total."""
    score = [[sum(c == r for c, r in zip(cr, rr)) for rr in ref_rows] for cr in cand_rows]
    pairs = linear_sum_assignment(score, maximize=True)
    return sum(score[i][j] for i, j in zip(*pairs))

Below a selectivity of 0.5 the key stops meaning anything, and the rows are compared as a multiset instead, whole row against whole row, without partial credit.

Scoring the cells

precision(cells) = matched cells / (candidate rows × w)
recall(cells)    = matched cells / (reference rows × w)

where w is the number of paired columns. The example matched 3 + 3 + 2 + 0 = 8 cells: precision 8/12 and recall 8/12, both 0.667. The missing HELM · C002 row was never looked up, and it shows up where it should, in the recall denominator.

Component: where the SQL went wrong

The output families say that the data is wrong. The component family says which part of the query made it wrong. Both queries are parsed and reduced to six sets, each scored with precision and recall:

The family is close in spirit to the component matching that Spider’s evaluation reports. What made it usable on a real system was the preparation. Every column is resolved to table.column, so the aliases the model picked stop mattering. A join written in the ON clause and the same equality written in WHERE score the same. And the two sides are cleaned differently but parsed identically: the candidate loses only what the agent adds by design and the gold never has, a pagination clause for example. A step applied to one side and not the other would measure the harness instead of the model.

An example. The gold query reads orders and customers, filters on a date and a category, and returns a sum and a count per customer. The candidate joined order_lines it didn’t need, dropped the category filter and forgot the count:

Precision falls where the model added something, recall where it left something out. On a run, the same table aggregated over all cases tells you which part of SQL the system struggles with: filters, joins, aggregations. That’s something a prompt, an example or a schema description can target.

Reading the numbers together

Separate families pay off when you read them side by side. The shape the output numbers make names the failure:

One detail makes the table faster to read: the two halves of cells share a numerator, so their ratio is the row-count ratio. Precision below recall means too many rows, recall below precision too few.

This is what a single score can’t give you. When execution accuracy says 0, all of these branches look the same.

Measuring honestly

A metric suite can mislead in ways that have nothing to do with the system it measures. A few rules kept these numbers about the system.

Undefined is not zero, and not one. When neither query groups by anything, the group-by precision has no denominator. Scoring it 1.0, as I first did, made group-by precision read 0.53 on a 17-case run: a number composed entirely of the nine cases where neither query grouped at all. A number about the questions, not about the model. Scoring it 0 would be just as wrong. So a metric with no denominator is undefined and left out of the mean:

def precision_recall(predicted: set, gold: set) -> tuple[float | None, float | None]:
    hit = len(predicted & gold)
    precision = hit / len(predicted) if predicted else None
    recall = hit / len(gold) if gold else None
    return precision, recall

Every mean carries its n. A mean over four cases and a mean over sixteen are not the same claim, and the number alone can’t say which one it is.

Ceilings are counted, never corrected. A gold query that doesn’t execute, a result set truncated at the row limit, rows scored without a usable key: each is counted next to the numbers, so a smaller n is never mistaken for a worse score.

Nothing decides. No threshold, no pass mark, no gate. The suite describes, and reading it is an engineering judgement.

Execution accuracy didn’t disappear, either. It survived as a test of the scorer itself. The output metrics rest on an alignment step I wrote, and if that step has a bug, both metrics move together and nothing catches it. Exact result-set equality has no parameters to get wrong. So when both output metrics read a perfect 1.0 and the exact comparison says the tables differ, the fault is in the scorer, not in the model.

What it doesn’t measure

A few gaps are worth stating, with the reason behind each.

• The references run live. The gold SQL executes on the real ERP database at every run, so both sides always see the same data. The price is that the data moves: when a ceiling count rises between two runs, the conditions changed, and the run is measuring that as much as the system.
• The system is measured from the outside. The evaluation sees what the system offered, what it wrote, what it returned and how long it took. What happens inside the agent loop, such as how many tool calls it made or how many of its queries were rejected along the way, isn’t part of these numbers.
• No LLM as a judge. I wanted every number to be reproducible: the same answer scored twice gives the same score, whatever model is around that month. And a judge gives a verdict, right or wrong, or a grade from one to five, where these families point at the part of the system that failed.

Every one of these numbers is something that can be computed, reproduced and argued about. That turned out to matter more than having one number to put on a slide.