資料代理程式的完整流程

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

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

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

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

讀完本章,你將能建立商店的資料倉儲,讓一個已錄製回應的模型針對商業問題撰寫 SQL,執行該 SQL 並找出它回傳的錯誤數字,改以單一已定義的指標回答同一個問題,並在五個黃金問題上為兩種做法評分。這是本書完整成果的粗略版本:後續每一章都會把其中一個部分做得更扎實。

本書要回應的需求,通常只用一句話提出:讓使用者直接向資料提問。財務部門的人想知道上個月各地區的淨營收,又不想等儀表板,於是有人交給你一個語言模型。最直觀的做法,是把倉儲的綱要與問題交給模型,執行它寫出的 SQL,再顯示結果。面對精心挑選的問題,這種做法往往可行,足以做示範;但在本書的五個黃金問題上,它答對的題數是0 of 5。真正的問題在於它的失敗,因為其中多數看起來並不像失敗。無法執行的查詢是別人看得見的錯誤;能夠執行、卻回傳高出幾個百分點的數字的查詢,則會被放進簡報裡。

本章先建立最直觀的版本,執行它,並明確指出它的數字從哪裡開始出錯。接著建立本書所推薦替代做法的最小版本:這個代理程式完全看不到資料表,只能使用已定義的指標,而且當指標無法涵蓋某個問題時,它會明確說明。兩種做法都用同樣的五個問題與經過核對的答案來評分,這個習慣是本書其餘部分所仰賴的。

你只需要 Python 3.12.15 與 SQL,不需要其他東西:不需要 GPU、雲端帳號或 API 金鑰。每個範例都在 duckdb 2.0.0 上離線執行,使用本書產生的資料;其中用到的每一則模型回應,都曾由開放模型錄製一次,並從檔案重播。

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

Meet the shop and its warehouse

最近一次變動於第 2026.11.15 版

An agent that answers questions from a warehouse can only be as right as the warehouse's definitions let it be. Real warehouses hold more than tables: they hold decisions about what counts as an order, which currency a total is in and which day a timestamp belongs to. Those decisions live in the marts, in the SQL that built them and in the heads of the people who wrote it. So to see where an agent goes wrong, you need a warehouse with those decisions in it, and the traps they create.

This book follows one case throughout: an online shop that sells outdoor and camping gear in three regions, EMEA, AMER and APAC, which we call the shop. It has no brand name and stands for no real business. Its data is generated by the book's own code, lib/shop_data.py, from one fixed seed, so every reader builds exactly the same rows. Orders run from 2024-02-01 to 2026-06-30, and nothing in the data depends on the day you run an example.

The shop's warehouse, which every example queries, is a DuckDB database with two schemas, as an analytics engineer would build them. The raw schema holds what the source systems loaded: orders, order lines, refunds, customers, products, sessions, marketing spend and exchange rates. The analytics schema holds the marts built from the raw tables in SQL, such as fct_orders, one row per real order with its amounts in euros. lib/warehouse.py does the build. Its connect() generates the data, loads raw, runs the SQL that builds analytics, and returns a connection that can read but not write, with access to other files switched off.

The row counts are the quickest way to see them, so the example builds the warehouse and prints them.

