The prompt needs column values, not only column names

Once a retriever has chosen which tables a language model may see, something has to decide what to say about them. That description is the whole input to the generation step — the step that turns an English question into a SQL statement — and its output quality is mostly a function of those inputs. Which inputs to include is a design decision rather than a property of the model, and six kinds of context can go into a schema block. They are not equally valuable, and the ordering is not the one most teams guess.

A filter that matches nothing#

Someone asked for the count of completed orders in July. The retriever did its job — fct_orders came back first, well inside budget — and the model wrote:

SELECT COUNT(*) FROM analytics.fct_orders
WHERE status = 'completed'
  AND order_date >= '2025-07-01' AND order_date < '2025-08-01';

Zero rows. The column holds COMPLETE, PEND, CANC, REFND and PART_REFND, five uppercase abbreviations chosen in 2019 by whoever wrote the checkout service. Nothing in the schema said so. The prompt contained status VARCHAR(16), which is true and useless, and the model did the only thing available to it, which was to guess a value from the English of the question.

Follow what the model had to work with, because every step of that guess was forced. It was asked about completed orders. It was shown a column called status and told the column holds text of up to a certain length. Nothing anywhere in its input listed what text. A filter has to name a literal, so a literal had to come from somewhere, and the only vocabulary in the room was the question's own. The word in the question was "completed", so the model wrote 'completed'. Every one of those steps is the step a competent person would take with the same information.

The tempting belief here is that the type declaration is a defence — that VARCHAR(16) constrains what may sit in that column, so a statement passing whatever checks the type implies is a statement filtering on something real. It is tempting because in most programming languages a type does bound the values: an enum admits exactly its members, and the compiler refuses anything else. In a warehouse column the type bounds the shape and nothing else. VARCHAR(16) says the contents are text of some bounded length, and 'completed', 'COMPLETE' and 'banana' are all text of bounded length, so the declaration is exactly as satisfied by a literal that matches no row as by one that matches millions.

That is the shape worth recognising: a quantity that stays constant while the dangerous property varies. VARCHAR(16) is the same string for a column holding five codes, for one holding unbounded free text, and for one that is empty. It cannot move when the domain moves, so it cannot warn about the domain. Everything downstream inherits that blindness: the statement above parses, type-checks, passes any permission check, executes without error and returns a number. The only thing wrong with it is a fact about the data that no part of the machinery was ever told.

What each input buys#

Measured on the 300-question evaluation set from chapter 1, holding retrieval fixed at 100% recall so that generation is the only thing moving, adding each input class in turn:

Read that third column as gaps rather than as levels, and check that the gaps close. Take the accuracy of each row minus the accuracy of the row above it and the five differences are eight, sixteen, six, five and two points. Add those five to the 44% the bare DDL scored and the total is the 81% on the last line, with nothing left over. The column is a single cumulative ladder rather than five independent experiments run against a common baseline.

That closing is worth a moment, because it licenses one reading and forbids another. What it licenses is treating the figures as marginal: each row is measured with everything above it already present, so the number beside a row is what that input was worth given the ones before it. What it forbids is lifting a row out and quoting it on its own. The 16 points credited to sampled values are 16 points on top of column descriptions, and descriptions and sampled values overlap in what they fix — a sufficiently careful description of status could have named the five codes and collected some of the same gain. Reorder the ladder and every number in it moves. There is no way to recover, from this table, what any single input would be worth in isolation, and the experiment that would answer that question was not the one that was run.

The single largest step is sampled values, worth 16 percentage points for 900 tokens, and it is the input most often left out because it is the only one that requires a job running against the warehouse rather than a file someone writes.

Ranking the rows a second way reverses much of that impression, and both rankings are worth carrying. By accuracy gained, the order is sampled values, then descriptions, then joins, then examples, then the dialect note. By accuracy gained per token spent, it is almost the opposite at the ends: the join declarations buy six points for 300 tokens, which is more accuracy per token than any line in the column, and the retrieved examples buy five points for 1,700, which is the least. Descriptions are second by absolute gain and second-worst by return per token. Which ranking applies depends on what is scarce. When the budget is not binding, take the absolute gains in order. When it is — which is the normal case, since these tables compete with the schema budget chapter 1 sets — the per-token ranking is the one that decides what gets cut first.

