Schema-retrieval recall decides whether the SQL can be right at all

Before a language model writes a line of SQL against a company warehouse, something has to decide which of that warehouse's tables the model is allowed to see. In the one this topic is built on there are 1,900 tables and roughly 40,000 columns, and a prompt has room for a handful of them. That decision is schema retrieval, and the number describing how well it is going is schema-retrieval recall: the share of questions for which every table the correct query needs was retrieved. Both the correct answers and the quiet wrong ones are decided there.

The machinery around it is worth laying out first, since every stage of it comes back in this chapter and the seven after it. A question arrives as an English sentence — typed into a Slack channel, in the arrangement this topic describes. A service reads the sentence, picks out which of the company's 1,900 tables might bear on it, renders a description of those tables into a prompt, and asks a language model for a SQL query; that query is checked, executed against the warehouse under a service account, and the rows that come back are written up as the reply the asker reads. That arrangement is structured-data RAG — retrieval-augmented generation, the pattern of fetching the context that bears on a question and placing it in a model's prompt so the answer is generated from that context rather than from the model's memory, pointed at a database instead of at a corpus of documents. What is retrieved here is schema: table names, column names and their descriptions, the relationships between tables, sampled values, a few example queries. What the model generates from it is a query, and the step that turns an English question into that query is called text-to-SQL, which is the name most of the tooling carries.

The reason to build any of this is that the numbers a company runs on are not written down anywhere in a form that can be looked up. "How many enterprise accounts churned in Q2" is a computation over a few million rows and nobody has performed it yet, so document retrieval over decks and wiki pages returns whatever figure someone last pasted into a slide, and asking a model the question directly returns a fluent invention, since the model has never seen this warehouse. The route that does work is a person: an analyst who knows the schema writes the query, which costs ten minutes of their attention and two days of queue time. A text-to-SQL system is an attempt to put that computation within reach of whoever wants the number, at the price of the query now being written by something that cannot be asked whether it was sure.

You meet it as the natural-language box in a warehouse or business-intelligence product — Snowflake's Cortex Analyst, Databricks' Genie and Power BI Copilot all ship a version — as an internal Slack bot of the kind above, and, most often for a backend engineer, as one tool inside an agent: a single query_warehouse function, described in a sentence, wired to a database credential. Whatever the surface, the stages underneath are the same ones.

Whether such a thing should be built at all is the subject of chapter 8, and the six chapters between here and there are what it takes to build one whose answers can be relied on. The stage this chapter covers is the leftmost box in that chain.

Why the leftmost box sets a ceiling#

A language model has no connection to a warehouse. Nothing in the generation step opens a cursor, lists a catalogue or reads a row; the model receives text and emits text. So the only channel by which the existence of a table can reach it is the prompt. The second step is that the model has not learned these tables from anywhere else either: the schema is private, and a name like fct_ord_ln_v2 appears in no public code, no documentation and no training corpus. Both routes to knowing that a table exists are therefore closed except one.

From those two facts the rest follows. Every table name in a generated query is either copied out of the prompt or invented on the spot. An invented name is rejected by the database, loudly and immediately, which means it does not survive to become an answer. A copied name executes. So the queries that can both run and return something are exactly the queries expressible over the retrieved tables — and if the query that would answer the question correctly needs a table outside that set, then that set contains no correct query at all. The best remaining outcome is a query that is valid over the wrong tables.

That is why a selection miss cannot be debugged by improving the generator. It writes a reasonable query over the tables it was given, and the tables it was given could not support a correct answer. Whatever the rest of the chain does — validation, execution limits, result checks — the ceiling on correctness was set before the model produced a token.

Two kinds of quantity in that chain get argued about as though they were the same kind of thing. That the warehouse holds 1,900 tables is a requirement, a fact about the business no decision inside this system changes. The size of the schema budget, how many tables it turns out to hold, how much detail each one is rendered with, and the definition of the recall metric are design decisions: chosen, changeable tomorrow, and each one moves the ceiling.