load-the-warehouse.py Python 3.12.15 · duckdb 2.0.0 通過
"""Build the shop's warehouse from its seed and list every table with its row count. The point about test orders rests on the `raw.orders` and `analytics.fct_orders` rows; the last two lines give the shop's test orders and its cancelled orders."""from lib.shop_data import SHOP_SEEDfrom lib.warehouse import connect, table_sizescon = connect()                  # generates the shop, loads raw, builds the marts, reopens read-onlyprint(f"seed: {SHOP_SEED}")for table, rows in table_sizes(con):    print(f"{table}: {rows:,}")# The trailing comma unpacks the one row this query returns. order_date_local is the order's day in its# region's time zone, the day sales are counted on; is_test marks orders placed from the shop's test accounts.(first, last, test_orders, cancelled), = con.execute("""    SELECT (SELECT min(order_date_local) FROM analytics.fct_orders),           (SELECT max(order_date_local) FROM analytics.fct_orders),           (SELECT count(*) FROM raw.orders WHERE is_test),           (SELECT count(*) FROM analytics.fct_orders WHERE status = 'cancelled')""").fetchall()print(f"first order day: {first}")print(f"last order day: {last}")print(f"test orders in raw.orders: {test_orders}")print(f"cancelled orders in fct_orders: {cancelled}")
輸出python load-the-warehouse.py
seed: 2718
raw.customers: 3,100
raw.fx_rates: 2,649
raw.marketing_spend: 2,643
raw.order_lines: 38,439
raw.orders: 15,000
raw.products: 120
raw.refunds: 1,077
raw.sessions: 60,000
analytics.dim_customers: 3,040
analytics.dim_date: 912
analytics.dim_products: 120
analytics.fct_order_lines: 38,071
analytics.fct_orders: 14,856
analytics.fct_refunds: 1,068
analytics.fct_sessions: 60,000
analytics.orders_legacy: 15,000
first order day: 2024-02-01
last order day: 2026-06-30
test orders in raw.orders: 144
cancelled orders in fct_orders: 819

The counts already show three decisions, and the first is that the mart drops the shop's own test orders. raw.orders holds 15,000 orders and analytics.fct_orders holds 14,856, so the 144 test orders, placed from the shop's test accounts, never reach the mart. The customers shrink between raw.customers and analytics.dim_customers for another reason: some customers signed up twice, with emails that differ only in case, and test accounts are left out. The mart keeps the 819 cancelled orders, each with its status, because some questions are about cancellations.

Other decisions aren't visible in a count. EMEA prices include VAT and AMER and APAC prices don't, so gross and net revenue differ by region. Timestamps are stored in UTC, while the shop counts sales on each region's local business day. The shop's fiscal year starts on February 1, and is named for the year it ends in. And analytics.orders_legacy, an older mart that old dashboards still read, has a column called revenue that holds a different number: gross, tax included, test orders kept. Nothing in the schema says which of these rules applies to which question.

Working rule: Build the agent against a warehouse that has your real traps in it: test rows, cancelled orders, several currencies, local business days, a fiscal calendar and at least one legacy table with a tempting name. Generate it from a seed when you can't share production data, so anyone can rebuild it. A clean demo schema hides exactly the failures you need to see.

提出問題,由模型產生 SQL

最近一次變動於第 2026.11.12 版

有了商店的資料倉儲之後,下一步顯然就是讓模型來查詢它。僅用結構描述(schema-only)的 text-to-SQL,指的是只提供模型每個欄位的名稱與型別,以及使用者的問題,除此之外什麼都不給,由模型寫出 SQL。依我們的經驗,許多人的第一版系統都從這裡開始,因為結構描述是倉儲唯一現成的描述,而且資料庫會讓它保持最新。它遺漏的是各種定義,因為結構描述並不記載這些內容。

若書中的範例每次都呼叫模型,每次執行的答案都會不同,也需要 API 金鑰。因此本書中每一則模型回覆都是錄製下來的。錄製檔是一組已儲存的模型回覆,附有一份清單檔(manifest),由儲存庫 recordings/ 資料夾中的腳本一次產生,再由各範例重播。所用的模型是 Qwen3-4B-Instruct-2507,Qwen 以 Apache 2.0 授權釋出。它有 40 億個參數,回答時沒有獨立的思考階段,所以回覆就只有答案本身(Qwen 的模型卡)。我們執行的是它的 4 位元量化版本,這是一個約 2.5 GB 的檔案,並以 SHA-256 雜湊值固定(該檔案),使用 llama-cpp-python 在四個 CPU 執行緒上執行,因此任何人不需要 GPU 就能重新錄製。

取樣設定採用 Qwen 為此模型建議的值:temperature 0.7、top-p 0.8、top-k 20、min-p 0(Qwen 的模型卡)。每次呼叫前都會重設種子。SQL 的回覆上限為 384 個 token,其他回覆為 256 個。這個上限是我們自行選定的,目的是讓錄製在 CPU 上能快速跑完,而 Qwen 建議的上限是 16,384。每份錄製檔的 manifest.json 會記載腳本、模型與其檔案雜湊值、提示的雜湊值、取樣設定、日期,以及每次呼叫的耗時。

