raw text-to-SQL が誤った数値を返す場面

読了まで約55 分分、8 節 節の 6 例 例。

第2026.11.15版 2026年11月19日時点で例が通過 78 例中 78 例が合格 変更点

AIによる翻訳。英語版は人による検証済みである。 当社の書籍の作り方

英語版の書籍(版2026.11.15)から翻訳した。 原文を読む

この章を終えるころには、スキーマだけを与えた text-to-SQL が実際のウェアハウスでもっともらしい誤った数値を返す五つの経路を挙げ、それぞれを店のウェアハウス上で示し、ゴールデンクエスチョンの集合にわたって数え上げ、プロンプトの改善で直せるものと、スキーマに含まれない定義を必要とするものとを見分けられるようになる。

第1章では、ウェアハウスのスキーマだけを与えてモデルに 2026 年 3 月の地域別純売上を尋ね、キャンセル済みの注文を数えたために合計が 8.3% 過大になった結果を得た。この章では、EMEA、AMER、APAC でアウトドア用品とキャンプ用品を販売する店について、さらに 11 の質問を投げる。各質問はウェアハウスの落とし穴のいずれかに触れるように書かれており、それぞれの質問についてモデルの SQL を実行する。ウェアハウスは、固定のシードから本書が生成する DuckDB データベースである。ロード時のスキーマである raw と、マートのスキーマである analytics を備えている。

この懸念は広く共有されている。dbt Labs による 2026 年のアナリティクスエンジニアリング調査では、回答者の 71% が「誤った出力や幻覚による出力がステークホルダーに届くこと」を主な懸念として挙げた(dbt Labs, 2026 年 4 月 14 日)。この調査の回答者は 363 人で、2025 年末から 2026 年初頭にかけて集められた。ベンダーが実施したもので、回答することを選んだ人々を対象としているため、測られているのは懸念の大きさであり、誤った数値が実際に出荷される頻度ではない。また、誤った数値がどこから生じるのかも示していない。以下の各節では、店のウェアハウス上の五つの場面を一つずつ示し、最後の節でそれぞれの出現頻度を数える。

すべての例は schema-only-sql という記録を再生する。この記録は、固定(ピン留め)されたオープンモデルである Qwen3-4B-Instruct-2507 に、全テーブルのスキーマと一つの質問を示して DuckDB のクエリを一つ求めたときの応答である。記録の作り方と、各応答が正確なメッセージのハッシュで引かれる理由は、第1章で説明している。各例では、続いてモデルのクエリをウェアハウス上で実行し、その結果をゴールデン結果と比較する。ゴールデン結果とは、財務チームの定義に沿って書かれ、実行して確認された SQL による、その質問の答えであり、質問とともに examples/data/golden/questions.toml に保存されている。

この部分はまだ翻訳されていないため、英語で表示している。

Joins that fan out and inflate a sum

最終変更:版2026.11.12

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.

