Where raw text-to-SQL gets the number wrong
읽는 데 약 52분분 걸린다, 8절에 6개 예제이(가) 있음.
AI로 번역함. 영어 원문을 사람이 검증함. 우리가 책을 만드는 방식
영어판 2026.11.15판을 번역한 것이다. 원문 읽기
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
By the end of this chapter, you can name the five ways schema-only text-to-SQL returns a plausible wrong number on a real warehouse, show each one on the shop's warehouse, count them across a set of golden questions, and tell which a better prompt can fix and which need definitions the schema doesn't hold.
Chapter 1 asked a model for net revenue by region in March 2026, given only the warehouse's schema, and got a total 8.3% too high, because the query counted cancelled orders. This chapter asks 11 more questions of the shop, which sells outdoor and camping gear across EMEA, AMER and APAC, each written to touch one of the warehouse's traps, and runs the model's SQL on each. The warehouse is a DuckDB database the book generates from a fixed seed, with a raw schema as loaded and an analytics schema of marts.
The worry is common. In dbt Labs' 2026 survey of analytics engineering, 71% of respondents named "incorrect or hallucinated outputs reaching stakeholders" as a top concern (dbt Labs, April 14, 2026). The survey had 363 respondents, collected in late 2025 and early 2026, and it is a vendor's survey of people who chose to answer, so it measures worry, not how often wrong numbers ship. It doesn't say where the wrong numbers come from. The sections below show five places on the shop's warehouse, one at a time, and the last of them counts how often each appears.
Every example replays the recording called schema-only-sql: the replies a pinned open model, Qwen3-4B-Instruct-2507, gave when it was shown the schema of every table and one question, and asked for one DuckDB query. Chapter 1 explains how the recording was made and why each reply is looked up by the hash of the exact messages. Each example then runs the model's query on the warehouse and compares the result with the golden result: the question's answer from SQL written against the finance team's definitions and checked by running it, kept with the question in examples/data/golden/questions.toml.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
Joins that fan out and inflate a sum
Fan-out is what happens when a query joins a table to another that has several rows for each of its rows, and then sums a column of the first. An order has one row in the orders mart and several in the order lines, so after the join each order's amount appears once per line, and a sum over it counts the order several times. The query runs, the number has the right units, and nothing in the result marks it. An analyst who knows the grain of each table aggregates the lines per order before joining, or filters with IN instead of a join. A model that sees only column names has to infer the grain from them, and the schema doesn't state it.
In the shop's warehouse, analytics.fct_orders holds one row per order with its net_revenue_eur, and analytics.fct_order_lines holds one row per line with its product_id. The question is one a category manager asks: "How much net revenue came from orders that included a tent in 2025?" It is golden question gq-007. Its golden SQL counts each order's whole net revenue once, if any of its lines is a product in the Tents category. Count the rows the model's join produces, against the golden query: the sum looks plausible but isn't.
1"""Replay the model's SQL for net revenue from orders with a tent, then count each order once. Read the `inflated by` line: it is the fan-out, measured with the model's own joins and filters kept as they are."""2from lib.golden import golden_result, load_golden3from 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-007")10sql = extract_sql(RecordedModel("schema-only-sql").complete(schema_only_messages(describe_schema(con), question.question)))11print(sql)1213# The model's own joins and filters, kept as they are, so only the counting changes.14# placed_at is UTC, so EXTRACT gives the year by UTC, as the model wrote it.15matched = """FROM analytics.fct_orders f JOIN analytics.fct_order_lines fl ON f.order_id = fl.order_id16 JOIN analytics.dim_products p ON fl.product_id = p.product_id17 WHERE {product} AND EXTRACT(YEAR FROM f.placed_at) = 2025"""18# The model's name match, which misses shelters, and the whole Tents category, which has them.19by_name, by_category = "p.product_name LIKE '%tent%'", "p.category = 'Tents'"20# One row for each matched line and a count of the distinct orders behind them; the trailing comma unpacks that one row.21(lines, orders), = con.execute(f"SELECT count(*), count(DISTINCT f.order_id) {matched.format(product=by_name)}").fetchall()22# A semi-join: each order's whole net revenue, counted once, however many of its lines match.23once = "SELECT sum(net_revenue_eur) FROM analytics.fct_orders WHERE order_id IN (SELECT f.order_id {matched})"24model_total = run_sql(con, sql).rows[0][0]25counted_once = con.execute(once.format(matched=matched.format(product=by_name))).fetchall()[0][0]26whole_category = con.execute(once.format(matched=matched.format(product=by_category))).fetchall()[0][0]2728print(f"rows the join matched: {lines}")29print(f"orders behind them: {orders}")30print(f"model's answer: {model_total:,.2f}")31print(f"each order counted once: {counted_once:,.2f}")32print(f"inflated by: {model_total / counted_once - 1:.1%}")33print(f"tents and shelters, counted once: {whole_category:,.2f}")34print(f"golden: {golden_result(con, question)[0][0]:,.2f}")3536# The same query shape with no product filter: every 2025 order joined to all of its lines.37# `/` is decimal division in DuckDB, so the average keeps its fraction.38(per_order,), = con.execute("SELECT count(*) / count(DISTINCT order_id) FROM analytics.fct_order_lines").fetchall()39# The second subquery is the true sum: each 2025 order counted once, from the orders table alone.40(all_joined, all_once), = con.execute("""SELECT41 (SELECT sum(f.net_revenue_eur) FROM analytics.fct_orders f JOIN analytics.fct_order_lines fl ON f.order_id = fl.order_id42 WHERE EXTRACT(YEAR FROM f.placed_at) = 2025),43 (SELECT sum(net_revenue_eur) FROM analytics.fct_orders WHERE EXTRACT(YEAR FROM placed_at) = 2025)""").fetchall()44print(f"lines per order: {per_order:.2f}")45print(f"all 2025 orders joined to their lines, sum over true sum: {all_joined / all_once:.2f}")
python tent-orders-fan-out.pySELECT SUM(f.net_revenue_eur) AS net_revenue
FROM analytics.fct_orders f
JOIN analytics.fct_order_lines fl ON f.order_id = fl.order_id
JOIN analytics.dim_products p ON fl.product_id = p.product_id
WHERE p.product_name LIKE '%tent%'
AND EXTRACT(YEAR FROM f.placed_at) = 2025
rows the join matched: 493
orders behind them: 480
model's answer: 256,467.71
each order counted once: 246,717.82
inflated by: 4.0%
tents and shelters, counted once: 400,909.10
golden: 377,497.40
lines per order: 2.56
all 2025 orders joined to their lines, sum over true sum: 3.38
The fan-out inflated the answer by 4.0%. The model joined orders to lines and products and summed f.net_revenue_eur over every joined row, and the join matched 493 rows for 480 orders, so every order with more than one tent line was counted more than once. Counting each order once, with the same joins and filters, gives 246,717.82 euros, less than the model's answer.
The name match is the query's largest error: it matches products whose name contains tent, which misses the shelters in the same category. Counting the whole Tents category, once per order, gives 400,909.10, and leaving out cancelled orders and counting local business days brings it to the golden 377,497.40. Two errors pulled in opposite directions: the fan-out added a little and the name match took away far more. A reviewer comparing the figure with last year's might see nothing odd.
How large a fan-out is depends on the data, not on the query. Few of the shop's orders have two tent lines, so the inflation here is small. Where a join repeats most rows several times, the same query shape multiplies a sum. Join every order of 2025 to all its lines, with no product filter, and the sum of net revenue comes to 3.38 times the true figure. Each order's revenue counts once for each of its lines, and the shop's orders have 2.56 lines on average; the ratio is that count weighted by revenue, so it can exceed the plain average. The size you measure on one question tells you nothing about the next.
Working rule: When a query joins tables of different grains, check the grain of every column it sums. Aggregate the finer table to the coarser grain before joining, or filter with IN or EXISTS, and compare the row count after the join with the count of distinct keys. Expect a model given only the schema to miss this, because the schema names columns, not grains.
스키마에 담기지 않는 정의
팬아웃은 기계적인 오류다. 테이블의 그레인을 아는 사람이라면 SQL에서 이를 찾아낼 수 있다. 정의는 다르다. 매출, 주문 금액, 활성 고객 같은 단어는 한 비즈니스 안에서 하나의 의미를 가지며, 그 의미는 해당 수치를 소유한 사람들이 정한다. 그 의미는 재무 정책이나 대시보드의 SQL, 또는 누군가의 기억 속에 있다. 스키마에는 그 단어에 각각 들어맞는 열이 여러 개 있을 수 있으며, 비즈니스가 그중 어느 것을 뜻하는지는 스키마가 알려 주지 않는다. 잘못된 열을 고른 쿼리는 다른 질문에 대한 올바른 SQL이다.
이 상점의 재무팀은 매출을 순매출로 정의한다. 순매출은 유로 단위이고, 할인 후 세금 전 금액이며, 취소되지 않은 실제 주문만 포함하고, 주문의 현지 영업일을 기준으로 한다. 주문 마트에는 gross_revenue_eur과 net_revenue_eur이 모두 있다. EMEA의 가격에는 VAT가 포함되어 있고 다른 지역은 판매세를 별도로 더하므로, 두 열의 차이는 지역마다 다르다. 질문은 골든 질문 gq-008이다. "2025년 우리의 총매출은 얼마였는가?" 이 질문에는 순(net)이라는 말이 없는데, 상점에서 일하는 사람들은 그렇게 말하지 않기 때문이다. 어느 선택이 가장 큰 차이를 만들까? 예제는 총매출 방식에서 골든 방식까지 선택을 한 번에 하나씩 바꾼다.
1"""Replay the model's SQL for total revenue in 2025, then change one choice at a time until it reaches the golden result. The line that matters is `overstated by`, and the step from gross to net, which matches the tax in the gross figure."""2from lib.golden import golden_result, load_golden3from 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-008")10sql = extract_sql(RecordedModel("schema-only-sql").complete(schema_only_messages(describe_schema(con), question.question)))11print(sql)121314def total(measure: str, where: str) -> float:15 return con.execute(f"SELECT sum({measure}) FROM analytics.fct_orders WHERE {where}").fetchall()[0][0]161718# The model filters on placed_at, the UTC timestamp, so "2025" here is 2025 by UTC, not by local business day.19utc_2025 = "placed_at >= '2025-01-01' AND placed_at < '2026-01-01'"20model_answer, golden = run_sql(con, sql).rows[0][0], golden_result(con, question)[0][0]21# Each step changes one choice from the step before it, so the values read as a walk to the golden figure.22steps = {23 "model's answer (gross)": model_answer,24 "net instead of gross": total("net_revenue_eur", utc_2025),25 "without cancelled orders": total("net_revenue_eur", f"{utc_2025} AND status <> 'cancelled'"),26 "golden (local business days)": golden,27}28for label, value in steps.items():29 print(f"{label}: {value:,.2f}")30# The tax the gross figure carries, summed over the same rows the model's query reads.31print(f"tax in the gross figure: {total('tax_eur', utc_2025):,.2f}")32print(f"overstated by: {model_answer / golden - 1:.1%}")
python gross-or-net-revenue.pySELECT SUM(gross_revenue_eur) AS total_revenue
FROM analytics.fct_orders
WHERE placed_at >= '2025-01-01' AND placed_at < '2026-01-01'
model's answer (gross): 1,987,532.10
net instead of gross: 1,745,126.98
without cancelled orders: 1,652,978.12
golden (local business days): 1,651,527.86
tax in the gross figure: 242,404.96
overstated by: 20.3%
총매출 수치는 골든 답보다 20.3%만큼 과대하다. 모델은 gross_revenue_eur을 골랐는데, 재무팀을 만나 본 적이 없는 사람에게는 "총매출"의 합리적인 해석이다. 모델은 1,987,532.10유로라고 답했으나 골든은 1,651,527.86이다.
이 차이의 대부분은 세금이다. 같은 행에서 순매출로 바꾸면 1,745,126.98이 되는데, 이는 세금 242,404.96유로와 거의 정확히 같은 만큼의 감소이며 그 사이에는 몇 센트의 반올림 차이만 있다. 취소된 주문을 제외하면 합계가 1,652,978.12유로가 되어 나머지의 거의 전부가 제거되고, UTC 날짜 대신 현지 영업일로 세면 마지막 남은 작은 차이가 메워진다.
같은 선택이 녹화 기록의 다른 곳에서도 잘못되었다. 2026년 3월 지역별 평균 주문 금액을 묻는 gq-009에서 모델은 취소된 주문을 포함한 모든 주문에 대해 gross_revenue_eur의 평균을 냈지만, 상점의 정의는 순매출을 취소되지 않은 주문 수로 나눈다. 판매 지역별 총매출을 묻는 gq-012에서도 모델은 다시 총매출을 골랐다. 어느 쪽도 부주의한 선택은 아니다. 각각 상점 밖에서 그 단어가 흔히 갖는 의미다.
우리가 확인한 오류 분류 체계 중 이 경우에 해당하는 분류를 가진 것은 없다. 상세한 분류 체계 중 하나인 NL2SQL-BUGs는 전문가가 주석을 단 2,018건의 사례를 바탕으로 text-to-SQL의 의미 오류를 9개 범주와 31개 하위 범주로 나눈다(Liu et al., KDD 2025). 이 체계는 이 쿼리를 잘못된 열을 뜻하는 Attribute Mismatch로 분류할 텐데, 이는 원인이 아니라 증상을 가리킨다. "비즈니스가 이것을 스키마와 다르게 정의한다"에 해당하는 분류는 하나도 없다. 우리가 찾은 가장 가까운 사례는 벤치마크 주석을 감사한 연구에 있으며, 질문과 그 질문에 필요한 도메인 지식 사이의 불일치를 명시한다(Jin et al., CIDR 2026). 이 공백은 두 논문에 대한 우리의 해석이며 측정된 결과가 아니고, 다른 분류 체계에는 해당 분류가 있을 수 있다.
작업 규칙: 질문에 나오는 모든 비즈니스 용어는 일치시킬 열이 아니라 찾아봐야 할 정의로 다룬다. 각 정의를 누가 소유하는지 기록하고, 에이전트에게는 계산에 쓸 수 있는 열만 주지 말고 정의 자체를 관리되는 지표로, 최소한 용어집 항목으로 제공한다. 어떤 용어에 문서화된 정의가 없으면 에이전트가 임의로 고르지 않고 되묻거나 답변을 거절하도록 한다.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
Time zones and the fiscal calendar
Time looks like the least ambiguous part of a question, and on the shop's warehouse it is where the model went wrong as often as anywhere. A date in a question has a calendar and a time zone, and a warehouse usually stores timestamps in UTC while the business counts days in local time and years in its own fiscal calendar. A definition such as "fiscal year" is at least a phrase the model can see in the question. The time zone is invisible: the question says "May", and the query has to know whose May.
The shop counts each order on its region's local business day, stored as order_date_local in the orders mart, beside the UTC timestamp placed_at. Its fiscal year starts on February 1 and is named for the year it ends in, so fiscal 2026 runs from February 1, 2025 to January 31, 2026, and analytics.dim_date holds the fiscal columns. Golden question gq-004 asks how many customers placed their first order in fiscal year 2026. Golden question gq-013 asks how many orders each sales region received in May 2026. One tests the fiscal year, the other the local business day.
1"""Two time traps in the model's SQL: a calendar year where the question meant a fiscal one, and UTC days. Read the `gq-004 model` line, the `gq-013` lines, and the counts of orders whose two clocks disagree."""2from lib.golden import golden_result, load_golden3from lib.model import RecordedModel4from lib.prompts import schema_only_messages5from lib.tools import extract_sql, run_sql6from lib.warehouse import connect, describe_schema78con = connect()9schema, model = describe_schema(con), RecordedModel("schema-only-sql")10golden = {q.question_id: q for q in load_golden()}111213def model_answer(question_id: str):14 sql = extract_sql(model.complete(schema_only_messages(schema, golden[question_id].question)))15 print(sql)16 return run_sql(con, sql).rows171819# gq-004: customers whose first order was in fiscal year 2026, which runs from February 1, 2025 to January 31, 2026.20print(f"gq-004 model: {model_answer('gq-004')[0][0]}")21print(f"gq-004 golden: {golden_result(con, golden['gq-004'])[0][0]}")2223# gq-013: orders per region in May 2026. The model filters on placed_at, in UTC; the shop counts local days.24# Both queries return (region, count) rows, which dict turns into a lookup by region.25model_rows, golden_rows = dict(model_answer("gq-013")), dict(golden_result(con, golden["gq-013"]))26for region in sorted(golden_rows):27 print(f"gq-013 {region} model: {model_rows[region]}")28 print(f"gq-013 {region} golden: {golden_rows[region]}")29# Orders in May by exactly one of the two clocks, counting 2026 by local day.30(moved,), = con.execute("""SELECT count(*) FROM analytics.fct_orders31 WHERE (month(placed_at) = 5) <> (month(order_date_local) = 5) AND year(order_date_local) = 2026""").fetchall()32print(f"orders across May's edges: {moved}")3334# Which way each of those orders crossed, by region: in May by the model's UTC filter only, or by the local day only.35# UTC is a half-open range that ends before June 1; the local day is a date, so BETWEEN also takes May 31.36crossings = con.execute("""SELECT region,37 count(*) FILTER (WHERE in_utc AND NOT in_local), count(*) FILTER (WHERE in_local AND NOT in_utc)38 FROM (SELECT region, placed_at >= '2026-05-01' AND placed_at < '2026-06-01' AS in_utc,39 order_date_local BETWEEN DATE '2026-05-01' AND DATE '2026-05-31' AS in_local40 FROM analytics.fct_orders) GROUP BY region ORDER BY region""").fetchall()41for region, utc_only, local_only in crossings:42 print(f"gq-013 {region} in May by UTC only: {utc_only}")43 print(f"gq-013 {region} in May by local day only: {local_only}")44print(f"all regions in May by UTC only: {sum(c[1] for c in crossings)}")45print(f"all regions in May by local day only: {sum(c[2] for c in crossings)}")
python fiscal-year-and-local-day.pySELECT COUNT(*) AS customer_count
FROM analytics.dim_customers
WHERE first_order_on >= '2026-01-01' AND first_order_on < '2027-01-01'
gq-004 model: 225
gq-004 golden: 825
SELECT
region,
COUNT(order_id) AS order_count
FROM
analytics.fct_orders
WHERE
placed_at >= '2026-05-01' AND placed_at < '2026-06-01'
GROUP BY
region
ORDER BY
order_count DESC
gq-013 AMER model: 219
gq-013 AMER golden: 220
gq-013 APAC model: 148
gq-013 APAC golden: 147
gq-013 EMEA model: 317
gq-013 EMEA golden: 317
orders across May's edges: 6
gq-013 AMER in May by UTC only: 2
gq-013 AMER in May by local day only: 3
gq-013 APAC in May by UTC only: 1
gq-013 APAC in May by local day only: 0
gq-013 EMEA in May by UTC only: 0
gq-013 EMEA in May by local day only: 0
all regions in May by UTC only: 3
all regions in May by local day only: 3
The model's gq-004 query is tidy and runs, and it answers the wrong year. It counts customers whose first order fell in calendar 2026: 225, where the golden answer is 825. Because the shop's data ends on 2026-06-30, it counted half a calendar year, not a whole fiscal one, so the error is large here. For a period where the two years overlap more, the same mistake would be small and harder to see.
May is off by one order in two regions, because the filter uses placed_at, the UTC timestamp. For AMER it says 219 orders against a golden 220, and for APAC 148 against 147; EMEA matches the golden answer.
Across all regions, 6 orders of 2026 fall in May by one clock and not the other, and they partly cancel. In AMER, 3 orders count in May only by the local day and 2 only by UTC, so the model ends one order short. In APAC, 1 order counts in May only by UTC, so the model ends one over.
Errors this small are the dangerous kind. They survive a sanity check, and they make two reports of the same month disagree by a few orders, which costs a team an afternoon to explain.
The recording has the other half of the problem too. For two questions about fiscal periods, gq-001 and gq-010, the model filtered fct_orders on fiscal_year and fiscal_quarter, which exist only in dim_date, and the queries failed to run. Those failures are cheap, because someone sees them. The calendar-year query for gq-004 is the expensive one.
Working rule: Give the agent your calendar, not only your columns: which timestamp is UTC, which date is the business day, and when the fiscal year starts and how it is named. Test every period question at a boundary, such as the first day of a month or the first month of a fiscal year, where UTC and local days or calendar and fiscal years disagree. Prefer a date dimension the agent joins over date arithmetic it writes.
이름이 그럴듯한 테이블
정의가 올바른 테이블 안에서 잘못될 수 있듯이, 테이블 전체가 잘못된 것일 수도 있다. 대부분의 웨어하우스에는 더 이상 아무도 조회해서는 안 되지만 아무도 삭제할 수 없는 테이블이 있다. 예전 대시보드가 읽는 오래된 마트, 마이그레이션 때 만든 사본, 한 번의 분석을 위해 만든 테이블이 그 예이다. 이런 테이블은 사람을 위해 만들어졌기 때문에 웨어하우스에서 가장 친숙한 컬럼 이름을 가진 경우가 많고, 그 정의는 옛 정의이다. 이름으로 테이블을 고르는 모델은 이런 테이블을 선호할 수 있다. 기업용 text-to-SQL 과제를 모은 벤치마크인 Spider 2.0은 두 에이전트 프레임워크에서 실패한 과제 300건을 표본으로 살펴보았을 때, 오류의 10.1%가 잘못된 테이블 선택 때문이라는 결과를 얻었다(Lei et al., ICLR 2025).
이 가게의 웨어하우스에도 그런 테이블이 하나 있다. analytics.orders_legacy이며, revenue와 sales_region라는 컬럼을 가지고 있다. 이 테이블의 매출은 세금이 포함된 총매출이고, 테스트 주문과 취소된 주문도 그대로 남아 있다. 골든 질문 gq-012, 즉 "2025년 판매 지역별 총매출은 얼마였는가?"는 이 테이블의 컬럼 이름을 둘 다 사용하며, 모델이 이 테이블로 끌리도록 작성되었다. 판매 지역별 주문 수를 묻는 gq-013도 마찬가지이다. 이어서 두 가지를 확인한다. 하나는 레거시 테이블이 무엇을 답하는가이고, 다른 하나는 모델의 쿼리가 이 테이블을 한 번이라도 읽는가이다.
1"""What the legacy mart says about revenue by sales region in 2025, and how often the model's queries read that mart. The line to read is `legacy total over golden`, and the count of queries that read `orders_legacy`."""2from lib.golden import golden_result, load_golden3from lib.model import RecordedModel4from lib.prompts import schema_only_messages5from lib.tools import extract_sql6from lib.warehouse import connect, describe_schema78con = connect()9question = next(q for q in load_golden() if q.question_id == "gq-012")10golden = dict(golden_result(con, question))11# The legacy mart's revenue is gross, tax included, and it keeps test and cancelled orders.12legacy = dict(con.execute("""SELECT sales_region, sum(revenue) FROM analytics.orders_legacy13 WHERE year(order_date) = 2025 GROUP BY sales_region""").fetchall())14# Test orders are flagged only in raw, which the marts leave out; the legacy mart keeps them.15(test_orders,), = con.execute("""SELECT count(*) FROM analytics.orders_legacy AS l JOIN raw.orders AS o ON o.order_id = l.id16 WHERE o.is_test AND year(l.order_date) = 2025""").fetchall()1718for region in sorted(golden):19 print(f"{region} golden: {golden[region]:,.2f}")20 print(f"{region} orders_legacy: {legacy[region]:,.2f}")21print(f"legacy total over golden: {sum(legacy.values()) / sum(golden.values()) - 1:+.1%}")22print(f"test orders in the legacy rows: {test_orders}")2324# The model's schema-only queries for the first 16 golden questions: did any read the legacy mart?25model, schema = RecordedModel("schema-only-sql"), describe_schema(con)26# IDs are zero-padded, so comparing them as text selects gq-001 to gq-016.27asked = [q for q in load_golden() if q.question_id <= "gq-016"]28sqls = [extract_sql(model.complete(schema_only_messages(schema, q.question))) for q in asked]29# A query counts as reading the legacy mart when its text names orders_legacy.30print(f"model queries that read orders_legacy: {sum('orders_legacy' in sql for sql in sqls)} of {len(sqls)}")31print(f"golden questions added in chapter 2, gq-006 to gq-016: {sum('gq-006' <= q.question_id <= 'gq-016' for q in asked)}")
python legacy-table-answer.pyAMER golden: 497,466.04
AMER orders_legacy: 576,980.12
APAC golden: 405,055.09
APAC orders_legacy: 465,770.13
EMEA golden: 749,006.73
EMEA orders_legacy: 962,439.56
legacy total over golden: +21.4%
test orders in the legacy rows: 62
model queries that read orders_legacy: 0 of 16
golden questions added in chapter 2, gq-006 to gq-016: 11
레거시 테이블의 revenue 컬럼을 2025년 기준으로 sales_region별로 합산하면, 매출이라고 적힌 컬럼 이름 아래에서 골든 값보다 +21.4%만큼 큰 합계가 나온다. EMEA의 경우 이 테이블은 962,439.56유로를 내놓는데, 골든 순매출은 749,006.73이다. 전체 지역을 통틀어 2025년 행에는 테스트 주문이 62건 들어 있다.
기록된 모델은 이 테이블을 한 번도 읽지 않았다. 골든 질문 gq-001부터 gq-016까지에 대한 쿼리 중 orders_legacy을 건드린 것은 0 of 16건이었다. 이 웨어하우스에서는 fct_orders가 region 컬럼과 두 가지 매출 컬럼을 모두 가지고 있었고, 모델은 매번 이 테이블을 더 선호했다. 이는 이 모델, 이 스키마, 이 질문들에 대한 결과일 뿐 보장은 아니다. 웨어하우스가 커질수록 위험도 커진다. Spider 2.0-lite의 데이터베이스는 평균 755개가 넘는 컬럼을 가진 반면, 학술 벤치마크인 BIRD는 평균 약 54개이다(Lei et al., ICLR 2025). 수백 개의 테이블 가운데에서는 레거시 테이블의 친숙한 이름이 질문의 단어와 더 자주 맞아떨어질 것으로 예상한다. 에이전트에 검색된 예제나 설명을 제공할 때도 위험이 커질 것으로 예상하는데, 이름을 대상으로 한 검색이 레거시 테이블을 높은 순위에 올릴 수 있기 때문이다.
이런 이유로 이 책 전반에서 orders_legacy을 웨어하우스에 그대로 둔다. 삭제할 수 없는 테이블은 에이전트가 찾아낼 수 있는 테이블이며, 방어는 모델 아래 계층에 두어야 한다. 곧 모델이 읽을 수 있는 테이블의 목록이다.
작업 규칙: 에이전트가 읽을 수 있는 테이블의 목록을 만들고, 이름이 아무리 친숙하더라도 레거시 테이블과 스테이징 테이블은 목록에서 뺀다. 모델이 작성한 모든 쿼리는 실행하기 전에 그 쿼리가 쓰는 테이블을 목록과 대조한다. 테스트에서 에이전트가 그럴듯한 테이블을 한 번도 고르지 않았더라도 이 검사는 그대로 유지한다. 새로운 질문, 더 큰 스키마, 검색된 예제가 그 결과를 바꿀 수 있기 때문이다.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
Filters nobody wrote down
Some rules about which rows count are so familiar to the people who own a number that nobody writes them down: cancelled orders don't count as revenue, test accounts aren't customers, a refund belongs to the month of its order. Such a rule lives in the mart's SQL, in the finance team's reports and in people's heads. A query that leaves it out returns every row, which looks like the most complete answer, not a wrong one. Chapter 1 showed it once, on net revenue by region.
The people who write text-to-SQL benchmarks miss these filters too. In the BIRD benchmark's california_schools database, the gold SQL leaves out the condition on the rtype column, which is what separates schools from districts, because the annotators had labelled that column "unuseful" (Jin et al., CIDR 2026). A rule nobody wrote down goes missing from the answer key as well as from the model's query.
The shop's marts apply one such rule for you: they remove test orders. They can't remove cancelled orders, because some questions are about cancellations, so fct_orders keeps them with their status, and the order lines keep them too. The question is golden question gq-015, "How many units of each product category did we sell in 2025?", which needs the status from one table to filter rows in another. The model's query never drops cancelled orders, and the example shows the cost.
1"""Replay the model's SQL for units sold by category in 2025, and find the filters it left out. Read the `overstated by` line, and the units on cancelled orders, which the missing status filter explains."""2from lib.golden import golden_result, load_golden3from 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-015")10sql = extract_sql(RecordedModel("schema-only-sql").complete(schema_only_messages(describe_schema(con), question.question)))11print(sql)12# The model's query returns (category, units) rows, which dict turns into a lookup by category.13model_units = dict(run_sql(con, sql).rows)14golden_units = dict(golden_result(con, question))15# The status is on the order, so the lines join to fct_orders to read it.16(cancelled,), = con.execute("""SELECT sum(l.quantity) FROM analytics.fct_order_lines AS l17 JOIN analytics.fct_orders AS o USING (order_id)18 WHERE o.status = 'cancelled' AND o.placed_at >= '2025-01-01' AND o.placed_at < '2026-01-01'""").fetchall()19# raw keeps test orders, which the marts drop; the is_test flag is only in raw.20(test_units,), = con.execute("""SELECT sum(l.quantity) FROM raw.order_lines AS l JOIN raw.orders AS o USING (order_id)21 WHERE o.is_test AND year(o.placed_at) = 2025""").fetchall()2223print(f"Cooking model: {model_units['Cooking']}")24print(f"Cooking golden: {golden_units['Cooking']}")25print(f"all categories model: {sum(model_units.values())}")26print(f"all categories golden: {sum(golden_units.values())}")27print(f"units on cancelled orders, in the model's dates: {cancelled}")28print(f"overstated by: {sum(model_units.values()) / sum(golden_units.values()) - 1:.1%}")29print(f"units on test orders, which the marts already leave out: {test_units}")3031# Orders that weren't cancelled but fall in 2025 by one clock and not the other: in by UTC only, minus in by local day only.32(edge_net,), = con.execute("""SELECT sum(l.quantity) FILTER (WHERE year(o.placed_at) = 2025 AND year(o.order_date_local) <> 2025)33 - sum(l.quantity) FILTER (WHERE year(o.placed_at) <> 2025 AND year(o.order_date_local) = 2025)34 FROM analytics.fct_order_lines AS l JOIN analytics.fct_orders AS o USING (order_id)35 WHERE o.status <> 'cancelled'""").fetchall()36print(f"net units on orders across the year's edges: {edge_net}")
python cancelled-lines-counted.pySELECT
p.category,
SUM(ol.quantity) AS total_units_sold
FROM
analytics.fct_order_lines ol
JOIN
analytics.dim_products p ON ol.product_id = p.product_id
WHERE
ol.order_id IN (
SELECT order_id
FROM analytics.fct_orders
WHERE placed_at >= '2025-01-01' AND placed_at < '2026-01-01'
)
GROUP BY
p.category
ORDER BY
total_units_sold DESC
Cooking model: 5727
Cooking golden: 5427
all categories model: 22324
all categories golden: 21108
units on cancelled orders, in the model's dates: 1206
overstated by: 5.8%
units on test orders, which the marts already leave out: 202
net units on orders across the year's edges: 10
Leaving the status out is almost the whole error. The model summed quantity from fct_order_lines for 2025 orders with no status condition, so it counts units from cancelled orders in its date range. Across all categories it says 22324 units against the golden 21108, 5.8% too many. The difference is almost all cancelled orders; the remaining 10 units are the net of orders whose UTC and local days fall in different years.
For Cooking the model says 5727 units, where the golden answer is 5427.
The test orders show the other side. The raw order lines hold 202 units on test orders in 2025, and the model's answer didn't include them, because it read the mart and the mart had applied that rule. A filter the mart applies is one the model can't forget. In every query it wrote for this chapter, the model read the marts, never the raw tables, so the test orders never reached an answer.
The query also didn't ask whether a sold unit was later returned. The shop's definition of units sold doesn't subtract returns, so the golden answer doesn't either. That is one more rule that has to be written down where the agent can read it, or the next person will reasonably answer it the other way.
Working rule: Move every rule about which rows count into the marts where you can, so no query can forget it. Where a mart has to keep the rows, as with cancelled orders, write the rule into the definition the agent uses, and add a golden question that fails when the rule is left out. Read the gold for the same rules, because the person who wrote it can miss them as readily as the model can.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
Count the failures by kind
Each section so far showed one failure on one question. To decide what to fix first, you need to know how often each kind occurs across your questions, and which failures are visible errors and which are plausible wrong numbers. The set is small, so the counts describe this run, not a rate you can quote.
This run takes the model's schema-only SQL for each golden question from gq-001 to gq-016 with an answer, 15 of them, so its failures can be counted. It leaves out gq-005, a forecast that should be declined, which is chapter 8's subject. It grades each result against the golden result as correct, wrong or error, and labels each query with its failure kinds. The labels come from lib/failure_kinds.py, which parses the model's query and its golden SQL with sqlglot and compares them. For instance, a query that uses placed_at where the golden SQL uses the local business day gets the label time. The rules are ours, written for this warehouse's traps; the module's docstring lists each one.
1"""Run the model's schema-only SQL on each golden question that has an answer, grade each result, and label the failure kinds of each query. The counts of correct, wrong and error answers, and the kinds the wrong ones carry, are what the prose quotes."""2from collections import Counter34import duckdb5from lib.failure_kinds import KINDS, failure_kinds6from lib.golden import golden_result, grade, load_golden7from lib.model import RecordedModel8from lib.prompts import schema_only_messages9from lib.tools import extract_sql, run_sql10from lib.warehouse import connect, describe_schema1112con = connect()13schema, model = describe_schema(con), RecordedModel("schema-only-sql")14# Kind "answer" leaves out gq-005, the forecast to decline; IDs are zero-padded, so gq-001 to gq-016 is a text range.15questions = [q for q in load_golden() if q.kind == "answer" and q.question_id <= "gq-016"]16outcomes, kinds_in_wrong, errors = Counter(), Counter(), {}1718print("question outcome kinds")19for q in questions:20 sql = extract_sql(model.complete(schema_only_messages(schema, q.question)))21 try:22 outcome = grade(q, run_sql(con, sql), golden_result(con, q))23 except duckdb.Error as e:24 # A query the database refuses to run is an error, not a wrong number.25 outcome, errors[q.question_id] = "error", str(e).splitlines()[0]26 # Each kind is a rule in lib/failure_kinds.py that compares the query with the golden SQL; a query can carry none or several.27 kinds = failure_kinds(sql, q.golden_sql)28 outcomes[outcome] += 129 if outcome == "wrong":30 kinds_in_wrong.update(kinds)31 print(f"{q.question_id} {outcome:<7} {', '.join(kinds) or '-'}")3233for question_id, message in errors.items():34 print(f"{question_id} error: {message}")35print(f"questions graded: {len(questions)}")36for outcome in ("correct", "wrong", "error"):37 print(f"{outcome}: {outcomes[outcome]} of {len(questions)}")38# DuckDB's messages: one for a column the table read does not have, one for a type mismatch such as LIKE on a timestamp.39print(f"errors with a missing column: {sum('Referenced column' in m for m in errors.values())}")40print(f"errors with a type mismatch: {sum('No function matches' in m for m in errors.values())}")41for kind in KINDS:42 print(f"wrong answers with {kind}: {kinds_in_wrong[kind]}")4344# gq-016 counts distinct customers: do those with an order across 2025's edges have other 2025 orders by both clocks?45(edge_customers, covered), = con.execute("""SELECT count(DISTINCT customer_id), count(DISTINCT customer_id) FILTER (WHERE customer_id IN46 (SELECT customer_id FROM analytics.fct_orders WHERE year(placed_at) = 2025 AND year(order_date_local) = 2025))47 FROM analytics.fct_orders WHERE (year(placed_at) = 2025) <> (year(order_date_local) = 2025)""").fetchall()48print(f"gq-016 customers with an order across 2025's edges: {edge_customers}")49print(f"gq-016 of them, with other 2025 orders by both clocks: {covered}")
python classify-schema-only-errors.pyquestion outcome kinds
gq-001 error unwritten filter
gq-002 wrong unwritten filter
gq-003 error unwritten filter
gq-004 wrong time
gq-006 error time, unwritten filter
gq-007 wrong fan-out, time, unwritten filter
gq-008 wrong definition, time, unwritten filter
gq-009 wrong definition, unwritten filter
gq-010 error unwritten filter
gq-011 error time, unwritten filter
gq-012 wrong definition, time, unwritten filter
gq-013 wrong time
gq-014 correct time
gq-015 wrong time, unwritten filter
gq-016 correct time
gq-001 error: Binder Error: Referenced column "fiscal_year" not found in FROM clause!
gq-003 error: Binder Error: Referenced column "calendar_year" not found in FROM clause!
gq-006 error: Binder Error: Referenced column "quantity" not found in FROM clause!
gq-010 error: Binder Error: Referenced column "fiscal_quarter" not found in FROM clause!
gq-011 error: Binder Error: No function matches the given name and argument types '~~(TIMESTAMP, STRING_LITERAL)'. You might need to add explicit type casts.
questions graded: 15
correct: 2 of 15
wrong: 8 of 15
error: 5 of 15
errors with a missing column: 4
errors with a type mismatch: 1
wrong answers with fan-out: 1
wrong answers with definition: 3
wrong answers with time: 6
wrong answers with tempting table: 0
wrong answers with unwritten filter: 6
gq-016 customers with an order across 2025's edges: 6
gq-016 of them, with other 2025 orders by both clocks: 6
Most failures ran and returned a number, the harder kind to catch: 8 of 15 wrong answers, against 5 of 15 queries that failed to run. Of the errors, 4 used a column missing from the table they read, such as fiscal_year in fct_orders, and 1 matched a timestamp against a text pattern with LIKE.
The wrong answers are the expensive ones. 6 of them carry a time error and 6 an unwritten filter. Most wrong answers carried two or three kinds at once, which is why the fan-out and definition sections changed one choice at a time to separate them.
Definitions show up in 3 wrong answers, fan-out in 1, and the tempting table in 0, as its section found.
A correct grade doesn't mean a correct query. The correct answers, gq-014 and gq-016, both carry the time label: their queries count UTC dates, and the counts matched for different reasons. For gq-014, orders crossed May's edges in both directions, 3 in by UTC only and 3 in by local day only, as the time section showed. For gq-016, a count of distinct customers, 6 customers had an order at the year's edges, and 6 of them had other orders in 2025 by both clocks, so the count couldn't move. The tent name match in gq-007 has no label, because no rule looks for it.
Each of the book's five kinds has a close neighbour in published taxonomies, under a different name. The mapping is our judgement:
| Kind in this book | Nearest published class |
|---|---|
| fan-out | Join Condition Mismatch; Aggregate Functions [1] |
| definition | none; closest is a domain-knowledge mismatch (E3) [2] |
| time | Date/Time Functions [1] |
| tempting table | Table Mismatch [1] |
| unwritten filter | Implicit Condition Missing; Explicit Condition Missing [1] |
Source [1] is NL2SQL-BUGs (Liu et al., KDD 2025), and [2] the CIDR 2026 audit of benchmark annotations (Jin et al.). Their classes are finer than ours, and ours are coarser on purpose: each of the five points to one fix in the warehouse or in what the agent is given.
That mapping also answers which failures a better prompt can fix. A prompt can carry general practice, such as aggregating lines before joining them to orders, and it can carry facts about one warehouse, such as which date column is the business day. It can't carry the shop's definitions for every word people use, and a prompt that tries becomes a second, unreviewed copy of them. We expect a prompt to reduce fan-out and some time errors, and only definitions the agent can read and the warehouse enforces to remove the other three kinds. That is a judgement this chapter's run supports, not one it measures. Benchmarks show a similar gap at a larger scale. On Spider 2.0, an agent framework built on o1-preview solved 21.3% of the enterprise tasks, while methods based on GPT-4 had reached 91.2% on Spider 1.0 and 73.0% on BIRD (Lei et al., ICLR 2025). The three numbers come from different systems and task formats, so they illustrate the gap rather than measure it.
Working rule: Count failures by kind on your own golden questions before you choose a fix, and keep errors that fail loudly apart from wrong numbers that don't. Label kinds by comparing each query with its golden SQL, with rules you can read, and read the wrong answers no rule labels. Fix the most common kind of wrong number first, and expect it to be a definition, a calendar or a filter more often than a syntax mistake.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
What goes wrong in production
The failures in this chapter came from questions written to find them. In production the questions are written by people who don't know where the traps are.
Two errors cancel and the number looks right. The tent question's fan-out and name match pulled in opposite directions, and the UTC counts for two questions matched because errors cancelled or couldn't show. A spot check against a known figure passes these. You notice only by comparing every result with a golden result under one rule, and by reading the SQL of answers that pass, not only of those that fail.
A definition changes and the old one keeps answering. When the finance team changes what counts as revenue, every query that computed it from columns keeps the old meaning. The golden questions for revenue start failing if their golden SQL was updated, and keep passing if it wasn't. Keep golden SQL beside the definitions it depends on, and review a change to either together with the other.
The legacy table turns up later. In this chapter's run, the model never chose orders_legacy. A larger schema, retrieved examples or a question in the legacy table's vocabulary can change that without any change to your code. Check each query's tables against a list of allowed tables, and log which tables answered each question, so a shift shows up in a count.
Small time errors make reports disagree. A count that differs by one order from the finance report starts a reconciliation, and the cause is a UTC date where the business uses local days. You notice when two reports of the same period disagree, which is late. Add golden questions at period boundaries, where the two clocks differ, and keep them in the set.
A filter is applied in one place and not another. Test orders are removed in the marts, so any query over the raw tables brings them back. An agent with access to raw can read it, for a column the marts don't have. Keep the raw schema out of the agent's reach, or give it the filters as part of every table's description.
The failure labeller misses new kinds. lib/failure_kinds.py finds the five kinds this chapter knows about, and nothing else, as the tent name match showed. A dashboard of failure counts by kind looks complete while a new kind grows unlabelled. Read a sample of wrong answers by hand on a schedule, and add a rule when a new kind shows up twice.
Working rule: Before an agent's numbers reach anyone, grade every answer against golden results, keep golden questions at period boundaries and for every definition, list the tables the agent may read, and read a sample of wrong answers by hand. Count wrong numbers by kind, and treat a rising count in any kind as an alarm.
이 장에서 내린 결정
- 그럴듯하게 틀린 숫자의 다섯 가지 유형: 팬아웃, 스키마에 담겨 있지 않은 정의, 시간대와 회계 달력, 잘못 고르기 쉬운 테이블, 쓰이지 않은 필터. 각 유형은 웨어하우스나 에이전트에 주어지는 내용에서 고칠 지점을 가리킨다.
- 각 실패는 설명하지 않고 실행한다. 모델이 일으킨 모든 실패를 그 실제 쿼리로 실행하며, 두 오류가 겹치는 경우에는 예제가 선택을 하나씩 바꿔 가며 각각의 영향을 측정한다.
- 11 골든 질문이 세트에 추가되었다.
gq-006부터gq-016까지이며, 각 질문은 웨어하우스의 함정 하나를 건드리도록 작성되었고 골든 SQL은 재무팀의 정의를 따른다. - 실패 유형은 읽을 수 있는 규칙으로 분류한다. 규칙은
lib/failure_kinds.py에 있으며, 각 쿼리를 골든 SQL과 비교해 분류한다. 어떤 규칙으로도 분류되지 않는 오답은 사람이 직접 읽는다. - 오류와 틀린 숫자는 따로 센다. 실행되지 않는 쿼리는 비용이 적게 드는 실패이고, 그럴듯한 숫자를 반환하는 쿼리가 먼저 고쳐야 할 대상이다.
- 레거시 테이블은 웨어하우스에 남겨 둔다. 여기서 모델은 한 번도 이 테이블을 고르지 않았지만, 삭제할 수 없는 테이블은 에이전트가 찾아낼 수 있는 테이블이기 때문이다.
- 대부분의 유형에서 해결책은 프롬프트가 아니라 정의다. 프롬프트는 일반적인 관행을 담을 수 있지만, 비즈니스의 정의는 에이전트가 읽고 웨어하우스가 강제하는 곳에 있어야 한다.
이 판의 이 장
2026.11.15판예제 통과 2026년 11월 19이 장에서 6개 예제개 중 6개가 통과한다
영어판 2026.11.15판을 번역한 것이다.