在範例這一端,lib/model.py 提供 RecordedModel("schema-only-sql"),用來重播該錄製檔。它以實際送出之訊息的 SHA-256 查找每則回覆。只要提示差一個字元,就沒有對應的錄製,重播會以錯誤停止,而不是回傳另一個提示的回覆。提示本身是 lib/prompts.py 中的常數 SCHEMA_ONLY_SQL,由錄製腳本與各範例共用。

這個問題是財務團隊每個月都會問的:「2026 年 3 月各區域的淨營收是多少?」它同時也是 gq-002,一個附有已核對答案的黃金問題;下一節會用它來檢驗答案。在評斷它的數字之前,先讀模型寫出的 SQL。

first-question-to-sql.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 通過
"""Send the warehouse's schema and one question to the recorded model, and print the SQL it wrote. The prose reads that SQL for its table, its columns and its date filter, against the `analytics.fct_orders` line printed above it."""from lib.golden import load_goldenfrom lib.model import RecordedModelfrom lib.prompts import schema_only_messagesfrom lib.warehouse import connect, describe_schemaschema = describe_schema(connect())# gq-002 is net revenue in each region in March 2026, the question the prose follows.question = next(q for q in load_golden() if q.question_id == "gq-002")messages = schema_only_messages(schema, question.question)# Each table's schema is one CREATE TABLE statement, so counting them counts the tables.print(f"tables in the prompt: {schema.count('CREATE TABLE')}")# The trailing space makes this match the whole table name, not a longer one that starts the same way.print(next(line for line in schema.splitlines() if "analytics.fct_orders " in line))model = RecordedModel("schema-only-sql")       # replays the pinned model's real repliescall = model.call_for(messages)print(f"prompt tokens: {call['prompt_tokens']}")print(f"question: {question.question}")print(call["reply"])
輸出python first-question-to-sql.py
tables in the prompt: 16
CREATE TABLE analytics.fct_orders (order_id INTEGER, customer_id INTEGER, region VARCHAR, country VARCHAR, channel VARCHAR, status VARCHAR, placed_at TIMESTAMP, order_date_local DATE, currency VARCHAR, line_count BIGINT, gross_revenue_eur DECIMAL(12,2), net_revenue_eur DECIMAL(12,2), discount_eur DECIMAL(12,2), tax_eur DECIMAL(12,2), _loaded_at TIMESTAMP);
prompt tokens: 827
question: What was net revenue in each region in March 2026?
```sql
SELECT
    region,
    SUM(net_revenue_eur) AS net_revenue
FROM
    analytics.fct_orders
WHERE
    order_date_local >= '2026-03-01'
    AND order_date_local < '2026-04-01'
GROUP BY
    region
ORDER BY
    net_revenue DESC;
```

提示以 CREATE TABLE 陳述式列出兩個結構描述中的全部 16 張資料表,連同指示與問題共 827 個 token。這裡關鍵的是 analytics.fct_orders 那一行。它有一個 net_revenue_eur 欄位、一個 order_date_local 欄位、一個 region 和一個 status。

模型寫出的 SQL,正是許多分析師會先寫的那一種。它選用資料集市(mart),而不是原始資料表或 orders_legacy。它加總的是 net_revenue_eur,而非毛額。它依 order_date_local 篩選,所以每筆訂單會落在其所屬區域的營業日,而不是 UTC 日。它依區域分組,並給輸出欄位一個合理的名稱。若你在拉取請求(pull request)中審查這個查詢,可能會核准它。問題在於它回傳的數字是否正確,而這份輸出中沒有任何東西能告訴你。

實用原則: 把僅用結構描述的 text-to-SQL 視為要超越的基準,而不是成品。在你自己的倉儲上執行一次,並保留它的回覆,因為這會讓你看出結構描述沒有承載你的哪些定義。請錄製你的測試所依賴的每一則模型回覆,並固定模型檔案、提示雜湊值與取樣設定,讓你發現的失敗能由他人重現。

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

