A data agent end to end

By the end of this chapter, you can build the shop's warehouse, have a recorded model write SQL for a business question, run that SQL and catch the wrong number it returns, answer the same question from one defined metric, and score both approaches on five golden questions. It is the whole result of this book in rough form: every later chapter makes one part of it solid.

You need Python 3.12.15 and SQL, and nothing else: no GPU, no cloud account and no API key. Every example runs offline on DuckDB 2.0.0, against data the book generates, and every model reply it uses was recorded once from an open model and is replayed from a file.

The request this book answers usually arrives as one sentence: let people ask the data. Someone in finance wants net revenue by region for last month without waiting for a dashboard, and someone hands you a language model. The obvious build is to give the model the warehouse's schema and the question, run the SQL it writes and show the result. On a hand-picked question that build often works, which is enough to demo; on this book's five golden questions it gets 0 of 5 right. Its failures are the problem, because most of them don't look like failures. A query that fails to run is an error someone sees. A query that runs and returns a number a few percent too high goes into a slide.

This chapter builds the obvious version first, runs it, and shows exactly where its number goes wrong. Then it builds the smallest version of the alternative the book recommends: an agent that can't see tables at all, only a defined metric, and that says so when the metric doesn't cover a question. Both are scored on the same five questions with checked answers, which is the habit the rest of the book depends on.

Meet the shop and its warehouse

An agent that answers questions from a warehouse can only be as right as the warehouse's definitions let it be. Real warehouses hold more than tables: they hold decisions about what counts as an order, which currency a total is in and which day a timestamp belongs to. Those decisions live in the marts, in the SQL that built them and in the heads of the people who wrote it. To see where an agent goes wrong, you need a warehouse with those decisions in it, and with the traps they create.

This book follows one case throughout: an online shop that sells outdoor and camping gear in three regions, EMEA, AMER and APAC, which we call the shop. The shop has no brand name and stands for no real business. Its data is generated by the book's own code, lib/shop_data.py, from one fixed seed, so every reader builds exactly the same rows. Orders run from 2024-02-01 to 2026-06-30, and nothing in the data depends on the day you run an example.

The shop's warehouse, which every example in this book queries, is a DuckDB database with two schemas, as an analytics engineer would build them. The raw schema holds what the source systems loaded: orders, order lines, refunds, customers, products, sessions, marketing spend and exchange rates. The analytics schema holds the marts built from them in SQL, such as fct_orders, one row per real order with its amounts in euros. lib/warehouse.py does the build. Its connect() generates the data, loads raw, runs the SQL that builds analytics, and returns a connection that can read but not write, with access to other files switched off.

1"""Build the shop's warehouse from its seed and list every table with its row count."""2from lib.shop_data import SHOP_SEED3from lib.warehouse import connect, table_sizes45con = connect()                  # generates the shop, loads raw, builds the marts, reopens read-only6print(f"seed: {SHOP_SEED}")7for table, rows in table_sizes(con):8    print(f"{table}: {rows:,}")910(first, last, test_orders, cancelled), = con.execute("""11    SELECT (SELECT min(order_date_local) FROM analytics.fct_orders),12           (SELECT max(order_date_local) FROM analytics.fct_orders),13           (SELECT count(*) FROM raw.orders WHERE is_test),14           (SELECT count(*) FROM analytics.fct_orders WHERE status = 'cancelled')15""").fetchall()16print(f"first order day: {first}")17print(f"last order day: {last}")18print(f"test orders in raw.orders: {test_orders}")19print(f"cancelled orders in fct_orders: {cancelled}")

seed: 2718
raw.customers: 3,100
raw.fx_rates: 2,649
raw.marketing_spend: 2,643
raw.order_lines: 38,439
raw.orders: 15,000
raw.products: 120
raw.refunds: 1,077
raw.sessions: 60,000
analytics.dim_customers: 3,040
analytics.dim_date: 912
analytics.dim_products: 120
analytics.fct_order_lines: 38,071
analytics.fct_orders: 14,856
analytics.fct_refunds: 1,068
analytics.fct_sessions: 60,000
analytics.orders_legacy: 15,000
first order day: 2024-02-01
last order day: 2026-06-30
test orders in raw.orders: 144
cancelled orders in fct_orders: 819

