The loop optimises the signal you gave it, not the goal

A text-to-SQL feature takes a question typed in English, generates a SQL query for it, and runs that query against a warehouse — the central analytics database a company computes its reports from. Wrapped in the evaluator-optimizer loop of the previous chapter, the check in the evaluator slot is usually the obvious one: hand the query to the database and see whether it comes back without raising. That check is cheap, reproducible in exactly the sense chapter 1 derived, and available on every question anybody can ask. It is also the objective the loop will optimise, and this chapter is about the gap between that objective and the feature's actual promise, which is that the number on the screen is the right number.

The gap is not a matter of degree. A query that joins subscription_events to subscriptions on the wrong key, and filters on event_at where it should have used effective_at, is a query the database is perfectly willing to run. It counts subscriptions that churned on 28 March and were re-activated in April, because the event that ended them lands outside the filter. The result arrives fast, has no nulls in it, and looks exactly like the number that would have been right. Nothing in the check distinguishes the two, and the loop cannot search for a distinction the check does not make.

Write the accepting predicate as a sentence#

The verifier in that system was thirty lines long and had been reviewed by three people, none of whom had said out loud what it accepts. It looked like this.

def verify(sql: str, conn) -> list[str]:
    try:
        with conn.cursor() as cur:
            cur.execute("BEGIN READ ONLY")
            cur.execute(f"EXPLAIN {sql}")
            cur.execute(sql)
            rows = cur.fetchmany(1000)
            cur.execute("ROLLBACK")
    except psycopg.Error as e:
        return [f"{e.__class__.__name__}: {e}"]
    if len(rows) > 1000:
        return ["result set too large to summarise"]
    return []

The exercise that makes this legible takes a minute and is worth doing on every verifier you own. Write down, in one English sentence, the set of outputs the function returns an empty list for, and finish the sentence with the words and nothing else. For this one: this query is syntactically valid, references only relations and columns the read-only role can see, completes without raising, and returns at most 1,000 rows — and nothing else. Every property in that sentence is real and worth having. None of them mentions the question the user asked, which does not even appear as an argument to the function.

Work the sentence's last clause against the code and it turns out to be doing no work either, which is worth following through because the reasoning generalises. cur.fetchmany(1000) retrieves at most 1,000 rows and leaves the rest in the cursor. So rows has at most 1,000 elements on every path that reaches the comparison, len(rows) > 1000 is false whatever the query was, and the branch under it never executes. A query that would return a million rows is truncated rather than rejected: the check reads the first thousand, finds a thousand, and returns an empty list. The clause is true of every output the function is ever handed, which is the definition of a constraint that removes nothing from the accepting set. Delete the branch and the accepting set is unchanged, which is the test for whether a constraint is load-bearing. What remains after the deletion is the honest sentence: this query runs against the warehouse — and nothing else.

There is a faster version of the reading exercise for when you are working through someone else's code, and it is worth having because it costs four seconds. Look at the function's parameters. A verifier can only make claims about the values it receives, so anything absent from the signature is outside its reach by construction. verify(sql, conn) cannot check faithfulness to a question it never sees, verify(claim) cannot check faithfulness to a document it never sees, and verify(patch, suite) can check only what the suite exercises. The signature is a stronger summary of a check's scope than the body.

Stronger, and one-directional, which is the half that gets forgotten. Absence from the signature is proof that a property cannot be checked. Presence in the signature is not proof that it is. conn is right there in the parameter list and the function still establishes nothing about whether the query means what the user asked, because it uses the connection to execute rather than to compare. The signature gives you an upper bound on what a check can claim, and the body decides how much of that bound is used.

That sentence — the honest one, after the dead clause comes out — is the objective. The loop is a search procedure that maximises it, sampling candidate queries and steering the next sample with the failure text from the previous one, and it stops as soon as it finds a member of the accepting set. Whatever correlation exists between "runs" and "correct" is doing all the work.

Where the retries make it worse#

The correlation is not merely relied upon; it is eroded, attempt by attempt, and the mechanism is specific enough to trace. Here is a real three-attempt trace from the same system, for the question what was churned MRR in EU accounts last quarter?, where MRR is monthly recurring revenue, the amount the subscription base is contracted to pay each month, and churned MRR is the part of it lost to cancellations.

