Where raw text-to-SQL gets the number wrong
阅读约需 79分钟 分钟,8节中含6个示例。
这部分尚未翻译,因此以英文显示。
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.
Chapter 1 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. This chapter asks 11 more questions of the shop, which sells outdoor and camping gear across EMEA, AMER and APAC, each written to touch one of the warehouse's traps, and runs the model's SQL on each. The warehouse is a DuckDB database the book generates from a fixed seed, with a raw schema as loaded and an analytics schema of marts.
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). 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. It doesn't say 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 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 question's answer 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
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. Count the rows the model's join produces, against the golden query: the sum looks plausible but isn't.
1"""Replay the model's SQL for net revenue from orders with a tent, then count each order once. Read the `inflated by` line: it is the fan-out, measured with the model's own joins and filters kept as they are."""2from lib.golden import golden_result, load_golden3from 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-007")10sql = extract_sql(RecordedModel("schema-only-sql").complete(schema_only_messages(describe_schema(con), question.question)))11print(sql)1213# The model's own joins and filters, kept as they are, so only the counting changes.14# placed_at is UTC, so EXTRACT gives the year by UTC, as the model wrote it.15matched = """FROM analytics.fct_orders f JOIN analytics.fct_order_lines fl ON f.order_id = fl.order_id16 JOIN analytics.dim_products p ON fl.product_id = p.product_id17 WHERE {product} AND EXTRACT(YEAR FROM f.placed_at) = 2025"""18# The model's name match, which misses shelters, and the whole Tents category, which has them.19by_name, by_category = "p.product_name LIKE '%tent%'", "p.category = 'Tents'"20# One row for each matched line and a count of the distinct orders behind them; the trailing comma unpacks that one row.21(lines, orders), = con.execute(f"SELECT count(*), count(DISTINCT f.order_id) {matched.format(product=by_name)}").fetchall()22# A semi-join: each order's whole net revenue, counted once, however many of its lines match.23once = "SELECT sum(net_revenue_eur) FROM analytics.fct_orders WHERE order_id IN (SELECT f.order_id {matched})"24model_total = run_sql(con, sql).rows[0][0]25counted_once = con.execute(once.format(matched=matched.format(product=by_name))).fetchall()[0][0]26whole_category = con.execute(once.format(matched=matched.format(product=by_category))).fetchall()[0][0]2728print(f"rows the join matched: {lines}")29print(f"orders behind them: {orders}")30print(f"model's answer: {model_total:,.2f}")31print(f"each order counted once: {counted_once:,.2f}")32print(f"inflated by: {model_total / counted_once - 1:.1%}")33print(f"tents and shelters, counted once: {whole_category:,.2f}")34print(f"golden: {golden_result(con, question)[0][0]:,.2f}")3536# The same query shape with no product filter: every 2025 order joined to all of its lines.37# `/` is decimal division in DuckDB, so the average keeps its fraction.38(per_order,), = con.execute("SELECT count(*) / count(DISTINCT order_id) FROM analytics.fct_order_lines").fetchall()39# The second subquery is the true sum: each 2025 order counted once, from the orders table alone.40(all_joined, all_once), = con.execute("""SELECT41 (SELECT sum(f.net_revenue_eur) FROM analytics.fct_orders f JOIN analytics.fct_order_lines fl ON f.order_id = fl.order_id42 WHERE EXTRACT(YEAR FROM f.placed_at) = 2025),43 (SELECT sum(net_revenue_eur) FROM analytics.fct_orders WHERE EXTRACT(YEAR FROM placed_at) = 2025)""").fetchall()44print(f"lines per order: {per_order:.2f}")45print(f"all 2025 orders joined to their lines, sum over true sum: {all_joined / all_once:.2f}")
python tent-orders-fan-out.pySELECT 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 fan-out inflated the answer by 4.0%. The model joined orders to lines and products and summed f.net_revenue_eur over every joined row, and the join matched 493 rows for 480 orders, so every order with more than one tent line was counted more than once. Counting each order once, with the same joins and filters, gives 246,717.82 euros, less than the model's answer.
The name match is the query's largest error: it matches products whose name contains tent, which misses the shelters 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 comparing the 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.
模式中没有的定义
扇出是一种机械性错误:只要了解各表的粒度,任何人都能在 SQL 中发现它。定义则不同。收入、订单金额、活跃客户之类的词,在一家企业里只有一种含义,由拥有该数字的人确定,而这一含义存在于财务制度、某个仪表盘的 SQL,或者某个人的记忆中。模式里可能有好几列都符合这个词,却从不说明业务指的是哪一列。选错列的查询,对另一个问题来说是正确的 SQL。
这家商店的财务团队把收入定义为净收入:以欧元计,扣除折扣,不含税,来自未取消的真实订单,并按订单所在地的工作日统计。订单数据集市中同时有 gross_revenue_eur 和 net_revenue_eur。EMEA 的价格含增值税,其他地区则在价格之外另加销售税,因此两列之间的差额因地区而异。问题是黄金问题 gq-008:“2025 年我们的总收入是多少?”问题里没有说净收入,因为在这家商店工作的人不这么说。哪一种选择代价最大?示例每次改动一处,从毛收入逐步改到黄金答案。
1"""Replay the model's SQL for total revenue in 2025, then change one choice at a time until it reaches the golden result. The line that matters is `overstated by`, and the step from gross to net, which matches the tax in the gross figure."""2from lib.golden import golden_result, load_golden3from 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-008")10sql = extract_sql(RecordedModel("schema-only-sql").complete(schema_only_messages(describe_schema(con), question.question)))11print(sql)121314def total(measure: str, where: str) -> float:15 return con.execute(f"SELECT sum({measure}) FROM analytics.fct_orders WHERE {where}").fetchall()[0][0]161718# The model filters on placed_at, the UTC timestamp, so "2025" here is 2025 by UTC, not by local business day.19utc_2025 = "placed_at >= '2025-01-01' AND placed_at < '2026-01-01'"20model_answer, golden = run_sql(con, sql).rows[0][0], golden_result(con, question)[0][0]21# Each step changes one choice from the step before it, so the values read as a walk to the golden figure.22steps = {23 "model's answer (gross)": model_answer,24 "net instead of gross": total("net_revenue_eur", utc_2025),25 "without cancelled orders": total("net_revenue_eur", f"{utc_2025} AND status <> 'cancelled'"),26 "golden (local business days)": golden,27}28for label, value in steps.items():29 print(f"{label}: {value:,.2f}")30# The tax the gross figure carries, summed over the same rows the model's query reads.31print(f"tax in the gross figure: {total('tax_eur', utc_2025):,.2f}")32print(f"overstated by: {model_answer / golden - 1:.1%}")
python gross-or-net-revenue.pySELECT 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%
毛收入口径比黄金答案高出 20.3%。模型选择了 gross_revenue_eur,对一个从未接触过财务团队的人来说,这是对“总收入”的合理理解。它给出的答案是 1,987,532.10 欧元,而黄金答案是 1,651,527.86。
这一差距大部分来自税。在相同的行上改用净收入,得到 1,745,126.98,降幅几乎恰好等于税额 242,404.96 欧元,两者之间只相差几分钱的舍入。去掉已取消的订单后,总额为 1,652,978.12 欧元,这一步去掉了其余差距中的绝大部分;再按本地工作日而不是 UTC 日期统计,则补上了最后一小段差距。
同样的选择在录制的其他地方也出了错。对于 gq-009,即 2026 年 3 月各地区的平均订单金额,模型对所有订单(包括已取消的订单)求 gross_revenue_eur 的平均值,而商店的定义是用净收入除以未取消的订单数。对于 gq-012,即各销售区域的总收入,模型再次选了毛收入。这两次选择都不是出于疏忽。每一种都是该词在商店之外的一种常见含义。
我们查阅的两种错误分类法都没有对应这种情形的类别。其中一种详细的分类法 NL2SQL-BUGs,根据 2,018 个由专家标注的案例,把文本到 SQL 中的语义错误分为 9 个类别和 31 个子类别(Liu et al., KDD 2025)。它会把这条查询归入属性不匹配,即列错误,这只指出了症状而没有指出原因:它的所有类别中,没有一类是“业务对此的定义与模式不同”。我们找到的最接近的说法出现在一项对基准标注的审计中,其中指出了问题与其所需领域知识之间的不匹配(Jin et al., CIDR 2026)。这一缺口是我们对两篇论文的解读,而不是测量结果,其他分类法可能有这一类别。
工作准则: 把问题中的每个业务词都当作需要查找的定义,而不是需要匹配的列。写明每个定义由谁负责,并把定义本身交给智能体,形式为受治理的指标,或至少是一条术语表条目,绝不能只给出可用来计算它的那些列。当某个词没有书面定义时,让智能体提问或拒绝回答,而不是自行选择。
这部分尚未翻译,因此以英文显示。
Time zones and the fiscal calendar
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 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. 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. One tests the fiscal year, the other the local business day.
1"""Two time traps in the model's SQL: a calendar year where the question meant a fiscal one, and UTC days. Read the `gq-004 model` line, the `gq-013` lines, and the counts of orders whose two clocks disagree."""2from lib.golden import golden_result, load_golden3from lib.model import RecordedModel4from lib.prompts import schema_only_messages5from lib.tools import extract_sql, run_sql6from lib.warehouse import connect, describe_schema78con = connect()9schema, model = describe_schema(con), RecordedModel("schema-only-sql")10golden = {q.question_id: q for q in load_golden()}111213def model_answer(question_id: str):14 sql = extract_sql(model.complete(schema_only_messages(schema, golden[question_id].question)))15 print(sql)16 return run_sql(con, sql).rows171819# gq-004: customers whose first order was in fiscal year 2026, which runs from February 1, 2025 to January 31, 2026.20print(f"gq-004 model: {model_answer('gq-004')[0][0]}")21print(f"gq-004 golden: {golden_result(con, golden['gq-004'])[0][0]}")2223# gq-013: orders per region in May 2026. The model filters on placed_at, in UTC; the shop counts local days.24# Both queries return (region, count) rows, which dict turns into a lookup by region.25model_rows, golden_rows = dict(model_answer("gq-013")), dict(golden_result(con, golden["gq-013"]))26for region in sorted(golden_rows):27 print(f"gq-013 {region} model: {model_rows[region]}")28 print(f"gq-013 {region} golden: {golden_rows[region]}")29# Orders in May by exactly one of the two clocks, counting 2026 by local day.30(moved,), = con.execute("""SELECT count(*) FROM analytics.fct_orders31 WHERE (month(placed_at) = 5) <> (month(order_date_local) = 5) AND year(order_date_local) = 2026""").fetchall()32print(f"orders across May's edges: {moved}")3334# Which way each of those orders crossed, by region: in May by the model's UTC filter only, or by the local day only.35# UTC is a half-open range that ends before June 1; the local day is a date, so BETWEEN also takes May 31.36crossings = con.execute("""SELECT region,37 count(*) FILTER (WHERE in_utc AND NOT in_local), count(*) FILTER (WHERE in_local AND NOT in_utc)38 FROM (SELECT region, placed_at >= '2026-05-01' AND placed_at < '2026-06-01' AS in_utc,39 order_date_local BETWEEN DATE '2026-05-01' AND DATE '2026-05-31' AS in_local40 FROM analytics.fct_orders) GROUP BY region ORDER BY region""").fetchall()41for region, utc_only, local_only in crossings:42 print(f"gq-013 {region} in May by UTC only: {utc_only}")43 print(f"gq-013 {region} in May by local day only: {local_only}")44print(f"all regions in May by UTC only: {sum(c[1] for c in crossings)}")45print(f"all regions in May by local day only: {sum(c[2] for c in crossings)}")
python fiscal-year-and-local-day.pySELECT 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
The model's gq-004 query is tidy and runs, and it answers the wrong year. It counts customers whose first order fell in calendar 2026: 225, where the golden answer is 825. Because the shop's data ends on 2026-06-30, it counted half a calendar year, not 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.
May is off by one order in two regions, because the filter uses placed_at, the UTC timestamp. For AMER it says 219 orders against a golden 220, and for APAC 148 against 147; EMEA matches the golden answer.
Across all regions, 6 orders of 2026 fall in May by one clock and not 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
A definition 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).
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. Two checks follow: what the legacy table answers, and whether the model's queries ever read it.
1"""What the legacy mart says about revenue by sales region in 2025, and how often the model's queries read that mart. The line to read is `legacy total over golden`, and the count of queries that read `orders_legacy`."""2from lib.golden import golden_result, load_golden3from lib.model import RecordedModel4from lib.prompts import schema_only_messages5from lib.tools import extract_sql6from lib.warehouse import connect, describe_schema78con = connect()9question = next(q for q in load_golden() if q.question_id == "gq-012")10golden = dict(golden_result(con, question))11# The legacy mart's revenue is gross, tax included, and it keeps test and cancelled orders.12legacy = dict(con.execute("""SELECT sales_region, sum(revenue) FROM analytics.orders_legacy13 WHERE year(order_date) = 2025 GROUP BY sales_region""").fetchall())14# Test orders are flagged only in raw, which the marts leave out; the legacy mart keeps them.15(test_orders,), = con.execute("""SELECT count(*) FROM analytics.orders_legacy AS l JOIN raw.orders AS o ON o.order_id = l.id16 WHERE o.is_test AND year(l.order_date) = 2025""").fetchall()1718for region in sorted(golden):19 print(f"{region} golden: {golden[region]:,.2f}")20 print(f"{region} orders_legacy: {legacy[region]:,.2f}")21print(f"legacy total over golden: {sum(legacy.values()) / sum(golden.values()) - 1:+.1%}")22print(f"test orders in the legacy rows: {test_orders}")2324# The model's schema-only queries for the first 16 golden questions: did any read the legacy mart?25model, schema = RecordedModel("schema-only-sql"), describe_schema(con)26# IDs are zero-padded, so comparing them as text selects gq-001 to gq-016.27asked = [q for q in load_golden() if q.question_id <= "gq-016"]28sqls = [extract_sql(model.complete(schema_only_messages(schema, q.question))) for q in asked]29# A query counts as reading the legacy mart when its text names orders_legacy.30print(f"model queries that read orders_legacy: {sum('orders_legacy' in sql for sql in sqls)} of {len(sqls)}")31print(f"golden questions added in chapter 2, gq-006 to gq-016: {sum('gq-006' <= q.question_id <= 'gq-016' for q in asked)}")
python legacy-table-answer.pyAMER 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
Anyone who sums the legacy table's revenue column by sales_region for 2025 gets a total +21.4% over the golden one, under a column name that says it is revenue. For EMEA the table gives 962,439.56 euros, where the golden net revenue is 749,006.73. Across all regions, its 2025 rows include 62 test orders.
The recorded model never 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). 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 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
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 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). 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. The model's query never drops cancelled orders, and the example shows the cost.
1"""Replay the model's SQL for units sold by category in 2025, and find the filters it left out. Read the `overstated by` line, and the units on cancelled orders, which the missing status filter explains."""2from lib.golden import golden_result, load_golden3from 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-015")10sql = extract_sql(RecordedModel("schema-only-sql").complete(schema_only_messages(describe_schema(con), question.question)))11print(sql)12# The model's query returns (category, units) rows, which dict turns into a lookup by category.13model_units = dict(run_sql(con, sql).rows)14golden_units = dict(golden_result(con, question))15# The status is on the order, so the lines join to fct_orders to read it.16(cancelled,), = con.execute("""SELECT sum(l.quantity) FROM analytics.fct_order_lines AS l17 JOIN analytics.fct_orders AS o USING (order_id)18 WHERE o.status = 'cancelled' AND o.placed_at >= '2025-01-01' AND o.placed_at < '2026-01-01'""").fetchall()19# raw keeps test orders, which the marts drop; the is_test flag is only in raw.20(test_units,), = con.execute("""SELECT sum(l.quantity) FROM raw.order_lines AS l JOIN raw.orders AS o USING (order_id)21 WHERE o.is_test AND year(o.placed_at) = 2025""").fetchall()2223print(f"Cooking model: {model_units['Cooking']}")24print(f"Cooking golden: {golden_units['Cooking']}")25print(f"all categories model: {sum(model_units.values())}")26print(f"all categories golden: {sum(golden_units.values())}")27print(f"units on cancelled orders, in the model's dates: {cancelled}")28print(f"overstated by: {sum(model_units.values()) / sum(golden_units.values()) - 1:.1%}")29print(f"units on test orders, which the marts already leave out: {test_units}")3031# 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.32(edge_net,), = con.execute("""SELECT sum(l.quantity) FILTER (WHERE year(o.placed_at) = 2025 AND year(o.order_date_local) <> 2025)33 - sum(l.quantity) FILTER (WHERE year(o.placed_at) <> 2025 AND year(o.order_date_local) = 2025)34 FROM analytics.fct_order_lines AS l JOIN analytics.fct_orders AS o USING (order_id)35 WHERE o.status <> 'cancelled'""").fetchall()36print(f"net units on orders across the year's edges: {edge_net}")
python cancelled-lines-counted.pySELECT
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
Leaving the status out is almost the whole error. The model summed quantity from fct_order_lines for 2025 orders with no status condition, so it counts units from cancelled orders in its date range. Across all categories it says 22324 units against the golden 21108, 5.8% too many. The difference is almost all cancelled orders; the remaining 10 units are the net of orders whose UTC and local days fall in different years.
For Cooking the model says 5727 units, where the golden answer is 5427.
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, 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 it 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
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. The set is small, so the counts describe this run, not a rate you can quote.
This run takes the model's schema-only SQL for each golden question from gq-001 to gq-016 with an answer, 15 of them, so its failures can be counted. 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 its failure kinds. 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; the module's docstring lists each one.
1"""Run the model's schema-only SQL on each golden question that has an answer, grade each result, and label the failure kinds of each query. The counts of correct, wrong and error answers, and the kinds the wrong ones carry, are what the prose quotes."""2from collections import Counter34import duckdb5from lib.failure_kinds import KINDS, failure_kinds6from lib.golden import golden_result, grade, load_golden7from lib.model import RecordedModel8from lib.prompts import schema_only_messages9from lib.tools import extract_sql, run_sql10from lib.warehouse import connect, describe_schema1112con = connect()13schema, model = describe_schema(con), RecordedModel("schema-only-sql")14# Kind "answer" leaves out gq-005, the forecast to decline; IDs are zero-padded, so gq-001 to gq-016 is a text range.15questions = [q for q in load_golden() if q.kind == "answer" and q.question_id <= "gq-016"]16outcomes, kinds_in_wrong, errors = Counter(), Counter(), {}1718print("question outcome kinds")19for q in questions:20 sql = extract_sql(model.complete(schema_only_messages(schema, q.question)))21 try:22 outcome = grade(q, run_sql(con, sql), golden_result(con, q))23 except duckdb.Error as e:24 # A query the database refuses to run is an error, not a wrong number.25 outcome, errors[q.question_id] = "error", str(e).splitlines()[0]26 # Each kind is a rule in lib/failure_kinds.py that compares the query with the golden SQL; a query can carry none or several.27 kinds = failure_kinds(sql, q.golden_sql)28 outcomes[outcome] += 129 if outcome == "wrong":30 kinds_in_wrong.update(kinds)31 print(f"{q.question_id} {outcome:<7} {', '.join(kinds) or '-'}")3233for question_id, message in errors.items():34 print(f"{question_id} error: {message}")35print(f"questions graded: {len(questions)}")36for outcome in ("correct", "wrong", "error"):37 print(f"{outcome}: {outcomes[outcome]} of {len(questions)}")38# DuckDB's messages: one for a column the table read does not have, one for a type mismatch such as LIKE on a timestamp.39print(f"errors with a missing column: {sum('Referenced column' in m for m in errors.values())}")40print(f"errors with a type mismatch: {sum('No function matches' in m for m in errors.values())}")41for kind in KINDS:42 print(f"wrong answers with {kind}: {kinds_in_wrong[kind]}")4344# gq-016 counts distinct customers: do those with an order across 2025's edges have other 2025 orders by both clocks?45(edge_customers, covered), = con.execute("""SELECT count(DISTINCT customer_id), count(DISTINCT customer_id) FILTER (WHERE customer_id IN46 (SELECT customer_id FROM analytics.fct_orders WHERE year(placed_at) = 2025 AND year(order_date_local) = 2025))47 FROM analytics.fct_orders WHERE (year(placed_at) = 2025) <> (year(order_date_local) = 2025)""").fetchall()48print(f"gq-016 customers with an order across 2025's edges: {edge_customers}")49print(f"gq-016 of them, with other 2025 orders by both clocks: {covered}")
python classify-schema-only-errors.pyquestion 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
Most failures ran and returned a number, the harder kind to catch: 8 of 15 wrong answers, against 5 of 15 queries that failed to run. Of the errors, 4 used a column missing from the table they read, such as fiscal_year in fct_orders, and 1 matched a timestamp against a text pattern with LIKE.
The wrong answers are the expensive ones. 6 of them carry a time error and 6 an unwritten filter. Most wrong answers carried two or three kinds at once, which is why the fan-out and definition sections changed one choice at a time to separate them.
Definitions show up in 3 wrong answers, fan-out in 1, and the tempting table in 0, as its section found.
A correct grade doesn't mean a correct query. The 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 showed. 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. The tent name match in gq-007 has no label, because no rule looks for it.
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), and [2] the CIDR 2026 audit of benchmark annotations (Jin et al.). 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). 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.
生产环境中会出什么问题
本章中的失败,都来自专门为发现它们而编写的问题。而在生产环境中,提问的人并不知道陷阱在哪里。
两个错误相互抵消,数字看起来正确。 帐篷问题中的扇出与名称匹配两个错误方向相反,而两个问题的 UTC 计数之所以吻合,是因为错误相互抵消,或者根本无从显现。对照已知数字做抽查,会让这些错误通过。要发现它们,只能按同一条规则把每个结果与标准结果逐一比较,并且不仅阅读未通过的答案的 SQL,也阅读通过的答案的 SQL。
定义变了,旧定义仍在作答。 财务团队改变收入的统计口径后,每个由列直接计算收入的查询仍然沿用旧含义。如果收入的标准 SQL 已经更新,收入的标准问题就会开始失败;如果没有更新,它们会继续通过。应当把标准 SQL 与它所依赖的定义放在一起,并且在修改其中任何一方时,连同另一方一起评审。
旧表后来又出现了。 在本章的运行中,模型从未选择 orders_legacy。更大的模式、检索到的示例,或者使用旧表术语的问题,都可能在你不改动任何代码的情况下改变这一点。应当对照允许使用的表的清单检查每条查询所用的表,并记录每个问题是由哪些表回答的,这样变化就会体现在计数上。
细小的时间错误使报表互相矛盾。 某个计数与财务报表相差一笔订单,就会引发一次对账,而原因是业务按本地日期统计,查询用的却是 UTC 日期。你要等到同一时期的两份报表不一致时才会发现,这已经太晚。应当在两种时钟不同的周期边界处添加标准问题,并一直保留在集合中。
过滤条件在一处生效,在另一处却没有。 测试订单在数据集市中被剔除,因此任何针对原始表的查询都会把它们带回来。能够访问 raw 的智能体,可以为了数据集市中没有的列而去读取它。应当让原始模式不在智能体的可及范围内,或者把过滤条件写进每张表的描述中。
失败标注器漏掉新的类型。 lib/failure_kinds.py 只能找出本章已知的五种失败类型,别的都找不出,帐篷名称匹配就说明了这一点。按类型统计失败数量的仪表盘看上去很完整,而新的类型却在无人标注的情况下增长。应当定期手工阅读一批错误答案的样本,并在某种新类型出现两次时添加一条规则。
工作准则: 在智能体给出的数字送到任何人手中之前,要对照标准结果为每个答案评分,在周期边界处以及针对每个定义保留标准问题,列出智能体可以读取的表,并手工阅读一批错误答案的样本。按类型统计错误数字,任何一种类型的计数上升,都应视为警报。
本章的决定
- **五类看似合理的错误数字:**扇出、模式中没有的定义、时区与财年日历、一张诱人的表,以及一个没有写出的过滤条件。每一类都指向仓库中或交给智能体的内容里的一处修复。
- **每个失败都实际运行,而不是只作描述。**模型出现的每个失败都在其真实查询上运行;当两个错误叠加时,示例每次只改变一个选择,以分别度量各自的影响。
- **11道黄金问题加入了问题集,**即
gq-006至gq-016,每道题都针对仓库的某一个陷阱而写,其黄金 SQL 遵循财务团队的定义。 - **失败类型由可以阅读的规则来标注,**规则位于
lib/failure_kinds.py,方法是将每条查询与其黄金 SQL 比较;没有任何规则能标注的错误答案,则由人工阅读判定。 - **报错与错误数字分开计数。**无法运行的查询是代价低的失败;返回一个看似合理的数字的查询,才是应当最先修复的。
- **旧表仍保留在仓库中,**尽管模型在这里从未选择过它,因为一张无法删除的表,就是智能体能够找到的表。
- **对大多数类型而言,修复靠的是定义,而不是提示词。**提示词可以承载通用的做法;业务的定义则应放在智能体能读到、仓库能强制执行的地方。
本章,在本版次中
版次 2026.11.15示例于 2026年11月19日 通过本章 6个示例 个示例中有 6 个通过
译自英文版图书,版次 2026.11.15。