從你自己的工作建立黃金問題
閱讀約需 68 分鐘 分鐘,9 節 中共有 8 個範例。
讀完本章後,你能根據公司裡的人實際提出的問題撰寫黃金問題,並為每一題附上黃金 SQL、提問者的角色,以及該題所需的答案類型。你能以黃金結果而非 SQL 文字來比較代理程式的結果,在信任黃金標準之前先檢查它本身,也能說明為什麼公開基準測試上的分數不等於你的資料倉儲上的分數。
第 1 章用五個黃金問題為兩種做法評分,而五個問題只能作為冒煙測試。提示詞的某項修改若修好或弄壞其中一題,分數就會變動五分之一;若修好一題又弄壞另一題,分數則完全不動。這五題也沒有任何一題測試代理程式面對含糊問題,或提問者的角色不得查看的問題時是否會拒答。本書後續每一章都會修改代理程式的某一部分,並宣稱該修改有幫助。這些宣稱需要一組大到足以量測的題目,題目要涵蓋資料倉儲的陷阱、角色與拒答,並且保留一部分,不讓任何修改針對它調校。
第 2 章又加入了十一題,每題針對一個陷阱。本章保留全部十六題,並將題組擴充到 48 個黃金問題,內容涉及商店的訂單、退款與客戶,代理程式查詢的就是這間商店所產生的資料倉儲。本章先說明為什麼要自己撰寫問題,因為公開基準測試已經存在,而且下載比撰寫便宜。
為什麼公開基準不等於你的測試
用於 text-to-SQL 或資料代理(data agent)的公開基準,會提供問題、資料庫、每個問題的標準答案(gold answer),以及他人在同一組資料上回報的分數。這使它適合用來比較模型,卻無法告訴你代理在你的資料倉儲上表現如何,原因有兩個。第一,基準的問題、資料與定義都屬於別人,因此高分只代表模型能處理他們的工作。第二,標準答案是由人撰寫的主張,所以分數同時也衡量了標準答案正確的頻率。
第二個原因已有實際量測。CIDR 2026 發表的一項稽核,請專家對照問題與結構描述(schema)檢查每一條標準查詢,執行它,並執行其子查詢。結果發現,BIRD Mini-Dev 的初版(500 題)有 52.8% 存在標註錯誤;Spider 2.0-Snow 中標準 SQL 公開的 121 題,在其 spider2-snow-0713 檔案裡有 66.1% 存在標註錯誤(Jin et al., CIDR 2026)。最常見的錯誤是標準 SQL 誤解了資料或結構描述。作者修正 100 題 BIRD Dev 問題並重新執行五個開源代理後,分數的相對變動從 −3% 到 +31%,其中一個代理從第四名升到第一名。基準也會在你不知情時改變:BIRD 團隊在 2026 年 10 月 26 日與 27 日修訂了 Mini-Dev,更正參考 SQL 並替換部分問題,同時保留 2025 年版本作為存檔(BIRD Mini-Dev)。這些數字是該論文的量測結果,不是我們的。本書引用 BIRD 與 Spider,但不隨書提供,因為本書只附帶以 CC BY 4.0、CC0 或自有授權釋出的資料,而 BIRD 採用 CC BY-SA 4.0。
第一個原因可以用本書能附帶的資料來呈現,因此本章取用 DABstep(Data Agent Benchmark for Multi-step Reasoning,多步推理資料代理基準)的一小部分,它採用 CC BY 4.0 授權。DABstep 針對一家支付處理商的交易提出問題,只對最終答案評分,並將大部分任務保密,用於其排行榜(論文)。該資料集共有 450 項任務,只有 10 項開發(dev)任務公開了答案(資料集)。本書的副本位於 examples/data/dabstep/,內含 DABstep 較低難度等級的三項開發任務,以及完整的支付資料表(138,236 筆交易);其中移除了任何任務都用不到的四個識別欄位:雜湊後的 IP 位址、消費者電子郵件、卡號,以及每張卡的銀行識別碼。SOURCE.md 記載了資料集所固定的修訂版本與每一項更動,LICENSE 則是資料集本身的授權聲明。支付資料表中的商家是虛構的企業,名稱沿用 DABstep 發布時的內容。lib/dabstep.py 會將該資料表載入 DuckDB。
我們以同一個模型 Qwen3-4B-Instruct-2507,在兩組資料上執行第 1 章的僅結構描述做法,並由錄製的回應重播。模型看到資料表的結構描述與一個問題,撰寫 SQL,然後執行。在 DABstep 上,模型接著讀取查詢結果,並以任務要求的格式給出最終答案。錄製檔 dabstep-runs 保存了這些回應。
1"""Score schema-only SQL on three public DABstep tasks and on the shop's dev questions, with the same model.2The point about task 49 rests on its "SQL ran: False" line, which the assert checks, and on the two top-country lines3that follow it, which read the same fraud data by two definitions."""4from lib import dabstep5from lib.golden import load_golden, schema_only_outcome6from lib.model import RecordedModel7from lib.warehouse import connect, describe_schema89payments = dabstep.connect()10runs = [dabstep.answer_task(payments, RecordedModel("dabstep-runs"), task) for task in dabstep.load_tasks()] # replays recorded replies11for run in runs:12 print(f"task {run.task.task_id}: answered {run.answer[:40]!r}, expected {run.task.answer!r}, correct: {run.correct}")13 print(f"task {run.task.task_id} SQL ran: {not run.result.startswith('error')}")14print(f"DABstep correct: {sum(run.correct for run in runs)} of {len(runs)}")15task49 = next(run for run in runs if str(run.task.task_id) == "49")16assert task49.result.startswith("error") # a query that fails comes back as text that starts with "error"1718# Task 49 asks for the top country "for fraud". DABstep's manual defines fraud as fraudulent volume over total volume.19by_count, by_volume = payments.execute("""20 SELECT arg_max(ip_country, frauds), arg_max(ip_country, fraud_share) FROM (21 SELECT ip_country, count(*) FILTER (has_fraudulent_dispute) AS frauds,22 sum(eur_amount) FILTER (has_fraudulent_dispute) / sum(eur_amount) AS fraud_share23 FROM payments GROUP BY ip_country)""").fetchall()[0] # one row: the top country by each measure24print(f"task 49 top by count of frauds: {by_count}")25print(f"task 49 top by fraud volume ratio: {by_volume}")2627con = connect()28schema, model = describe_schema(con), RecordedModel("schema-only-sql") # the recorded replies to the schema-only prompt29dev = [q for q in load_golden() if q.split == "dev"]30outcomes = [schema_only_outcome(con, model, schema, q) for q in dev]31print(f"shop dev correct: {outcomes.count('correct')} of {len(dev)}")
python public-vs-own-score.pytask 5: answered 'NL', expected 'NL', correct: True
task 5 SQL ran: True
task 49: answered '3. ES', expected 'B. BE', correct: False
task 49 SQL ran: False
task 70: answered '```sql\nSELECT \n AVG(CAST(p.has_fraudule', expected 'Not Applicable', correct: False
task 70 SQL ran: False
DABstep correct: 1 of 3
task 49 top by count of frauds: NL
task 49 top by fraud volume ratio: BE
shop dev correct: 2 of 24
兩組資料問的是不同的事,因此任一分數都無法預測另一個,差距本身也不是重點。僅結構描述的 SQL 答對 1 of 3 項 DABstep 任務(以簡化版的 DABstep 準精確比對評分),以及 2 of 24 個商店開發問題。DABstep 的任務只涉及一張資料表,沒有會計年度曆、沒有已取消的訂單,也沒有舊版資料集市(legacy mart);而商店的問題恰好卡在這些地方。
任務 49 顯示了標準答案取決於什麼。它詢問「詐欺」方面排名第一的國家,標準答案是 BE。若按詐欺交易的筆數計算,排名第一的國家是 NL;若以詐欺占支付金額的比例衡量,則是 BE。DABstep 的手冊解決了這個問題:詐欺是「詐欺金額占總金額的比率」(手冊),僅憑問題本身則無從判斷。此處模型為任務 49 撰寫的 SQL 無法執行,卻仍然說出了一個國家。任務 70 的標準答案是 Not Applicable。依我們對手冊的理解,其中描述了與詐欺相關的費用,卻沒有罰款。模型回覆的是一條查詢,而不是答案。
實用原則: 使用公開基準在共同任務上比較模型,絕不要把它當成代理能在你的資料倉儲上運作的證據。引用分數時,請一併註明基準的版本,因為標準答案集會被修正。請以根據你自己的工作、採用你自己的定義所撰寫的黃金問題(golden questions)來衡量你自己的代理,並預期兩種分數會不一致。
此部分尚未翻譯,因此以英文呈現。
Write questions from the work people do
A public benchmark measures someone else's work, so this set has to come from the shop. The best source is the questions people already ask: requests in the analytics team's queue, the numbers in a monthly finance pack, the filters people set on dashboards. Write each in the asker's words, not tidied into the schema's vocabulary, because the agent will hear the asker's words. Then write the golden SQL that answers it under the business's definitions, and check it.
Each golden question holds the same fields. The question is the asker's text. The role is who asks, which decides what they may see, and a regional_lead also carries their region. A golden question's kind says what a right answer looks like: answer when the warehouse can answer it, decline when it can't, and forbidden when the role may not see the answer. A decline also has a decline_type, ambiguous or unanswerable. An answerable question has golden_sql, a comparison rule (order_matters and a tolerance), and a note that names the definition the gold follows. Each question also lists the warehouse's traps it tests, such as fiscal-calendar or late-refunds, and sits in the dev or the test split.
The shop's set lives in examples/data/golden/questions.toml, and load_golden() in lib/golden.py reads it into GoldenQuestion objects. The file's header states the two definitions most questions depend on. Net revenue is the finance team's: euros after discounts and before tax, from real orders that weren't cancelled, on the order's local business day. An order count includes cancelled orders and leaves out test orders. One question in full shows what its fields hold.
1"""Load the shop's golden questions, show what one holds and how the set divides, and compute one gold's other reading.2The prose's refund point rests on the last two lines, which group the same refunds by the order's month and by the refund's."""3from collections import Counter45from lib.golden import golden_result, load_golden6from lib.warehouse import connect78con = connect()9questions = load_golden()10print(f"golden questions: {len(questions)}")11for kind, count in sorted(Counter(q.kind for q in questions).items()):12 print(f"{kind}: {count}")13for decline_type, count in sorted(Counter(q.decline_type for q in questions if q.kind == "decline").items()):14 print(f"decline, {decline_type}: {count}")15print(f"roles: {', '.join(sorted({q.role for q in questions}))}")1617q = next(q for q in questions if q.question_id == "gq-018")18print()19print(f"{q.question_id} ({q.split}, {q.kind}, asked by {q.role})")20print(f"question: {q.question}")21print(f"golden SQL: {' '.join(q.golden_sql.split())}")22print(f"traps: {', '.join(q.traps)}")23print(f"note: {q.note}")2425# The other reading: refunds grouped by the month they were made, not the month the order was placed.26by_refund_month = con.execute(27 "SELECT round(sum(amount_eur), 2) FROM analytics.fct_refunds WHERE refund_month = DATE '2026-03-01'").fetchall()28print(f"refunded on orders placed in March 2026: {golden_result(con, q)[0][0]:,.2f}") # gq-018's gold: by the order's month29print(f"refunded in March 2026, by refund month: {by_refund_month[0][0]:,.2f}")
python load-golden-questions.pygolden questions: 48
answer: 34
decline: 8
forbidden: 6
decline, ambiguous: 4
decline, unanswerable: 4
roles: analyst, finance, marketing, regional_lead, support
gq-018 (dev, answer, asked by finance)
question: How much was refunded, in euros, on orders placed in March 2026?
golden SQL: SELECT round(sum(r.amount_eur), 2) AS refunded FROM analytics.fct_refunds AS r WHERE r.order_month = DATE '2026-03-01'
traps: late-refunds, currency
note: Refunds belong to the order's month here, including refunds made in April or later.
refunded on orders placed in March 2026: 13,046.38
refunded in March 2026, by refund month: 6,646.99
Most of the set asks for an answer; the rest tests when to hold back. The set holds 48 questions: 34 to answer, 8 to decline and 6 that the asking role may not see.
Of the declines, 4 are ambiguous and 4 are unanswerable. Every role in the case asks at least one question.
The question the example prints, gq-018, is one a finance team asks after every month closes. Its golden SQL is short, and its value is in the definition: refunds belong to the month the order was placed, including refunds made weeks later. Refunds on orders placed in March 2026 come to 13,046.38 euros, and a reasonable query that picks refunds by the month they were made returns 6,646.99. Both numbers are useful. The golden question records which one this asker means, and its note says so in words a reviewer can check without reading SQL.
Most of the work is deciding what the asker meant and finding the definition someone owns. Write the note first. If you can't write it in one sentence, the question is ambiguous, and it becomes a decline.
Working rule: Collect golden questions from real requests, keep the asker's wording, and give each one a role, a kind and a note that names the definition its gold follows. Write the golden SQL against the marts the agent will use, and have the person who owns the definition read the note. Expect to spend more time on the definitions than on the SQL.
此部分尚未翻譯,因此以英文呈現。
Compare results, not SQL text
With the golden questions written, the next decision is how to tell whether an agent's answer matches. Comparing the agent's SQL with the golden SQL fails both ways. Two queries can be written differently and return the same rows, and two queries a few words apart can return different numbers. What the asker sees is the result, so that's what to compare. An execution match means the agent's result equals the golden result under the question's comparison rule. Benchmarks do the same: BIRD reports execution accuracy, which compares the rows each query returns (calculate_ex in BIRD's evaluation code), and DABstep scores the final answer by a quasi-exact match (the paper).
In the shop, gq-001 asks for net revenue in fiscal year 2026. Its golden SQL sums net_revenue_eur over fct_orders, joined to dim_date for the fiscal year. Its check query, which the section on checking the gold uses, sums net_amount_eur over fct_order_lines with a date range instead. Two queries show where text and results part ways, each compared with the golden SQL: the check query, and the golden SQL with its filter on cancelled orders removed. For each, it asks whether the text is equal, whether it is equal after sqlglot normalizes both, and whether the results match under results_match().
1"""Two queries written differently for the same golden question: compare their text, then compare their results.2The point rests on the last two result lines, "results match": the order-lines query matches the golden result, and3the query that forgets cancelled orders does not. The asserts at the end check both."""4import sqlglot5from lib.golden import golden_result, load_golden, results_match6from lib.warehouse import connect78con = connect()9q = next(q for q in load_golden() if q.question_id == "gq-001") # net revenue in fiscal year 202610by_orders, by_lines = q.golden_sql, q.check_sql # orders joined to dim_date; lines by date range11forgets_cancelled = by_orders.replace("o.status <> 'cancelled' AND ", "") # the golden SQL's cancelled-order filter, removed121314def normalized(sql: str) -> str:15 # parses and writes the SQL back in DuckDB's form, so spacing and identifier case don't count as differences16 return sqlglot.parse_one(sql, read="duckdb").sql(dialect="duckdb", normalize=True, pretty=False)171819golden = golden_result(con, q)20for name, sql in [("by lines", by_lines), ("forgets cancelled", forgets_cancelled)]:21 rows = con.execute(sql).fetchall()22 print(f"{name}: {rows[0][0]:,.2f} against golden {golden[0][0]:,.2f}")23 print(f"{name} same text: {sql == by_orders}")24 print(f"{name} same normalized text: {normalized(sql) == normalized(by_orders)}")25 print(f"{name} results match: {results_match(rows, golden)}")26# a change to either result must fail this run, as the prose states both facts27assert results_match(con.execute(by_lines).fetchall(), golden)28assert not results_match(con.execute(forgets_cancelled).fetchall(), golden)
python results-match-rules.pyby lines: 1,664,762.75 against golden 1,664,763.72
by lines same text: False
by lines same normalized text: False
by lines results match: True
forgets cancelled: 1,755,242.00 against golden 1,664,763.72
forgets cancelled same text: False
forgets cancelled same normalized text: False
forgets cancelled results match: False
The query over order lines reads a different table, so its text differs from the gold's even after normalizing. Its result matches: it totals 1,664,762.75 against golden 1,664,763.72, under a euro from the gold, since each line is rounded to the cent and the tolerance absorbs that. The query that forgets cancelled orders returns 1,755,242.00 against golden 1,664,763.72, and its result doesn't match. In text, it is one predicate away from the gold. Normalizing doesn't rescue a comparison of text in either direction, because no parser can tell that two different joins give the same sum on this data.
An execution match has its own blind spot. A query can return the right number for the wrong reason on today's data, such as a filter that happens to remove no rows. Checking more questions, and reading the SQL behind surprising matches, is the defence. That's one reason the set has 48 questions and not 5.
Working rule: Score an agent by comparing its result with the golden result, never by comparing SQL text, normalized or not. Keep one comparison function for every score you report, so a change in the score is a change in the agent. When a match surprises you, read the agent's SQL, because a result can match by coincidence.
此部分尚未翻譯,因此以英文呈現。
Order, rounding and column names
A comparison of results still needs rules for the ways two correct results differ. Rows come back in any order unless the query sorts them. Columns carry whatever names the writer chose, in whatever order. Numbers are rounded differently. A rule that is too strict marks correct answers wrong, and a rule that is too loose lets wrong ones through.
results_match(), the comparison every score in this book uses, makes these choices. Rows may come in any order, unless the golden question sets order_matters. Column names are ignored, and columns may come in any order: it tries each way of lining up the answer's columns with the golden ones. Extra columns are allowed, such as a month next to a total for that month, but the number of rows must be equal. Numbers must agree with the gold within 0.05% relative by default, plus an absolute slack of half a unit in the last place the gold rounds to. For money, rounded to the cent, that's half a cent, the default. Changing gq-002's golden result, net revenue in each region in March 2026, the ways real answers differ, and comparing each with the original shows which differences the comparison forgives.
1"""Change the golden result for net revenue by region in March 2026 in the ways real answers differ, and compare.2Then move a ratio gold, gq-033's conversion rates, by a near miss. The prose's claims about thousands and about the3near miss rest on the lines that print True or False, and the asserts check them."""4from lib.golden import golden_result, load_golden, results_match5from lib.warehouse import connect67con = connect()8q = next(q for q in load_golden() if q.question_id == "gq-002")9golden = sorted((region, float(revenue)) for region, revenue in golden_result(con, q)) # sorted by region, so the variants line up10print(f"golden: {golden}")11print(f"tolerance: {q.tolerance:.2%} relative")12print(f"absolute slack: {q.absolute}")1314variants = {15 "rows reversed": golden[::-1],16 "columns swapped": [(revenue, region) for region, revenue in golden],17 "extra month column": [(region, "2026-03", revenue) for region, revenue in golden],18 "rounded to whole euros": [(region, round(revenue)) for region, revenue in golden],19 "rounded to thousands": [(region, round(revenue, -3)) for region, revenue in golden], # round(x, -3) rounds to the nearest thousand20 "in thousands of euros": [(region, revenue / 1000) for region, revenue in golden], # same numbers, unit changed to thousands21 "one region missing": golden[:-1], # drops the last region22}23for name, rows in variants.items():24 print(f"{name}: {results_match(rows, golden, q.order_matters, q.tolerance)}")25# reporting in thousands of euros must fail: a change of unit the agent doesn't state is a real error26assert not results_match(variants["in thousands of euros"], golden, q.order_matters, q.tolerance)2728ranked = sorted(golden, key=lambda row: -row[1]) # highest revenue first, so the reversed list is the wrong order29print(f"ranking reversed, order matters: {results_match(ranked[::-1], ranked, order_matters=True)}")3031# A ratio: gq-033's conversion rates, rounded to four places, with its absolute slack of half that last place.32rates_q = next(q for q in load_golden() if q.question_id == "gq-033")33rates = [(channel, float(rate)) for channel, rate in golden_result(con, rates_q)]34near_miss = [(channel, rate + 0.004) for channel, rate in rates] # 0.004 is about 3% of each rate35print(f"gq-033 rates: {min(r for _, r in rates):.4f} to {max(r for _, r in rates):.4f}")36print(f"gq-033 absolute slack: {rates_q.absolute:.5f}")37print(f"rates 0.004 higher, gq-033's rule: "38 f"{results_match(near_miss, rates, rates_q.order_matters, rates_q.tolerance, rates_q.absolute)}")39print(f"rates 0.004 higher, half a cent of slack: {results_match(near_miss, rates, rates_q.order_matters, rates_q.tolerance)}")40# under the question's own slack the near miss is a miss; with half a cent of slack the same near miss matches41assert not results_match(near_miss, rates, rates_q.order_matters, rates_q.tolerance, rates_q.absolute)42assert results_match(near_miss, rates, rates_q.order_matters, rates_q.tolerance)
python tolerant-comparison.pygolden: [('AMER', 51762.14), ('APAC', 35296.93), ('EMEA', 63023.76)]
tolerance: 0.05% relative
absolute slack: 0.005
rows reversed: True
columns swapped: True
extra month column: True
rounded to whole euros: True
rounded to thousands: False
in thousands of euros: False
one region missing: False
ranking reversed, order matters: False
gq-033 rates: 0.1156 to 0.1284
gq-033 absolute slack: 0.00005
rates 0.004 higher, gq-033's rule: False
rates 0.004 higher, half a cent of slack: True
Reversed rows, swapped columns and an extra month column all match, and so does rounding to whole euros, which moves a regional total by at most half a euro. Rounding to thousands of euros fails, and so does reporting in thousands of euros. A person reading a small number under a heading of thousands would understand it, but the comparison can't see the heading. That's deliberate. An agent that changes units without saying so is a risk, so the golden question fixes the unit and the agent must meet it. A missing region fails. For a ranking, such as gq-039's, order_matters is set, so the regions ranked by revenue in reverse order fail too.
The tolerance needs care in both directions. Ratios such as a conversion rate are small numbers, where half a cent of slack is a lot. gq-033's conversion rates run from 0.1156 to 0.1284, so 0.005 is about 4% of each. Its gold rounds to four places, and its absolute slack is half that last place, 0.00005. Adding 0.004 to every rate is a near miss at this scale: under gq-033's rule it fails, and with half a cent of slack it would match. The book's other ratios, gq-031 and gq-034, follow the same rule.
Working rule: Ignore row order, column order and column names, allow extra columns, and require the same number of rows and numbers within a small relative tolerance. Round each gold to a stated place, and allow half a unit in that place on top. Set order_matters only when the question asks for a ranking or a sequence. Fix units and scale in the question or its note, and treat an answer in other units as wrong, because nobody reading it can tell.
此部分尚未翻譯,因此以英文呈現。
Check the gold before you trust it
Public benchmarks show what happens to unchecked gold: about half of one set was wrong. A golden question written in an afternoon is no different. Its SQL can miss a filter, read the wrong table or follow a definition nobody owns, and every score built on it inherits the mistake. So check each gold before it scores anything. It must run. It must return rows, without NULL values, since an empty gold matches any query that also returns nothing. Where you can, a second query written another way, the gold's check_sql, must return the same result as the golden SQL. And its note must name the definition it follows, so a person can say whether it's the right one.
The shop's set gets those checks, with each check query written independently of its golden SQL: from order lines instead of orders, from a date range instead of the fiscal calendar, or by counting another way. Then look at one question in two readings: which country is top for refunds in fiscal 2026, by the number of refunds and by refund rate.
1"""Check the gold before trusting it: every golden SQL runs and returns rows, and each check query agrees with it.2The prose's gold claims rest on the counts of runs, empty results and agreeing checks, and its refund point on the last two readings."""3import duckdb4from lib.golden import golden_result, load_golden, results_match5from lib.warehouse import connect67con = connect()8answerable = [q for q in load_golden() if q.kind == "answer"]9results, failed = {}, []10for q in answerable:11 try:12 results[q.question_id] = golden_result(con, q)13 except duckdb.Error:14 failed.append(q.question_id)15empty = [qid for qid, rows in results.items() if not rows or any(v is None for row in rows for v in row)] # a NULL in any cell fails, too16print(f"golden SQL that runs: {len(results)} of {len(answerable)}")17print(f"golden SQL that fails: {', '.join(failed) or 'none'}")18print(f"empty or NULL results: {len(empty)}")1920checked = [q for q in answerable if q.check_sql and q.question_id in results]21agree = [q for q in checked if results_match(con.execute(q.check_sql).fetchall(), results[q.question_id],22 q.order_matters, q.tolerance, q.absolute)]23print(f"questions with a check query: {len(checked)}")24print(f"checks that agree: {len(agree)} of {len(checked)}")2526# One question, two readings: "top country for refunds" by number of refunds, and by refund rate (gq-028's definition).27# orders join to their local business day; the LEFT JOIN keeps orders that have no refund, so they still count28by_count, by_rate = con.execute("""29 SELECT arg_max(country, refunds), arg_max(country, refunded / gross) FROM (30 SELECT o.country, count(r.refund_id) AS refunds, sum(r.amount_eur) AS refunded,31 sum(o.gross_revenue_eur) AS gross32 FROM analytics.fct_orders AS o33 JOIN analytics.dim_date AS d ON d.date_day = o.order_date_local34 LEFT JOIN analytics.fct_refunds AS r USING (order_id)35 WHERE o.status <> 'cancelled' AND d.fiscal_year = 202636 GROUP BY o.country)""").fetchall()[0] # one row: arg_max returns the country at the largest value37print(f"top country by number of refunds: {by_count}")38print(f"top country by refund rate: {by_rate}")39print(f"gq-028 golden: {results['gq-028'][0][0]}")
python gold-runs-and-agrees.pygolden SQL that runs: 34 of 34
golden SQL that fails: none
empty or NULL results: 0
questions with a check query: 7
checks that agree: 7 of 7
top country by number of refunds: US
top country by refund rate: NL
gq-028 golden: NL
The golden SQL runs for 34 of 34 answerable questions, and 0 of them come back empty or with a NULL. Of the 7 questions with a check query, the check agrees with its gold in 7 of 7.
The two readings of the refund question disagree, the same trap as DABstep's task 49: by refunds the top country is US, and by refund rate it is NL. The golden question gq-028 says "highest refund rate" in its text, and its note defines the rate as refunded euros over gross revenue of the same orders. Without both, an agent that answered the other reading would be scored wrong for answering a question nobody wrote down.
The checks above find gold that is broken. Reading the agent's wrong answers finds gold that is unclear. gq-031 first asked about the average number of "items" in an order, and the schema-only baseline from chapter 1 got it wrong: its SQL averaged line_count, the number of order lines, where the gold sums units. "Items" allows both readings. The defect was in the question, not the agent, so the question now asks for units, counting quantities, not order lines. Recorded replies are keyed by the exact question text, so the reworded question needed a new recording. Replaying both wordings below shows the effect.
1"""Read a wrong answer: gq-031 once asked about "items", which allows units or order lines. It now asks for units.2The two `outcome` lines settle whether each wording's baseline answer is right against the gold printed first."""3from dataclasses import replace45from lib.golden import golden_result, load_golden, schema_only_outcome6from lib.model import RecordedModel7from lib.prompts import schema_only_messages8from lib.tools import extract_sql9from lib.warehouse import connect, describe_schema1011con = connect()12schema, model = describe_schema(con), RecordedModel("schema-only-sql")13q = next(q for q in load_golden() if q.question_id == "gq-031")14print(f"gq-031 golden: {golden_result(con, q)[0][0]}")15for wording, asked in [("items", replace(q, question="How many items did an order contain on average in 2025?")), # the old wording; the recording keys replies by question text16 ("units", q)]:17 sql = extract_sql(model.complete(schema_only_messages(schema, asked.question)))18 print(f"{wording} wording: {asked.question}")19 print(f"baseline answer, {wording} wording: {con.execute(sql).fetchall()[0][0]:.4f}")20 print(f"baseline outcome, {wording} wording: {schema_only_outcome(con, model, schema, asked)}")
python reworded-golden-question.pygq-031 golden: 3.4117
items wording: How many items did an order contain on average in 2025?
baseline answer, items wording: 2.5691
baseline outcome, items wording: wrong
units wording: How many units did an order contain on average in 2025, counting quantities, not order lines?
baseline answer, units wording: 3.4124
baseline outcome, units wording: correct
The units wording is right, though its query is still off. The items wording got 2.5691, wrong against a gold of 3.4117, and the units wording got 3.4124. The units query keeps cancelled orders and takes the year from the UTC timestamp, and on this data neither moves the average past the tolerance. That's the blind spot of an execution match: the answer is right, and the query isn't.
At a larger scale, a model can propose suspect golds for a person to check. The extended version of the CIDR audit describes an agent built for this, which flagged erroneous BIRD Mini-Dev examples with 83% precision (Jin et al., arXiv:2601.08778). A person still decides each one.
Working rule: Before a golden question scores anything, run its SQL, reject empty and NULL results, compare it with an independent query where you can, and have the definition's owner read its note. Read the agent's wrong answers for unclear questions, and fix the question, not the score. When a question can be read two ways, name the reading in its text, or make it a decline.
涵蓋拒答與角色,而不只是答案
經過檢查的標準答案集讓每一道問題都值得信賴,但整組問題仍須測試代理人實際會遇到的情況。若問題集只有可回答的問題,就會獎勵凡事都回答的代理人:它即使亂猜也不會失分。因此,這間商店的問題集納入了正確答案是拒答的問題,並分成兩種類型,因為正確的行為並不相同。含糊的問題,例如「誰是我們最好的客戶?」,有數種合理的答案,代理人應說明它需要確認哪些選項。無法回答的問題,例如運費的成本,所詢問的資料並不在資料倉儲中,代理人應如實說明。禁止的問題則是可以回答,但提問的角色不得取得答案:商店的財務團隊不得查看客戶的電子郵件,區域主管只能查看自己所屬的區域。第 8 章建立拒答機制,第 9 章建立存取規則。本章要確保問題集能為這兩者評分。
涵蓋範圍也包括資料倉儲的陷阱與商店的角色。商店資料倉儲中內建的每一種陷阱,從測試訂單到舊版資料集市,都應出現在兩個分割中,如此一來,任何破壞某個陷阱處理方式的變更,都會在其中一個分數上顯現。依分割統計問題的類型、角色與陷阱,就能看出問題集在哪些地方偏薄。
1"""Count what the golden set covers in each split: kinds, roles and the warehouse's traps.2The prose's coverage point rests on the kind counts and on the last line, which names any trap missing from a split."""3from collections import Counter45from lib.golden import load_golden67questions = load_golden()8splits = ("dev", "test")9for split in splits:10 print(f"{split} questions: {sum(q.split == split for q in questions)}")11print("split answer decline forbidden")12for split in splits:13 kinds = Counter(q.kind for q in questions if q.split == split)14 print(f"{split:<5} {kinds['answer']:>6} {kinds['decline']:>7} {kinds['forbidden']:>9}")1516for split in splits:17 roles = Counter(q.role for q in questions if q.split == split and q.kind == "answer")18 print(f"{split} answers by role: {dict(sorted(roles.items()))}")1920traps = {split: Counter(t for q in questions if q.split == split for t in q.traps) for split in splits}21print("trap dev test")22for trap in sorted(traps["dev"] | traps["test"]):23 print(f"{trap:<20} {traps['dev'][trap]:>3} {traps['test'][trap]:>4}")24missing = sorted(t for t in traps["dev"] | traps["test"] if not traps["dev"][t] or not traps["test"][t]) # Counter gives 0 for an absent trap25print(f"traps missing from a split: {', '.join(missing) or 'none'}")
python golden-coverage.pydev questions: 24
test questions: 24
split answer decline forbidden
dev 17 4 3
test 17 4 3
dev answers by role: {'analyst': 15, 'finance': 1, 'regional_lead': 1}
test answers by role: {'analyst': 10, 'finance': 3, 'marketing': 2, 'regional_lead': 1, 'support': 1}
trap dev test
cancelled-orders 5 8
currency 2 3
definition 2 2
duplicate-customers 2 2
fan-out 2 2
fiscal-calendar 3 4
late-refunds 1 2
legacy-table 2 1
local-business-day 2 2
net-vs-gross 2 2
null-channel 1 1
role-scope 4 4
test-orders 5 4
vat 2 1
traps missing from a split: none
類型是平衡的:每個分割中,可回答的問題、拒答問題與禁止的問題數量相同。每一種陷阱都出現在兩個分割中,最後一行為 traps missing from a split: none。有些陷阱在同一個分割中只靠一道問題支撐,例如測試分割中的 VAT,因此只要漏掉一個標籤,就會讓某個陷阱從該分割消失,而這一行就會指出它。
角色則不平衡。開發分割中可回答的問題,是由分析師、財務與一位區域主管提出的。行銷與客服只在測試分割中提出可回答的問題。由於提示與脈絡是依據開發分割調整的,沒有任何調整會接觸到代理人應能回答的行銷問題,第一次為這類問題評分,是在保留的測試集上。我們讓這種不平衡保持可見,而不是在看過分數之後才在分割之間搬動問題;接下來為這兩個角色撰寫的問題會放入開發分割。
**實用原則:**一開始就納入拒答問題與禁止的問題,並將拒答分為含糊與無法回答兩種,因為正確的答案不同。為每一道問題標註它所測試的陷阱,並在評分之前依分割統計類型、角色與陷阱。要補足缺口,應撰寫新的問題,而不是在看過代理人的表現之後,將既有的問題在分割之間搬動。
保留一份測試切分
一份涵蓋了正確內容的資料集,仍可能因為使用方式而造成誤導。每當你因為某個問題失敗而修改提示詞,就讓代理程式更貼近那些問題一點。修改的次數夠多之後,它們的分數衡量的是你調校得多好,而不是代理程式實際運作得多好。保留切分(held-out split)是常見的防範做法:在一部分上調校,只有在回報結果時才對另一部分評分。基準測試的隱藏測試集可以降低答案洩漏到訓練資料中的風險(DABstep 論文),但無法阻止人們針對其問題的風格調校。你自己的保留切分,必須靠工作方式來保護。
商店的資料集有 24 個 dev 問題,每一章都可以用來調校;另有 24 個 test 問題,每一章只為了回報的數字而對它評分。在兩個切分上為第 1 章只使用結構描述的基準評分,就得到之後每一章都必須超越的門檻。
1"""The baseline every later chapter must beat: schema-only SQL scored on the dev split and on the held-out test split.2The prose's split counts are the per-split lines, and its declines, forbidden and answer counts the last three lines."""3from collections import Counter45from lib.golden import load_golden, schema_only_outcome6from lib.model import RecordedModel7from lib.warehouse import connect, describe_schema89con = connect()10schema, model = describe_schema(con), RecordedModel("schema-only-sql")11outcomes = {"dev": Counter(), "test": Counter()}12by_kind = Counter()13for q in load_golden():14 outcome = schema_only_outcome(con, model, schema, q)15 outcomes[q.split][outcome] += 116 by_kind[q.kind, outcome == "correct"] += 1 # (kind, correct) pairs, counted over both splits1718for split, counts in outcomes.items():19 total = sum(counts.values())20 for outcome in ("correct", "wrong", "error", "declined"): # "error" when the SQL fails; grade() gives the rest21 print(f"{split} {outcome}: {counts[outcome]} of {total}")22 print(f"{split} accuracy: {counts['correct'] / total:.1%}")23for kind in ("answer", "decline", "forbidden"):24 print(f"{kind} correct, both splits: {by_kind[kind, True]} of {by_kind[kind, True] + by_kind[kind, False]}")
python baseline-on-both-splits.pydev correct: 2 of 24
dev wrong: 14 of 24
dev error: 8 of 24
dev declined: 0 of 24
dev accuracy: 8.3%
test correct: 3 of 24
test wrong: 13 of 24
test error: 8 of 24
test declined: 0 of 24
test accuracy: 12.5%
answer correct, both splits: 5 of 34
decline correct, both splits: 0 of 8
forbidden correct, both splits: 0 of 6
基準在需要判斷的地方最弱。在兩個切分合計中,它答對 0 of 8 個應拒絕的問題;禁止類問題答對 0 of 6 個,可回答的問題答對 5 of 34 個。它從不拒絕任何問題,因為它只會撰寫 SQL。
它答對 2 of 24 個 dev 問題,準確率為 8.3%;答對 3 of 24 個測試問題,準確率為 12.5%。其餘的問題都失敗了:查詢無法執行、結果錯誤,或是回答了本應拒絕的問題。
兩個切分只相差一個問題,而且目前兩者都還沒有被拿來調校。一旦各章開始在 dev 上修改上下文,預期兩者會逐漸拉開差距。如果 dev 分數持續上升而測試分數沒有,就表示這些修改是在貼合 dev 問題,而不是貼合商店的實際需求。每個切分只有 24 個問題,一個問題約等於四個百分點的準確率,因此一兩個問題的差異應視為雜訊,並且要比較修改前後的同一批問題,而不是比較兩個總分。
實用原則: 在調校任何東西之前,只切分一次黃金問題,並且只在 dev 上調校。只有在回報結果時才為測試切分評分,並且並列回報兩個切分。當測試問題被看過太多次,以致人們開始針對它們調校時,就撰寫新的測試問題,並把舊的退回 dev。
正式環境中會出什麼問題
本書中用來衡量代理程式(agent)的,是商店的 48 個黃金問題。同樣的做法用在真實的資料倉儲上,會以下列方式失敗,而每一種都有察覺的方法。
定義改變時,黃金標準會過時。 如果財務部門更改了淨營收的定義,所有加總 net_revenue_eur 的黃金查詢都可能出錯,卻不會失敗。請把黃金問題與定義、指標一起放在版本庫中,一併審查變更,並在每次變更時重新執行檢查查詢。檢查查詢一旦不再吻合,就是警訊。
黃金結果會隨資料到達而變動。 退款在訂單成立後的數個月內仍會陸續到達,因此每次退款資料表載入時,gq-018 在 2026 年 3 月訂單上的退款都會增加。針對未結算期間的黃金問題,每週的黃金結果都不同,若比對的是上週儲存的結果,就會無故失敗。請像 golden_result() 那樣,在評分時由黃金 SQL 計算黃金結果;對於數字必須固定的問題,則優先選用已結算的期間。
問題集不再像人們實際提出的問題。 問題會隨業務而變:新的通路、新的地區、新的會計年度政策。只寫一次的問題集會逐漸偏離實際。請從人們問代理程式的問題中抽樣,為它答錯或拒答的問題撰寫黃金問題,並加入 dev。
測試切分發生洩漏。 有人閱讀未通過的測試問題,並針對這些問題調整提示詞,結果測試分數上升,其他方面卻毫無改善。當針對某一個問題的修改使 dev 與 test 分數一起變動時,你就能察覺。請讓測試問題遠離調校討論;一旦洩漏,就要更新這些問題。
基準測試的分數會隨基準測試而變。 廠商在 BIRD 或 Spider 上的準確率,描述的是維護者持續修正的某一個版本。比較模型時,請在每個分數旁記錄基準測試的版本,並在切換之前用自己的問題集重新評分。
比較方式掩蓋了單位的改變。 代理程式若開始以千歐元為單位回傳營收,所有金額問題會同時失敗,而較寬鬆的容差反而會掩蓋這一點。請讓容差保持小而且是相對的;當許多問題同時失敗時,先閱讀其中一個答案,再去動規則。
實用原則: 把黃金問題集當作程式碼對待:與定義放在一起、在 CI 中執行其檢查、在評分時計算黃金結果、把來自真實流量的問題加入 dev,並保護測試切分。分數變動時,先閱讀有變化的答案,再去更動問題集或比較方式。
本章做出的決定
- **主要評測集是商店自己的:**黃金問題依據商店各團隊的實際工作撰寫,附有角色、類型與定義,存放於
examples/data/golden/questions.toml。 - **公開基準只引用,不用於計分。**BIRD 與 Spider 2.0 僅以引文形式出現。DABstep 的三項任務子集採用 CC BY 4.0 授權,並以修訂版本固定,用來說明公開分數與你的資料倉儲上的分數並不一致。
- **DABstep 的資料連同其授權一起提供。**該子集保留資料集原有的
LICENSE,移除四個識別碼欄位,保留每一列資料與商家的虛構名稱,並由SOURCE.md說明每項變更。 - **比較的是結果,而非 SQL 文字,**所有計分都使用同一個函式
results_match():除非問題要求排序,否則列的順序不拘;忽略欄位名稱與順序;允許多餘的欄位;數值允許微小的相對誤差,再加上標準答案最後一個四捨五入位數的半個單位。 - **每個標準答案在計分前都先經過檢查:**它能執行、回傳的列不含 NULL,且在有獨立查詢可比對時與之相符。其註記會指明它所依據的定義。
- **拒答與禁止類問題也是評測集的一部分:**拒答分為語意含糊與無法回答兩類,禁止類問題則涵蓋商店的各個角色。
- **評測集只切分一次,分成大小相等的開發集與測試集,**並使各類型保持平衡。各章在開發集上調整,只有在要報告數字時才用測試集計分。
- **僅憑綱要產生的 SQL 是基準線,**適用於兩個切分,之後每一章都會報告相對於它的變化。
本章,於本版次中
版次 2026.11.15範例於 2026年11 月 19 通過測試本章 8 個範例 個範例中有 8 個通過
譯自英文書,版次 2026.11.15。