端到端的数据智能体

阅读约需 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

商店的数据仓库建好之后,下一步自然是让模型来查询它。仅依据模式的文本到 SQL(schema-only text-to-SQL)是指:只向模型提供每一列的名称和类型,以及提问者的问题,除此之外不给任何信息,由模型写出 SQL。根据我们的经验,许多团队的第一版都从这里开始,因为模式是仓库中唯一现成的描述,而且数据库会让它保持最新。它所遗漏的是各种定义,因为模式中并不包含这些内容。

如果书中的示例每次都调用模型,每次运行得到的答案就会不同,还需要 API 密钥。因此,本书中的每一条模型回复都是录制好的。录制是指一组连同清单一起保存的模型回复,由仓库 recordings/ 文件夹中的脚本一次性生成,再由示例回放。所用模型是 Qwen3-4B-Instruct-2507,Qwen 以 Apache 2.0 许可证发布。它有 40 亿参数,回答时没有单独的思考阶段,所以一条回复就只是答案本身(Qwen 的模型卡)。我们运行它的 4 位量化版本,这是一个约 2.5 GB 的文件,以其 SHA-256 哈希值固定(该文件),使用 llama-cpp-python 在四个 CPU 线程上运行,因此任何人无需 GPU 就能重新录制。

采样设置沿用 Qwen 为该模型推荐的值:温度 0.7,top-p 0.8,top-k 20,min-p 0(Qwen 的模型卡)。每次调用之前都会重置随机种子。SQL 的回复上限为 384 个词元,其余回复为 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 个词元。这里要紧的是 analytics.fct_orders 那一行。它有一个 net_revenue_eur 列、一个 order_date_local 列、一个 region 和一个 status。

模型写出的 SQL 正是许多分析师会先写出的那种。它选用了数据集市表,而没有选原始表和 orders_legacy。它对 net_revenue_eur 求和,而不是对毛收入求和。它按 order_date_local 过滤,因此每笔订单落在其所属地区的营业日,而不是 UTC 日。它按地区分组,并给结果列起了一个合理的名称。如果你在拉取请求中评审这条查询,可能会批准它。问题在于它返回的数字是否正确,而这份输出中没有任何内容能告诉你。

工作准则: 把仅依据模式的文本到 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. 本章做出的决定

返回顶部全部章节