처음부터 끝까지 만드는 데이터 에이전트
읽는 데 약 49분분 걸린다, 8절에 6개 예제이(가) 있음.
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
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.
1"""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."""2from lib.shop_data import SHOP_SEED3from lib.warehouse import connect, table_sizes45con = connect() # generates the shop, loads raw, builds the marts, reopens read-only6print(f"seed: {SHOP_SEED}")7for table, rows in table_sizes(con):8 print(f"{table}: {rows:,}")910# The trailing comma unpacks the one row this query returns. order_date_local is the order's day in its11# region's time zone, the day sales are counted on; is_test marks orders placed from the shop's test accounts.12(first, last, test_orders, cancelled), = con.execute("""13 SELECT (SELECT min(order_date_local) FROM analytics.fct_orders),14 (SELECT max(order_date_local) FROM analytics.fct_orders),15 (SELECT count(*) FROM raw.orders WHERE is_test),16 (SELECT count(*) FROM analytics.fct_orders WHERE status = 'cancelled')17""").fetchall()18print(f"first order day: {first}")19print(f"last order day: {last}")20print(f"test orders in raw.orders: {test_orders}")21print(f"cancelled orders in fct_orders: {cancelled}")
python load-the-warehouse.pyseed: 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 받기
상점의 웨어하우스를 구축했으니, 다음 단계로 모델이 그 웨어하우스를 조회하게 하는 것이 자연스럽다. 스키마만 쓰는 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을 읽어 보자.
1"""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."""2from lib.golden import load_golden3from lib.model import RecordedModel4from lib.prompts import schema_only_messages5from lib.warehouse import connect, describe_schema67schema = describe_schema(connect())8# gq-002 is net revenue in each region in March 2026, the question the prose follows.9question = next(q for q in load_golden() if q.question_id == "gq-002")10messages = schema_only_messages(schema, question.question)1112# Each table's schema is one CREATE TABLE statement, so counting them counts the tables.13print(f"tables in the prompt: {schema.count('CREATE TABLE')}")14# The trailing space makes this match the whole table name, not a longer one that starts the same way.15print(next(line for line in schema.splitlines() if "analytics.fct_orders " in line))1617model = RecordedModel("schema-only-sql") # replays the pinned model's real replies18call = model.call_for(messages)19print(f"prompt tokens: {call['prompt_tokens']}")20print(f"question: {question.question}")21print(call["reply"])
python first-question-to-sql.pytables 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;
```
프롬프트에는 두 스키마의 테이블 16개 전부가 CREATE TABLE 문으로 들어 있으며, 지시문과 질문을 합해 827토큰이다. 여기서 중요한 것은 analytics.fct_orders의 줄이다. 이 테이블에는 net_revenue_eur 열, order_date_local 열, region, status가 있다.
모델이 작성한 SQL은 많은 분석가가 가장 먼저 쓸 법한 것이다. 원본 테이블이나 orders_legacy이 아니라 마트를 고른다. 총매출이 아니라 net_revenue_eur을 합산한다. order_date_local으로 필터링하므로 각 주문은 UTC 날짜가 아니라 해당 지역의 영업일에 놓인다. 지역별로 묶고 열에 알맞은 이름을 붙인다. 풀 리퀘스트에서 이 쿼리를 검토한다면 승인할 수도 있다. 문제는 쿼리가 돌려주는 숫자가 맞는가이며, 이 출력만으로는 알 수 없다.
작업 규칙: 스키마만 쓰는 text-to-SQL은 제품이 아니라 넘어서야 할 기준선으로 다룬다. 자신의 웨어하우스에서 한 번 실행해 보고 그 응답을 보관한다. 스키마가 담지 못하는 정의가 무엇인지 드러나기 때문이다. 테스트가 의존하는 모든 모델 응답은 모델 파일, 프롬프트 해시, 고정된 샘플링에 묶어 기록해 두어, 발견한 실패를 다른 사람도 재현할 수 있게 한다.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
Run the SQL and check the number
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.
1"""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."""2from lib.golden import golden_result, load_golden, results_match3from lib.model import RecordedModel4from lib.prompts import schema_only_messages5from lib.tools import extract_sql, run_sql6from lib.warehouse import connect, describe_schema78con = connect()9question = next(q for q in load_golden() if q.question_id == "gq-002") # gq-002: net revenue in each region in March 202610reply = RecordedModel("schema-only-sql").complete(schema_only_messages(describe_schema(con), question.question))1112model_answer = dict(run_sql(con, extract_sql(reply)).rows) # the query returns (region, net revenue) rows, keyed here by region13golden = dict(golden_result(con, question))14# order_date_local is a date, so BETWEEN with March 31 includes all of March.15cancelled = dict(con.execute("""16 SELECT region, sum(net_revenue_eur) FROM analytics.fct_orders17 WHERE status = 'cancelled' AND order_date_local BETWEEN DATE '2026-03-01' AND DATE '2026-03-31'18 GROUP BY region""").fetchall())1920for region in sorted(golden):21 print(f"{region} model: {model_answer[region]:,.2f}")22 print(f"{region} golden: {golden[region]:,.2f}")23 print(f"{region} cancelled orders: {cancelled[region]:,.2f}")24print(f"model total: {sum(model_answer.values()):,.2f}")25print(f"golden total: {sum(golden.values()):,.2f}")26# as a share of the golden total27print(f"overstated by: {sum(model_answer.values()) / sum(golden.values()) - 1:.1%}")28matches = results_match(model_answer.items(), golden.items())29assert not matches # the model's SQL keeps cancelled orders, so its rows can't equal the golden rows30print(f"matches golden: {'yes' if matches else 'no'}")
python run-the-model-sql.pyAMER 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
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.
1"""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."""2from lib.agent import run_agent3from lib.golden import golden_result, load_golden, results_match4from lib.metrics import METRICS, NET_REVENUE5from lib.model import RecordedModel6from lib.prompts import metric_tools_prompt7from lib.tools import Toolbox8from lib.warehouse import connect910con = connect()11question = next(q for q in load_golden() if q.question_id == "gq-002") # the same question the SQL examples answer12print(f"metric: {NET_REVENUE.name}")13print(f"definition: {NET_REVENUE.description}")1415system = metric_tools_prompt(METRICS.values())16trace = run_agent(question.question, system, RecordedModel("metric-tool-runs"), Toolbox(con))17answer = trace.answer18# the first step is the model's tool call, and its arguments are the request the code compiles19print(f"request: {trace.steps[0].arguments}")20print(answer.provenance.sql)21print(answer.show())22print(answer.provenance.describe())23matches = results_match(answer.rows, golden_result(con, question))24assert matches # the metric's rule drops cancelled orders, as the golden SQL does25print(f"matches golden: {'yes' if matches else 'no'}")
python one-metric-answer.pymetric: 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
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.
1"""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."""2from collections import Counter34import duckdb5from lib.agent import run_agent6from lib.golden import golden_result, grade, load_golden7from lib.metrics import METRICS8from lib.model import RecordedModel9from lib.prompts import metric_tools_prompt, schema_only_messages10from lib.tools import Toolbox, extract_sql, run_sql11from lib.warehouse import connect, describe_schema1213con = connect()14schema, system = describe_schema(con), metric_tools_prompt(METRICS.values())15sql_model, metric_model = RecordedModel("schema-only-sql"), RecordedModel("metric-tool-runs")16totals = {"schema-only": Counter(), "metric": Counter()}1718print("question kind schema-only metric")19for q in load_golden()[:5]: # the first five; chapter 3 builds the full set20 golden = golden_result(con, q)21 try:22 sql = extract_sql(sql_model.complete(schema_only_messages(schema, q.question)))23 sql_outcome = grade(q, run_sql(con, sql), golden)24 except duckdb.Error: # a query that doesn't run raises a DuckDB error, which the score counts as an error25 sql_outcome = "error"26 metric_outcome = grade(q, run_agent(q.question, system, metric_model, Toolbox(con)).answer, golden)27 totals["schema-only"][sql_outcome] += 128 totals["metric"][metric_outcome] += 129 print(f"{q.question_id} {q.kind:<7} {sql_outcome:<11} {metric_outcome}")3031for name, counts in totals.items():32 for outcome in ("correct", "wrong", "error", "declined"):33 print(f"{name} {outcome}: {counts[outcome]} of {sum(counts.values())}")
python score-five-golden.pyquestion 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.
한 번의 실행에 대한 트레이스 읽기
점수는 에이전트가 얼마나 자주 맞았는지를 알려 준다. 트레이스는 한 번의 실행에 대해 그 이유를 알려 준다. 트레이스는 run_agent()가 기록한 한 번의 실행의 모든 단계로, 모델의 각 응답과 되돌려 보낸 각 도구 결과를 포함한다. 트레이스를 읽으면 올바른 결과가 올바른 이유로 나온 것인지, 그리고 거절이 무엇에 근거했는지 알 수 있다.
에이전트는 다섯 문항 점수에서 gq-004를 거절했으므로, 이 재현은 그 실행의 모든 단계를 출력한다.
1"""Print the trace of one recorded agent run: every model reply and tool result, then what the run returned. The `reason:` line printed last settles whether the decline's stated cause is true."""2from lib.agent import run_agent3from lib.golden import load_golden4from lib.metrics import METRICS5from lib.model import RecordedModel6from lib.prompts import metric_tools_prompt7from lib.tools import Toolbox8from lib.warehouse import connect910con = connect()11# gq-004: customers whose first order fell in fiscal 2026, which net_revenue can't count12question = next(q for q in load_golden() if q.question_id == "gq-004")13system = metric_tools_prompt(METRICS.values())14trace = run_agent(question.question, system, RecordedModel("metric-tool-runs"), Toolbox(con))1516print(trace.show())17# each step is either a model reply or a tool result; this counts the replies18print(f"model calls: {sum(step.kind == 'model' for step in trace.steps)}")19print(f"answered: {trace.answer.answered}")20print(f"reason: {trace.answer.reason}")
python print-one-trace.pyquestion: How many customers placed their first order in fiscal year 2026?
step 1 (model):
{"tool": "cannot_answer", "arguments": {"reason": "The data does not include information about the number of customers who placed their first order in a fiscal year."}}
step 2 (tool):
declined: The data does not include information about the number of customers who placed their first order in a fiscal year.
model calls: 1
answered: False
reason: The data does not include information about the number of customers who placed their first order in a fiscal year.
거절은 올바른 판단이었지만 그 이유는 틀렸다. 거절이 옳은 까닭은 net_revenue가 고객 수를 셀 수 없기 때문이다. 그런데 제시된 이유는 회계연도에 첫 주문을 한 고객의 수에 대한 정보가 데이터에 없다는 것이고, 실제로 analytics.dim_customers에는 first_order_on 열이 있다. 실행은 모델 호출 1회 만에 결과 대신 이유를 담은 cannot_answer 호출로 끝났다. 모델은 지표의 설명과 차원만 보았기 때문에, 보지 못한 데이터에 대해 주장을 펼친 것이다. 독자는 웨어하우스가 이 질문에 답할 수 없다고 결론짓겠지만, 이는 사실이 아니다.
작업 규칙: 점수만이 아니라 트레이스를 읽고, 거절을 먼저 읽는다. 각 거절의 이유가 "고객 수를 세는 지표가 없다"처럼 에이전트에 주어진 것에 관한 내용인지, 에이전트가 본 적 없는 데이터에 대한 주장은 아닌지 확인한다. 이유가 거짓인 거절은 점수가 거절로 처리하더라도 결함으로 본다.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
What goes wrong in production
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.
이 장에서 내린 결정
- 이 책에서 계속 다루는 사례는 우리가 생성한 상점이다. 상점의 웨어하우스는 하나의 시드에서 생성하며 실제 웨어하우스에 있는 함정을 갖추고 있다. 따라서 모든 독자가 같은 행을 얻고 어떤 것도 비공개 데이터에 의존하지 않는다.
- 웨어하우스는 읽기 전용이다.
warehouse.connect()는 쓸 수 없고 다른 파일도 열 수 없는 연결을 반환하므로, 검사하지 않은 SQL도 읽기만 할 수 있다. - 기준선은 실제로 기록한다. 스키마만 사용하는 SQL의 응답은 위에 고정한 모델에서 나오며, Qwen이 권장하는 샘플링을 사용하고 매 호출 전에 시드를 재설정한다.
- 모든 모델 응답은 메시지 해시로 기록하고 재생한다. 프롬프트가 바뀌면 이전 응답을 재사용하지 않고 분명하게 실패한다.
- SQL보다 지표가 먼저다. 모델은 정의된 지표와 그 인자를 고르고, 코드가 요청을 검증한 뒤 SQL을 작성한다.
- 거절도 하나의 답이다. 에이전트에는
cannot_answer도구가 있으며, 점수 계산에서는 거절을 오답과 따로 센다. - 하나의 비교로 모든 것을 채점한다. SQL 텍스트가 아니라 행을 비교하며, 매번 같은 규칙을 적용한다. 행이 같고, 정답의 모든 열이 있으며, 순서는 무관하고, 작은 허용 오차를 둔다.
- 루프는 프레임워크가 아니라 우리가 직접 만든다.
lib/agent.py에 있는 수십 줄의 코드이며 라이브러리 릴리스가 바꿀 수 없다. 프레임워크의 개발은 중단될 수 있다. 오픈 소스 text-to-SQL 프레임워크인 vanna는 2026년 3월 29일에 관리자들이 보관 처리했다(저장소).
이 판의 이 장
2026.11.15판예제 통과 2026년 11월 19이 장에서 6개 예제개 중 6개가 통과한다
영어판 2026.11.15판을 번역한 것이다.