Where raw text-to-SQL gets the number wrong

閱讀約需 74 分鐘 分鐘,8 節 中共有 6 個範例。

版次 2026.11.15 範例於 2026年11 月 19 通過測試 78 個範例中有 78 個通過 變更內容

由 AI 翻譯。經人工以英文驗證。 我們如何製作書籍

譯自英文書,版次 2026.11.15。 閱讀原文

此部分尚未翻譯,因此以英文呈現。

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

最近一次變動於第 2026.11.12 版

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.

tent-orders-fan-out.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 通過
"""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."""from lib.golden import golden_result, load_goldenfrom lib.model import RecordedModelfrom lib.prompts import schema_only_messagesfrom lib.tools import extract_sql, run_sqlfrom lib.warehouse import connect, describe_schemacon = 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.# placed_at is UTC, so EXTRACT gives the year by UTC, as the model wrote it.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"""# The model's name match, which misses shelters, and the whole Tents category, which has them.by_name, by_category = "p.product_name LIKE '%tent%'", "p.category = 'Tents'"# One row for each matched line and a count of the distinct orders behind them; the trailing comma unpacks that one row.(lines, orders), = con.execute(f"SELECT count(*), count(DISTINCT f.order_id) {matched.format(product=by_name)}").fetchall()# A semi-join: each order's whole net revenue, counted once, however many of its lines match.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.# `/` is decimal division in DuckDB, so the average keeps its fraction.(per_order,), = con.execute("SELECT count(*) / count(DISTINCT order_id) FROM analytics.fct_order_lines").fetchall()# The second subquery is the true sum: each 2025 order counted once, from the orders table alone.(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}")
輸出python tent-orders-fan-out.py
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 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.

結構描述未涵蓋的定義

最近一次變動於第 2026.11.12 版

扇出是機械性的錯誤:只要了解各資料表的粒度,就能在 SQL 中看出來。定義則不同。營收、訂單金額或活躍客戶這類詞,在一家企業裡只有一種意義,由負責該數字的人決定,而這個意義存在於財務政策、儀表板的 SQL,或某個人的記憶中。結構描述可能有好幾個欄位都符合這個詞,卻從不說明企業指的是哪一個。查詢若選錯了欄位,它本身是正確的 SQL,只是回答了另一個問題。

這家商店的財務團隊將營收定義為淨營收:以歐元計價、扣除折扣、未含稅、來自未取消的真實訂單,並依訂單所在地的營業日計算。訂單資料集市同時有 gross_revenue_eur 與 net_revenue_eur。EMEA 的價格已含加值稅,其他地區則另外加計銷售稅,因此兩個欄位之間的差距因地區而異。問題是黃金問題 gq-008:「2025 年我們的總營收是多少?」這個問題沒有說「淨」,因為在這家商店工作的人不會這樣說。哪一個選擇的代價最大?這個範例一次改變一項,從毛額逐步改到黃金答案。

gross-or-net-revenue.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 通過
"""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."""from lib.golden import golden_result, load_goldenfrom lib.model import RecordedModelfrom lib.prompts import schema_only_messagesfrom lib.tools import extract_sql, run_sqlfrom lib.warehouse import connect, describe_schemacon = 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]# The model filters on placed_at, the UTC timestamp, so "2025" here is 2025 by UTC, not by local business day.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]# Each step changes one choice from the step before it, so the values read as a walk to the golden figure.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}")# The tax the gross figure carries, summed over the same rows the model's query reads.print(f"tax in the gross figure: {total('tax_eur', utc_2025):,.2f}")print(f"overstated by: {model_answer / golden - 1:.1%}")
輸出python gross-or-net-revenue.py
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%

毛額數字比黃金答案高估了 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 個經專家標註的案例,將 text-to-SQL 的語意錯誤分為 9 個類別與 31 個子類別(Liu et al., KDD 2025)。它會把這個查詢歸入屬性不符(Attribute Mismatch),也就是欄位錯誤,這只指出了症狀而非原因:它的類別中沒有一項是「企業的定義與結構描述不同」。我們找到最接近的說法,出現在一項對基準測試標註的稽核中,其中指出問題與其所需領域知識之間的不一致(Jin et al., CIDR 2026)。這項缺口是我們對兩篇論文的解讀,並非實測結果,其他分類法可能有這個類別。