The descriptions step is worth reading carefully too, because eight points for 1,100 tokens is the second-best trade in the table by that first ranking and the work is unglamorous. Most of what it buys is disambiguation between near-synonyms. This warehouse has order_total, net_total, gross_amount and line_amount across two tables, and a model choosing among them from names alone picks correctly rather less than half the time. A description that says what the column measures, in what unit, relative to what — "order value in EUR cents, including tax, excluding shipping; equals the sum of line_amount over the order's items plus tax" — removes the choice by making three of the four obviously wrong for a given question. The descriptions that do not help are the ones generated from the column name, and a catalogue full of "the order total" for order_total costs tokens and buys nothing.

One thing the token column does not support is a comparison against production spend. These are means over a configuration where retrieval was pinned to perfect recall, so what got rendered was the set of tables the gold query needed. A production retriever fills its budget with whatever it ranked highest, which is a different set of a different size, and the mean token spend chapter 1 reports for the live system is measuring that instead. Two means over the same 300 questions can disagree without either being wrong when the thing being averaged is not the same thing.

A table card is a rendered artefact, not a schema dump#

The schema block the model sees is generated nightly by a profiling job and cached. Here is what one table looks like in the prompt, at a level of detail that fits the budget for eight tables:

-- analytics.fct_orders
-- One row per order. Excludes test orders (is_test = FALSE enforced in the view).
-- Loaded hourly; watermark: MAX(order_date) is typically 2-3h behind now().
CREATE TABLE analytics.fct_orders (
  order_id      VARCHAR   -- PK. e.g. 'ORD-8814402'  (3.1M distinct)
  customer_id   VARCHAR   -- FK -> dim_customers.customer_id  (412k distinct)
  status        VARCHAR   -- 5 values: COMPLETE (2.79M), CANC (188k),
                          --   REFND (94k), PART_REFND (21k), PEND (7k)
  channel       VARCHAR   -- 4 values: WEB, IOS, ANDROID, PARTNER_API
  order_total   NUMBER    -- Order value in EUR cents, incl. tax, excl. shipping.
                          --   e.g. 4599 = EUR 45.99. min 0, p50 3,410, max 1,204,900
  currency_code VARCHAR   -- ISO-4217. 3 values: EUR (2.6M), GBP (390k), USD (98k)
  order_date    TIMESTAMP -- UTC. Booking time, not fulfilment. Range 2019-03-04 .. now
  is_deleted    BOOLEAN   -- Soft delete. Always filter is_deleted = FALSE.
);
-- Grain: [order_id]
-- Joins: fct_order_items ON order_id  (1:N, mean 3.2 items per order)
--        dim_customers   ON customer_id (N:1)

Every line of that block removes a specific class of wrong query. The comment on order_total removes an entire genre of answer that is off by a factor of 100 and looks reasonable. The is_deleted note removes a 1.4% overcount that nobody would ever notice. The distinct counts tell the model which columns are worth grouping by. The join line, with its mean of 3.2 items per order, is the fact that chapter 5 turns into a check.

The card also checks itself, in one place, and the check is worth performing because of what it settles. Add the five row counts in the status comment and they come to 3.1M exactly, which is the distinct order_id count three lines above. That closes with no residual, and three claims fall out of it. The grain line at the bottom of the card, which asserts one row per order, is corroborated rather than merely stated: one row per order is what makes the distinct order count equal to the row count that the status values partition. The other two are nowhere on the card and are readable off it anyway. The five values really are the whole domain rather than the five most common, because a partial list would leave a remainder. And status is never null, since a null row would belong to no listed value and the sum would come up short.

The currency_code line does not close the same way. Its three counts fall a few thousand rows short of 3.1M, and the temptation is to read that as evidence of something — nullable rows, a fourth currency, a stale profile. It is evidence of nothing. The largest of the three counts is quoted to two significant figures, and the rounding that permits is wider than the whole shortfall, so a true count anywhere inside that rounding closes the column exactly. A sum only closes as tightly as its coarsest term allows. That is what separates the two lines: the status counts are quoted finely enough that landing on 3.1M exactly cannot be an artefact of rounding, and the currency counts are not.

One thing on this card cannot be settled from the card itself, and it is worth knowing rather than guessing. The profiling query in the next section restricts itself to the last 90 days, while the card carries a date range reaching back to 2019 and distinct counts that read as lifetime figures. Either the counts come from a query other than the one shown, or the domain list and the counts cover different windows. Which of those is true changes what the distinct counts mean and is not derivable from anything printed here, so it is a question to put to whoever owns the profiler rather than an inference to make.

