从你自己的工作中构建黄金问题
阅读约需 67分钟 分钟,9节中含8个示例。
读完本章,你将能够根据企业中人们提出的问题编写黄金问题,并为每个问题配上黄金 SQL、提问的角色以及所需的答案类型。你可以拿智能体的结果与黄金结果比较,而不是比较 SQL 文本;可以在信任黄金标准之前先检查它本身;还可以说明为什么公开基准上的分数并不等于你的数据仓库上的分数。
第 1 章用五个黄金问题给两种方法打了分,而五个问题只能当作冒烟测试。对提示词的一处改动,若修好或弄坏其中一个问题,分数就会变动五分之一;若修好一个又弄坏另一个,分数则纹丝不动。这五个问题中,没有一个用来检验对含糊问题的拒答,也没有一个用来检验对提问者所属角色无权查看的问题的拒答。本书后面的每一章都会改动智能体的某一部分,并声称这一改动带来了提升。这些说法需要一个大到足以度量的问题集:其中的问题要覆盖数据仓库的各种陷阱、角色和拒答,还要留出一部分,不拿它来调优任何改动。
第 2 章又增加了十一个问题,每个针对一种陷阱。本章保留全部十六个问题,并把问题集扩充到 48 个黄金问题,内容涉及这家商店的订单、退款和客户,智能体查询的就是这家商店生成的数据仓库。本章先讨论为什么要自己编写问题,因为公开基准已经存在,下载它们比编写问题更省事。
为什么公开基准不等于你的测试
面向 text-to-SQL 或数据智能体的公开基准会提供问题、数据库、每个问题的标准答案,以及他人在同一数据集上报告过的分数。这使它适合用来比较模型,但无法说明智能体在你的数据仓库上表现如何,原因有二。其一,基准中的问题、数据和定义都属于他人,所以高分只说明模型能处理他们的工作。其二,标准答案是人写下的论断,因此分数同时也在衡量标准答案本身的正确率。
第二个原因已有实测。CIDR 2026 上发表的一项审计,请专家对照问题和模式检查每条标准查询,运行它,并运行其子查询。结果发现,在 BIRD Mini-Dev 的首个 500 题版本中,52.8% 存在标注错误;在 Spider 2.0-Snow 中标准 SQL 公开的 121 个问题里,66.1% 存在标注错误,对应其 spider2-snow-0713 文件(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 个开发任务公开了答案(数据集)。本书的副本位于 examples/data/dabstep/,包含 DABstep 较低难度级别的三个开发任务,以及含 138,236 笔交易的完整支付表,但去掉了任何任务都用不到的四个标识列:哈希处理后的 IP 地址、购物者电子邮箱和卡号,以及每张卡的银行识别码。SOURCE.md 记录了数据集的固定修订版本和每一处改动,LICENSE 是数据集自带的声明。支付表中的商户是虚构的企业,名称沿用 DABstep 发布时的写法。lib/dabstep.py 将该表加载到 DuckDB 中。
我们在两组数据上运行第 1 章的仅用模式方法,使用同一个模型 Qwen3-4B-Instruct-2507,并从录制内容回放。模型看到表的模式和一个问题,写出 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 在 DABstep 任务上答对 1 of 3,评分采用 DABstep 准精确匹配的简化版本;在开发问题上答对 2 of 24。DABstep 的任务只涉及一张表,没有财年日历、没有已取消订单,也没有遗留数据集市,而商店的问题恰恰在这些方面出错。
任务 49 说明了标准答案取决于什么。它问的是“就欺诈而言”排名第一的国家,标准答案是 BE。按欺诈交易的笔数计算,排名第一的国家是 NL;按欺诈金额占支付总额的比例衡量,则是 BE。DABstep 的手册给出了定论:欺诈是“欺诈金额与总金额之比”(手册),仅凭问题本身则无法判定。在任务 49 中,模型写出的 SQL 未能运行,却仍然给出了一个国家。任务 70 的标准答案是 Not Applicable。依我们对手册的理解,它描述了与欺诈相关的费用,但没有罚款。模型回复的是一条查询,而不是答案。
工作准则: 公开基准用于在共同任务上比较模型,不能当作智能体在你的数据仓库上可用的证据。引用分数时要注明基准的版本,因为标准答案集会被修正。应当用根据你自己的工作、采用你自己的定义写成的黄金问题来衡量你的智能体,并预期两个分数会不一致。
这部分尚未翻译,因此以英文显示。
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。有些陷阱在一个划分中只依赖一个问题,例如测试划分中的增值税,因此只要缺少一个标签,就会使某个陷阱从一个划分中消失,而这一行会指出它。
角色并不均衡。开发划分中可回答的问题,由分析师、财务和一位区域负责人提出。市场和支持角色只在测试划分中提出可回答的问题。由于提示词和上下文是在开发集上调优的,调优时不会遇到智能体应当回答的市场类问题,这类问题第一次被打分是在留出集上。我们让这种不均衡保持可见,而不是在看到得分之后在划分之间移动问题;接下来为这两个角色编写的问题会放入开发集。
工作准则: 从一开始就包含拒答和禁止类问题,并把拒答分为含糊和无法回答两类,因为正确答案不同。为每个问题标注它所测试的陷阱,并在打分之前按划分统计类别、角色和陷阱。要弥补缺口,就编写新问题,而不是在看到智能体的表现之后,把现有问题在划分之间移动。
留出测试集
一个覆盖了正确内容的集合,仍可能因使用方式而产生误导。每当你因为某个问题失败而修改提示词,就会让智能体更贴近这些问题一点。修改足够多次之后,它在这些问题上的得分衡量的是你调得有多好,而不是智能体本身有多好用。留出一部分数据是通行的防范办法:在一部分上调优,只在报告结果时才对另一部分打分。基准的隐藏测试集可以降低答案泄漏到训练数据中的风险(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。
生产环境中会出什么问题
本书中的智能体,是用商店的 48 个黄金问题来衡量的。在真实的数据仓库上采用同样的做法,会以下面这些方式出问题,而每一种问题都有相应的发现办法。
定义一变,黄金答案就会过时。 如果财务部门修改了净收入的定义,那么所有对 net_revenue_eur 求和的黄金查询都可能出错,却不会报失败。应将黄金问题与定义、指标一起放在代码仓库中,一并评审变更,并在每次变更时重新运行检查查询。检查查询一旦不再一致,就是警报。
黄金结果会随数据到达而变化。 订单产生后的几个月里,退款仍会不断到达,因此每当退款表加载一次,gq-018 在 2026 年 3 月订单上的退款就会增加。针对未结账期的黄金问题,每周得到的黄金结果都不同,而与上周存储的结果相比较,会无缘无故地失败。应像 golden_result() 那样,在评分时由黄金 SQL 计算黄金结果;对于数字必须保持不变的问题,应优先选用已结账期。
问题集不再像人们实际提出的问题。 问题随业务而变:新渠道、新地区、新的财政政策。只写一次的问题集会逐渐偏离这些问题。应从人们向智能体提出的问题中抽样,为智能体答错或拒绝回答的问题编写黄金问题,并将它们加入 dev。
测试划分发生泄漏。 有人阅读未通过的测试问题,并针对它们调整提示词,于是测试分数上升,而其他方面毫无改进。如果在针对某一个问题做出改动后,dev 与测试的分数一同变动,就能发现这一点。应让测试问题远离调优讨论,并在它们已经泄漏时予以更新。
基准分数随基准本身而变。 供应商在 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。