The counts already show three of the warehouse's decisions. raw.orders holds 15,000 orders, and analytics.fct_orders holds 14,856: the 144 test orders, placed from the shop's own test accounts, are removed from the mart. raw.customers holds 3,100 rows and analytics.dim_customers 3,040, because some customers signed up twice with emails that differ only in case, and the test accounts are left out. The mart keeps the 819 cancelled orders, each with its status, because some questions are about cancellations.

Other decisions aren't visible in a count. EMEA prices include VAT and AMER and APAC prices don't, so gross and net revenue differ by region. Timestamps are stored in UTC, while the shop counts sales on each region's local business day. The fiscal year starts on February 1, and is named for the year it ends in. And analytics.orders_legacy, an older mart that old dashboards still read, has a column called revenue that holds a different number: gross, tax included, test orders kept. Nothing in the schema says which of these rules applies to which question.

Working rule: Build the agent against a warehouse that has your real traps in it: test rows, cancelled orders, several currencies, local business days, a fiscal calendar and at least one legacy table with a tempting name. Generate it from a seed when you can't share production data, so anyone can rebuild it. A clean demo schema hides exactly the failures you need to see.

Ask a question, get SQL from a model

With the shop's warehouse built, the obvious next step is to let a model query it. Schema-only text-to-SQL means the model is given the name and type of every column and the person's question, and nothing else, and writes the SQL. In our experience it is where many first builds start, because the schema is the one description of the warehouse that already exists, and the one the database itself keeps current. What it leaves out are the definitions, which the schema doesn't hold.

A book whose examples called a model would give a different answer on every run, and would need an API key. So every model reply in this book is recorded. A recording is a saved set of model replies with a manifest, made once by a script in the repository's recordings/ folder and replayed by the examples. The model is Qwen3-4B-Instruct-2507, which Qwen releases under the Apache 2.0 licence. It has 4.0 billion parameters and answers without a separate thinking phase, so a reply is the answer alone (model card). We run a 4-bit quantization of it, a file of about 2.5 GB pinned by its SHA-256 hash (the file), on four CPU threads with llama-cpp-python, so anyone can record it again without a GPU.

The sampling settings are the ones Qwen recommends for this model: temperature 0.7, top-p 0.8, top-k 20 and min-p 0 (model card). The seed is reset before every call. Replies are capped at 384 tokens for SQL and 256 otherwise. That cap is our choice, to keep a recording run short on a CPU, where Qwen suggests 16,384. Each recording's manifest.json names the script, the model and its file hash, the prompt's hash, the sampling, the date and each call's time.

On the examples' side, lib/model.py provides RecordedModel("schema-only-sql"), which replays the recording called schema-only-sql. It looks each reply up by the SHA-256 of the exact messages sent. A prompt that differs by one character has no recording, and the replay stops with an error instead of returning a reply to some other prompt. The prompt itself is a constant in lib/prompts.py, SCHEMA_ONLY_SQL, shared by the recording script and the examples.

The question is one a finance team asks every month: "What was net revenue in each region in March 2026?" It is also one of the book's golden questions, the questions with checked answers that the book scores against: gq-002, which the next section uses to check the answer.

1"""Send the warehouse's schema and one question to the recorded model, and print the SQL it wrote."""2from lib.golden import load_golden3from lib.model import RecordedModel4from lib.prompts import schema_only_messages5from lib.warehouse import connect, describe_schema67schema = describe_schema(connect())8question = next(q for q in load_golden() if q.question_id == "gq-002")9messages = schema_only_messages(schema, question.question)1011print(f"tables in the prompt: {schema.count('CREATE TABLE')}")12print(next(line for line in schema.splitlines() if "analytics.fct_orders " in line))1314model = RecordedModel("schema-only-sql")       # replays the pinned model's real replies15call = model.call_for(messages)16print(f"prompt tokens: {call['prompt_tokens']}")17print(f"question: {question.question}")18print(call["reply"])