What does not fit in eight thousand tokens#

The catalogue is 1,900 tables and roughly 40,000 columns, so about 21 columns per table. Rendered as DDL — the data definition language, meaning the CREATE TABLE statements that declare each table's columns and types — with a type and a short comment per column, a column line costs around 14 tokens and each table carries about 25 tokens of header, closing brace and qualified name.

Those two rates are enough to price the whole catalogue, and it is worth doing the multiplication rather than reading the result. Every column in the warehouse produces exactly one line, wherever it lives, so the column cost is the column count times the per-column rate: 40,000 × 14. Every table produces its wrapper exactly once regardless of how wide it is, so the table cost is the table count times the per-table rate: 1,900 × 25. The two are additive because no token is counted in both — a line is either a column line or part of a wrapper. That gives roughly 607,000 tokens for the catalogue alone, before any foreign-key declarations, sampled values or example queries.

Against an 8,000-token schema budget, you can afford 1.3% of it. The obvious reply is that context windows are large now and 607,000 tokens fits in several current models, which is true and does not help. Sending the full catalogue on every question costs about $1.82 in input tokens at $3 per million, adds seconds of latency to a Slack interaction people expect to feel instant, and reliably makes generation worse rather than better, because 1,892 irrelevant tables are 1,892 opportunities to pick a plausible wrong one. There are four tables in this warehouse whose names contain orders and three whose names contain revenue, and every one of them is a real table that some pipeline writes to. That count of distractors is the catalogue less the eight tables the budget holds, and plausible is the word doing the work in it.

A comfortable belief usually forms here: that a larger context window is a defence against selection error, and once the whole catalogue fits, selection stops mattering. It is tempting because window size is the one quantity here improving on its own, without anyone at this company doing any work. It is false because the window is a quantity that stays fixed while the dangerous property varies underneath it. A window large enough for 1,900 tables is equally large enough for the four orders tables and the three revenue ones to sit side by side in it, and the count of near-miss neighbours grows with the catalogue at exactly the rate the catalogue does. Widening the window admits distractors and signal together, and choosing between tables that all look right gets harder rather than easier.

The 1.3% is an upper bound rather than the figure. It compares an 8,000-token budget against a catalogue priced at bare DDL, one comment per column, and what actually goes into the prompt is richer: sampled values, distributions, join declarations and a grain statement, which is what chapter 2 is about. A richer rendering costs more tokens per table, so the same budget holds fewer of them. Two figures that look inconsistent — a catalogue pricing out at a few hundred tokens a table, and a budget holding only eight — are measuring two renderings of one warehouse, and the gap between them is a design decision about card detail rather than an error in either number.

Selection is therefore a component of the system with its own inputs, its own failure modes and its own metric, and it stays a component when the windows get bigger. The budget is a design parameter you choose — 8k here — rather than a constraint you are waiting for the model vendors to lift.

The metric is per-question and all-or-nothing#

Schema-retrieval recall is the fraction of evaluation questions for which every table the correct query needs appears in the retrieved set. A question whose gold query — the SQL an analyst has confirmed answers it correctly — touches four tables scores 1 if all four were retrieved and 0 if three were, because a query missing one of its four tables is not 75% correct; it is a different query.

That definition matters because the tempting alternative — averaging recall over tables, then over questions — reads much higher and moves for the wrong reasons. On the evaluation set below, table-level recall was 0.93 while question-level recall was 0.71.

The two numbers come from one retrieval run and differ only in how the same hits are added up, which is what makes the gap between them readable. Write a question's gold set as some number of tables, and the number of those that came back as its hits. Table-level recall averages the ratio of hits to gold tables; question-level recall counts a question only when that ratio is exactly one. For a question needing a single table the two definitions collapse into each other, because the ratio can only be zero or one — the size of the gold set cancels out of the arithmetic entirely. Every bit of daylight between 0.93 and 0.71 therefore comes from questions needing more than one table.