Run the SQL and check the number

最近一次變動於第 2026.11.12 版

The model's query for March 2026 looks right, and it runs. A query that fails is caught when someone sees the error; one that runs and returns a plausible number is the failure that matters, because nothing marks it as wrong. The only way to know is to compare the result with an answer someone has checked.

A golden question is a question with its checked answer: the golden SQL, written against the owner's definition, the golden result that SQL returns, and the kind of answer expected. The golden SQL is checked by running it, which chapter 3 covers. The book keeps its golden questions in examples/data/golden/questions.toml, and lib/golden.py loads them. The golden SQL for gq-002 follows the shop's definition of net revenue, which the finance team owns: revenue in euros after discounts and before tax, from real orders that weren't cancelled, on the order's local business day.

Put the model's query beside the golden one, region by region, with each region's cancelled orders alongside to check the gap against.

run-the-model-sql.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 通過
"""Run the SQL the model wrote for net revenue by region in March 2026, and check it against the golden result. The prose reads each region's model, golden and cancelled-orders lines, and the final match line."""from lib.golden import golden_result, load_golden, results_matchfrom 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-002")   # gq-002: net revenue in each region in March 2026reply = RecordedModel("schema-only-sql").complete(schema_only_messages(describe_schema(con), question.question))model_answer = dict(run_sql(con, extract_sql(reply)).rows)   # the query returns (region, net revenue) rows, keyed here by regiongolden = dict(golden_result(con, question))# order_date_local is a date, so BETWEEN with March 31 includes all of March.cancelled = dict(con.execute("""    SELECT region, sum(net_revenue_eur) FROM analytics.fct_orders    WHERE status = 'cancelled' AND order_date_local BETWEEN DATE '2026-03-01' AND DATE '2026-03-31'    GROUP BY region""").fetchall())for region in sorted(golden):    print(f"{region} model: {model_answer[region]:,.2f}")    print(f"{region} golden: {golden[region]:,.2f}")    print(f"{region} cancelled orders: {cancelled[region]:,.2f}")print(f"model total: {sum(model_answer.values()):,.2f}")print(f"golden total: {sum(golden.values()):,.2f}")# as a share of the golden totalprint(f"overstated by: {sum(model_answer.values()) / sum(golden.values()) - 1:.1%}")matches = results_match(model_answer.items(), golden.items())assert not matches           # the model's SQL keeps cancelled orders, so its rows can't equal the golden rowsprint(f"matches golden: {'yes' if matches else 'no'}")
輸出python run-the-model-sql.py
AMER model: 55,207.80
AMER golden: 51,762.14
AMER cancelled orders: 3,445.66
APAC model: 40,832.21
APAC golden: 35,296.93
APAC cancelled orders: 5,535.28
EMEA model: 66,502.78
EMEA golden: 63,023.76
EMEA cancelled orders: 3,479.02
model total: 162,542.79
golden total: 150,082.83
overstated by: 8.3%
matches golden: no

The query overstates net revenue by exactly the cancelled orders it counts. For EMEA it reports 66,502.78 euros against a golden 63,023.76, and the gap, 3,479.02, is the net revenue of EMEA's cancelled orders that month. AMER and APAC follow the same pattern. Across the three regions, the model's total is 8.3% above the golden total.

The query wasn't careless. The schema shows a status column, but nothing in it says cancelled orders don't count toward revenue. That rule lives in the finance team's definition, which a model reading column names can't see. A larger model might guess the rule more often, but a guess is the wrong mechanism: the next question may depend on a rule nobody would guess, such as which day a refund belongs to.

Notice what the check compares: the rows, not the SQL text, and here the rows don't match. results_match() in lib/golden.py, the rule every score in this book uses, rejects them, which the example checks.

Working rule: Never accept an agent's number because its SQL looks right. Check it against a golden result before anyone relies on it, and when the two differ, find the rows that explain the difference before you change anything. Expect the difference to be a definition, such as which orders count, more often than a syntax mistake.

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

Answer from one defined metric

最近一次變動於第 2026.11.15 版

Counting cancelled orders broke the first number, because the rule that excludes them isn't in the schema. The fix this book recommends is to stop asking the model for SQL over tables, and to give it defined metrics instead. A metric is a business measure written down once, by the people who own it: a name, a description a person can check, one aggregate over one mart, the rule for which rows count, and the dimensions it may be grouped and filtered by. The model's job shrinks to choosing a metric and its arguments. Code that you wrote and tested turns that choice into SQL.

This chapter has one metric, net_revenue, in lib/metrics.py. Its rule for which rows count is the finance team's: status <> 'cancelled' on analytics.fct_orders. Its dimensions are region, channel, fiscal_year, fiscal_quarter, calendar_year and calendar_month, each mapped to one SQL expression. A metric request names the metric, the dimensions to group by and one value per filtered dimension, and compile_request() turns it into SQL. Dimension names come only from that list, and filter values are passed as query parameters, never pasted into the SQL text.

The model reaches the metric through tools. A tool is a function the model may ask the code to run, by replying with a JSON object that names it and gives its arguments. This agent has two, in lib/tools.py. query_metric computes a metric for a request. cannot_answer declines, with a one-sentence reason, when no metric covers the question. The loop that connects them is the book's own, run_agent() in lib/agent.py. It sends the messages, reads one tool call from the reply, runs the tool and stops when the tool returns an answer, after at most three model calls. The prompt, METRIC_TOOLS in lib/prompts.py, lists the metric's description and each dimension's meaning, including when the fiscal year starts. It lists no tables.

The recording metric-tool-runs holds the same model's replies to the metric prompt, recorded the way schema-only-sql was. Here is gq-002 again, through the metric.

one-metric-answer.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 通過
"""Answer the same question from one defined metric: the model picks the metric, the code writes and runs the SQL. The point rests on the request and the compiled SQL printed here, and on the assertion that the answer equals the golden result."""from lib.agent import run_agentfrom lib.golden import golden_result, load_golden, results_matchfrom lib.metrics import METRICS, NET_REVENUEfrom lib.model import RecordedModelfrom lib.prompts import metric_tools_promptfrom lib.tools import Toolboxfrom lib.warehouse import connectcon = connect()question = next(q for q in load_golden() if q.question_id == "gq-002")   # the same question the SQL examples answerprint(f"metric: {NET_REVENUE.name}")print(f"definition: {NET_REVENUE.description}")system = metric_tools_prompt(METRICS.values())trace = run_agent(question.question, system, RecordedModel("metric-tool-runs"), Toolbox(con))answer = trace.answer# the first step is the model's tool call, and its arguments are the request the code compilesprint(f"request: {trace.steps[0].arguments}")print(answer.provenance.sql)print(answer.show())print(answer.provenance.describe())matches = results_match(answer.rows, golden_result(con, question))assert matches               # the metric's rule drops cancelled orders, as the golden SQL doesprint(f"matches golden: {'yes' if matches else 'no'}")
輸出python one-metric-answer.py
metric: net_revenue
definition: Revenue in EUR after discounts and before tax, from real orders that weren't cancelled, counted on the order's local business day. Returned orders count; refunds are not subtracted.
request: {'metric': 'net_revenue', 'group_by': ['region'], 'filters': {'calendar_month': 3, 'calendar_year': 2026}}
SELECT o.region AS region, round(sum(o.net_revenue_eur), 2) AS net_revenue
FROM analytics.fct_orders AS o
JOIN analytics.dim_date AS d ON d.date_day = o.order_date_local
WHERE o.status <> 'cancelled' AND d.calendar_month = $1 AND d.calendar_year = $2
GROUP BY o.region
ORDER BY o.region
region  net_revenue
AMER    51,762.14
APAC    35,296.93
EMEA    63,023.76
source: metric (net_revenue)
filters: {'calendar_month': 3, 'calendar_year': 2026}
role: analyst
data as of: 2026-07-02 02:00:00
matches golden: yes

The model asked for net_revenue, grouped by region and filtered to calendar month 3 of calendar year 2026. The SQL the code compiled from that request applies the cancelled-orders rule, joins the date dimension on the local business day and passes the month and year as parameters. Each region matches its golden result.

The answer also carries its provenance: the metric that produced it, the filters, the role it was computed for, and how fresh the data is. The data as of line is when the newest row in fct_orders was loaded, read from its _loaded_at column. A person can see the definition and the currency of a number without reading SQL.

One metric is a long way from a semantic layer. It can't join through entities, it has one grain and it holds one definition. Chapter 5 replaces it with a semantic layer that does those things. The shape stays, though: the model chooses a governed definition, and code writes the SQL.

Working rule: Route a question to a defined metric before you let a model write SQL. Give the model the metric's description and its dimensions, and let it return a request, which your code validates and compiles. Write SQL from a model only for questions no metric covers, and only behind guards.

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

Score both on five golden questions

最近一次變動於第 2026.11.15 版

One question answered from the metric shows the approach can work. It doesn't show that it works more often than schema-only SQL. For that, score both on golden questions under the same comparison rule. A score on five questions is a smoke test, not a measurement, and it can't support a claim about this model, or about text-to-SQL in general. It still separates outcomes that one accuracy figure hides.

Each answer gets one of four outcomes. An answer is correct when the result matches the golden result, or when it declines a question that should be declined. A wrong answer returns a different result, or returns any result for a question that should be declined. An error is an answer whose SQL doesn't run. A declined answer says it can't answer a question that has an answer. A decline is not a wrong number, and a score that counts the two together hides the difference this book cares about most.

The comparison rule, results_match() in lib/golden.py, accepts an answer when it has as many rows as the golden result and its columns include every golden column's values. Columns are matched by their values, not their names, so column order doesn't matter, and neither do extra columns, such as a fiscal_year column beside the total. Row order doesn't matter unless the question says it does. Numbers must agree within 0.05 percent, or within half a cent. Chapter 3 discusses each of these choices.

The five golden questions cover the traps one at a time:

ID Question Kind
gq-001 Net revenue in fiscal year 2026 answer
gq-002 Net revenue in each region in March 2026 answer
gq-003 Net revenue by sales channel in calendar 2025 answer
gq-004 Customers whose first order was in fiscal 2026 answer
gq-005 Net revenue next quarter decline

Both approaches face all five, and the output has one row per question, showing where each one fails.

score-five-golden.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 通過
"""Score both approaches on five golden questions: schema-only SQL, and the agent with one defined metric. The prose's counts are the totals printed on the last lines, one per outcome and approach."""from collections import Counterimport duckdbfrom lib.agent import run_agentfrom lib.golden import golden_result, grade, load_goldenfrom lib.metrics import METRICSfrom lib.model import RecordedModelfrom lib.prompts import metric_tools_prompt, schema_only_messagesfrom lib.tools import Toolbox, extract_sql, run_sqlfrom lib.warehouse import connect, describe_schemacon = connect()schema, system = describe_schema(con), metric_tools_prompt(METRICS.values())sql_model, metric_model = RecordedModel("schema-only-sql"), RecordedModel("metric-tool-runs")totals = {"schema-only": Counter(), "metric": Counter()}print("question  kind     schema-only  metric")for q in load_golden()[:5]:      # the first five; chapter 3 builds the full set    golden = golden_result(con, q)    try:        sql = extract_sql(sql_model.complete(schema_only_messages(schema, q.question)))        sql_outcome = grade(q, run_sql(con, sql), golden)    except duckdb.Error:         # a query that doesn't run raises a DuckDB error, which the score counts as an error        sql_outcome = "error"    metric_outcome = grade(q, run_agent(q.question, system, metric_model, Toolbox(con)).answer, golden)    totals["schema-only"][sql_outcome] += 1    totals["metric"][metric_outcome] += 1    print(f"{q.question_id}    {q.kind:<7}  {sql_outcome:<11}  {metric_outcome}")for name, counts in totals.items():    for outcome in ("correct", "wrong", "error", "declined"):        print(f"{name} {outcome}: {counts[outcome]} of {sum(counts.values())}")
輸出python score-five-golden.py
question  kind     schema-only  metric
gq-001    answer   error        correct
gq-002    answer   wrong        correct
gq-003    answer   error        correct
gq-004    answer   wrong        declined
gq-005    decline  wrong        correct
schema-only correct: 0 of 5
schema-only wrong: 3 of 5
schema-only error: 2 of 5
schema-only declined: 0 of 5
metric correct: 4 of 5
metric wrong: 0 of 5
metric error: 0 of 5
metric declined: 1 of 5