tables in the prompt: 16
CREATE TABLE analytics.fct_orders (order_id INTEGER, customer_id INTEGER, region VARCHAR, country VARCHAR, channel VARCHAR, status VARCHAR, placed_at TIMESTAMP, order_date_local DATE, currency VARCHAR, line_count BIGINT, gross_revenue_eur DECIMAL(12,2), net_revenue_eur DECIMAL(12,2), discount_eur DECIMAL(12,2), tax_eur DECIMAL(12,2), _loaded_at TIMESTAMP);
prompt tokens: 827
question: What was net revenue in each region in March 2026?
```sql
SELECT
    region,
    SUM(net_revenue_eur) AS net_revenue
FROM
    analytics.fct_orders
WHERE
    order_date_local >= '2026-03-01'
    AND order_date_local < '2026-04-01'
GROUP BY
    region
ORDER BY
    net_revenue DESC;
```

The prompt lists all 16 tables of both schemas as CREATE TABLE statements, 827 tokens with the instructions and the question. The line for analytics.fct_orders is the one that matters here. It has a net_revenue_eur column, an order_date_local column, a region and a status.

The SQL the model wrote is what many analysts would write first. It picks the mart over the raw tables and over orders_legacy. It sums net_revenue_eur, not gross. It filters on order_date_local, so each order lands on its region's business day instead of its UTC day. It groups by region and gives the column a sensible name. If you were reviewing this query in a pull request, you might approve it. The question is whether the number it returns is right, and nothing in this output can tell you.

Working rule: Treat schema-only text-to-SQL as the baseline to beat, not as the product. Run it once on your own warehouse and keep its replies, because it shows you which of your definitions the schema doesn't carry. Record every model reply your tests depend on, pinned to a model file, a prompt hash and fixed sampling, so a failure you find can be reproduced by someone else.

Run the SQL and check the number

The model's query for March 2026 looks right, and it runs. A query that fails gets caught, because someone sees the error. A query that runs and returns a plausible number is the failure that matters, because nothing marks it as wrong. The only way to know is to compare the result with an answer someone has checked.

A golden question is a question with its checked answer: the golden SQL, written against the owner's definition, the golden result that SQL returns, and the kind of answer expected. The golden SQL is checked by running it, which chapter 3 covers. The book keeps its golden questions in examples/data/golden/questions.toml, and lib/golden.py loads them. The golden SQL for gq-002 follows the shop's definition of net revenue, which the finance team owns: revenue in euros after discounts and before tax, from real orders that weren't cancelled, on the order's local business day.

1"""Run the SQL the model wrote for net revenue by region in March 2026, and check it against the golden result."""2from lib.golden import golden_result, load_golden, results_match3from lib.model import RecordedModel4from lib.prompts import schema_only_messages5from lib.tools import extract_sql, run_sql6from lib.warehouse import connect, describe_schema78con = connect()9question = next(q for q in load_golden() if q.question_id == "gq-002")10reply = RecordedModel("schema-only-sql").complete(schema_only_messages(describe_schema(con), question.question))1112model_answer = dict(run_sql(con, extract_sql(reply)).rows)13golden = dict(golden_result(con, question))14cancelled = dict(con.execute("""15    SELECT region, sum(net_revenue_eur) FROM analytics.fct_orders16    WHERE status = 'cancelled' AND order_date_local BETWEEN DATE '2026-03-01' AND DATE '2026-03-31'17    GROUP BY region""").fetchall())1819for region in sorted(golden):20    print(f"{region} model: {model_answer[region]:,.2f}")21    print(f"{region} golden: {golden[region]:,.2f}")22    print(f"{region} cancelled orders: {cancelled[region]:,.2f}")23print(f"model total: {sum(model_answer.values()):,.2f}")24print(f"golden total: {sum(golden.values()):,.2f}")25print(f"overstated by: {sum(model_answer.values()) / sum(golden.values()) - 1:.1%}")26print(f"matches golden: {results_match(model_answer.items(), golden.items())}")

AMER model: 55,207.80
AMER golden: 51,762.14
AMER cancelled orders: 3,445.66
APAC model: 40,832.21
APAC golden: 35,296.93
APAC cancelled orders: 5,535.28
EMEA model: 66,502.78
EMEA golden: 63,023.76
EMEA cancelled orders: 3,479.02
model total: 162,542.79
golden total: 150,082.83
overstated by: 8.3%
matches golden: False