Two of the card's comments repay being read as arithmetic. order_total holds EUR cents, and the example fixes the conversion: 4599 is EUR 45.99, so reading the raw column as euros multiplies every currency figure by a hundred. That is the factor of 100 above, and the p50 of 3,410 is the sanity check that catches it — read as cents it is a median basket of a few tens of euros, which is an ordinary e-commerce order, and read as euros it is a few thousand, which is not. The is_deleted figure works the other way. A soft delete leaves the row in the table with a flag set, so a COUNT(*) that omits the filter counts rows the business considers gone; here that is 1.4% of them. Nobody queries an order count they know to within 1.4%, so the error passes every check a reader can apply by eye, which is precisely why the note has to be in the prompt rather than in someone's head.

Sampling values without shipping the database into the prompt#

The rule that keeps this affordable is that the treatment depends on cardinality. A column with fewer than about 50 distinct values gets its full domain — the complete list of values that column actually holds — with row counts, since that is both a value list and a distribution hint. A column with more gets three example values and a format description, because the model needs to know that order_id looks like ORD-8814402 and does not need 3.1 million of them.

The split is doing something more specific than saving tokens. For a small domain, the useful fact is membership: which literals exist, so that a filter can name one. For a large domain, membership is not usable — the model will never be shown 3.1 million order ids, and a filter on a specific order comes from the question rather than from the card — so the useful fact is shape, enough for the model to recognise an identifier when the question contains one and to avoid inventing a format. Two different questions, two different answers, one threshold between them.

-- profile_columns.sql, run nightly per table in the mart layer
SELECT
  'status' AS column_name,
  APPROX_COUNT_DISTINCT(status) AS n_distinct,
  ARRAY_AGG(DISTINCT status) WITHIN GROUP (ORDER BY status) AS domain
FROM analytics.fct_orders
WHERE order_date >= DATEADD(day, -90, CURRENT_DATE())
HAVING APPROX_COUNT_DISTINCT(status) <= 50;

Restricting the profile to the last 90 days matters more than it looks. A status value that was retired in 2021 is still in the table and will otherwise be presented to the model as a live option, and a question about last month will get a filter for a code that has not been written in four years. Over 220 mart tables — the curated, business-facing layer the data team maintains on top of the raw ingested data — the sweep runs in about four minutes on a small warehouse and costs a few cents a night, which is not a number worth optimising.

Two columns must never be profiled: anything holding personal data and anything holding secrets. Sampled values go into a prompt, prompts go into logs, and a dim_customers.email card that helpfully lists three real addresses has just copied production PII into your observability stack. The profiler takes a deny-list of columns and emits -- values withheld (PII) for them, which also tells the model not to invent a filter on that column.

Declared joins stop the model inventing relationships#

Three hundred tokens of join declarations is the cheapest line in the table and it removes a failure that is hard to spot in review. Given fct_orders and dim_customers in the same prompt with no relationship stated, a generator will produce a join on whatever pair of columns looks like it should work, and ON o.user_id = c.id is a perfectly natural guess in a warehouse where neither column exists. That one fails loudly. The one that does not fail is a join on a column that exists and is not the key — ON o.account_id = c.account_id in a schema where accounts and customers are different things and the columns coincidentally share a name, producing a result that is smaller than it should be by however many customers belong to accounts with several customers.

Cheapest there means value for money rather than smallest bill. The dialect declaration costs fewer tokens outright, and on the accuracy-per-token reading above no other input in the block returns as much as the join line does.

The difference between a join that fails loudly and one that does not is worth stating as a rule, because it generalises past this pair of tables. A database validates the existence of every name in a statement and nothing about their meaning. A join on names that do not exist is therefore caught by the only check the database performs, and arrives as an error. A join on names that do exist passes that check completely, whatever the columns mean, and the consequence shows up only in the row count: a join on a non-key column changes how many result rows each input row produces, and a changed multiplicity moves every count and every sum computed over the result. Chapter 5 is about that effect in the case where it is easiest to trigger, a fact table joined to its own line items.

The declaration carries three facts: the key pair, the direction, and the multiplicity. fct_order_items ON order_id (1:N, mean 3.2) tells the model that this join adds rows, which occasionally makes it avoid a join it did not need. Multiplicity is also the input to the check in chapter 5, so the same metadata pays twice, and it is worth keeping in one place rather than duplicating it between the prompt renderer and the verifier.