tent-orders-fan-out.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 合格
"""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."""from lib.golden import golden_result, load_goldenfrom 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-007")sql = extract_sql(RecordedModel("schema-only-sql").complete(schema_only_messages(describe_schema(con), question.question)))print(sql)# The model's own joins and filters, kept as they are, so only the counting changes.# placed_at is UTC, so EXTRACT gives the year by UTC, as the model wrote it.matched = """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 {product} AND EXTRACT(YEAR FROM f.placed_at) = 2025"""# The model's name match, which misses shelters, and the whole Tents category, which has them.by_name, by_category = "p.product_name LIKE '%tent%'", "p.category = 'Tents'"# One row for each matched line and a count of the distinct orders behind them; the trailing comma unpacks that one row.(lines, orders), = con.execute(f"SELECT count(*), count(DISTINCT f.order_id) {matched.format(product=by_name)}").fetchall()# A semi-join: each order's whole net revenue, counted once, however many of its lines match.once = "SELECT sum(net_revenue_eur) FROM analytics.fct_orders WHERE order_id IN (SELECT f.order_id {matched})"model_total = run_sql(con, sql).rows[0][0]counted_once = con.execute(once.format(matched=matched.format(product=by_name))).fetchall()[0][0]whole_category = con.execute(once.format(matched=matched.format(product=by_category))).fetchall()[0][0]print(f"rows the join matched: {lines}")print(f"orders behind them: {orders}")print(f"model's answer: {model_total:,.2f}")print(f"each order counted once: {counted_once:,.2f}")print(f"inflated by: {model_total / counted_once - 1:.1%}")print(f"tents and shelters, counted once: {whole_category:,.2f}")print(f"golden: {golden_result(con, question)[0][0]:,.2f}")# The same query shape with no product filter: every 2025 order joined to all of its lines.# `/` is decimal division in DuckDB, so the average keeps its fraction.(per_order,), = con.execute("SELECT count(*) / count(DISTINCT order_id) FROM analytics.fct_order_lines").fetchall()# The second subquery is the true sum: each 2025 order counted once, from the orders table alone.(all_joined, all_once), = con.execute("""SELECT    (SELECT sum(f.net_revenue_eur) FROM analytics.fct_orders f JOIN analytics.fct_order_lines fl ON f.order_id = fl.order_id     WHERE EXTRACT(YEAR FROM f.placed_at) = 2025),    (SELECT sum(net_revenue_eur) FROM analytics.fct_orders WHERE EXTRACT(YEAR FROM placed_at) = 2025)""").fetchall()print(f"lines per order: {per_order:.2f}")print(f"all 2025 orders joined to their lines, sum over true sum: {all_joined / all_once:.2f}")
出力python tent-orders-fan-out.py
SELECT 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.

スキーマに含まれない定義

最終変更:版2026.11.12

ファンアウトは機械的な誤りである。テーブルのグレインを知っている人であれば、SQL を見て見つけられる。定義はこれとは異なる。売上、注文額、アクティブ顧客といった語は、ビジネスの中ではただ一つの意味を持つ。その意味を決めるのは数値の責任者であり、財務方針、ダッシュボードの SQL、あるいは誰かの記憶の中に存在する。スキーマには、その語に当てはまる列が複数ある場合があるが、ビジネスがどの列を意味しているのかは何も示さない。誤った列を選んだクエリは、別の問いに対しては正しい SQL である。

このショップの財務チームは、売上を純売上として定義している。すなわち、ユーロ建てで、値引き後、税抜きで、キャンセルされていない実際の注文を対象とし、注文の現地営業日で集計する。オーダーマートには gross_revenue_eur と net_revenue_eur の両方がある。EMEA の価格は VAT を含み、ほかの地域では売上税が上乗せされるため、2 つの列の差は地域によって異なる。問いはゴールデンクエスチョン gq-008 「2025 年の総売上はいくらだったか」である。この問いに「純」と書かれていないのは、ショップで働く人は「純」とは言わないからである。どの選択が最も大きな代償を生むのか。この例では、総額ベースからゴールデンの答えまで、1 つずつ変更していく。

gross-or-net-revenue.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 合格
"""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."""from lib.golden import golden_result, load_goldenfrom 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-008")sql = extract_sql(RecordedModel("schema-only-sql").complete(schema_only_messages(describe_schema(con), question.question)))print(sql)def total(measure: str, where: str) -> float:    return con.execute(f"SELECT sum({measure}) FROM analytics.fct_orders WHERE {where}").fetchall()[0][0]# The model filters on placed_at, the UTC timestamp, so "2025" here is 2025 by UTC, not by local business day.utc_2025 = "placed_at >= '2025-01-01' AND placed_at < '2026-01-01'"model_answer, golden = run_sql(con, sql).rows[0][0], golden_result(con, question)[0][0]# Each step changes one choice from the step before it, so the values read as a walk to the golden figure.steps = {    "model's answer (gross)": model_answer,    "net instead of gross": total("net_revenue_eur", utc_2025),    "without cancelled orders": total("net_revenue_eur", f"{utc_2025} AND status <> 'cancelled'"),    "golden (local business days)": golden,}for label, value in steps.items():    print(f"{label}: {value:,.2f}")# The tax the gross figure carries, summed over the same rows the model's query reads.print(f"tax in the gross figure: {total('tax_eur', utc_2025):,.2f}")print(f"overstated by: {model_answer / golden - 1:.1%}")
出力python gross-or-net-revenue.py
SELECT 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 の日付ではなく現地営業日で数えると、最後の小さな差が埋まる。

録画の別の箇所でも、同じ選択が誤りにつながっていた。gq-009(2026 年 3 月の地域別平均注文額)では、モデルはキャンセル済みを含むすべての注文について gross_revenue_eur を平均した。ショップの定義では、純売上をキャンセルされていない注文数で割る。gq-012(販売地域ごとの総売上)でも、モデルはまた総額ベースを選んだ。どちらの選択も不注意によるものではない。いずれも、ショップの外ではその語の一般的な意味である。

確認した 2 つのエラー分類のどちらにも、この種の誤りに当たるクラスはない。詳細な分類の一つである NL2SQL-BUGs は、専門家が注釈を付けた 2,018 件の事例から、text-to-SQL の意味的エラーを 9 つのカテゴリと 31 のサブカテゴリに分類している(Liu et al., KDD 2025)。この分類では、このクエリは誤った列を意味する Attribute Mismatch に分類されるだろうが、この名称は原因ではなく症状を指している。「ビジネスがスキーマとは異なる定義をしている」というクラスはない。我々が見つけた中で最も近いものは、ベンチマークの注釈を監査した研究にあり、問いと、その問いが必要とするドメイン知識との不一致を挙げている(Jin et al., CIDR 2026)。この欠落は 2 つの論文に対する我々の解釈であり、測定された結果ではない。ほかの分類にはこのクラスがある可能性もある。

実践ルール: 問いに含まれるビジネス用語は、照合すべき列ではなく、調べるべき定義として扱う。各定義の責任者を書き留め、エージェントには、計算元になりうる列だけでなく、定義そのものを、管理されたメトリクスとして、少なくとも用語集の項目として与える。ある語に文書化された定義がない場合は、エージェントに、選ばせるのではなく、確認させるか回答を断らせる。

この部分はまだ翻訳されていないため、英語で表示している。

Time zones and the fiscal calendar

最終変更:版2026.11.15

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.

fiscal-year-and-local-day.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 合格
"""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."""from lib.golden import golden_result, load_goldenfrom lib.model import RecordedModelfrom lib.prompts import schema_only_messagesfrom lib.tools import extract_sql, run_sqlfrom lib.warehouse import connect, describe_schemacon = connect()schema, model = describe_schema(con), RecordedModel("schema-only-sql")golden = {q.question_id: q for q in load_golden()}def model_answer(question_id: str):    sql = extract_sql(model.complete(schema_only_messages(schema, golden[question_id].question)))    print(sql)    return run_sql(con, sql).rows# gq-004: customers whose first order was in fiscal year 2026, which runs from February 1, 2025 to January 31, 2026.print(f"gq-004 model: {model_answer('gq-004')[0][0]}")print(f"gq-004 golden: {golden_result(con, golden['gq-004'])[0][0]}")# gq-013: orders per region in May 2026. The model filters on placed_at, in UTC; the shop counts local days.# Both queries return (region, count) rows, which dict turns into a lookup by region.model_rows, golden_rows = dict(model_answer("gq-013")), dict(golden_result(con, golden["gq-013"]))for region in sorted(golden_rows):    print(f"gq-013 {region} model: {model_rows[region]}")    print(f"gq-013 {region} golden: {golden_rows[region]}")# Orders in May by exactly one of the two clocks, counting 2026 by local day.(moved,), = con.execute("""SELECT count(*) FROM analytics.fct_orders    WHERE (month(placed_at) = 5) <> (month(order_date_local) = 5) AND year(order_date_local) = 2026""").fetchall()print(f"orders across May's edges: {moved}")# Which way each of those orders crossed, by region: in May by the model's UTC filter only, or by the local day only.# UTC is a half-open range that ends before June 1; the local day is a date, so BETWEEN also takes May 31.crossings = con.execute("""SELECT region,    count(*) FILTER (WHERE in_utc AND NOT in_local), count(*) FILTER (WHERE in_local AND NOT in_utc)    FROM (SELECT region, placed_at >= '2026-05-01' AND placed_at < '2026-06-01' AS in_utc,                 order_date_local BETWEEN DATE '2026-05-01' AND DATE '2026-05-31' AS in_local          FROM analytics.fct_orders) GROUP BY region ORDER BY region""").fetchall()for region, utc_only, local_only in crossings:    print(f"gq-013 {region} in May by UTC only: {utc_only}")    print(f"gq-013 {region} in May by local day only: {local_only}")print(f"all regions in May by UTC only: {sum(c[1] for c in crossings)}")print(f"all regions in May by local day only: {sum(c[2] for c in crossings)}")
出力python fiscal-year-and-local-day.py
SELECT 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.

紛らわしい名前を持つテーブル

最終変更:版2026.11.12

定義は、正しいテーブルの中でも誤ることがある。また、テーブル全体が誤っていることもある。ウェアハウスの多くには、もう誰もクエリすべきではないが、誰も削除できないテーブルが残っている。古いダッシュボードが読む旧来のマート、移行時のコピー、一度きりの分析のために作られたテーブルなどである。このようなテーブルは、人間向けに作られたため、ウェアハウスの中で最も分かりやすい列名を持っていることが多く、その定義は古いものである。名前でテーブルを選ぶモデルは、このテーブルを好んで選ぶ可能性がある。企業向けテキストからSQLへの変換タスクのベンチマークである Spider 2.0 は、2つのエージェントフレームワークの失敗タスク300件の標本において、誤りの10.1%が誤ったテーブルの選択によるものであったと報告している(Lei et al., ICLR 2025)。

この店のウェアハウスにも、そのようなテーブルが1つある。analytics.orders_legacy であり、revenue と sales_region という列を持つ。その売上は税込みの総額であり、テスト注文とキャンセル済み注文も残っている。ゴールデンクエスチョン gq-012「2025年の販売地域別の総売上はいくらか」は、この2つの列名を両方とも使っており、モデルをこのテーブルへ誘うように書かれている。販売地域別の注文数を尋ねる gq-013 も同様である。次の2つを確認する。レガシーテーブルが何を答えとして返すか、そしてモデルのクエリがそれを読むことがあるかどうかである。

legacy-table-answer.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 合格
"""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`."""from lib.golden import golden_result, load_goldenfrom lib.model import RecordedModelfrom lib.prompts import schema_only_messagesfrom lib.tools import extract_sqlfrom lib.warehouse import connect, describe_schemacon = connect()question = next(q for q in load_golden() if q.question_id == "gq-012")golden = dict(golden_result(con, question))# The legacy mart's revenue is gross, tax included, and it keeps test and cancelled orders.legacy = dict(con.execute("""SELECT sales_region, sum(revenue) FROM analytics.orders_legacy                             WHERE year(order_date) = 2025 GROUP BY sales_region""").fetchall())# Test orders are flagged only in raw, which the marts leave out; the legacy mart keeps them.(test_orders,), = con.execute("""SELECT count(*) FROM analytics.orders_legacy AS l JOIN raw.orders AS o ON o.order_id = l.id                                 WHERE o.is_test AND year(l.order_date) = 2025""").fetchall()for region in sorted(golden):    print(f"{region} golden: {golden[region]:,.2f}")    print(f"{region} orders_legacy: {legacy[region]:,.2f}")print(f"legacy total over golden: {sum(legacy.values()) / sum(golden.values()) - 1:+.1%}")print(f"test orders in the legacy rows: {test_orders}")# The model's schema-only queries for the first 16 golden questions: did any read the legacy mart?model, schema = RecordedModel("schema-only-sql"), describe_schema(con)# IDs are zero-padded, so comparing them as text selects gq-001 to gq-016.asked = [q for q in load_golden() if q.question_id <= "gq-016"]sqls = [extract_sql(model.complete(schema_only_messages(schema, q.question))) for q in asked]# A query counts as reading the legacy mart when its text names orders_legacy.print(f"model queries that read orders_legacy: {sum('orders_legacy' in sql for sql in sqls)} of {len(sqls)}")print(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.py
AMER 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 列と2つの売上列をすべて持っており、モデルは毎回そちらを選んだ。これはこのモデル、このスキーマ、これらの質問についての結果であり、保証ではない。リスクはウェアハウスの規模とともに高まる。Spider 2.0-lite のデータベースは平均で755列を超えるが、学術的なベンチマークである BIRD では約54列である(Lei et al., ICLR 2025)。数百のテーブルの中では、レガシーテーブルの分かりやすい名前が質問の語と一致する頻度が高くなると考えられる。検索で取得した例や説明文をエージェントに与える場合も、リスクは高まると考えられる。名前に対する検索では、レガシーテーブルが上位に順位づけられる可能性があるからである。

この理由から、本書では全体を通じて orders_legacy をウェアハウスに残しておく。削除できないテーブルは、エージェントが見つけられるテーブルである。防御はモデルより下の層に置くべきであり、それは読んでよいテーブルの一覧である。

実践ルール: エージェントが読んでよいテーブルを一覧にし、レガシーテーブルやステージングテーブルは、最も分かりやすい名前を持っていても(最上級ではなく)たとえ分かりやすい名前を持っていても一覧に含めない。モデルが書いたクエリは、実行前に、そのクエリが使うテーブルをすべて一覧と照合する。テストでエージェントが紛らわしいテーブルを一度も選ばなかった場合でも、この照合は続ける。新しい質問、より大きなスキーマ、取得された例によって、その結果が変わりうるからである。

この部分はまだ翻訳されていないため、英語で表示している。

Filters nobody wrote down

最終変更:版2026.11.12

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.

cancelled-lines-counted.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 合格
"""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."""from lib.golden import golden_result, load_goldenfrom 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-015")sql = extract_sql(RecordedModel("schema-only-sql").complete(schema_only_messages(describe_schema(con), question.question)))print(sql)# The model's query returns (category, units) rows, which dict turns into a lookup by category.model_units = dict(run_sql(con, sql).rows)golden_units = dict(golden_result(con, question))# The status is on the order, so the lines join to fct_orders to read it.(cancelled,), = con.execute("""SELECT sum(l.quantity) FROM analytics.fct_order_lines AS l    JOIN analytics.fct_orders AS o USING (order_id)    WHERE o.status = 'cancelled' AND o.placed_at >= '2025-01-01' AND o.placed_at < '2026-01-01'""").fetchall()# raw keeps test orders, which the marts drop; the is_test flag is only in raw.(test_units,), = con.execute("""SELECT sum(l.quantity) FROM raw.order_lines AS l JOIN raw.orders AS o USING (order_id)    WHERE o.is_test AND year(o.placed_at) = 2025""").fetchall()print(f"Cooking model: {model_units['Cooking']}")print(f"Cooking golden: {golden_units['Cooking']}")print(f"all categories model: {sum(model_units.values())}")print(f"all categories golden: {sum(golden_units.values())}")print(f"units on cancelled orders, in the model's dates: {cancelled}")print(f"overstated by: {sum(model_units.values()) / sum(golden_units.values()) - 1:.1%}")print(f"units on test orders, which the marts already leave out: {test_units}")# 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.(edge_net,), = con.execute("""SELECT sum(l.quantity) FILTER (WHERE year(o.placed_at) = 2025 AND year(o.order_date_local) <> 2025)                                   - sum(l.quantity) FILTER (WHERE year(o.placed_at) <> 2025 AND year(o.order_date_local) = 2025)    FROM analytics.fct_order_lines AS l JOIN analytics.fct_orders AS o USING (order_id)    WHERE o.status <> 'cancelled'""").fetchall()print(f"net units on orders across the year's edges: {edge_net}")
出力python cancelled-lines-counted.py
SELECT
    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

最終変更:版2026.11.12

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.

classify-schema-only-errors.py Python 3.12.15 · duckdb 2.0.0 · pyyaml 6.0.3 · sqlglot 30.31.0 合格
"""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."""from collections import Counterimport duckdbfrom lib.failure_kinds import KINDS, failure_kindsfrom lib.golden import golden_result, grade, load_goldenfrom lib.model import RecordedModelfrom lib.prompts import schema_only_messagesfrom lib.tools import extract_sql, run_sqlfrom lib.warehouse import connect, describe_schemacon = connect()schema, model = describe_schema(con), RecordedModel("schema-only-sql")# Kind "answer" leaves out gq-005, the forecast to decline; IDs are zero-padded, so gq-001 to gq-016 is a text range.questions = [q for q in load_golden() if q.kind == "answer" and q.question_id <= "gq-016"]outcomes, kinds_in_wrong, errors = Counter(), Counter(), {}print("question  outcome  kinds")for q in questions:    sql = extract_sql(model.complete(schema_only_messages(schema, q.question)))    try:        outcome = grade(q, run_sql(con, sql), golden_result(con, q))    except duckdb.Error as e:        # A query the database refuses to run is an error, not a wrong number.        outcome, errors[q.question_id] = "error", str(e).splitlines()[0]    # 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.    kinds = failure_kinds(sql, q.golden_sql)    outcomes[outcome] += 1    if outcome == "wrong":        kinds_in_wrong.update(kinds)    print(f"{q.question_id}    {outcome:<7}  {', '.join(kinds) or '-'}")for question_id, message in errors.items():    print(f"{question_id} error: {message}")print(f"questions graded: {len(questions)}")for outcome in ("correct", "wrong", "error"):    print(f"{outcome}: {outcomes[outcome]} of {len(questions)}")# DuckDB's messages: one for a column the table read does not have, one for a type mismatch such as LIKE on a timestamp.print(f"errors with a missing column: {sum('Referenced column' in m for m in errors.values())}")print(f"errors with a type mismatch: {sum('No function matches' in m for m in errors.values())}")for kind in KINDS:    print(f"wrong answers with {kind}: {kinds_in_wrong[kind]}")# gq-016 counts distinct customers: do those with an order across 2025's edges have other 2025 orders by both clocks?(edge_customers, covered), = con.execute("""SELECT count(DISTINCT customer_id), count(DISTINCT customer_id) FILTER (WHERE customer_id IN    (SELECT customer_id FROM analytics.fct_orders WHERE year(placed_at) = 2025 AND year(order_date_local) = 2025))    FROM analytics.fct_orders WHERE (year(placed_at) = 2025) <> (year(order_date_local) = 2025)""").fetchall()print(f"gq-016 customers with an order across 2025's edges: {edge_customers}")print(f"gq-016 of them, with other 2025 orders by both clocks: {covered}")
出力python classify-schema-only-errors.py
question  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.

本番で起こる問題

最終変更:版2026.11.12

本章の失敗は、それを見つけるために書かれた質問から生じたものである。本番では、罠のありかを知らない人が質問を書く。

二つの誤りが打ち消し合い、数値が正しそうに見える。 テントの質問におけるファンアウトと名前の照合は逆方向に作用していた。二つの質問のUTC での件数が一致したのも、誤りが打ち消し合ったか、誤りが表に出なかったためである。既知の数値との抜き取り確認は、これらを通過させてしまう。気づくことができるのは、すべての結果を一つの規則で正解結果と比較し、失敗した回答だけでなく合格した回答の SQL も読んだときだけである。

定義が変わっても、古い定義が答え続ける。 経理部門が売上の範囲を変更すると、列から売上を計算しているすべてのクエリは古い意味のままになる。売上に関する正解質問は、正解 SQL を更新していれば失敗し始め、更新していなければ合格し続ける。正解 SQL は、それが依存する定義の隣に置き、どちらかへの変更は他方と合わせてレビューする。

レガシーテーブルは後になって現れる。 本章の実行では、モデルは orders_legacy を一度も選ばなかった。スキーマが大きくなる、取得した例を使う、あるいはレガシーテーブルの語彙で質問が書かれると、コードを何も変えなくてもこの状況は変わりうる。各クエリのテーブルを許可テーブルの一覧と照合し、どのテーブルがどの質問に答えたかを記録しておけば、変化は件数の違いとして現れる。

わずかな時刻の誤りで、レポートが食い違う。 経理のレポートと注文が 1 件だけ違う件数は、突合作業の発端になる。その原因は、業務が現地時間の日を使うのに UTC の日付を使っていることである。同じ期間の二つのレポートが食い違ってはじめて気づくので、発見は遅い。二つの時計が異なる期間の境界に正解質問を加え、それをセットに残しておく。

フィルタが一か所では適用され、別の場所では適用されない。 テスト注文はマート層で除外されているので、生テーブルに対するクエリはそれらを復活させる。raw にアクセスできるエージェントは、マートにない列を求めてそれを読むことができる。生スキーマをエージェントの手の届かない場所に置くか、すべてのテーブルの説明にフィルタを含めて与える。

失敗のラベル付けは新しい種類を見逃す。 lib/failure_kinds.py が見つけるのは本章で扱う五つの種類だけであり、テントの名前照合が示したように、それ以外は見つけられない。種類別の失敗件数のダッシュボードは完全に見えるが、その裏でラベルのない新しい種類が増えていく。誤答の標本を定期的に手作業で読み、新しい種類が二度現れたら規則を追加する。

実践ルール: エージェントの数値が誰かの目に触れる前に、すべての回答を正解結果と照合して採点する。期間の境界と、すべての定義について正解質問を維持する。エージェントが読めるテーブルを一覧にし、誤答の標本を手作業で読む。誤った数値を種類別に数え、いずれかの種類で件数が増えていれば警報として扱う。

本章で下した判断

  • もっともらしい誤った数値の5つの類型: ファンアウト、スキーマが保持していない定義、タイムゾーンと会計カレンダー、紛らわしいテーブル、書かれていないフィルタである。いずれも、ウェアハウスまたはエージェントに与える情報の側に修正点がある。
  • 各失敗は、説明するのではなく実行する。 モデルが起こした失敗は、すべて実際のクエリで実行する。二つの誤りが重なる場合は、例の中で選択を一つずつ変えて、それぞれの影響を測定する。
  • 11のゴールデン質問をセットに加えた。 gq-006からgq-016までであり、それぞれがウェアハウスの罠のいずれかに触れるように書かれている。ゴールデンSQLは財務チームの定義に従っている。
  • 失敗の種類には、読んで確認できるルールでラベルを付ける。 ルールはlib/failure_kinds.pyにあり、各クエリをゴールデンSQLと比較して判定する。どのルールにも該当しない誤答は、手作業で読んで判断する。
  • エラーと誤った数値は分けて数える。 実行に失敗するクエリは、コストの小さな失敗である。もっともらしい数値を返すクエリこそ、最初に修正すべきものである。
  • レガシーテーブルはウェアハウスに残す。 本章ではモデルが一度も選ばなかったが、削除できないテーブルは、エージェントが見つけられるテーブルだからである。
  • ほとんどの種類で、修正の手段はプロンプトではなく定義である。 プロンプトで伝えられるのは一般的な作法までである。ビジネス上の定義は、エージェントが読み、ウェアハウスが強制する場所に置くべきである。

この版における本章

第2026.11.15版2026年11月19日時点で例が通過この章の6 例件のうち6件が合格

英語版の書籍(版2026.11.15)から翻訳した。

この節を引用する

形式

いずれの形式も、本訳書が従う英語版の書籍を出典として示す。

いずれの形式もこの版を明示している。そのリンクは現行版を開き、節の見出しには本文が最後に変更された日付が示される。アクセス日と取得日は、これを開いた日である。

このページの内容

全11章中の第2章: raw text-to-SQL が誤った数値を返す場面

  1. Joins that fan out and inflate a sum
  2. スキーマに含まれない定義
  3. Time zones and the fiscal calendar
  4. 紛らわしい名前を持つテーブル
  5. Filters nobody wrote down
  6. Count the failures by kind
  7. 本番で起こる問題
  8. 本章で下した判断

ページの先頭に戻るすべての章