The model's query says EMEA had 66,502.78 euros of net revenue in March 2026. The golden result is 63,023.76. The difference is 3,479.02, which is exactly the net revenue of EMEA's cancelled orders that month, and the same holds for AMER and APAC. Across the three regions, the model's total of 162,542.79 overstates the golden 150,082.83 by 8.3%.

The query wasn't careless. The schema shows a status column, but nothing in it says that cancelled orders don't count toward revenue. That rule is in the finance team's definition, and a model reading column names can't see it. A larger model might guess the rule more often, but a guess is the wrong mechanism: the next question may depend on a rule nobody would guess, such as which day a refund belongs to.

Notice also what the check needed. It compared two results, not two queries: the model's SQL and the golden SQL are written differently, and that doesn't matter. What matters is whether they return the same rows. The example's last line, matches golden: False, comes from results_match() in lib/golden.py, which every score in this book uses.

Working rule: Never accept an agent's number because its SQL looks right. Check it against a golden result before anyone relies on it, and when the two differ, find the rows that explain the difference before you change anything. Expect the difference to be a definition, such as which orders count, more often than a syntax mistake.

Answer from one defined metric

The model's query for March got the number wrong because the rule it needed wasn't in the schema. The fix this book recommends is to stop asking the model for SQL over tables, and to give it defined metrics instead. A metric is a business measure written down once, by the people who own it: a name, a description a person can check, one aggregate over one mart, the rule for which rows count, and the dimensions it may be grouped and filtered by. The model's job shrinks to choosing a metric and its arguments. Code that you wrote and tested turns that choice into SQL.

This chapter has exactly one metric, net_revenue, in lib/metrics.py. Its rule for which rows count is the finance team's: status <> 'cancelled' on analytics.fct_orders. Its dimensions are region, channel, fiscal_year, fiscal_quarter, calendar_year and calendar_month, each mapped to one SQL expression. A metric request names the metric, the dimensions to group by and one value per filtered dimension, and compile_request() turns it into SQL. Dimension names come only from that list, and filter values are passed as query parameters, never pasted into the SQL text.

The model reaches the metric through tools. A tool is a function the model may ask the code to run, by replying with a JSON object that names it and gives its arguments. This agent has two, in lib/tools.py. query_metric computes a metric for a request. cannot_answer declines, with a one-sentence reason, when no metric covers the question. The loop that connects them is the book's own, run_agent() in lib/agent.py. It sends the messages, reads one tool call from the reply, runs the tool and stops when the tool returns an answer, after at most three model calls. The prompt, METRIC_TOOLS in lib/prompts.py, lists the metric's description and each dimension's meaning, including when the fiscal year starts. It lists no tables.

The recording metric-tool-runs holds the same model's replies to this prompt, made the same way as schema-only-sql. The example asks the agent gq-002, net revenue by region in March 2026.

1"""Answer the same question from one defined metric: the model picks the metric, the code writes and runs the SQL."""2from lib.agent import run_agent3from lib.golden import golden_result, load_golden, results_match4from lib.metrics import METRICS, NET_REVENUE5from lib.model import RecordedModel6from lib.prompts import metric_tools_prompt7from lib.tools import Toolbox8from lib.warehouse import connect910con = connect()11question = next(q for q in load_golden() if q.question_id == "gq-002")12print(f"metric: {NET_REVENUE.name}")13print(f"definition: {NET_REVENUE.description}")1415system = metric_tools_prompt(METRICS.values())16trace = run_agent(question.question, system, RecordedModel("metric-tool-runs"), Toolbox(con))17answer = trace.answer18print(f"request: {trace.steps[0].arguments}")19print(answer.provenance.sql)20print(answer.show())21print(answer.provenance.describe())22print(f"matches golden: {results_match(answer.rows, golden_result(con, question))}")