Schema-only SQL gets 0 of 5 questions right, and the way it fails matters more than that count. It fails to run on 2 of 5 questions: for gq-001 and gq-003 the model filtered fct_orders on fiscal_year and calendar_year, columns that exist only in dim_date. Those failures are visible, which makes them the cheap ones. Its wrong answers, on 3 of 5 questions, are not. gq-002 counts cancelled orders. gq-004 counts customers whose first order fell in calendar 2026 instead of fiscal 2026. And gq-005, a forecast the warehouse can't make, gets a query over the quarter that contains today's date, which runs and returns a total instead of saying the question can't be answered.

The agent with one metric gets 4 of 5 questions right, including the decline for the forecast, and makes 0 of 5 wrong answers. It declines gq-004, which the warehouse can answer but net_revenue can't, since it counts revenue, not customers. That decline is the cost of the approach, and it is the right cost. A person who hears "I can't answer that from the metrics I have" knows to ask someone, while a person who hears a wrong count doesn't.

Small as it is, the score does show one thing. On this warehouse, the failures came from definitions the schema doesn't hold, and moving the definitions into code removed them. Chapter 3 builds the golden set to 48 questions, with a held-out split, and every later chapter reports its numbers on it.

Working rule: Score every change on golden questions written from your own work, and report correct, wrong, error and declined separately. Include questions that should be declined from the start, or an agent that never declines will look perfect. Use a handful of questions as a smoke test while you build, and a larger set with a held-out split before you claim anything.