實用原則: 把問題中的每個業務用詞視為需要查找的定義,而不是需要比對的欄位。記下每個定義由誰負責,並把定義本身交給代理程式,形式可以是受治理的指標,或至少是詞彙表條目,絕不能只提供可用來計算的欄位。當某個詞沒有書面定義時,應讓代理程式詢問或拒絕回答,而不是自行選擇。

此部分尚未翻譯,因此以英文呈現。

Time zones and the fiscal calendar

最近一次變動於第 2026.11.15 版

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.

fiscal-year-and-local-day.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 通過
"""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."""from lib.golden import golden_result, load_goldenfrom lib.model import RecordedModelfrom lib.prompts import schema_only_messagesfrom lib.tools import extract_sql, run_sqlfrom lib.warehouse import connect, describe_schemacon = 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.# Both queries return (region, count) rows, which dict turns into a lookup by region.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]}")# Orders in May by exactly one of the two clocks, counting 2026 by local day.(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.# UTC is a half-open range that ends before June 1; the local day is a date, so BETWEEN also takes May 31.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)}")
輸出python fiscal-year-and-local-day.py
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

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.

名稱誘人的資料表

最近一次變動於第 2026.11.12 版

定義可能在正確的資料表裡出錯;整張資料表也可能選錯。多數資料倉儲都留有沒有人應該再查詢、卻也沒有人能刪除的資料表:舊儀表板仍在讀取的舊資料集市、遷移時留下的副本、為某一次分析而建的資料表。這類資料表的欄位名稱往往是整個資料倉儲裡最親切易懂的,因為它原本就是為人而建,而它的定義卻是舊的。依名稱挑選資料表的模型,可能會偏好它。Spider 2.0 是一套企業級 text-to-SQL 任務的基準測試,它在兩個代理框架共 300 個失敗任務的樣本中發現,選錯資料表占錯誤的 10.1%(Lei et al., ICLR 2025)。

這間商店的資料倉儲裡就有一張:analytics.orders_legacy,其中有名為 revenue 與 sales_region 的欄位。它的營收是含稅的毛額,並且保留了測試訂單與已取消的訂單。黃金問題 gq-012「2025 年各銷售區域的總營收是多少?」同時用到這兩個欄位名稱,其設計就是要誘使模型選用該表;gq-013 也是如此,它詢問的是各銷售區域的訂單。接下來有兩項檢查:舊資料表會給出什麼答案,以及模型的查詢是否曾讀取它。

legacy-table-answer.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 通過
"""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`."""from lib.golden import golden_result, load_goldenfrom lib.model import RecordedModelfrom lib.prompts import schema_only_messagesfrom lib.tools import extract_sqlfrom lib.warehouse import connect, describe_schemacon = connect()question = next(q for q in load_golden() if q.question_id == "gq-012")golden = dict(golden_result(con, question))# The legacy mart's revenue is gross, tax included, and it keeps test and cancelled orders.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 are flagged only in raw, which the marts leave out; the legacy mart keeps them.(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)# IDs are zero-padded, so comparing them as text selects gq-001 to gq-016.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]# A query counts as reading the legacy mart when its text names orders_legacy.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)}")
輸出python legacy-table-answer.py
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

若有人將舊資料表 2025 年的 revenue 欄位依 sales_region 加總,得到的總額會比黃金答案高出 +21.4%,而該欄位的名稱明明白白寫著是營收。以 EMEA 為例,該表給出 962,439.56 歐元,而黃金的淨營收為 749,006.73。所有區域合計,其 2025 年的資料列包含 62 筆測試訂單。