metric: net_revenue
definition: Revenue in EUR after discounts and before tax, from real orders that weren't cancelled, counted on the order's local business day. Returned orders count; refunds are not subtracted.
request: {'metric': 'net_revenue', 'group_by': ['region'], 'filters': {'calendar_month': 3, 'calendar_year': 2026}}
SELECT o.region AS region, round(sum(o.net_revenue_eur), 2) AS net_revenue
FROM analytics.fct_orders AS o
JOIN analytics.dim_date AS d ON d.date_day = o.order_date_local
WHERE o.status <> 'cancelled' AND d.calendar_month = $1 AND d.calendar_year = $2
GROUP BY o.region
ORDER BY o.region
region  net_revenue
AMER    51,762.14
APAC    35,296.93
EMEA    63,023.76
source: metric (net_revenue)
filters: {'calendar_month': 3, 'calendar_year': 2026}
role: analyst
data as of: 2026-07-02 02:00:00
matches golden: True

The model asked for net_revenue grouped by region, filtered to calendar month 3 of calendar year 2026. The compiled SQL applies the cancelled-orders rule, joins the date dimension on the local business day and passes the month and year as parameters. All three regions come out at the golden result, and the last line reads matches golden: True.

The answer also carries its provenance: the metric that produced it, the filters, the role it was computed for, and how fresh the data is. The data as of line is when the newest row in fct_orders was loaded, read from its _loaded_at column. A person reading this answer can see which definition produced the number and how current it is, without reading SQL.

One metric is a long way from a semantic layer. It can't join through entities, it has one grain and it holds one definition. Chapter 5 replaces it with a semantic layer that does those things. The shape is already the one the book keeps: the model chooses a governed definition, and code writes the SQL.

Working rule: Route a question to a defined metric before you let a model write SQL. Give the model the metric's description and its dimensions, and let it return a request, which your code validates and compiles. Write SQL from a model only for questions no metric covers, and only behind guards.

Score both on five golden questions

One question answered from the metric shows the approach can work. It doesn't show that it works more often than schema-only SQL. For that you score both on a set of golden questions, under the same comparison rule, and count the outcomes. A score on five questions is a smoke test, not a measurement, but it already separates outcomes that a single accuracy figure hides.

Each answer gets one of four outcomes. It is correct when the result matches the golden result, or when it declines a question that should be declined. It is wrong when it returns a different result, or returns any result for a question that should be declined. It is an error when the SQL doesn't run. It is declined when it says it can't answer a question that has an answer. A decline is not a wrong number, and a score that counts the two together hides the difference this book cares about most.

The comparison rule, results_match() in lib/golden.py, accepts an answer when it has as many rows as the golden result and its columns include every golden column's values. Columns are matched by their values, not their names, so column order doesn't matter, and neither do extra columns, such as a fiscal_year column beside the total for that fiscal year. Row order doesn't matter unless the question says it does. Numbers must agree within 0.05 percent, or within half a cent. Chapter 3 discusses each of these choices.

The five golden questions cover the traps one at a time:

1"""Score both approaches on five golden questions: schema-only SQL, and the agent with one defined metric."""2from collections import Counter34import duckdb5from lib.agent import run_agent6from lib.golden import golden_result, grade, load_golden7from lib.metrics import METRICS8from lib.model import RecordedModel9from lib.prompts import metric_tools_prompt, schema_only_messages10from lib.tools import Toolbox, extract_sql, run_sql11from lib.warehouse import connect, describe_schema1213con = connect()14schema, system = describe_schema(con), metric_tools_prompt(METRICS.values())15sql_model, metric_model = RecordedModel("schema-only-sql"), RecordedModel("metric-tool-runs")16totals = {"schema-only": Counter(), "metric": Counter()}1718print("question  kind     schema-only  metric")19for q in load_golden()[:5]:      # the first five; chapter 3 builds the full set20    golden = golden_result(con, q)21    try:22        sql = extract_sql(sql_model.complete(schema_only_messages(schema, q.question)))23        sql_outcome = grade(q, run_sql(con, sql), golden)24    except duckdb.Error:25        sql_outcome = "error"26    metric_outcome = grade(q, run_agent(q.question, system, metric_model, Toolbox(con)).answer, golden)27    totals["schema-only"][sql_outcome] += 128    totals["metric"][metric_outcome] += 129    print(f"{q.question_id}    {q.kind:<7}  {sql_outcome:<11}  {metric_outcome}")3031for name, counts in totals.items():32    for outcome in ("correct", "wrong", "error", "declined"):33        print(f"{name} {outcome}: {counts[outcome]} of {sum(counts.values())}")