閱讀單次執行的追蹤紀錄

最近一次變動於第 2026.11.12 版

分數說明代理答對的頻率,追蹤紀錄則說明單次執行為何如此。追蹤紀錄是由 run_agent() 保存的單次執行的每一個步驟:每一則模型回覆,以及每一筆送回的工具結果。閱讀追蹤紀錄,可以得知正確的結果是否基於正確的理由,也可以得知拒答所依據的是什麼。

代理在五題評分中拒答了 gq-004,因此這次重播會印出該次執行的每一個步驟。

這次拒答的判斷是正確的,但理由是錯的。拒答之所以正確,是因為 net_revenue 無法計算客戶數。然而,理由卻說資料中沒有某個會計年度首次下單的客戶數量的相關資訊,但 analytics.dim_customers 其實有 first_order_on 欄位。這次執行最後呼叫了 cannot_answer,給出的是理由而非結果,整個過程共進行了 1 次模型呼叫。模型只看到指標的描述與維度,卻對它看不到的資料提出了論斷。讀者會因此認為資料倉儲無法回答這個問題,而這並不是事實。

實用原則: 不要只看分數,也要閱讀追蹤紀錄,並且先讀拒答的部分。檢查每一次拒答的理由,是否針對代理所取得的內容,例如「沒有任何指標可以計算客戶數」,而不是對它從未看過的資料所做的論斷。即使分數將理由錯誤的拒答計為已拒答,也應將它視為缺陷。

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