Working the other direction reconstructs the pair. The evaluation set below has 87 questions with at least one table missing. Suppose each of those missed exactly one table out of four: each contributes three-quarters instead of a full unit to the table average, while contributing nothing to the question count. Adding the ones that landed perfectly to those partial contributions and dividing by the set size gives a figure that rounds to the 0.93 quoted. Had the same 87 misses fallen on questions needing three tables each, the table average would fall visibly short of it — and had they fallen on single-table questions the two metrics would have to agree. The shape of the gap is therefore forced by the numbers rather than asserted: it is entirely made of questions that needed three or four tables and got all but one, which are exactly the questions the system answers confidently and wrongly.

The stumble in this chapter lives in that gap, and it is not an arithmetic slip. It is a belief that a defence works: that a retrieval metric reading 0.93 means retrieval is not the problem. The belief is tempting because 0.93 is a measurement rather than an opinion, taken on real questions, and it went up when the retriever improved. What makes it false is that the table average divides each failure by the width of the query it broke. One missing table costs a full unit on a one-table question and a quarter of a unit on a four-table one, so the metric quietly discounts a failure in proportion to how complicated the query was. Complicated is the dangerous property here: a four-table query is the one whose result nobody can eyeball, and it is the one this metric is least sensitive to. The number stays high precisely as the danger rises.

One analogy helps with the all-or-nothing part, and it is offered as an analogy rather than as a fact about the system. A four-digit combination lock with three digits right opens nothing, and there is no sense in which it is three-quarters unlocked; partial credit is meaningless because the object has one binary output. Schema retrieval scores the same way, and that is where the resemblance stops. A lock that is nearly right stays shut, and the person in front of it knows they have failed. A table set that is nearly right opens, and produces a number.

The other tempting alternative is to skip component metrics and measure the thing users care about.

End-to-end accuracy is the right business metric and a poor sole metric, because it is unattributable. When it falls from 78% to 61% after a release, the candidate causes are a retriever regression, a change to how the schema is rendered, a model version bump, a new dialect quirk, a table that was renamed upstream, and a bug in the metric itself. Component metrics exist so that failures have addresses. Recall drops, you look at retrieval; recall is flat and end-to-end drops, you look downstream.

Building the set the number is measured on#

Three hundred questions is enough to move on with, and they should not be invented by the team building the system. Two sources give you real ones. The analytics team's request queue — Slack threads, Jira tickets, the "quick question" channel — is a log of the exact phrasing business users produce, including the vague ones. The BI tool's query log gives you SQL that ran, and for a good share of it you can recover the question it answered from the dashboard title or the ticket that prompted it.

Each evaluation item is a question, a gold SQL query that an analyst confirms answers it, and nothing else. You do not hand-label the table set, because the gold SQL already contains it and a parser extracts it more reliably than a person does at 4pm.

import sqlglot
from sqlglot import exp

def gold_tables(sql: str, dialect: str = "snowflake") -> set[str]:
    """Tables a query actually reads, excluding its own CTE names."""
    tree = sqlglot.parse_one(sql, dialect=dialect)
    ctes = {c.alias_or_name.lower() for c in tree.find_all(exp.CTE)}
    names = set()
    for t in tree.find_all(exp.Table):
        name = t.name.lower()
        if name in ctes:
            continue
        names.add(f"{(t.db or 'analytics').lower()}.{name}")
    return names

def recall_at_budget(eval_set, retrieve, token_budget: int = 8000) -> float:
    hits = 0
    for item in eval_set:
        retrieved = {t.lower() for t in retrieve(item["question"], token_budget)}
        if gold_tables(item["gold_sql"]) <= retrieved:
            hits += 1
    return hits / len(eval_set)

The subset test on the last line is the whole metric: retrieval succeeded when the gold table set is contained in what came back. Running this over 300 questions takes about 40 seconds, most of it in the retriever, which means it is cheap enough to run on every change to the indexing code rather than once a quarter.