What to leave out is a shorter list than what to include. Indexes, clustering keys, storage parameters, constraint names and column ordinal positions cost tokens and change nothing about which query gets written. Views should be presented as tables, since the generator does not need to know that analytics_marts.orders is a view over analytics.fct_orders, and telling it invites a query against the base table it cannot read.

Ordering inside the block matters at the margin. The retriever produces a ranking, and rendering in that order with the budget consumed top-down means that when the block is truncated, what falls off the end is the table the retriever was least confident about. Truncating a table card halfway leaves a partial column list that reads as complete, so the renderer drops whole cards rather than lines, and records in the answer log how many candidates were dropped.

The examples are retrieved, not fixed#

Few-shot examples — pairs of question and approved SQL, shown in the prompt so the model can pattern-match on them — work here for a reason specific to SQL: they are the only place house conventions get expressed. The fiscal quarter starts in February. Revenue is converted at the rate in dim_fx_daily, joined on the booking date rather than today. Enterprise segment means segment = 'ENT' and is_internal = FALSE, because the sales demo accounts carry the enterprise segment too. None of that is derivable from the DDL and all of it appears in the query log.

Selecting the examples by similarity to the incoming question beats a fixed block, because six examples chosen for the question spend the same tokens as six generic ones and carry the conventions that apply. A curated pool of about 60 pairs — question, approved SQL — covers the common shapes, and it is maintained by the same analysts who approve the table descriptions. The retrieval over that pool is the ordinary kind and does not need to be clever.

When the statement errors, repair once#

Of the 300 evaluation questions, 41 produced a statement that passed validation and then failed at execution. The errors divide into ones the database describes precisely — unknown column orders.customer_email, ambiguous column reference order_date, function DATE_SUB does not exist — and ones it describes vaguely, such as a timeout or a numeric overflow in a cast. Feeding the error text back with the original prompt and asking for a corrected statement fixed 33 of the 41 on the first attempt.

The bound is the part that matters, and it is a bound on attempts rather than on time. Repair is not a convergent process: the error text carries information for the precise errors and almost none for the vague ones, so a second and third attempt do not have a decreasing failure rate in any reliable way.

The reason convergence is the wrong expectation is visible in what the error message contains. "Unknown column orders.customer_email" names the exact token to remove, which is why the first repair attempt succeeds as often as it does — the model is being handed the location of its own mistake. A timeout names nothing: it says the statement was too slow, without saying which join or which missing predicate made it so, and a model given that text has no more information than it had before it wrote the statement. So the population that survives the first repair is enriched in exactly the errors the mechanism cannot address, and a process whose remaining cases are the ones it is worst at does not converge by being run again.

Every repaired statement goes back through validation from scratch. The repair prompt contains a database error message, and a database error message is text that came from outside the system, which makes it exactly the sort of input topic 7.5 warns about routing straight into an execution path.

Correct tables, correct joins, wrong grain#

The whole of this chapter moves the rate at which the model writes the query someone meant. It does not change what happens when it writes a different one. A prompt carrying full descriptions, live enum domains, declared join cardinalities and six relevant examples still permits a statement that joins fct_orders to fct_order_items, filters correctly, and sums an order-level column across line-item rows — a query that no amount of schema context prevents, because every table, column and join in it is correct.

The reason schema context cannot reach that failure is a matter of what a schema block is able to say. Everything in a card is a statement about what exists: these columns, these values, these keys, this multiplicity. The defect in that query is not about what exists but about what the statement does with it — summing at one grain a figure that is only meaningful at another. No fact about the tables rules it out, because the query is consistent with every fact about the tables. Catching it needs something that looks at the result and asks whether its shape matches the question, which is a different component entirely.

It also does nothing about intent. A question that says "last month" when the asker meant the last 30 days will be answered against the calendar month, correctly and unhelpfully, and the schema block has no view on that. Nothing in the data distinguishes the two readings, so no amount of profiling surfaces the ambiguity; only asking the person does.

The inputs to generation are chosen, not given, and the choice is worth making deliberately: descriptions that say what a column measures and in what unit, the actual domain of every small-cardinality column, declared keys with their multiplicity, examples retrieved for the question at hand, and the dialect. The one that is skipped most often is the one that pays most, because it is the only one that requires standing up a job against the warehouse instead of writing a file.

What matters immediately is narrower. The output of this step is a string that a model produced, sitting in a variable, about to be handed to a database connection. Nothing has yet constrained what that string may do.