What goes wrong in production

最近一次變動於第 2026.11.12 版

The rough agent built here works on five questions. These are how the design fails once people depend on it, and how you'd notice each.

A plausible number goes into a decision. Schema-only SQL that runs returns a number with no warning attached, as gq-002 showed with a total 8.3% too high. Nobody notices until someone compares it with the finance team's report, often weeks later. The early warning is scoring on golden questions before release and after every change, and showing provenance with each answer, so a reader sees which definition was used.

Definitions drift away from the metric. The metric's description says cancelled orders don't count, and its SQL enforces that. When the finance team changes a rule, such as excluding a marketplace's fees, the description, the SQL and the golden SQL all need the same change. If one is missed, the agent's answers and the golden results move apart, and the score drops. That drop is the alarm, so keep the golden questions in the same repository as the metric definitions, and review changes to both together.

The agent declines for a reason that isn't true. The trace of gq-004 declined correctly but claimed the data has no first-order dates. A person who believes that reason stops asking. You notice by reading declines in traces, and by keeping golden questions that the warehouse can answer but the metrics can't, so a false reason shows up as a decline on a question you know is answerable.

A model reply changes without anyone noticing. If an example called a model live, a new model version or a changed prompt would change every downstream number silently. The replay in lib/model.py looks replies up by the hash of the exact messages, so a changed prompt stops with MissingRecording. Record again on purpose, and treat a new model version like a library release: record, score and compare.