Two choices inside that function are the metric rather than implementation detail, and they are easy to read past. The first is that the comparison is a subset test and not an overlap count, which is what makes the score all-or-nothing at the level of one question: <= is true only when nothing required is absent, and it is indifferent to how many extra tables came back. The second is the CTE exclusion. A common table expression is a query-local name, defined by the statement itself in a WITH clause, so a gold query containing WITH recent_orders AS (…) mentions recent_orders in exactly the same syntactic position a real table would occupy. Counting it as a gold table would mark the question unretrievable for ever, since no retriever can return a name that exists only inside that one statement. Filtering the CTE names out first is what keeps the denominator honest, and it is also the reason parsing beats hand-labelling: a person transcribes the names they see.

One detail is easy to get wrong and expensive to discover later. The retriever must be called with the same token budget the production prompt uses, and the budget must be enforced by rendering the candidate tables and counting, rather than by a fixed top-k. A top-8 that happens to select eight narrow tables spends 2,100 tokens; a top-8 that selects three wide fact tables and five dimensions spends 11,400 and gets truncated in production in a way your evaluation never saw.

A fixed top-k fails because it holds constant the wrong quantity. A count of tables is not a count of tokens, and the conversion between them depends on how wide each table is, which varies by an order of magnitude across a warehouse holding both fact tables and small dimensions. An evaluation scored on the count records a hit for a set production will truncate, and truncation removes the tables the retriever ranked last — disproportionately the ones a question needed and only just reached. Evaluation and production therefore disagree most on the questions that came closest to being right.

Seventy-one percent, and where the missing twenty-nine goes#

The first measurement came back at 71%: 213 questions where every needed table was retrieved, 87 where at least one was missing. The decision at that point is whether the next two weeks go into the retriever or into the schema documentation, and the way to decide is to look at the 87 rather than to argue about it.

The triage rule is mechanical. For each missed table, check whether any content word from the question appears anywhere in the table's name, its column names, or its description. If it does, the retriever had a signal and failed to use it. If it does not, no retriever can rank that table, because there is nothing to match on and embeddings do not invent semantics that the text does not contain.

Two things about that table close exactly, and each buys something different. The counts in the middle column sum to 87 with nothing left over, and 213 plus 87 is the 300 the set contains. Every question is in exactly one place and none is in two. That licenses the claim the next two paragraphs rest on: the triage is exhaustive, so "the majority of misses are a documentation problem" describes all of them rather than the ones somebody got round to reading, and no unclassified remainder is left for a fourth failure mode to hide in.

What it does not license is confidence in where each boundary was drawn. The rule that assigns a class is a word-overlap test, and a table whose description happens to contain one content word from the question lands in "retrievable but out-ranked" even when that single word was far too thin a signal to rank on. Misclassifications of that kind move counts between the first two rows without disturbing the total, so the sum closing tells you nothing about them. The six ambiguous questions carry a second residual: an ambiguous question has no single gold query, so the analyst supplying one made a judgement, and the denominator of 300 has soft edges to precisely that extent.

The 52 are the majority and they are not a retrieval problem. A table named fct_ord_ln_v2 with columns amt_gr, amt_nt, rgn_cd and dt_bk shares no token with any sentence a business user will ever type. Writing one sentence per table and one clause per filterable column — "Order line items, one row per line; amt_gr is gross merchandise value in EUR cents before discounts; dt_bk is the booking date in UTC" — makes those tables reachable by an ordinary retriever. Two hundred and twenty tables cover about 90% of question volume in the request queue, and drafting descriptions for those with the model reading the DDL plus sample rows, then having an analyst approve or correct each one, is roughly two analyst-days. That is a better-understood two days than "improve the retriever."

The 29 are a retrieval problem and have conventional fixes: index the table card text rather than just the name, run BM25, the standard keyword-overlap score, alongside embeddings so that an exact token like refunds is not diluted, and expand the selected set along declared foreign keys so that pulling fct_orders also considers dim_customers. The query-side rewriting from topic 9.7 applies unchanged here, since "churned" needs to reach a card that says "lapsed" and "cancelled".