Attempt two fixed a genuine type error, which is the case for the loop. Attempt three did something different, and the difference is the whole chapter in one step. Told that c.region does not exist, the model faced a choice between two repairs. One is to find the column that actually carries the concept, which in this schema is c.billing_country joined through addresses; that repair requires knowing something about the schema, and it can fail again, because the guessed replacement column may not exist either. The other is to delete the clause that mentions the missing column. Deleting it cannot fail, because a predicate that is not there cannot reference a column that is not there. One repair is a guess with a failure probability attached and the other is guaranteed to remove the error, and the loop's objective rewards removal of the error.

So the model dropped the EU restriction. The query now computes churned MRR across every region, returns €1.42 million where the answer was €310,000, and satisfies the objective completely. Notice that the error is one-sided: dropping a filter can only widen a result set, so a query repaired this way overstates rather than understates. That one-sidedness is a general property of repairs-by-deletion and it is why the wrong answers this loop produces are systematically too big.

Across a month of traffic the team went back and classified 400 sampled runs by hand, and the two figures worth keeping apart both come out of that exercise. Queries that passed on the first attempt were wrong 12% of the time. Queries that passed on the third attempt or later were wrong 41% of the time, and the dominant failure mode inside that bucket was a dropped or weakened predicate — exactly the repair that trades an error for a wider result set. The loop had not made the system worse on average, and it had concentrated its damage in the cases where users were most likely to be asking something the schema does not answer straightforwardly.

The failure text is part of the objective#

The team's first improvement did not touch the accepting predicate at all. Postgres reports column c.region does not exist and stops. The loop was passing that string through unchanged, and the model was choosing between two repairs with no information to prefer one — which is the state the previous section described, and in that state the guaranteed repair wins.

Adding information changes the arithmetic of the choice. Replacing the raw error with a resolved one — look the unknown identifier up against the catalogue, find the nearest names by trigram similarity, which scores two identifiers on how many three-character sequences they share, and append them — turns the guess into something much closer to a lookup.

def enrich(err: psycopg.Error, catalogue: Catalogue) -> str:
    m = UNDEFINED_COLUMN.match(str(err))
    if not m:
        return str(err)
    name = m.group("column")
    near = catalogue.similar_columns(name, limit=3)   # trigram over information_schema
    return (f"{err}\nColumns with similar names: "
            + ", ".join(f"{c.table}.{c.column} ({c.comment})" for c in near))

With customers.billing_country, addresses.country_code and regions.name attached to the error, the repair that keeps the filter stops being a guess: the replacement column is named in the feedback, so substituting it is about as likely to succeed as deleting the clause is. Measured over a replay of the same month of traffic, the share of high-attempt runs whose final query had lost a predicate fell from 41% to 17%.

Both of those percentages describe high-attempt runs and they are not the same quantity, which is worth saying because the coincidence of the first figure invites the mistake. The earlier 41% is the share of late passes that were wrong by any mechanism. This 41% is the share whose final query had lost a predicate, and the 17% is what that share fell to. A predicate-loss rate and a wrong-answer rate over the same population are two counts of different things, and an improvement in one is evidence about the other only to the extent that predicate loss is the dominant mechanism — which the hand classification said it was, for that bucket, in that month.

Nothing about what the verifier accepts changed, and the accepting set is still every runnable query. What changed is the path the search takes through it, because the cheapest repair is now one that keeps the filter. That is a general property worth holding onto: a verifier defines where the loop can stop, and the feedback text decides which of those stopping points it reaches first. Both are yours to design, and the second one is usually cheaper to improve.

It also has a limit that shows up immediately, and the limit follows from the same reasoning that produced the improvement. Enriched errors work by making the correct repair cheap to find; they can only do that when a correct repair exists. Where the schema genuinely has no column carrying the concept the question needs — 9% of the sampled failures — there is no substitute to name, the nearest-neighbour list is a list of wrong answers, and deleting the clause is once again the only repair that terminates. The loop still converges on a query that runs, because that is what it was asked for. The improvement has made the remaining wrong answers harder to spot, by making the obviously-mangled ones rare.

What it costs to check the thing you meant#

There is a verifier that catches wrong numbers, and the reason nobody had written it is that it is expensive in ways the executable-query check is not. Four candidates are worth pricing properly, and they differ along a dimension that gets missed: whether they can run at request time on a question nobody has seen before.

The first column is the one that partitions the table, and it partitions it into two things that are not comparable. A row with "No" in it cannot be a stopping condition at all, whatever the rest of its row says, because a loop needs a verdict on the output in front of it. Golden result sets are the one most teams reach for and the one whose limitation is most often missed: 120 question–result pairs give you a measurement, and a measurement is not a stopping condition. You cannot verify a novel user question against a set that does not contain it.