The model file's chat template isn't the publisher's. The quantized file this book records with embeds a chat template that differs from the one Qwen publishes for this model (Qwen's template). The embedded one adds an empty thinking block to an assistant turn that ends the conversation, which Qwen's own template doesn't do. Our recording runtime, recordings/local_model.py, renders every prompt with Qwen's template at a pinned revision, checked by hash, and each manifest records whether the embedded template matched. Comparing the two before the first recording catches it, not a run of odd replies afterward.

Stale data answers confidently. The metric's answer said when the newest row in fct_orders was loaded. A mart whose load stopped would still answer, from old data, unless something checks that date against what the question needs. Show the date with every answer at least, and decline when it's too old for the question.

The model's SQL runs with no checks. In this chapter, run_sql() in lib/tools.py executes whatever the model wrote. Here the warehouse connection is read-only and can't open other files, which limits the damage to reading. A production warehouse connection usually has more rights than that. Never give a model's SQL a connection that can write: connect it read-only to a database that holds only what the asker's role may read (chapter 7), and add guards in front of it as defence in depth.

Working rule: Before anyone outside your team uses the agent, make every failure above visible: score on golden questions in CI, show provenance and data freshness with each answer, review declines in traces, replay recorded replies by message hash, and run model-written SQL only on a read-only connection. Start with golden scoring, because it is the only one of these checks that catches a plausible wrong number.

本章的決策

最近一次變動於第 2026.11.12 版
  • 本書貫穿始終的案例是我們生成的一間商店。 這間商店的資料倉儲由同一個種子生成,並帶有真實資料倉儲會有的陷阱,因此每位讀者得到的資料列都相同,也不依賴任何私有資料。
  • 資料倉儲是唯讀的。 warehouse.connect() 回傳的連線不能寫入,也不能開啟其他檔案,所以即使是未經檢查的 SQL,也只能讀取。
  • 基準線是真實錄製的。 僅提供結構描述的 SQL 做法,其回覆來自上方固定版本的模型,使用 Qwen 建議的取樣設定,並在每次呼叫前重設種子。
  • 每一則模型回覆都會錄製,並依訊息雜湊重播。 提示詞一有變動就會明確失敗,而不會沿用舊的回覆。
  • 先指標,後 SQL。 模型選擇一個已定義的指標及其引數,由程式碼驗證請求並撰寫 SQL。
  • 拒答也是一種回答。 代理程式有一個 cannot_answer 工具,計分時把拒答與答錯分開計算。
  • 以同一種比較方式為所有結果計分: 比較的是資料列而不是 SQL 文字,且每次都用相同規則:資料列相同、標準答案的每個欄位都要出現、順序不拘、容許小幅誤差。
  • 迴圈由我們自己撰寫,而不是由框架提供。 它是 lib/agent.py 中的幾十行程式碼,不會因函式庫改版而改變。框架的開發可能中止:開源的文字轉 SQL 框架 vanna 已於 2026 年 3 月 29 日被其維護者封存(儲存庫)。

本章,於本版次中

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

譯自英文書,版次 2026.11.15。

引用本節

格式

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

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

本頁內容

第 1 章,共 11 章: 資料代理程式的完整流程

  1. Meet the shop and its warehouse
  2. 提出問題,由模型產生 SQL
  3. Run the SQL and check the number
  4. Answer from one defined metric
  5. Score both on five golden questions
  6. 閱讀單次執行的追蹤紀錄
  7. What goes wrong in production
  8. 本章的決策

返回頂端所有章節