What a miss looks like from the outside#

A syntax error announces itself. The database refuses the statement, the service logs it, the user sees a failure and rephrases, and the whole event is visible in a dashboard within a minute. Nobody makes a decision on it.

A schema-selection miss produces a valid query over a plausible neighbour. Asked for last month's order count, an agent that failed to retrieve fct_orders and settled for orders_staging — which the ingestion job writes before deduplication and which retains test orders and cancelled rows — returns 1,284,930 against a true 1,190,441. The query parses, executes in 180ms, returns one clean integer, and is 8% high, which is inside the range where a number looks like a number. It goes on slide four. There is no error anywhere in the system, no alert, and no record that anything unusual happened, which is why the reconciliation that catches it happens six weeks later, if it happens.

The 8% is worth deriving rather than accepting, because it is the whole reason this failure survives. Subtract the true count from the returned one and divide by the true count: the surplus is the duplicated rows plus the test orders plus the cancelled ones, and against a base of 1,190,441 it comes to roughly the 8% quoted. Read that as a gap rather than as a level. Nobody knows last month's order count to within 8% by memory, and month-on-month order volume moves by more than that for ordinary commercial reasons, so the error sits underneath the resolution of every check a reader could apply by eye. An error of the same absolute size on a figure people do know precisely — a headcount, a number of open contracts — would be caught in the meeting. The detectability of a wrong number is a property of the reader's prior, not of the size of the mistake.

This asymmetry is the reason the component metric is worth the effort of building an evaluation set. The failure mode you are engineering against does not produce failures; it produces answers.

Recall as a ceiling, and the column-level metric underneath it#

Recall is a ceiling, not a floor. On the 300 questions, the subset where recall was 1 still produced correct final answers only 61% of the time, because having the right tables in the prompt says nothing about whether the model joined them correctly, filtered the right date column, or aggregated at the right grain, meaning the level of detail one row of the result stands for. Reading a high recall number as progress on answer quality is the standard error of the first month.

That 61% is a different measurement from the 61% in the release example above, and the coincidence is worth pointing at so it does not get read as one number appearing twice. The earlier one was end-to-end accuracy over all questions after a hypothetical regression; this one is answer accuracy restricted to the questions where retrieval was perfect. The second is conditional on the first component having done its job, which is the only reason it can be read as a statement about everything downstream of retrieval.

Two further boundaries are worth stating. Recall without a budget is trivially 100%, achieved by retrieving everything, so the number is meaningless unless it is always quoted as recall at a stated budget along with the mean tokens actually spent — 5,900 of 8,000, in this system. And table-level recall says nothing about columns. A table with 340 columns cannot go into the prompt whole, so something selects a subset, and a question that needs orders.fulfilment_region will fail even though orders was retrieved. Once the wide tables are in play, the same all-or-nothing metric gets computed over (table, column) pairs extracted from the gold SQL, and it always reads lower than the table version.

Both boundaries are the same defect seen twice. A recall score is a score relative to a stated constraint, and it rises whenever the constraint is relaxed or the unit of counting is coarsened: drop the budget and it goes to 100%, count tables rather than columns and it goes up again, because a question needing four particular columns out of a wide table scores a hit the moment the table arrives. Quoting the mean spend of 5,900 alongside the score guards against the first of those, though only partly — a mean says nothing about the questions that pushed against the 8,000, and those are the ones where truncation decided the answer.

The metric this chapter builds is deliberately harsh: no partial credit, a fixed budget, a gold set extracted by a parser rather than a person. That harshness makes it usable as a diagnostic, because a number moving only when the system improves beats a higher one that also moves when questions get easier.

Getting the right tables into the prompt is the first component. What you say about those tables once they are there — whether the model sees column names alone, or descriptions, or the values those columns actually hold — decides most of what happens next, and it is where the largest single accuracy gain in this pipeline comes from.