所記錄的模型從未讀取這張表。針對黃金問題 gq-001 至 gq-016,它的查詢在 0 of 16 個案例中接觸了 orders_legacy。在這個資料倉儲中,fct_orders 有 region 欄位,也有兩個營收欄位,而模型每次都偏好它。這是關於這個模型、這個結構描述與這些問題的結果,並非保證。風險會隨資料倉儲規模增加:Spider 2.0-lite 的資料庫平均有超過 755 個欄位,而學術基準測試 BIRD 約為 54 個(Lei et al., ICLR 2025)。在數百張資料表之中,我們預期舊資料表親切的名稱更常與問題用語相符。當代理取得檢索而來的範例或描述時,我們也預期風險會上升,因為以名稱進行的搜尋可能把舊資料表排在很前面。

基於這個原因,我們在全書中都把 orders_legacy 留在資料倉儲裡。無法刪除的資料表,就是代理可能找到的資料表,而防線應設在模型之下:一份它可以讀取的資料表清單。

實用原則: 列出代理可以讀取的資料表,並讓舊資料表與暫存資料表不在清單上,即使它們的名稱最親切易懂。在模型所寫的每個查詢執行之前,依清單檢查其用到的資料表。即使代理在你的測試中從未選擇誘人的資料表,仍要保留這項檢查:新的問題、更大的結構描述或檢索而來的範例,都可能改變結果。

此部分尚未翻譯,因此以英文呈現。

Filters nobody wrote down

最近一次變動於第 2026.11.12 版

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.

cancelled-lines-counted.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 通過
"""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."""from lib.golden import golden_result, load_goldenfrom lib.model import RecordedModelfrom lib.prompts import schema_only_messagesfrom lib.tools import extract_sql, run_sqlfrom lib.warehouse import connect, describe_schemacon = 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)# The model's query returns (category, units) rows, which dict turns into a lookup by category.model_units = dict(run_sql(con, sql).rows)golden_units = dict(golden_result(con, question))# The status is on the order, so the lines join to fct_orders to read it.(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()# raw keeps test orders, which the marts drop; the is_test flag is only in raw.(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}")
輸出python cancelled-lines-counted.py
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

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

最近一次變動於第 2026.11.12 版

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.

classify-schema-only-errors.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 通過
"""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."""from collections import Counterimport duckdbfrom lib.failure_kinds import KINDS, failure_kindsfrom lib.golden import golden_result, grade, load_goldenfrom lib.model import RecordedModelfrom lib.prompts import schema_only_messagesfrom lib.tools import extract_sql, run_sqlfrom lib.warehouse import connect, describe_schemacon = connect()schema, model = describe_schema(con), RecordedModel("schema-only-sql")# Kind "answer" leaves out gq-005, the forecast to decline; IDs are zero-padded, so gq-001 to gq-016 is a text range.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:        # A query the database refuses to run is an error, not a wrong number.        outcome, errors[q.question_id] = "error", str(e).splitlines()[0]    # 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.    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)}")# DuckDB's messages: one for a column the table read does not have, one for a type mismatch such as LIKE on a timestamp.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}")
輸出python classify-schema-only-errors.py
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

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.

正式環境中會出什麼問題

最近一次變動於第 2026.11.12 版

本章的失敗案例,都來自為了找出失敗而設計的問題。在正式環境中,提問的人並不知道陷阱在哪裡。

兩個錯誤互相抵消,數字看起來正確。 帳篷問題的扇出與名稱比對造成的偏差方向相反,而兩個問題的 UTC 計數之所以吻合,是因為錯誤互相抵消,或根本無從顯現。對照已知數字的抽樣檢查會放行這些情況。要發現它們,只能依同一條規則將每個結果與黃金結果比對,並且閱讀通過的答案所用的 SQL,而不只是閱讀失敗的答案。

定義改了,舊定義仍在回答。 當財務團隊更改營收的認列範圍時,每個直接用欄位計算營收的查詢,仍然沿用舊的意義。若營收黃金問題的黃金 SQL 已經更新,這些問題就會開始失敗;若沒有更新,則會繼續通過。請將黃金 SQL 與其所依賴的定義放在一起,並且在變更任一方時,連同另一方一併審查。