question  kind     schema-only  metric
gq-001    answer   error        correct
gq-002    answer   wrong        correct
gq-003    answer   error        correct
gq-004    answer   wrong        declined
gq-005    decline  wrong        correct
schema-only correct: 0 of 5
schema-only wrong: 3 of 5
schema-only error: 2 of 5
schema-only declined: 0 of 5
metric correct: 4 of 5
metric wrong: 0 of 5
metric error: 0 of 5
metric declined: 1 of 5

Schema-only SQL gets 0 of 5 questions right. Its queries fail to run on 2 of 5 questions: for gq-001 and gq-003 the model filtered fct_orders on fiscal_year and calendar_year, columns that exist only in dim_date. Those failures are visible, which makes them the cheap ones. Its wrong answers, on 3 of 5 questions, are not. gq-002 counts cancelled orders. gq-004 counts customers whose first order fell in calendar 2026 instead of fiscal 2026. And gq-005, a forecast the warehouse can't make, gets a query over the quarter that contains today's date, which runs and returns a total instead of saying the question can't be answered.

The agent with one metric gets 4 of 5 questions right, including the decline for the forecast, and makes 0 of 5 wrong answers. It declines gq-004, which the warehouse can answer but net_revenue can't, since it counts revenue, not customers. That decline is the cost of the approach, and it is the right cost. A person who hears "I can't answer that from the metrics I have" knows to ask someone, while a person who hears a wrong count doesn't.

Five questions can't support a claim about the model or about text-to-SQL in general. They show something narrower: on this warehouse, the failures came from definitions the schema doesn't hold, and moving the definitions into code removed them. Chapter 3 builds the golden set to 48 questions, with a held-out split, and every later chapter reports its numbers on it.

Working rule: Score every change on golden questions written from your own work, and report correct, wrong, error and declined separately. Include questions that should be declined from the start, or an agent that never declines will look perfect. Use a handful of questions as a smoke test while you build, and a larger set with a held-out split before you claim anything.

Read the trace of one run

A score says how often the agent was right. A trace says why, for one run. The trace is every step of one agent run, kept by run_agent(): each model reply, and each tool result sent back. Reading traces is how you find out whether a correct outcome was correct for the right reason, and what a decline was based on. The example prints the trace of gq-004, the question the agent declined in the five-question score.

1"""Print the trace of one recorded agent run: every model reply and tool result, then what the run returned."""2from lib.agent import run_agent3from lib.golden import load_golden4from lib.metrics import METRICS5from lib.model import RecordedModel6from lib.prompts import metric_tools_prompt7from lib.tools import Toolbox8from lib.warehouse import connect910con = connect()11question = next(q for q in load_golden() if q.question_id == "gq-004")12system = metric_tools_prompt(METRICS.values())13trace = run_agent(question.question, system, RecordedModel("metric-tool-runs"), Toolbox(con))1415print(trace.show())16print(f"model calls: {sum(step.kind == 'model' for step in trace.steps)}")17print(f"answered: {trace.answer.answered}")18print(f"reason: {trace.answer.reason}")

question: How many customers placed their first order in fiscal year 2026?
step 1 (model):
  {"tool": "cannot_answer", "arguments": {"reason": "The data does not include information about the number of customers who placed their first order in a fiscal year."}}
step 2 (tool):
  declined: The data does not include information about the number of customers who placed their first order in a fiscal year.
model calls: 1
answered: False
reason: The data does not include information about the number of customers who placed their first order in a fiscal year.

The run took 1 model call. The model called cannot_answer, and the tool ended the run with an answer that carries a reason instead of a result. Declining was the right move, since net_revenue can't count customers. The reason it gave is wrong, though. It says the data includes no information about the number of customers who placed their first order in a fiscal year, while analytics.dim_customers has a first_order_on column. The model saw only the metric's description and its dimensions, so it made a claim about data it couldn't see. A person reading that reason would conclude the warehouse can't answer the question, which isn't true.

