--- title: "Business Intelligence with Agents" chapter: "A data agent end to end" chapter_id: bi-agent-end-to-end book: business-intelligence-with-agents edition: 2026.11.3 verified: 2026-11-06 edition_code: G6KS-JFJ2 canonical: https://rollingedition.com/business-intelligence-with-agents/bi-agent-end-to-end license: CC BY 4.0 attribution: "“A data agent end to end,” chapter 1 of Business Intelligence with Agents, Edition 2026.11.3, examples passed Nov 6, 2026. By Rolling Edition, https://rollingedition.com/business-intelligence-with-agents/bi-agent-end-to-end. Text CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Code MIT, copyright Rolling Edition and contributors. Written with AI. Verified by people." --- # 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 {#meet-the-shop} 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. Example `load-the-warehouse` (Python 3.12.15 · duckdb 2.0.0), passing in Edition 2026.11.3: ```python """Build the shop's warehouse from its seed and list every table with its row count.""" from lib.shop_data import SHOP_SEED from lib.warehouse import connect, table_sizes con = connect() # generates the shop, loads raw, builds the marts, reopens read-only print(f"seed: {SHOP_SEED}") for table, rows in table_sizes(con): print(f"{table}: {rows:,}") (first, last, test_orders, cancelled), = con.execute(""" SELECT (SELECT min(order_date_local) FROM analytics.fct_orders), (SELECT max(order_date_local) FROM analytics.fct_orders), (SELECT count(*) FROM raw.orders WHERE is_test), (SELECT count(*) FROM analytics.fct_orders WHERE status = 'cancelled') """).fetchall() print(f"first order day: {first}") print(f"last order day: {last}") print(f"test orders in raw.orders: {test_orders}") print(f"cancelled orders in fct_orders: {cancelled}") ``` Output: ```text 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 {#ask-for-sql} With the [shop's warehouse](#meet-the-shop) 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](https://huggingface.co/Qwen/Qwen3-4B-Instruct-2507)). We run a 4-bit quantization of it, a file of about 2.5 GB pinned by its SHA-256 hash ([the file](https://huggingface.co/unsloth/Qwen3-4B-Instruct-2507-GGUF/tree/a06e946bb6b655725eafa393f4a9745d460374c9)), 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](https://huggingface.co/Qwen/Qwen3-4B-Instruct-2507)). 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. Example `first-question-to-sql` (Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.29.0), passing in Edition 2026.11.3: ```python """Send the warehouse's schema and one question to the recorded model, and print the SQL it wrote.""" from lib.golden import load_golden from lib.model import RecordedModel from lib.prompts import schema_only_messages from lib.warehouse import connect, describe_schema schema = describe_schema(connect()) question = next(q for q in load_golden() if q.question_id == "gq-002") messages = schema_only_messages(schema, question.question) print(f"tables in the prompt: {schema.count('CREATE TABLE')}") print(next(line for line in schema.splitlines() if "analytics.fct_orders " in line)) model = RecordedModel("schema-only-sql") # replays the pinned model's real replies call = model.call_for(messages) print(f"prompt tokens: {call['prompt_tokens']}") print(f"question: {question.question}") print(call["reply"]) ``` Output: ```text 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 {#check-the-first-number} The model's query for March 2026 [looks right](#ask-for-sql), 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. Example `run-the-model-sql` (Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.29.0), passing in Edition 2026.11.3: ```python """Run the SQL the model wrote for net revenue by region in March 2026, and check it against the golden result.""" from lib.golden import golden_result, load_golden, results_match from lib.model import RecordedModel from lib.prompts import schema_only_messages from lib.tools import extract_sql, run_sql from lib.warehouse import connect, describe_schema con = connect() question = next(q for q in load_golden() if q.question_id == "gq-002") reply = RecordedModel("schema-only-sql").complete(schema_only_messages(describe_schema(con), question.question)) model_answer = dict(run_sql(con, extract_sql(reply)).rows) golden = dict(golden_result(con, question)) cancelled = dict(con.execute(""" SELECT region, sum(net_revenue_eur) FROM analytics.fct_orders WHERE status = 'cancelled' AND order_date_local BETWEEN DATE '2026-03-01' AND DATE '2026-03-31' GROUP BY region""").fetchall()) for region in sorted(golden): print(f"{region} model: {model_answer[region]:,.2f}") print(f"{region} golden: {golden[region]:,.2f}") print(f"{region} cancelled orders: {cancelled[region]:,.2f}") print(f"model total: {sum(model_answer.values()):,.2f}") print(f"golden total: {sum(golden.values()):,.2f}") print(f"overstated by: {sum(model_answer.values()) / sum(golden.values()) - 1:.1%}") print(f"matches golden: {results_match(model_answer.items(), golden.items())}") ``` Output: ```text 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 {#answer-from-a-metric} The model's query for March [got the number wrong](#check-the-first-number) 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. Example `one-metric-answer` (Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.29.0), passing in Edition 2026.11.3: ```python """Answer the same question from one defined metric: the model picks the metric, the code writes and runs the SQL.""" from lib.agent import run_agent from lib.golden import golden_result, load_golden, results_match from lib.metrics import METRICS, NET_REVENUE from lib.model import RecordedModel from lib.prompts import metric_tools_prompt from lib.tools import Toolbox from lib.warehouse import connect con = connect() question = next(q for q in load_golden() if q.question_id == "gq-002") print(f"metric: {NET_REVENUE.name}") print(f"definition: {NET_REVENUE.description}") system = metric_tools_prompt(METRICS.values()) trace = run_agent(question.question, system, RecordedModel("metric-tool-runs"), Toolbox(con)) answer = trace.answer print(f"request: {trace.steps[0].arguments}") print(answer.provenance.sql) print(answer.show()) print(answer.provenance.describe()) print(f"matches golden: {results_match(answer.rows, golden_result(con, question))}") ``` Output: ```text 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 {#score-five-questions} One question answered [from the metric](#answer-from-a-metric) shows the approach can work. It doesn't show that it works more often than [schema-only SQL](#ask-for-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: | ID | Question | Kind | | --- | --- | --- | | `gq-001` | Net revenue in fiscal year 2026 | answer | | `gq-002` | Net revenue in each region in March 2026 | answer | | `gq-003` | Net revenue by sales channel in calendar 2025 | answer | | `gq-004` | Customers whose first order was in fiscal 2026 | answer | | `gq-005` | Net revenue next quarter | decline | Example `score-five-golden` (Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.29.0), passing in Edition 2026.11.3: ```python """Score both approaches on five golden questions: schema-only SQL, and the agent with one defined metric.""" from collections import Counter import duckdb from lib.agent import run_agent from lib.golden import golden_result, grade, load_golden from lib.metrics import METRICS from lib.model import RecordedModel from lib.prompts import metric_tools_prompt, schema_only_messages from lib.tools import Toolbox, extract_sql, run_sql from lib.warehouse import connect, describe_schema con = connect() schema, system = describe_schema(con), metric_tools_prompt(METRICS.values()) sql_model, metric_model = RecordedModel("schema-only-sql"), RecordedModel("metric-tool-runs") totals = {"schema-only": Counter(), "metric": Counter()} print("question kind schema-only metric") for q in load_golden()[:5]: # the first five; chapter 3 builds the full set golden = golden_result(con, q) try: sql = extract_sql(sql_model.complete(schema_only_messages(schema, q.question))) sql_outcome = grade(q, run_sql(con, sql), golden) except duckdb.Error: sql_outcome = "error" metric_outcome = grade(q, run_agent(q.question, system, metric_model, Toolbox(con)).answer, golden) totals["schema-only"][sql_outcome] += 1 totals["metric"][metric_outcome] += 1 print(f"{q.question_id} {q.kind:<7} {sql_outcome:<11} {metric_outcome}") for name, counts in totals.items(): for outcome in ("correct", "wrong", "error", "declined"): print(f"{name} {outcome}: {counts[outcome]} of {sum(counts.values())}") ``` Output: ```text 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 {#read-one-trace} 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](#score-five-questions). Example `print-one-trace` (Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.29.0), passing in Edition 2026.11.3: ```python """Print the trace of one recorded agent run: every model reply and tool result, then what the run returned.""" from lib.agent import run_agent from lib.golden import load_golden from lib.metrics import METRICS from lib.model import RecordedModel from lib.prompts import metric_tools_prompt from lib.tools import Toolbox from lib.warehouse import connect con = connect() question = next(q for q in load_golden() if q.question_id == "gq-004") system = metric_tools_prompt(METRICS.values()) trace = run_agent(question.question, system, RecordedModel("metric-tool-runs"), Toolbox(con)) print(trace.show()) print(f"model calls: {sum(step.kind == 'model' for step in trace.steps)}") print(f"answered: {trace.answer.answered}") print(f"reason: {trace.answer.reason}") ``` Output: ```text 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 {#end-to-end-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](#check-the-first-number) 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`](#read-one-trace) 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](https://huggingface.co/Qwen/Qwen3-4B-Instruct-2507/blob/cdbee75f17c01a7cc42f958dc650907174af0554/tokenizer_config.json)). 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](#views-per-role)), 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 {#end-to-end-decisions} - **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](https://github.com/vanna-ai/vanna)).