舊版資料表日後會再出現。 在本章的執行中,模型從未選用 orders_legacy。較大的結構描述、檢索到的範例,或是使用舊版資料表用語的問題,都可能在你完全沒改程式碼的情況下改變這一點。請依允許使用的資料表清單檢查每個查詢用到的資料表,並記錄每個問題是由哪些資料表回答的,讓轉變能在計數上顯現出來。

細微的時間錯誤使報表彼此不一致。 與財務報表相差一筆訂單的計數,會引發一場對帳,而原因在於使用了 UTC 日期,但業務是以當地日期計算。你通常要等到同一期間的兩份報表不一致時才會發現,而那時已經太晚。請在兩種時鐘出現差異的期間邊界加入黃金問題,並且一直保留在問題集中。

篩選條件只套用在某處,其他地方沒有。 測試訂單在資料集市中已被移除,因此任何針對原始資料表的查詢都會把它們帶回來。能存取 raw 的代理程式,可能為了取得資料集市沒有的欄位而讀取它。請讓原始結構描述遠離代理程式能觸及的範圍,或將篩選條件寫入每個資料表的描述中。

失敗標記器漏掉新的類型。 lib/failure_kinds.py 只能找出本章已知的五種類型,其他一概找不到,帳篷名稱比對的案例就說明了這點。依類型統計失敗次數的儀表板看起來很完整,但新的類型可能在未被標記的情況下持續增加。請定期以人工閱讀一批錯誤答案的樣本,並在新類型出現兩次時加入一條規則。

實用原則: 在代理程式的數字送到任何人手上之前,請依黃金結果評分每個答案,在期間邊界與每個定義上都保留黃金問題,列出代理程式可讀取的資料表,並以人工閱讀一批錯誤答案的樣本。依類型統計錯誤數字,並將任一類型的計數上升視為警訊。

本章做出的決定

  • **五種看似合理的錯誤數字:**扇出(fan-out)、結構描述中沒有的定義、時區與會計年度曆法、誘人的資料表,以及未寫明的篩選條件。每一種都指向資料倉儲或提供給代理程式的內容中可做的修正。
  • **每一種失敗都實際執行,而非只加以描述。**模型犯下的每個失敗,都以其真實查詢執行;當兩個錯誤同時出現時,範例每次只更動一項選擇,以分別衡量其影響。
  • **11個黃金問題加入了題組,**即 gq-006 至 gq-016,每一題都寫成會觸及資料倉儲的某個陷阱,其黃金 SQL 依循財務團隊的定義。
  • **失敗類型由可閱讀的規則標記,**規則位於 lib/failure_kinds.py,做法是將每個查詢與其黃金 SQL 比對;沒有任何規則可標記的錯誤答案,則由人工閱讀。
  • **錯誤與錯誤數字分開計算。**無法執行的查詢是代價低的失敗;回傳看似合理數字的查詢,才是應優先修正的。
  • **舊版資料表仍保留在資料倉儲中,**即使模型在此從未選用它,因為你無法刪除的資料表,就是代理程式找得到的資料表。
  • **多數類型的修正方式是定義,而非提示。**提示可以承載一般的做法;企業自己的定義則應放在代理程式讀得到、資料倉儲能強制執行的地方。

本章,於本版次中

版次 2026.11.15範例於 2026年11 月 19 通過測試本章 6 個範例 個範例中有 6 個通過

譯自英文書,版次 2026.11.15。

引用本節

格式

各種格式皆引用英文原書,本譯本即依循該原書。

每種格式都會標示本版。其連結會開啟目前版次,節標題會說明該節內文最後一次變動的時間。存取日期與擷取日期即為你開啟此頁的當日。

本頁內容

第 2 章,共 11 章: Where raw text-to-SQL gets the number wrong

  1. Joins that fan out and inflate a sum
  2. 結構描述未涵蓋的定義
  3. Time zones and the fiscal calendar
  4. 名稱誘人的資料表
  5. Filters nobody wrote down
  6. Count the failures by kind
  7. 正式環境中會出什麼問題
  8. 本章做出的決定

返回頂端所有章節