What the golden set buys is the number that told the team their pass rate was meaningless — run the current system against those 120 questions, compare result sets exactly, and you get an accuracy figure that does not move when you change the retry ceiling. That is the number to put next to the pass rate, and chapter 6 is about why the pass rate on its own is guaranteed to look good.

Among the request-time rows there is a knee in the per-query cost column, and it is two orders of magnitude wide. The semantic layer answers in about the time a dictionary lookup takes, because it is checking a structural property of the generated query against a set of definitions and never touches the warehouse. Every other request-time row costs a warehouse scan or two, which is hundreds of milliseconds and a per-query charge. What buys the knee is the build cost — weeks rather than days — and what it costs is the coverage column, since a question that cannot be phrased in certified metrics has no verdict at all. That is the trade the table is actually offering, and reading the rows for levels rather than for that trade is how teams end up pricing a semantic layer against a day of work on an execution check.

Dual generation is the only entry that is both request-time and open-domain, and it is worth being honest about what it does. It detects disagreement rather than error. Two generations that agree produce no signal at all, whether they are both right or both wrong, so everything the mechanism buys is conditional on their disagreeing, which makes independence the load-bearing assumption. The same model twice at temperature 0.7 agrees on the wrong join most of the time, because the wrong join is the likely reading of an ambiguous schema and both draws come from the same distribution over readings. Two different model families, prompted from the same schema documentation, disagreed on 14% of the questions in the team's sample and caught 61% of the hand-labelled wrong answers when they did.

Read those two figures with their condition attached, because it is easy to multiply them into a detection rate they do not support. The 61% is measured on the questions where the two families disagreed. It says nothing about wrong answers on the questions where they agreed, and those are the wrong answers a shared misreading of the schema produces — which is to say, the ones most likely to be there. What the pair of figures licenses is a floor: at least this much is catchable at this price. What it leaves as residual is the entire agreement region, whose error rate this measurement cannot see. Dual generation costs double the warehouse scan on every request and it converts a wrong answer into an escalation rather than a right answer, which is a fair trade for a finance number and a poor one for an exploratory query someone is running out of curiosity.

When the honest move is to shrink the question#

The uncomfortable case is the one where none of the request-time rows in that table is affordable and the offline row does not terminate anything. Open-domain natural language over a warehouse with 900 tables and no certified metric definitions is that case: the space of questions is unbounded, the mapping from question to correct query is exactly the hard part, and any verifier cheap enough to run per request is checking a proxy.

The response that works is not a better verifier, and it helps to see why the search for one is bounded rather than merely hard. A verifier deciding whether a query answers a question has to know what the right answer is; knowing what the right answer is means having derived the correct query; deriving the correct query is the feature. Any request-time check over an unbounded question space either does that work a second time by an independent route, which is the dual-generation row and its cost, or it checks something else. There is no third option hiding behind more engineering effort.

So the move is to narrow what the feature promises until a real verifier fits: restrict the assistant to the metrics finance has already defined, refuse questions outside them with a message naming what it does cover, and verify at request time that the generated query is derivable from the certified layer. That converts an unbounded problem into a bounded one and pays for it in refusals — in the team's traffic, roughly 45% of questions fell outside the certified metrics on day one. Half the questions people ask a data assistant get an answer, and the answers are checkable. That is a design decision about what the product is, made deliberately, and it is not the same kind of thing as the requirement it is responding to.

The alternative that also works keeps the open-domain feature and stops treating it as a system that produces numbers, which means surfacing the SQL prominently, labelling results as unverified, and putting the golden-set accuracy figure where the controller can see it before she pastes anything into a board deck. Both responses accept the same finding and spend it differently: one buys a verifier by shrinking the question space, the other buys nothing and tells the truth about it. What neither of them does is leave a 99.2% on a dashboard next to a feature whose promise that number has never touched.

Every verifier defines an accepting set, and a loop wrapped around it will find that set. Reading the set out loud, as a sentence ending in and nothing else, is the cheapest diagnostic available and it costs a minute. Reading the signature is cheaper still and gives you the upper bound rather than the actual scope. The gap between the sentence and the promise is not a gap the loop closes with more attempts; the attempts are what makes it wider, because a repair that deletes the offending clause always terminates and a repair that fixes the underlying misunderstanding might not.

What is left is a check that a fixture set will never test, because the outputs that expose it are the ones nobody thought to write down. Building those deliberately is the next chapter.