Working rule: Read traces, not only scores, and read the declines first. Check that each decline's reason is about what the agent was given, such as "no metric counts customers", and not a claim about data it never saw. Treat a decline with a false reason as a defect, even when the score counts it as declined.

What goes wrong in production

The rough agent this chapter built works on five questions. These are the ways the same design fails when people depend on it, and how you'd notice each one.

A plausible number goes into a decision. Schema-only SQL that runs returns a number with no warning attached, as gq-002 showed with a total 8.3% too high. Nobody notices until someone compares it with the finance team's report, often weeks later. You notice earlier only by scoring on golden questions before release and after every change, and by showing provenance with each answer, so a reader can see which definition was used.

Definitions drift away from the metric. The metric's description says cancelled orders don't count, and its SQL enforces that. When the finance team changes a rule, such as excluding a marketplace's fees, the description, the SQL and the golden SQL all need the same change. If one is missed, the agent's answers and the golden results move apart, and the score drops. That drop is the alarm, so keep the golden questions in the same repository as the metric definitions, and review changes to both together.

The agent declines for a reason that isn't true. The trace of gq-004 declined correctly but claimed the data has no first-order dates. A person who believes that reason stops asking. You notice by reading declines in traces, and by keeping golden questions that the warehouse can answer but the metrics can't, so a false reason shows up as a decline on a question you know is answerable.

A model reply changes without anyone noticing. If an example called a model live, a new model version or a changed prompt would change every downstream number silently. The replay in lib/model.py looks replies up by the hash of the exact messages, so a changed prompt stops with MissingRecording instead of answering from an old reply. Record again on purpose, and treat a new model version like a library release: record, score and compare.

The model file's chat template isn't the publisher's. The quantized file this book records with embeds a chat template that differs from the one Qwen publishes for this model (Qwen's template). The embedded one adds an empty thinking block to an assistant turn that ends the conversation, which Qwen's own template doesn't do. Our recording runtime, recordings/local_model.py, renders every prompt with Qwen's template at a pinned revision, checked by hash, and each manifest records whether the embedded template matched. You notice by comparing the two before the first recording, not after a run of odd replies.

Stale data answers confidently. The metric's answer said when the newest row in fct_orders was loaded. A mart whose load stopped would still answer, from old data, unless something checks that date against what the question needs. Show the date with every answer at least, and decline when it's too old for the question.

The model's SQL runs with no checks. In this chapter, run_sql() in lib/tools.py executes whatever the model wrote. Here the warehouse connection is read-only and can't open other files, which limits the damage to reading. A production warehouse connection usually has more rights than that. Never give a model's SQL a connection that can write: connect it read-only to a database that holds only what the asker's role may read (chapter 7), and add guards in front of it as defence in depth.

Working rule: Before anyone outside your team uses the agent, make every failure above visible: score on golden questions in CI, show provenance and data freshness with each answer, review declines in traces, replay recorded replies by message hash, and run model-written SQL only on a read-only connection. Start with golden scoring, because it is the only one of these checks that catches a plausible wrong number.

Decisions this chapter made

• The book's running case is a shop we generate. The shop's warehouse is built from one seed by the book's own generator, with the traps a real warehouse has, so every reader gets the same rows and nothing depends on private data.
• The warehouse is read-only. warehouse.connect() returns a connection that can't write and can't open other files, so even unchecked SQL can only read.
• Schema-only SQL is the baseline, recorded for real. Its replies come from a pinned open model, Qwen3-4B-Instruct-2507 in a 4-bit file pinned by hash, with Qwen's recommended sampling and a seed reset before every call.
• Every model reply is recorded and replayed by message hash. A changed prompt fails loudly instead of reusing an old reply.
• Metrics come before SQL. The model chooses a defined metric and its arguments; code validates the request and writes the SQL.
• Declining is an answer. The agent has a cannot_answer tool, and scores count declined separately from wrong.
• Results are compared, not SQL text, with one rule for every score: the same rows, every golden column present, any order, a small tolerance.
• The loop is ours, not a framework's. It is a few dozen lines in lib/agent.py that a library release can't change. A framework's development can stop: vanna, an open-source text-to-SQL framework, was archived by its maintainers on 29 March 2026 (repository).