--- title: "Business Intelligence with Agents" chapter: "Where raw text-to-SQL gets the number wrong" chapter_id: wrong-numbers 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/wrong-numbers license: CC BY 4.0 attribution: "“Where raw text-to-SQL gets the number wrong,” chapter 2 of Business Intelligence with Agents, Edition 2026.11.3, examples passed Nov 6, 2026. By Rolling Edition, https://rollingedition.com/business-intelligence-with-agents/wrong-numbers. 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." --- # Where raw text-to-SQL gets the number wrong By the end of this chapter, you can name the five ways schema-only text-to-SQL returns a plausible wrong number on a real warehouse, show each one on the shop's warehouse, count them across a set of golden questions, and tell which a better prompt can fix and which need definitions the schema doesn't hold. 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. Its warehouse is a DuckDB database the book generates from a fixed seed, with a `raw` schema as loaded and an `analytics` schema of marts. [Chapter 1](#check-the-first-number) asked a model for net revenue by region in March 2026, given only the warehouse's schema, and got a total 8.3% too high, because the query counted cancelled orders. That was one question. This chapter asks 11 more, each written to touch one of the warehouse's traps, and runs the model's SQL for every one. The worry is common. In dbt Labs' 2026 survey of analytics engineering, 71% of respondents named "incorrect or hallucinated outputs reaching stakeholders" as a top concern ([dbt Labs, April 14, 2026](https://www.getdbt.com/blog/new-dbt-labs-report-finds-ai-driven-acceleration-is-outpacing-trust-and-governance)). The survey had 363 respondents, collected in late 2025 and early 2026, and it is a vendor's survey of people who chose to answer, so it measures worry, not how often wrong numbers ship. What it doesn't say is where the wrong numbers come from. The sections below show five places on the shop's warehouse, one at a time, and the last of them counts how often each appears. Every example replays the recording called `schema-only-sql`: the replies a pinned open model, Qwen3-4B-Instruct-2507, gave when it was shown the schema of every table and one question, and asked for one DuckDB query. [Chapter 1](#ask-for-sql) explains how the recording was made and why each reply is looked up by the hash of the exact messages. Each example then runs the model's query on the warehouse and compares the result with the golden result: the answer to the same question from SQL written against the finance team's definitions and checked by running it, kept with the question in `examples/data/golden/questions.toml`. ## Joins that fan out and inflate a sum {#joins-that-fan-out} Fan-out is what happens when a query joins a table to another that has several rows for each of its rows, and then sums a column of the first. An order has one row in the orders mart and several in the order lines, so after the join each order's amount appears once per line, and a sum over it counts the order several times. The query runs, the number has the right units, and nothing in the result marks it. An analyst who knows the grain of each table aggregates the lines per order before joining, or filters with `IN` instead of a join. A model that sees only column names has to infer the grain from them, and the schema doesn't state it. In the shop's warehouse, `analytics.fct_orders` holds one row per order with its `net_revenue_eur`, and `analytics.fct_order_lines` holds one row per line with its `product_id`. The question is one a category manager asks: "How much net revenue came from orders that included a tent in 2025?" It is golden question `gq-007`. Its golden SQL counts each order's whole net revenue once, if any of its lines is a product in the `Tents` category. Example `tent-orders-fan-out` (Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.29.0), passing in Edition 2026.11.3: ```python """Replay the model's SQL for net revenue from orders with a tent, and measure what its join to order lines adds.""" from lib.golden import golden_result, load_golden 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-007") sql = extract_sql(RecordedModel("schema-only-sql").complete(schema_only_messages(describe_schema(con), question.question))) print(sql) # The model's own joins and filters, kept as they are, so only the counting changes. matched = """FROM analytics.fct_orders f JOIN analytics.fct_order_lines fl ON f.order_id = fl.order_id JOIN analytics.dim_products p ON fl.product_id = p.product_id WHERE {product} AND EXTRACT(YEAR FROM f.placed_at) = 2025""" by_name, by_category = "p.product_name LIKE '%tent%'", "p.category = 'Tents'" (lines, orders), = con.execute(f"SELECT count(*), count(DISTINCT f.order_id) {matched.format(product=by_name)}").fetchall() once = "SELECT sum(net_revenue_eur) FROM analytics.fct_orders WHERE order_id IN (SELECT f.order_id {matched})" model_total = run_sql(con, sql).rows[0][0] counted_once = con.execute(once.format(matched=matched.format(product=by_name))).fetchall()[0][0] whole_category = con.execute(once.format(matched=matched.format(product=by_category))).fetchall()[0][0] print(f"rows the join matched: {lines}") print(f"orders behind them: {orders}") print(f"model's answer: {model_total:,.2f}") print(f"each order counted once: {counted_once:,.2f}") print(f"inflated by: {model_total / counted_once - 1:.1%}") print(f"tents and shelters, counted once: {whole_category:,.2f}") print(f"golden: {golden_result(con, question)[0][0]:,.2f}") # The same query shape with no product filter: every 2025 order joined to all of its lines. (per_order,), = con.execute("SELECT count(*) / count(DISTINCT order_id) FROM analytics.fct_order_lines").fetchall() (all_joined, all_once), = con.execute("""SELECT (SELECT sum(f.net_revenue_eur) FROM analytics.fct_orders f JOIN analytics.fct_order_lines fl ON f.order_id = fl.order_id WHERE EXTRACT(YEAR FROM f.placed_at) = 2025), (SELECT sum(net_revenue_eur) FROM analytics.fct_orders WHERE EXTRACT(YEAR FROM placed_at) = 2025)""").fetchall() print(f"lines per order: {per_order:.2f}") print(f"all 2025 orders joined to their lines, sum over true sum: {all_joined / all_once:.2f}") ``` Output: ```text SELECT SUM(f.net_revenue_eur) AS net_revenue FROM analytics.fct_orders f JOIN analytics.fct_order_lines fl ON f.order_id = fl.order_id JOIN analytics.dim_products p ON fl.product_id = p.product_id WHERE p.product_name LIKE '%tent%' AND EXTRACT(YEAR FROM f.placed_at) = 2025 rows the join matched: 493 orders behind them: 480 model's answer: 256,467.71 each order counted once: 246,717.82 inflated by: 4.0% tents and shelters, counted once: 400,909.10 golden: 377,497.40 lines per order: 2.56 all 2025 orders joined to their lines, sum over true sum: 3.38 ``` The recorded model, given only the [schema](#ask-for-sql), joined orders to lines and products and summed `f.net_revenue_eur` over the joined rows. Its join matched 493 rows, which belong to 480 orders, so every order with more than one tent line was counted more than once. The example keeps the model's joins and filters and counts each order once instead: 246,717.82 euros against the model's 256,467.71. The fan-out inflated the answer by 4.0%. The fan-out isn't the query's largest error. The model matched products whose name contains `tent`, which misses the shelters the shop sells in the same category. Counting the whole `Tents` category, once per order, gives 400,909.10, and leaving out cancelled orders and counting local business days brings it to the golden 377,497.40. Two errors pulled in opposite directions: the fan-out added a little and the name match took away far more. A reviewer who compared the model's figure with last year's might see nothing odd. How large a fan-out is depends on the data, not on the query. Few of the shop's orders have two tent lines, so the inflation here is small. Where a join repeats most rows several times, the same query shape multiplies a sum. Join every order of 2025 to all its lines, with no product filter, and the sum of net revenue comes to 3.38 times the true figure. Each order's revenue counts once for each of its lines, and the shop's orders have 2.56 lines on average; the ratio is that count weighted by revenue, so it can exceed the plain average. The size you measure on one question tells you nothing about the next. **Working rule:** When a query joins tables of different grains, check the grain of every column it sums. Aggregate the finer table to the coarser grain before joining, or filter with `IN` or `EXISTS`, and compare the row count after the join with the count of distinct keys. Expect a model given only the schema to miss this, because the schema names columns, not grains. ## Definitions the schema doesn't hold {#definitions-not-in-schema} A [fan-out](#joins-that-fan-out) is a mechanical error: anyone who knows the tables' grains can spot it in the SQL. A definition is different. Words such as revenue, order value or active customer have one meaning in a business, chosen by the people who own the number, and that meaning lives in a finance policy, a dashboard's SQL or someone's memory. The schema can hold several columns that each fit the word, and it never says which one the business means. A query that picks the wrong one is correct SQL for a different question. The shop's finance team defines revenue as net revenue: in euros, after discounts, before tax, from real orders that weren't cancelled, on the order's local business day. The orders mart has both `gross_revenue_eur` and `net_revenue_eur`. EMEA's prices include VAT and the other regions add sales tax on top, so the gap between the two columns differs by region. The question is golden question `gq-008`: "What was our total revenue in 2025?" It doesn't say net, because people who work at the shop don't. Example `gross-or-net-revenue` (Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.29.0), passing in Edition 2026.11.3: ```python """Replay the model's SQL for total revenue in 2025, then change one choice at a time until it reaches the golden result.""" from lib.golden import golden_result, load_golden 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-008") sql = extract_sql(RecordedModel("schema-only-sql").complete(schema_only_messages(describe_schema(con), question.question))) print(sql) def total(measure: str, where: str) -> float: return con.execute(f"SELECT sum({measure}) FROM analytics.fct_orders WHERE {where}").fetchall()[0][0] utc_2025 = "placed_at >= '2025-01-01' AND placed_at < '2026-01-01'" model_answer, golden = run_sql(con, sql).rows[0][0], golden_result(con, question)[0][0] steps = { "model's answer (gross)": model_answer, "net instead of gross": total("net_revenue_eur", utc_2025), "without cancelled orders": total("net_revenue_eur", f"{utc_2025} AND status <> 'cancelled'"), "golden (local business days)": golden, } for label, value in steps.items(): print(f"{label}: {value:,.2f}") print(f"tax in the gross figure: {total('tax_eur', utc_2025):,.2f}") print(f"overstated by: {model_answer / golden - 1:.1%}") ``` Output: ```text SELECT SUM(gross_revenue_eur) AS total_revenue FROM analytics.fct_orders WHERE placed_at >= '2025-01-01' AND placed_at < '2026-01-01' model's answer (gross): 1,987,532.10 net instead of gross: 1,745,126.98 without cancelled orders: 1,652,978.12 golden (local business days): 1,651,527.86 tax in the gross figure: 242,404.96 overstated by: 20.3% ``` The recorded model, given only the [schema](#ask-for-sql), chose `gross_revenue_eur`, which is a reasonable reading of "total revenue" for someone who has never met the finance team. Its answer, 1,987,532.10 euros, overstates the golden 1,651,527.86 by 20.3%. The example changes one choice at a time to show where the difference comes from. Switching to net revenue on the same rows gives 1,745,126.98, a step of almost exactly the 242,404.96 euros of tax in the gross figure, with cents of rounding between them. Leaving out cancelled orders gives 1,652,978.12, and counting local business days instead of UTC dates closes the last, small gap. The same choice went wrong elsewhere in the recording. Asked for average order value by region in March 2026 (`gq-009`), the model averaged `gross_revenue_eur` over every order, cancelled ones included, where the shop's definition divides net revenue by orders that weren't cancelled. Asked for total revenue for each sales region (`gq-012`), it chose gross again. Neither choice is careless. Each is a common meaning of the word outside the shop. Neither error taxonomy we checked has a class for this. One detailed taxonomy, NL2SQL-BUGs, sorts semantic errors in text-to-SQL into 9 categories and 31 subcategories, from 2,018 expert-annotated cases ([Liu et al., KDD 2025](https://arxiv.org/abs/2503.11984)). It would file the query under *Attribute Mismatch*, a wrong column, which names the symptom and not the cause: none of its classes is "the business defines this differently from the schema". The closest we found is in an audit of benchmark annotations, which names mismatches between a question and the domain knowledge it needs ([Jin et al., CIDR 2026](https://vldb.org/cidrdb/papers/2026/p5-jin.pdf)). That gap is our reading of both papers, not a measured result, and other taxonomies may have the class. **Working rule:** Treat every business word in a question as a definition to look up, not a column to match. Write down who owns each definition, and give the agent the definition itself, as a governed metric or at least a glossary entry, never only the columns it could be computed from. When a word has no written definition, have the agent ask or decline instead of choosing. ## Time zones and the fiscal calendar {#time-and-calendars} Time looks like the least ambiguous part of a question, and on the shop's warehouse it is where the model went wrong as often as anywhere. A date in a question has a calendar and a time zone, and a warehouse usually stores timestamps in UTC while the business counts days in local time and years in its own fiscal calendar. A [definition](#definitions-not-in-schema) such as "fiscal year" is at least a phrase the model can see in the question. The time zone is invisible: the question says "May", and the query has to know whose May. The shop counts each order on its region's local business day, stored as `order_date_local` in the orders mart, beside the UTC timestamp `placed_at`. Its fiscal year starts on February 1 and is named for the year it ends in, so fiscal 2026 runs from February 1, 2025 to January 31, 2026, and `analytics.dim_date` holds the fiscal columns. The example replays two questions. Golden question `gq-004` asks how many customers placed their first order in fiscal year 2026. Golden question `gq-013` asks how many orders each sales region received in May 2026. Example `fiscal-year-and-local-day` (Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.29.0), passing in Edition 2026.11.3: ```python """Two time traps in the model's SQL: a calendar year where the question meant a fiscal one, and UTC days.""" from lib.golden import golden_result, load_golden 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() schema, model = describe_schema(con), RecordedModel("schema-only-sql") golden = {q.question_id: q for q in load_golden()} def model_answer(question_id: str): sql = extract_sql(model.complete(schema_only_messages(schema, golden[question_id].question))) print(sql) return run_sql(con, sql).rows # gq-004: customers whose first order was in fiscal year 2026, which runs from February 1, 2025 to January 31, 2026. print(f"gq-004 model: {model_answer('gq-004')[0][0]}") print(f"gq-004 golden: {golden_result(con, golden['gq-004'])[0][0]}") # gq-013: orders per region in May 2026. The model filters on placed_at, in UTC; the shop counts local days. model_rows, golden_rows = dict(model_answer("gq-013")), dict(golden_result(con, golden["gq-013"])) for region in sorted(golden_rows): print(f"gq-013 {region} model: {model_rows[region]}") print(f"gq-013 {region} golden: {golden_rows[region]}") (moved,), = con.execute("""SELECT count(*) FROM analytics.fct_orders WHERE (month(placed_at) = 5) <> (month(order_date_local) = 5) AND year(order_date_local) = 2026""").fetchall() print(f"orders across May's edges: {moved}") # Which way each of those orders crossed, by region: in May by the model's UTC filter only, or by the local day only. crossings = con.execute("""SELECT region, count(*) FILTER (WHERE in_utc AND NOT in_local), count(*) FILTER (WHERE in_local AND NOT in_utc) FROM (SELECT region, placed_at >= '2026-05-01' AND placed_at < '2026-06-01' AS in_utc, order_date_local BETWEEN DATE '2026-05-01' AND DATE '2026-05-31' AS in_local FROM analytics.fct_orders) GROUP BY region ORDER BY region""").fetchall() for region, utc_only, local_only in crossings: print(f"gq-013 {region} in May by UTC only: {utc_only}") print(f"gq-013 {region} in May by local day only: {local_only}") print(f"all regions in May by UTC only: {sum(c[1] for c in crossings)}") print(f"all regions in May by local day only: {sum(c[2] for c in crossings)}") ``` Output: ```text SELECT COUNT(*) AS customer_count FROM analytics.dim_customers WHERE first_order_on >= '2026-01-01' AND first_order_on < '2027-01-01' gq-004 model: 225 gq-004 golden: 825 SELECT region, COUNT(order_id) AS order_count FROM analytics.fct_orders WHERE placed_at >= '2026-05-01' AND placed_at < '2026-06-01' GROUP BY region ORDER BY order_count DESC gq-013 AMER model: 219 gq-013 AMER golden: 220 gq-013 APAC model: 148 gq-013 APAC golden: 147 gq-013 EMEA model: 317 gq-013 EMEA golden: 317 orders across May's edges: 6 gq-013 AMER in May by UTC only: 2 gq-013 AMER in May by local day only: 3 gq-013 APAC in May by UTC only: 1 gq-013 APAC in May by local day only: 0 gq-013 EMEA in May by UTC only: 0 gq-013 EMEA in May by local day only: 0 all regions in May by UTC only: 3 all regions in May by local day only: 3 ``` For `gq-004`, the recorded model, given only the [schema](#ask-for-sql), counted customers whose first order fell in calendar 2026: 225, where the golden answer is 825. The query is tidy and runs, and its year is the wrong one. Because the shop's data ends on 2026-06-30, it counted half a calendar year instead of a whole fiscal one, so the error is large here. For a period where the two years overlap more, the same mistake would be small and harder to see. For `gq-013`, the model filtered on `placed_at`, the UTC timestamp: AMER 219 against a golden 220, APAC 148 against 147. In all, 6 orders of 2026 fall in May by one clock and not by the other, and they partly cancel. In AMER, 3 orders count in May only by the local day and 2 only by UTC, so the model ends one order short. In APAC, 1 order counts in May only by UTC, so the model ends one over. Errors this small are the dangerous kind. They survive a sanity check, and they make two reports of the same month disagree by a few orders, which costs a team an afternoon to explain. The recording has the other half of the problem too. For two questions about fiscal periods, `gq-001` and `gq-010`, the model filtered `fct_orders` on `fiscal_year` and `fiscal_quarter`, which exist only in `dim_date`, and the queries failed to run. Those failures are cheap, because someone sees them. The calendar-year query for `gq-004` is the expensive one. **Working rule:** Give the agent your calendar, not only your columns: which timestamp is UTC, which date is the business day, and when the fiscal year starts and how it is named. Test every period question at a boundary, such as the first day of a month or the first month of a fiscal year, where UTC and local days or calendar and fiscal years disagree. Prefer a date dimension the agent joins over date arithmetic it writes. ## The table with the tempting name {#the-tempting-table} A [definition](#definitions-not-in-schema) can go wrong inside the right table; a whole table can be the wrong one. Most warehouses keep tables that nobody should query any more and nobody can drop: an older mart that old dashboards read, a copy from a migration, a table built for one analysis. Such a table often has the friendliest column names in the warehouse, because it was built for people, and its definitions are the old ones. A model choosing tables by name may prefer it. Spider 2.0, a benchmark of enterprise text-to-SQL tasks, found that choosing the wrong table accounted for 10.1% of the errors in a sample of 300 failed tasks from two agent frameworks ([Lei et al., ICLR 2025](https://arxiv.org/abs/2411.07763)). The shop's warehouse has one: `analytics.orders_legacy`, which has columns called `revenue` and `sales_region`. Its revenue is gross, tax included, and it keeps test and cancelled orders. Golden question `gq-012`, "What was total revenue for each sales region in 2025?", uses both of its column names, and was written to tempt the model toward it, as was `gq-013`, which asks for orders by sales region. The example shows what the legacy table answers, and then checks every query the model wrote for golden questions `gq-001` to `gq-016`. Example `legacy-table-answer` (Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.29.0), passing in Edition 2026.11.3: ```python """What the legacy mart says about revenue by sales region in 2025, and how often the model chose that mart.""" from lib.golden import golden_result, load_golden from lib.model import RecordedModel from lib.prompts import schema_only_messages from lib.tools import extract_sql from lib.warehouse import connect, describe_schema con = connect() question = next(q for q in load_golden() if q.question_id == "gq-012") golden = dict(golden_result(con, question)) legacy = dict(con.execute("""SELECT sales_region, sum(revenue) FROM analytics.orders_legacy WHERE year(order_date) = 2025 GROUP BY sales_region""").fetchall()) (test_orders,), = con.execute("""SELECT count(*) FROM analytics.orders_legacy AS l JOIN raw.orders AS o ON o.order_id = l.id WHERE o.is_test AND year(l.order_date) = 2025""").fetchall() for region in sorted(golden): print(f"{region} golden: {golden[region]:,.2f}") print(f"{region} orders_legacy: {legacy[region]:,.2f}") print(f"legacy total over golden: {sum(legacy.values()) / sum(golden.values()) - 1:+.1%}") print(f"test orders in the legacy rows: {test_orders}") # The model's schema-only queries for the first 16 golden questions: did any read the legacy mart? model, schema = RecordedModel("schema-only-sql"), describe_schema(con) asked = [q for q in load_golden() if q.question_id <= "gq-016"] sqls = [extract_sql(model.complete(schema_only_messages(schema, q.question))) for q in asked] print(f"model queries that read orders_legacy: {sum('orders_legacy' in sql for sql in sqls)} of {len(sqls)}") print(f"golden questions added in chapter 2, gq-006 to gq-016: {sum('gq-006' <= q.question_id <= 'gq-016' for q in asked)}") ``` Output: ```text AMER golden: 497,466.04 AMER orders_legacy: 576,980.12 APAC golden: 405,055.09 APAC orders_legacy: 465,770.13 EMEA golden: 749,006.73 EMEA orders_legacy: 962,439.56 legacy total over golden: +21.4% test orders in the legacy rows: 62 model queries that read orders_legacy: 0 of 16 golden questions added in chapter 2, gq-006 to gq-016: 11 ``` Summed by `sales_region` for 2025, the legacy table's `revenue` gives EMEA 962,439.56 euros, where the golden net revenue is 749,006.73. Across the regions it is +21.4% over the golden total, and its rows for 2025 include 62 test orders. Anyone who reads that table gets a number a fifth too high, under a column name that says it is revenue. The recorded model, given only the [schema](#ask-for-sql), didn't read it. Its queries for golden questions `gq-001` to `gq-016` touched `orders_legacy` in 0 of 16 cases. On this warehouse, `fct_orders` has a `region` column and both revenue columns, and the model preferred it every time. That is a result about this model, this schema and these questions, not a guarantee. The risk grows with the warehouse: Spider 2.0-lite's databases average over 755 columns, against about 54 in BIRD, an academic benchmark ([Lei et al., ICLR 2025](https://arxiv.org/abs/2411.07763)). Among hundreds of tables, we expect a legacy table's friendly names to match a question's words more often. We expect the risk to grow too when an agent is given retrieved examples or descriptions, because a search over names can rank the legacy table high. We keep `orders_legacy` in the shop's warehouse throughout the book for that reason. A table you can't drop is a table an agent can find, and the defence belongs below the model: a list of the tables it may read. **Working rule:** List the tables an agent may read, and leave legacy and staging tables off the list even when they have the friendliest names. Check every model-written query's tables against the list before it runs. When an agent never chooses a tempting table in your tests, keep the check anyway: a new question, a larger schema or a retrieved example can change that. ## Filters nobody wrote down {#unwritten-filters} Some rules about which rows count are so familiar to the people who own a number that nobody writes them down: cancelled orders don't count as revenue, test accounts aren't customers, a refund belongs to the month of its order. Such a rule lives in the mart's SQL, in the finance team's reports and in people's heads. A query that leaves it out returns every row, which looks like the most complete answer, not a wrong one. [Chapter 1](#check-the-first-number) showed it once, on net revenue by region. The people who write text-to-SQL benchmarks miss these filters too. In the BIRD benchmark's `california_schools` database, the gold SQL leaves out the condition on the `rtype` column, which is what separates schools from districts, because the annotators had labelled that column "unuseful" ([Jin et al., CIDR 2026](https://vldb.org/cidrdb/papers/2026/p5-jin.pdf)). A rule nobody wrote down goes missing from the answer key as well as from the model's query. The shop's marts apply one such rule for you: they remove test orders. They can't remove cancelled orders, because some questions are about cancellations, so `fct_orders` keeps them with their `status`, and the order lines keep them too. The question is golden question `gq-015`, "How many units of each product category did we sell in 2025?", which needs the status from one table to filter rows in another. Example `cancelled-lines-counted` (Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.29.0), passing in Edition 2026.11.3: ```python """Replay the model's SQL for units sold by category in 2025, and find the filters it left out.""" from lib.golden import golden_result, load_golden 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-015") sql = extract_sql(RecordedModel("schema-only-sql").complete(schema_only_messages(describe_schema(con), question.question))) print(sql) model_units = dict(run_sql(con, sql).rows) golden_units = dict(golden_result(con, question)) (cancelled,), = con.execute("""SELECT sum(l.quantity) FROM analytics.fct_order_lines AS l JOIN analytics.fct_orders AS o USING (order_id) WHERE o.status = 'cancelled' AND o.placed_at >= '2025-01-01' AND o.placed_at < '2026-01-01'""").fetchall() (test_units,), = con.execute("""SELECT sum(l.quantity) FROM raw.order_lines AS l JOIN raw.orders AS o USING (order_id) WHERE o.is_test AND year(o.placed_at) = 2025""").fetchall() print(f"Cooking model: {model_units['Cooking']}") print(f"Cooking golden: {golden_units['Cooking']}") print(f"all categories model: {sum(model_units.values())}") print(f"all categories golden: {sum(golden_units.values())}") print(f"units on cancelled orders, in the model's dates: {cancelled}") print(f"overstated by: {sum(model_units.values()) / sum(golden_units.values()) - 1:.1%}") print(f"units on test orders, which the marts already leave out: {test_units}") # Orders that weren't cancelled but fall in 2025 by one clock and not the other: in by UTC only, minus in by local day only. (edge_net,), = con.execute("""SELECT sum(l.quantity) FILTER (WHERE year(o.placed_at) = 2025 AND year(o.order_date_local) <> 2025) - sum(l.quantity) FILTER (WHERE year(o.placed_at) <> 2025 AND year(o.order_date_local) = 2025) FROM analytics.fct_order_lines AS l JOIN analytics.fct_orders AS o USING (order_id) WHERE o.status <> 'cancelled'""").fetchall() print(f"net units on orders across the year's edges: {edge_net}") ``` Output: ```text SELECT p.category, SUM(ol.quantity) AS total_units_sold FROM analytics.fct_order_lines ol JOIN analytics.dim_products p ON ol.product_id = p.product_id WHERE ol.order_id IN ( SELECT order_id FROM analytics.fct_orders WHERE placed_at >= '2025-01-01' AND placed_at < '2026-01-01' ) GROUP BY p.category ORDER BY total_units_sold DESC Cooking model: 5727 Cooking golden: 5427 all categories model: 22324 all categories golden: 21108 units on cancelled orders, in the model's dates: 1206 overstated by: 5.8% units on test orders, which the marts already leave out: 202 net units on orders across the year's edges: 10 ``` The recorded model, given only the [schema](#ask-for-sql), summed `quantity` from `fct_order_lines` for orders placed in 2025, and wrote no condition on status. For `Cooking` it says 5727 units, where the golden answer is 5427. Across all categories it says 22324 against 21108, 5.8% too many. The difference is almost all cancelled orders: 1206 units in the model's date range. The remaining 10 units are the net of orders whose UTC and local days fall in different years. The test orders show the other side. The raw order lines hold 202 units on test orders in 2025, and the model's answer didn't include them, because it read the mart and the mart had applied that rule. A filter the mart applies is one the model can't forget. In every query it wrote for this chapter's questions, the model read the marts, never the raw tables, so the test orders never reached an answer. The query also didn't ask whether a sold unit was later returned. The shop's definition of units sold doesn't subtract returns, so the golden answer doesn't either. That is one more rule that has to be written down where the agent can read it, or the next person will reasonably answer the question the other way. **Working rule:** Move every rule about which rows count into the marts where you can, so no query can forget it. Where a mart has to keep the rows, as with cancelled orders, write the rule into the definition the agent uses, and add a golden question that fails when the rule is left out. Read the gold for the same rules, because the person who wrote it can miss them as readily as the model can. ## Count the failures by kind {#count-failures-by-kind} Each section so far showed one failure on one question. To decide what to fix first, you need to know how often each kind occurs across your questions, and which failures are visible errors and which are plausible wrong numbers. This section counts them on the shop's golden questions. The set is small, so the counts describe this run, not a rate you can quote. The example runs the model's schema-only SQL for the golden questions from `gq-001` to `gq-016` that have an answer, 15 of them. It leaves out `gq-005`, a forecast that should be declined, which is chapter 8's subject. It grades each result against the golden result as correct, wrong or error, and labels each query with the kinds of failure in it. The labels come from `lib/failure_kinds.py`, which parses the model's query and its golden SQL with sqlglot and compares them. For instance, a query that uses `placed_at` where the golden SQL uses the local business day gets the label `time`. The rules are ours, written for this warehouse's traps, and the module's docstring lists each one. Example `classify-schema-only-errors` (Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.29.0), passing in Edition 2026.11.3: ```python """Run schema-only SQL on the golden questions that have an answer, and label each query's failures by kind.""" from collections import Counter import duckdb from lib.failure_kinds import KINDS, failure_kinds from lib.golden import golden_result, grade, load_golden 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() schema, model = describe_schema(con), RecordedModel("schema-only-sql") questions = [q for q in load_golden() if q.kind == "answer" and q.question_id <= "gq-016"] outcomes, kinds_in_wrong, errors = Counter(), Counter(), {} print("question outcome kinds") for q in questions: sql = extract_sql(model.complete(schema_only_messages(schema, q.question))) try: outcome = grade(q, run_sql(con, sql), golden_result(con, q)) except duckdb.Error as e: outcome, errors[q.question_id] = "error", str(e).splitlines()[0] kinds = failure_kinds(sql, q.golden_sql) outcomes[outcome] += 1 if outcome == "wrong": kinds_in_wrong.update(kinds) print(f"{q.question_id} {outcome:<7} {', '.join(kinds) or '-'}") for question_id, message in errors.items(): print(f"{question_id} error: {message}") print(f"questions graded: {len(questions)}") for outcome in ("correct", "wrong", "error"): print(f"{outcome}: {outcomes[outcome]} of {len(questions)}") print(f"errors with a missing column: {sum('Referenced column' in m for m in errors.values())}") print(f"errors with a type mismatch: {sum('No function matches' in m for m in errors.values())}") for kind in KINDS: print(f"wrong answers with {kind}: {kinds_in_wrong[kind]}") # gq-016 counts distinct customers: do those with an order across 2025's edges have other 2025 orders by both clocks? (edge_customers, covered), = con.execute("""SELECT count(DISTINCT customer_id), count(DISTINCT customer_id) FILTER (WHERE customer_id IN (SELECT customer_id FROM analytics.fct_orders WHERE year(placed_at) = 2025 AND year(order_date_local) = 2025)) FROM analytics.fct_orders WHERE (year(placed_at) = 2025) <> (year(order_date_local) = 2025)""").fetchall() print(f"gq-016 customers with an order across 2025's edges: {edge_customers}") print(f"gq-016 of them, with other 2025 orders by both clocks: {covered}") ``` Output: ```text question outcome kinds gq-001 error unwritten filter gq-002 wrong unwritten filter gq-003 error unwritten filter gq-004 wrong time gq-006 error time, unwritten filter gq-007 wrong fan-out, time, unwritten filter gq-008 wrong definition, time, unwritten filter gq-009 wrong definition, unwritten filter gq-010 error unwritten filter gq-011 error time, unwritten filter gq-012 wrong definition, time, unwritten filter gq-013 wrong time gq-014 correct time gq-015 wrong time, unwritten filter gq-016 correct time gq-001 error: Binder Error: Referenced column "fiscal_year" not found in FROM clause! gq-003 error: Binder Error: Referenced column "calendar_year" not found in FROM clause! gq-006 error: Binder Error: Referenced column "quantity" not found in FROM clause! gq-010 error: Binder Error: Referenced column "fiscal_quarter" not found in FROM clause! gq-011 error: Binder Error: No function matches the given name and argument types '~~(TIMESTAMP, STRING_LITERAL)'. You might need to add explicit type casts. questions graded: 15 correct: 2 of 15 wrong: 8 of 15 error: 5 of 15 errors with a missing column: 4 errors with a type mismatch: 1 wrong answers with fan-out: 1 wrong answers with definition: 3 wrong answers with time: 6 wrong answers with tempting table: 0 wrong answers with unwritten filter: 6 gq-016 customers with an order across 2025's edges: 6 gq-016 of them, with other 2025 orders by both clocks: 6 ``` The model got 2 of 15 right, and 5 of 15 queries failed to run: 4 used a column that isn't in the table they read, such as `fiscal_year` in `fct_orders`, and 1 matched a timestamp against a text pattern with `LIKE`. Those are the cheap failures. The expensive ones are the 8 of 15 that ran and returned a number. Among them, 6 carried a time error, 6 an unwritten filter, 3 a definition, 1 a fan-out and 0 the tempting table. Most wrong answers carried two or three kinds at once, which is why the [fan-out](#joins-that-fan-out) and [definition](#definitions-not-in-schema) sections changed one choice at a time to separate them. Two details in the output matter. The two correct answers, `gq-014` and `gq-016`, both carry the `time` label: their queries count UTC dates, and the counts matched for different reasons. For `gq-014`, orders crossed May's edges in both directions, 3 in by UTC only and 3 in by local day only, as the [time section](#time-and-calendars) showed region by region. For `gq-016`, a count of distinct customers, 6 customers had an order at the year's edges, and 6 of them had other orders in 2025 by both clocks, so the count couldn't move. A correct grade doesn't mean a correct query. And the name match for tents in `gq-007` has no label, because no rule looks for it. A labeller built for known traps finds only those. Each of the book's five kinds has a close neighbour in published taxonomies, under a different name. The mapping is our judgement: | Kind in this book | Nearest published class | | --- | --- | | fan-out | Join Condition Mismatch; Aggregate Functions [1] | | definition | none; closest is a domain-knowledge mismatch (E3) [2] | | time | Date/Time Functions [1] | | tempting table | Table Mismatch [1] | | unwritten filter | Implicit Condition Missing; Explicit Condition Missing [1] | Source [1] is NL2SQL-BUGs ([Liu et al., KDD 2025](https://arxiv.org/abs/2503.11984)), and [2] the CIDR 2026 audit of benchmark annotations ([Jin et al.](https://vldb.org/cidrdb/papers/2026/p5-jin.pdf)). Their classes are finer than ours, and ours are coarser on purpose: each of the five points to one fix in the warehouse or in what the agent is given. That mapping also answers which failures a better prompt can fix. A prompt can carry general practice, such as aggregating lines before joining them to orders, and it can carry facts about one warehouse, such as which date column is the business day. It can't carry the shop's definitions for every word people use, and a prompt that tries becomes a second, unreviewed copy of them. We expect a prompt to reduce fan-out and some time errors, and only definitions the agent can read and the warehouse enforces to remove the other three kinds. That is a judgement this chapter's run supports, not one it measures. Benchmarks show a similar gap at a larger scale. On Spider 2.0, an agent framework built on o1-preview solved 21.3% of the enterprise tasks, while methods based on GPT-4 had reached 91.2% on Spider 1.0 and 73.0% on BIRD ([Lei et al., ICLR 2025](https://arxiv.org/abs/2411.07763)). The three numbers come from different systems and task formats, so they illustrate the gap rather than measure it. **Working rule:** Count failures by kind on your own golden questions before you choose a fix, and keep errors that fail loudly apart from wrong numbers that don't. Label kinds by comparing each query with its golden SQL, with rules you can read, and read the wrong answers no rule labels. Fix the most common kind of wrong number first, and expect it to be a definition, a calendar or a filter more often than a syntax mistake. ## What goes wrong in production {#wrong-numbers-in-production} The failures in this chapter came from questions written to find them. In production the questions are written by people who don't know where the traps are, and the failures show up in other ways. **Two errors cancel and the number looks right.** The tent question's [fan-out and name match](#joins-that-fan-out) pulled in opposite directions, and the [UTC counts](#count-failures-by-kind) for two questions matched because errors cancelled or couldn't show. A spot check against a known figure passes these. You notice only by comparing every result with a golden result under one rule, and by reading the SQL of answers that pass, not only of those that fail. **A definition changes and the old one keeps answering.** When the finance team changes what counts as revenue, every query that computed it from columns keeps the old meaning. The golden questions for revenue start failing if their golden SQL was updated, and keep passing if it wasn't. Keep golden SQL beside the definitions it depends on, and review a change to either together with the other. **The legacy table turns up later.** In this chapter's run, the model never chose `orders_legacy`. A larger schema, retrieved examples or a question in the legacy table's vocabulary can change that without any change to your code. Check each query's tables against a list of allowed tables, and log which tables answered each question, so a shift shows up in a count. **Small time errors make reports disagree.** A count that differs by one order from the finance report starts a reconciliation, and the cause is a UTC date where the business uses local days. You notice when two reports of the same period disagree, which is late. Add golden questions at period boundaries, where the two clocks differ, and keep them in the set. **A filter is applied in one place and not another.** Test orders are removed in the marts, so any query over the raw tables brings them back. An agent with access to `raw` can read it, for a column the marts don't have. Keep the raw schema out of the agent's reach, or give it the filters as part of every table's description. **The failure labeller misses new kinds.** `lib/failure_kinds.py` finds the five kinds this chapter knows about, and nothing else, as the tent name match showed. A dashboard of failure counts by kind looks complete while a new kind grows unlabelled. Read a sample of wrong answers by hand on a schedule, and add a rule when a new kind shows up twice. **Working rule:** Before an agent's numbers reach anyone, grade every answer against golden results, keep golden questions at period boundaries and for every definition, list the tables the agent may read, and read a sample of wrong answers by hand. Count wrong numbers by kind, and treat a rising count in any kind as an alarm. ## Decisions this chapter made {#wrong-numbers-decisions} - **Five kinds of plausible wrong number:** fan-out, a definition the schema doesn't hold, time zones and the fiscal calendar, a tempting table, and an unwritten filter. Each points to a fix in the warehouse or in what the agent is given. - **Each failure is run, not described.** Every failure the model made is run on its real query, and where two errors combine, the example changes one choice at a time to measure each. - **11 golden questions joined the set,** `gq-006` to `gq-016`, each written to touch one of the warehouse's traps, with golden SQL that follows the finance team's definitions. - **Failure kinds are labelled by rules you can read,** in `lib/failure_kinds.py`, by comparing each query with its golden SQL, and wrong answers no rule labels are read by hand. - **Errors and wrong numbers are counted apart.** A query that fails to run is a cheap failure; one that returns a plausible number is the one to fix first. - **The legacy table stays in the warehouse,** though the model never chose it here, because a table you can't drop is one an agent can find. - **Definitions, not prompts, are the fix for most kinds.** A prompt can carry general practice; the business's definitions belong where the agent reads them and the warehouse enforces them.