Give the agent layered context
읽는 데 약 67분분 걸린다, 9절에 9개 예제이(가) 있음.
AI로 번역함. 영어 원문을 사람이 검증함. 우리가 책을 만드는 방식
영어판 2026.11.15판을 번역한 것이다. 원문 읽기
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
By the end of this chapter, you can give a model that writes SQL more than the schema, in layers: notes on columns whose names mislead, a glossary of the shop's business terms, and verified queries retrieved for each question. You can fit those layers to a token budget, measure what each one adds on the dev split of the golden questions, and check the best set once on the held-out test split.
A schema can't say what its columns mean. Chapter 1 gave a model only the shop's schema and asked for net revenue in each region in March 2026. The query it wrote ran, looked right and counted cancelled orders. The rule it missed exists, in the finance team's definition of net revenue, but nothing the model was given contains it. Layered context is the most direct fix for what a schema can't say: write the rules down where the model reads them. It isn't the fix this book ends with, since chapters 5 and 6 move the definitions into a semantic layer the model can't misread. But any agent that writes SQL needs context, including the guarded one in chapter 7, and the habit this chapter teaches, adding one layer at a time and measuring each, applies to every change that follows.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
What the schema says, and what it doesn't
A schema tells a model which tables exist, what their columns are called and what type each column holds. That is a lot, and it never goes out of date, because the database produces it. What it can't tell the model is what the columns mean: which rows a table holds, which rows count toward a business number, which time zone a timestamp is in, and which of two similar tables is the one people trust. Those meanings decide whether a query returns the right number, and they live in the SQL that built the marts and in the heads of the people who own them.
The book's measure of this is the golden questions, which chapter 3 built: questions with a verified answer, scored on the dev split while context and prompts are tuned, and once on the held-out test split. Every layer in this chapter is added to the schema, so the schema is the first layer, and the place to start is what it leaves open. build_context() in lib/context.py, the shared module that builds every layer in this chapter, returns the schema layer when it's asked for nothing else.
1"""What the schema tells a model about revenue, and three March 2026 totals that all fit it. The three printed totals2carry the paragraph's point: they differ, and only the one that leaves out cancelled orders follows the finance definition."""3from lib.context import build_context4from lib.warehouse import connect56con = connect()7schema = build_context(con, "", layers=("schema",)) # no question, so only the schema layer is built8print(f"tables in the schema: {schema.text.count('CREATE TABLE')}")9print(f"schema tokens (estimated): {schema.tokens}")10for line in schema.text.splitlines():11 if "analytics.fct_orders " in line or "analytics.orders_legacy " in line:12 print(line)1314march = "BETWEEN DATE '2026-03-01' AND DATE '2026-03-31'" # the window of the question, on the business day15candidates = {16 "orders_legacy.revenue": f"SELECT sum(revenue) FROM analytics.orders_legacy WHERE order_date {march}",17 "fct_orders.net_revenue_eur, every order":18 f"SELECT sum(net_revenue_eur) FROM analytics.fct_orders WHERE order_date_local {march}",19 "fct_orders.net_revenue_eur, not cancelled":20 f"SELECT sum(net_revenue_eur) FROM analytics.fct_orders WHERE order_date_local {march} "21 "AND status <> 'cancelled'", # net revenue leaves out cancelled orders, as the status note says22}23for name, sql in candidates.items():24 (total,), = con.execute(sql).fetchall()25 print(f"{name}: {total:,.2f}")
python schema-digest.pytables in the schema: 16
schema tokens (estimated): 912
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);
CREATE TABLE analytics.orders_legacy (id INTEGER, cust_id INTEGER, order_date DATE, order_status VARCHAR, sales_region VARCHAR, revenue DECIMAL(12,2));
orders_legacy.revenue: 186,635.93
fct_orders.net_revenue_eur, every order: 162,542.79
fct_orders.net_revenue_eur, not cancelled: 150,082.83
The schema layer is the whole warehouse: all 16 tables, about 912 tokens. Read the two printed lines as a model would. analytics.fct_orders has a net_revenue_eur column and a status column, and analytics.orders_legacy has a revenue column and an order_date.
Each of the three totals the example computes for March 2026 is a reasonable reading of those two lines, and they disagree. The legacy table's revenue gives 186,635.93 euros, and the mart's net_revenue_eur over every order gives 162,542.79, which is what chapter 1's model computed. Only the third, 150,082.83, follows the finance team's definition, and nothing in the schema points to it rather than the other two.
The whole of the shop's warehouse fits in those 912 tokens, so there's no case for trimming it: a study of strong models on the BIRD benchmark found that passing the whole schema when it fits can beat selecting tables first, because the selection step can drop a column the query needs (Maamari et al., 2024). A warehouse with thousands of tables is a different problem, which this chapter doesn't solve. The shop's problem isn't which tables to show. It's that what the tables show doesn't say which number is right.
Working rule: Keep the whole schema in the context when it fits, and add meaning to it rather than taking tables away. Before you write any other layer, take one real question and list every reading of the schema that answers it. Each reading the schema doesn't rule out is a rule some other layer has to state.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
Describe columns where names mislead
The schema leaves open which of several readings is right, and the cheapest place to close some of them is the column itself. A column note is a sentence about one table or column that says what its name doesn't: which rows the table holds, the unit and sign of an amount, the time zone of a timestamp, the values a column takes, or that a table is deprecated. A note isn't documentation of every column. A column called product_name needs none, and a note that repeats the name costs tokens and says nothing. The notes worth writing are on the columns a careful analyst new to the warehouse would misread.
Published evidence points the same way. A study that generated column descriptions with a language model found that adding them to the prompt improved text-to-SQL accuracy and, in an ablation that replaced column names with uninformative ones, that the descriptions helped most there (Wretblad et al., 2024); it is a single preprint on the BIRD benchmark. Databricks' guidance for its Genie Agents says that "quality table and column descriptions in Unity Catalog are critical for Genie accuracy" (Databricks), which is the vendor's advice, not a measurement.
The shop's notes live in examples/data/shop/columns.toml, one entry per table with an optional note per column, and load_column_notes() in lib/context.py turns each into one line, such as analytics.fct_orders.placed_at: When the order was placed, in UTC. The notes cover the traps chapter 2 found: orders_legacy is deprecated, net_revenue_eur still holds a value for cancelled orders, placed_at is UTC while order_date_local is the business day, discount is negative in raw.order_lines and positive in the marts, and the fiscal year starts on February 1.
The recording context-layers shows what the notes do once the examples replay it. The recording holds the pinned model's SQL for every dev question of kind answer, written once with each layer set this chapter measures, using the prompt LAYERED_SQL in lib/prompts.py. The recording was made by recordings/record_context_layers.py, with the same model, sampling and seed as chapter 1's, and the examples replay it by the hash of the exact messages.
1"""Add the column notes on columns whose names mislead, and count the dev questions they fix and break under the recorded2model. The `fixed` and `broken` lines settle the net effect of the notes, and the broken question shows how a note misleads."""3from lib.context import LAYER_SETS, build_context, grade_with_context, load_column_notes4from lib.golden import load_golden5from lib.model import RecordedModel6from lib.warehouse import connect78con = connect()9notes = load_column_notes()10print(f"column notes: {len(notes)}")11print(f"column notes tokens (estimated): {build_context(con, '', layers=('columns',)).tokens}")12for note in notes:13 if note.startswith(("analytics.fct_orders.status", "analytics.fct_orders.placed_at")):14 print(note)1516model = RecordedModel("context-layers")17before, after = LAYER_SETS[0], LAYER_SETS[1] # the sets are cumulative: [0] is the schema alone, [1] adds the notes18dev = [q for q in load_golden() if q.split == "dev" and q.kind == "answer"]19fixed, broken = [], []20for q in dev:21 was, now = grade_with_context(con, model, q, before), grade_with_context(con, model, q, after)22 if was != "correct" and now == "correct": # fixed: wrong or error without the notes, correct with them23 fixed.append(q.question_id)24 if was == "correct" and now != "correct":25 broken.append(q.question_id)26print(f"fixed by column notes: {len(fixed)} ({', '.join(fixed)})")27print(f"broken by column notes: {len(broken)} ({', '.join(broken)})")
python column-descriptions.pycolumn notes: 33
column notes tokens (estimated): 1087
analytics.fct_orders.status: One of placed, shipped, delivered, cancelled, returned. Revenue leaves out cancelled orders; order and customer counts include them. Returned orders count everywhere.
analytics.fct_orders.placed_at: When the order was placed, in UTC. Don't filter or group dates on it; use order_date_local.
fixed by column notes: 7 (gq-001, gq-002, gq-003, gq-004, gq-008, gq-009, gq-011)
broken by column notes: 1 (gq-018)
The 33 notes add about 1087 tokens, more than the schema itself. On the dev questions they fix 7 (gq-001, gq-002, gq-003, gq-004, gq-008, gq-009, gq-011), among them gq-002, the March question from chapter 1, whose query now filters out cancelled orders. They also break 1 (gq-018). That question asks for the refunds on orders placed in March 2026, and with the notes the model filtered on both the order's date and refund_month, so it dropped refunds paid in a later month. The note on refund_month was accurate. The model used it on a question that didn't call for it, which is what a note can't prevent.
Working rule: Write notes only for tables and columns whose names mislead: deprecated tables, amounts with a sign or unit you wouldn't guess, timestamps in a time zone other than the business's, and columns whose values include a placeholder such as (none). Write them as facts about the column, not as instructions for one question. Expect a note to fix many questions and occasionally mislead on one, and keep the questions it broke in the golden set so you see it happen.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
Add the business glossary
Column notes describe the tables. Many questions turn on something else: what a business word means at this company. "Revenue" could be gross or net. "Orders" could include cancelled ones or not. Refunds "in March" could mean refunds paid in March or refunds on March's orders. These are choices, made by the teams that own the numbers, and no table states them. A business glossary lists the business words whose meaning a company's teams chose, and writes each meaning in terms of the warehouse's tables and columns.
Evidence for this layer comes from BIRD, a text-to-SQL benchmark whose questions come with "external knowledge evidence": hints that link a question's words to the database, such as what a term means as a formula over columns. In the benchmark's paper, adding the evidence raised GPT-4's execution accuracy on the dev set from 30.90% to 46.35%, with 2023 models and zero-shot prompts (Li et al., 2023). The hints are written per question, though, which is closer to giving the model the answer's ingredients than to giving it a glossary written once.
The shop's glossary is examples/data/shop/glossary.toml. Its entries define revenue as net revenue unless the question says gross, orders as every order including cancelled ones, average order value, fiscal periods, new customers, refunds by the month they were paid, units sold, and the two meanings of "channel". load_glossary() turns each into one line.
The glossary wasn't written blind, and the book's history shows how it changed. In the same change that recorded the model's first dev replies, with the schema alone, the entry for orders was corrected: it had said that order counts leave out cancelled orders, which disagreed with the finance team's golden SQL. The entries for units sold and for customers who ordered were added in that change too, and the column note on status was reworded to match. Later, after the test questions had been recorded with the schema alone, the refund-rate entry was changed to the book-wide definition that chapter 5 uses and that test question gq-028 also follows: refunded amount divided by gross revenue, because a refund pays back tax and only gross revenue includes it. Each change made the glossary agree with golden SQL that it was then scored against. That is how a dev score gets fitted to dev, and the check on test comes back to what it costs.
1"""Add the business glossary on top of the column notes, and count what it changes on the dev questions. The gq-0022lines show the query the prose describes: with the glossary, the March query no longer filters on status."""3from lib.context import LAYER_SETS, build_context, grade_with_context, load_glossary4from lib.golden import load_golden5from lib.model import RecordedModel6from lib.prompts import layered_sql_messages7from lib.warehouse import connect89con = connect()10terms = load_glossary()11print(f"glossary terms: {len(terms)}")12print(f"glossary tokens (estimated): {build_context(con, '', layers=('glossary',)).tokens}")13print(next(t for t in terms if t.startswith("revenue:")))1415model = RecordedModel("context-layers")16before, after = LAYER_SETS[1], LAYER_SETS[2] # schema and column notes; then the glossary too17dev = [q for q in load_golden() if q.split == "dev" and q.kind == "answer"]18changes = {(was, now): [] for was in ("correct", "other") for now in ("correct", "other")} # "other" is wrong or error19for q in dev:20 was, now = (grade_with_context(con, model, q, layers) for layers in (before, after))21 changes["correct" if was == "correct" else "other", "correct" if now == "correct" else "other"].append(q.question_id)22print(f"fixed by the glossary: {len(changes['other', 'correct'])} ({', '.join(changes['other', 'correct'])})")23print(f"broken by the glossary: {len(changes['correct', 'other'])} ({', '.join(changes['correct', 'other'])})")2425q = next(q for q in dev if q.question_id == "gq-002")26reply = model.complete(layered_sql_messages(build_context(con, q.question, after).text, q.question))27filters_status = "status" in reply # a query that filters on status has to name the column28assert not filters_status # the chapter states it: with the glossary, gq-002 no longer filters on status29print(f"gq-002 with the glossary filters status: {'yes' if filters_status else 'no'}")
python glossary-context.pyglossary terms: 13
glossary tokens (estimated): 609
revenue: Net revenue unless the question says gross: sum of analytics.fct_orders.net_revenue_eur over orders whose status isn't 'cancelled'. In EUR.
fixed by the glossary: 3 (gq-012, gq-015, gq-018)
broken by the glossary: 3 (gq-002, gq-009, gq-016)
gq-002 with the glossary filters status: no
The glossary's gains and losses cancel out: it fixes 3 (gq-012, gq-015, gq-018) dev questions and breaks 3 (gq-002, gq-009, gq-016). Its 13 terms add about 609 tokens on top of the schema and the column notes. The broken ones are the instructive part. For gq-002, the same March question, the query written with the glossary no longer filters on status, although the revenue entry and the note on status both say to leave cancelled orders out. For gq-016, the model left cancelled orders out of a customer count, applying the revenue rule where the glossary says it doesn't apply. For gq-009, the average order value, it divided the average order's net revenue by the number of orders again, although the glossary defines the measure as net revenue divided by the number of orders.
Each configuration was recorded once, with one sampled reply per question, so a change of one or two questions out of 17 is within what a second sample could undo. Chapter 10 measures that spread with repeated runs. What this result supports is narrower: the glossary didn't make this model better on these questions, and a rule stated twice was still missed. A plausible reason, which we haven't measured here, is that a longer context makes a small model use each part of it less reliably. Studies of long contexts find that models use information in the middle of a prompt worse than at its edges (Liu et al., 2024), and that accuracy falls with length when the question and the passage don't share words (Modarressi et al., 2025).
Working rule: Write a glossary entry for each business word whose meaning is a choice someone made, and state it in terms of tables and columns. Prefer one place for each rule, a column note or a glossary entry; the shop's files state the cancelled-orders rule in both, and the miss on gq-002 shows that saying it twice doesn't make it stick. Measure the glossary on your dev questions before you trust it, and keep it only if it helps there or the next layer depends on it.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
Retrieve verified queries as examples
Column notes and a glossary state rules. A verified query shows the shop's rules applied: a question someone asked, with SQL a person checked against the shop's definitions. Given a few such pairs that resemble the question, a model can copy their shape, such as the join to dim_date for a fiscal period or the filter on status, instead of inventing one. A systematic comparison of prompt designs for text-to-SQL found that the best setup chose examples by the similarity of their questions, after masking domain-specific words, and by the similarity of their SQL to a draft query written first, and showed each as a question and its SQL alone (Gao et al., 2023). Warehouse vendors build the same idea into their assistants. Snowflake's Cortex Analyst keeps a verified query repository of "questions and corresponding SQL queries to answer them" (Snowflake), and Databricks says a Genie Agent can use an example SQL query directly or learn from it, and treats parameterized ones as trusted assets (Databricks). Both are the vendors' own descriptions, not measurements.
The shop's verified queries live in examples/data/shop/verified_queries.toml. Their fields follow Snowflake's field names, name, question, sql, verified_at and verified_by, plus the role the query was checked for. Two differ in content. Snowflake's verified_at is optional and counted in seconds since 1970, and its verified_by names the person who checked the query; the shop's files give a date and name a team, never a person. None of the verified queries is a golden question. They are near neighbours of questions in both splits, such as net revenue by fiscal quarter for fiscal 2025 or average order value by region for 2024, which is how a real repository grows: from questions people asked before. A verified query that was itself a golden question would make the score measure copying.
Retrieval in lib/context.py is lexical, with no embeddings. question_terms() lowercases the question, drops common words and masks three kinds of word: numbers, month names and the shop's three region codes. Every other word stays as it is. QueryIndex scores each verified question against the masked question with TF-IDF and cosine similarity, and search() keeps at most three matches that score at least 0.4. This is a cheap stand-in for the selection in Gao et al., not a reproduction of it: it masks far fewer words, and it drops the second step, which compares draft SQL.
1"""Retrieve the verified queries most like a question, by TF-IDF over the masked question's words. The matches kept at the20.4 default show that a threshold can leave a question with no example, and the masked phrases show what masking does."""3from lib.context import QueryIndex, load_verified_queries, question_terms45queries = load_verified_queries()6index = QueryIndex(queries)7print(f"verified queries: {len(queries)}")89for label, question in (("aov", "What was the average order value in each region in March 2026?"),10 ("parcels", "Which warehouse ships the most parcels?")):11 print(f"question: {question}")12 print(f"masked terms: {' '.join(question_terms(question))}")13 for score, query in index.search(question, k=3, min_score=0.0): # no threshold here, so the scores show the gap14 print(f" {score:.3f} {query.name}")15 print(f"{label} matches kept: {len(index.search(question))}") # the default threshold, 0.4, is the one the book uses1617# Months and numbers are masked; "calendar" and "year" are not common words, and "Q3" is not masked at all.18for phrase in ("March 2026", "January 2025", "calendar year 2024", "EMEA in Q3"):19 print(f"masked \"{phrase}\": {' '.join(question_terms(phrase))}")
python retrieve-similar-queries.pyverified queries: 16
question: What was the average order value in each region in March 2026?
masked terms: average order value region <month> <number>
0.835 average_order_value_by_region
0.422 gross_revenue_by_region_month
0.191 orders_of_one_customer
aov matches kept: 2
question: Which warehouse ships the most parcels?
masked terms: warehouse ships most parcels
0.375 units_sold_by_product
0.357 net_revenue_by_category
0.000 average_order_value_by_region
parcels matches kept: 0
masked "March 2026": <month> <number>
masked "January 2025": <month> <number>
masked "calendar year 2024": calendar year <number>
masked "EMEA in Q3": <region> q3
For the average order value in each region in March 2026, the closest of the 16 verified queries is the same measure by region for 2024, and 2 matches pass the threshold. The second question, about which warehouse ships the most parcels, is one the shop's data can't answer at all. Its best scores come from the single shared word "most", and none passes the threshold, so 0 queries are offered. Without the threshold, the model would get three confident-looking examples, two of them about products, for a question about shipping.
The last lines of the output show what masking does and doesn't do. "March 2026" and "January 2025" both become <month> <number>, so a question about one month matches a verified query about another. "Calendar year 2024" becomes calendar year <number>, because "calendar" and "year" are neither common words nor values, and a quarter written as "Q3" isn't masked at all. That is why the March question's best match, a question about calendar year 2024, scores less than a perfect 1.
Working rule: Retrieve a few verified queries per question, chosen by similarity of the masked question, and show each as its question and SQL. Set a minimum score and check it on questions the warehouse can't answer, because lexical retrieval always returns something. Never let a golden question into the verified set. Move to embeddings only when your own measurement shows lexical retrieval missing queries that a person would call similar.
컨텍스트를 예산에 맞추기
스키마, 열 설명, 용어집, 그리고 검색된 검증 쿼리를 더하면 가게의 컨텍스트는 이미 스키마만 있을 때보다 토큰을 몇 배 더 차지하며, 층을 하나 추가할 때마다 더 길어진다. 토큰 예산은 에이전트가 질문 하나에 대해 보낼 수 있는 컨텍스트의 최대량이다. 예산이 필요한 이유는 세 가지다. 첫째, 모델의 윈도는 유한하다. 이 책의 녹화용 런타임은 모델에 8,192 토큰을 주며, 그중 최대 384 토큰은 응답용으로 남겨 둔다. 둘째, 프롬프트 토큰은 하나하나가 CPU에서는 시간을, 호스팅 모델에서는 비용을 들게 한다. 셋째, 긴 컨텍스트는 고르게 활용되지 않는다. 모델은 긴 프롬프트의 중간에 있는 정보를 처음이나 끝에 있는 정보보다 덜 활용한다(Liu et al., 2024).
컨텍스트를 예산에 맞춘다는 것은 무엇을 뺄지 정하는 일이며, 그 결정에는 우선순위가 필요하다. build_context()는 층을 우선순위 순서로 받아 항목을 하나씩 더하고, 더하면 예산을 넘는 항목은 버린다. 따라서 뒤에 나오는 더 짧은 항목은 여전히 들어갈 수 있다. 스키마는 결코 버리지 않는다. 첫 번째 절에서 설명한 이유대로, 들어갈 수 있는 스키마는 통째로 보내야 하기 때문이다. 우선순위와 상관없이 컨텍스트는 항상 고정된 순서로 렌더링한다. 안정적인 층을 먼저, 검색된 쿼리를 그다음에, 질문을 마지막에 둔다. 이렇게 하면 질문이 가장 비슷한 예제 옆에 놓이고, 모델 러너가 모든 질문이 공유하는 프롬프트 부분을 호출마다 다시 읽지 않고 재사용할 수 있다. 이 책의 녹화를 만든 러너인 llama-cpp-python 0.3.36은 Llama.generate에서 이를 수행한다. 새 프롬프트의 토큰을 직전 프롬프트의 토큰과 비교해, 둘이 공유하는 가장 긴 접두부 뒤의 토큰만 평가한다(llama-cpp-python 0.3.36, llama.py).
lib/context.py의 토큰 수는 추정값이다. 예제가 모델의 토크나이저를 불러오지 않으므로 estimate_tokens()는 문자 수를 3.5로 나눈다. 아래에서는 이 추정값을 프롬프트를 녹화할 때 모델의 토크나이저가 보고한 수와 비교하고, 평균 주문 금액 질문의 컨텍스트인 gq-009을 예시용 예산 2,400 토큰에 두 가지 우선순위로 맞춘다.
1"""Fit one question's layered context to a token budget, and check the token estimate against the model's count. The2estimate runs high, the safe side for a budget, and the dropped lines show what each priority gives up."""3from lib.context import LAYERS, build_context, estimate_tokens4from lib.golden import load_golden5from lib.model import RecordedModel6from lib.prompts import layered_sql_messages7from lib.warehouse import connect89con = connect()10question = next(q for q in load_golden() if q.question_id == "gq-009").question11full = build_context(con, question)12for layer, tokens in full.layer_tokens().items():13 print(f"{layer} tokens (estimated): {tokens}")1415messages = layered_sql_messages(full.text, question)16call = RecordedModel("context-layers").call_for(messages)17print(f"prompt tokens (estimated): {estimate_tokens(''.join(m['content'] for m in messages))}") # every message, joined18print(f"prompt tokens (model's tokenizer): {call['prompt_tokens']}")1920budget = 2400 # an illustrative budget, well inside the 8,192-token window the recordings use21for name, priority in (("default", LAYERS), ("examples first", ("schema", "columns", "verified", "glossary"))): # verified ranks above glossary22 fitted = build_context(con, question, layers=priority, budget=budget)23 kept = {layer: len(items) for layer, items in fitted.items.items()}24 print(f"{name} priority: {fitted.tokens} tokens, kept {kept}")25 print(f"{name} dropped: {len(fitted.dropped)}")26print(f"render order: {', '.join(layer for layer, items in fitted.items.items() if items)}, then the question")
python context-token-budget.pyschema tokens (estimated): 912
columns tokens (estimated): 1087
glossary tokens (estimated): 609
verified tokens (estimated): 200
prompt tokens (estimated): 2917
prompt tokens (model's tokenizer): 2364
default priority: 2393 tokens, kept {'schema': 1, 'columns': 33, 'glossary': 9, 'verified': 0}
default dropped: 6
examples first priority: 2384 tokens, kept {'schema': 1, 'columns': 33, 'glossary': 5, 'verified': 2}
examples first dropped: 8
render order: schema, columns, glossary, verified, then the question
전체 프롬프트의 추정값은 2917 토큰이고, 모델의 토크나이저가 센 값은 2364이다. 추정값이 높게 나오는 것은 예산에 안전한 방향이다. 추정값으로 들인 컨텍스트는 항상 들어맞기 때문이다. 층을 소개한 순서를 따르는 기본 우선순위에서는 예산이 모든 열 설명과 용어집의 일부를 남기고 검색된 쿼리 두 개를 모두 버리며, 버려진 항목은 모두 6개다. 검증 쿼리를 용어집보다 높게 두면 같은 예산에서 쿼리 두 개를 남기고 대신 용어집 항목 8개를 버린다. 어느 순서도 일반적으로 옳지는 않다. 다음 절에서는 가게의 질문에서 어느 층이 자기 토큰만큼의 값을 하는지 측정하며, 순서를 정해야 하는 근거는 그 측정이다.
작업 규칙: 토큰 예산은 모델의 윈도보다 한참 안쪽으로 잡고, 각 질문의 컨텍스트는 층을 쓴 순서가 아니라 측정한 우선순위에 따라 예산에 맞춘다. 스키마가 들어갈 수 있으면 버리지 않는다. 무엇을 버렸든 안정적인 층을 먼저, 질문을 마지막에 렌더링한다. 토큰 추정값은 모델의 실제 수와 한 번 대조하고, 높은 쪽에 둔다.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
Measure what each layer adds
Each layer so far was judged by the questions it fixed and broke. To choose which layers to keep, and in what order a budget should drop them, you need all of them measured the same way. An ablation measures every layer the same way: it scores a series of configurations that differ by one layer each, on the same questions, under the same comparison rule. The questions are the dev split of the golden questions, because choosing among configurations is tuning, and tuning on the test split would leave nothing held out to check the choice.
The shop's dev split has 24 golden questions. This chapter scores the 17 of kind answer. The other 7 should be declined, because the warehouse can't answer them or the role asking may not see the answer, and a prompt that only writes SQL can't decline, so they would score as wrong under every layer set; chapters 8 and 9 score them. Each layer set adds one layer to the one before: the schema alone, then column notes, then the glossary, then up to three retrieved verified queries. The outcomes come from grade() in lib/golden.py, as in every chapter: correct when the result matches the golden result, wrong when it runs and doesn't, error when it doesn't run. The first layer set, the schema alone, isn't chapter 3's baseline either. That baseline used the schema-only prompt and scored every question in each split, while this run uses the layered prompt LAYERED_SQL with no other layer filled in and scores the answer questions only, so its numbers differ.
1"""Score each layer set on the dev split's answer questions, replaying the recorded model's SQL for each, and print the2totals, the average token counts and the outcome grid. The correct totals and the fixed and broken lines are the numbers the prose rests on."""3from collections import Counter45from lib.context import LAYER_SETS, build_context, grade_with_context, layer_set_name6from lib.golden import load_golden7from lib.model import RecordedModel8from lib.warehouse import connect910con = connect()11model = RecordedModel("context-layers")12dev_split = [q for q in load_golden() if q.split == "dev"]13dev = [q for q in dev_split if q.kind == "answer"] # the other questions are declined, and are scored in chapters 8 and 914print(f"dev split questions: {len(dev_split)}")15print(f"dev questions: {len(dev)}")16print(f"dev questions to decline: {len(dev_split) - len(dev)}")1718grid = {}19for layers in LAYER_SETS:20 name = layer_set_name(layers)21 grid[name] = [grade_with_context(con, model, q, layers) for q in dev]22 counts = Counter(grid[name])23 tokens = sum(build_context(con, q.question, layers).tokens for q in dev) // len(dev) # mean over the dev questions, whole tokens24 print(f"{name}: correct {counts['correct']}, wrong {counts['wrong']}, error {counts['error']}")25 print(f"{name} correct: {counts['correct']}")26 print(f"{name} error: {counts['error']}")27 print(f"{name} tokens: {tokens}")2829print("question " + " ".join(f"{i + 1}" for i in range(len(LAYER_SETS))))30for i, q in enumerate(dev):31 print(f"{q.question_id} " + " ".join(grid[name][i][0] for name in grid))3233# What the verified queries changed, against the run with the glossary.34before, after = grid[layer_set_name(LAYER_SETS[2])], grid[layer_set_name(LAYER_SETS[3])] # the glossary run, then the glossary plus verified queries35fixed = [q.question_id for q, was, now in zip(dev, before, after) if was != "correct" and now == "correct"]36broken = [q.question_id for q, was, now in zip(dev, before, after) if was == "correct" and now != "correct"]37print(f"fixed by verified queries: {len(fixed)}")38print(f"fixed by verified queries, IDs: {', '.join(fixed)}")39print(f"broken by verified queries: {len(broken)}")
python layer-ablation-dev.pydev split questions: 24
dev questions: 17
dev questions to decline: 7
schema: correct 3, wrong 9, error 5
schema correct: 3
schema error: 5
schema tokens: 912
schema+columns: correct 9, wrong 7, error 1
schema+columns correct: 9
schema+columns error: 1
schema+columns tokens: 1999
schema+columns+glossary: correct 9, wrong 8, error 0
schema+columns+glossary correct: 9
schema+columns+glossary error: 0
schema+columns+glossary tokens: 2608
schema+columns+glossary+verified: correct 14, wrong 2, error 1
schema+columns+glossary+verified correct: 14
schema+columns+glossary+verified error: 1
schema+columns+glossary+verified tokens: 2775
question 1 2 3 4
gq-001 e c c c
gq-002 w c w c
gq-003 e c c c
gq-004 w c c c
gq-006 e e w w
gq-007 w w w w
gq-008 w c c c
gq-009 w c w c
gq-010 e w w c
gq-011 w c c c
gq-012 w w c c
gq-013 e w w c
gq-014 c c c c
gq-015 w w c c
gq-016 c c w c
gq-017 w w w e
gq-018 c w c c
fixed by verified queries: 5
fixed by verified queries, IDs: gq-002, gq-009, gq-010, gq-013, gq-016
broken by verified queries: 0
Column notes and verified queries raise the score; the glossary doesn't. The schema alone gets 3 of the dev questions right, column notes take it to 9, and the glossary leaves it at 9. Verified queries on top give 14, the best set on dev.
The grid shows where the change came from. Against the run with the glossary, retrieved examples fixed 5 questions and broke 0. Two of the fixes are new: the fiscal-quarter question gq-010 and the order count by region gq-013, whose nearest verified queries show exactly the join and the count they needed. The other three, gq-002, gq-009 and gq-016, are the questions the glossary run had lost, so there the examples won back what the glossary had cost.
The totals say how much each layer helped. The failures say what the next layer should be, so the next two examples read them, starting with the schema-only queries that don't run.
1"""Why the dev queries written with the schema alone don't run: the column each one names, and the analytics tables that have it.2The per-table counts are the evidence the prose rests on: every failure is a column on the wrong table."""3import re4from collections import Counter56import duckdb7from lib.context import LAYER_SETS, build_context8from lib.golden import load_golden9from lib.model import RecordedModel10from lib.prompts import layered_sql_messages11from lib.tools import extract_sql, run_sql12from lib.warehouse import connect1314con, model = connect(), RecordedModel("context-layers")15dev = [q for q in load_golden() if q.split == "dev" and q.kind == "answer"]16where = Counter()17for q in dev:18 context = build_context(con, q.question, LAYER_SETS[0]) # the schema alone19 sql = extract_sql(model.complete(layered_sql_messages(context.text, q.question)))20 try:21 run_sql(con, sql)22 continue23 except duckdb.Error as e:24 column = re.search(r'column (?:named )?"(\w+)"', str(e)).group(1) # DuckDB says either form; take the name25 (tables,), = con.execute(26 "SELECT string_agg(table_schema || '.' || table_name, ', ' ORDER BY 1) FROM information_schema.columns " # sorted, so a column in two tables lists them in a fixed order27 "WHERE table_schema = 'analytics' AND column_name = ?", [column]).fetchall() # the marts only: where the model should have looked28 where[tables] += 129 print(f"{q.question_id}: no column {column}; it is in {tables}")30print(f"schema-only queries that don't run: {where.total()}")31for tables, n in where.items():32 print(f"missing column only {tables} has: {n}")
python missing-columns-schema-only.pygq-001: no column fiscal_year; it is in analytics.dim_date
gq-003: no column calendar_year; it is in analytics.dim_date
gq-006: no column quantity; it is in analytics.fct_order_lines
gq-010: no column fiscal_quarter; it is in analytics.dim_date
gq-013: no column order_date; it is in analytics.orders_legacy
schema-only queries that don't run: 5
missing column only analytics.dim_date has: 3
missing column only analytics.fct_order_lines has: 1
missing column only analytics.orders_legacy has: 1
Every failure is a column the model put on the wrong table. Of the 5, 3 ask the orders mart for a fiscal or calendar column that only dim_date has, 1 asks it for quantity, which only the order lines have, and 1 uses the legacy table's order_date.
These are the mistakes column notes are for. With the notes, 1 query in the whole dev split fails to run.
A second script prints the query for each dev question that all four layers still don't get right.
1"""The dev questions that stay wrong with all four layers, each with its query, and a check of whether gq-007 fails only on2the spelling of 'tents'. The count line settles how many dev questions stay wrong, and the gq-007 line settles whether the spelling alone causes its failure."""3import duckdb4from lib.context import LAYERS, build_context, grade_with_context5from lib.golden import golden_result, grade, load_golden6from lib.model import RecordedModel7from lib.prompts import layered_sql_messages8from lib.tools import extract_sql, run_sql9from lib.warehouse import connect1011con, model = connect(), RecordedModel("context-layers")12dev = [q for q in load_golden() if q.split == "dev" and q.kind == "answer"]131415def sql_with_all_layers(q):16 return extract_sql(model.complete(layered_sql_messages(build_context(con, q.question, LAYERS).text, q.question)))171819unsolved = [(q, outcome) for q in dev if (outcome := grade_with_context(con, model, q, LAYERS)) != "correct"] # LAYERS is all four20print(f"not correct with all four layers: {len(unsolved)}")21for q, outcome in unsolved:22 sql = sql_with_all_layers(q)23 print(f"{q.question_id} ({outcome}): {q.question}")24 print("\n".join(" " + line for line in sql.strip().splitlines()))25 if outcome == "error":26 try:27 run_sql(con, sql)28 except duckdb.Error as e:29 print(f" -- {str(e).splitlines()[0]}")3031gq007 = next(q for q in dev if q.question_id == "gq-007")32respelled = sql_with_all_layers(gq007).replace("'tents'", "'Tents'") # change the spelling only, to see whether it is the whole failure33print(f"gq-007 with 'Tents' spelled as stored: {grade(gq007, run_sql(con, respelled), golden_result(con, gq007))}")
python what-stays-wrong.pynot correct with all four layers: 3
gq-006 (wrong): What were net revenue and the number of units sold in each region in 2025?
SELECT
o.region,
SUM(o.net_revenue_eur) AS net_revenue,
SUM(l.quantity) AS units_sold
FROM analytics.fct_orders AS o
JOIN analytics.fct_order_lines AS l USING (order_id)
WHERE o.order_date_local >= '2025-01-01' AND o.order_date_local < '2026-01-01'
AND o.status <> 'cancelled'
GROUP BY o.region
ORDER BY net_revenue DESC
gq-007 (wrong): How much net revenue came from orders that included a tent in 2025?
SELECT
SUM(fol.net_amount_eur) AS net_revenue
FROM
analytics.fct_order_lines fol
JOIN
analytics.fct_orders fo ON fol.order_id = fo.order_id
JOIN
analytics.dim_products dp ON fol.product_id = dp.product_id
JOIN
analytics.dim_date dd ON fo.order_date_local = dd.date_day
WHERE
dp.category = 'tents'
AND dd.calendar_year = 2025
AND fo.status != 'cancelled'
gq-017 (error): What was net revenue in each of our countries in 2025?
SELECT d.country, 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_year = 2025
GROUP BY d.country
ORDER BY net_revenue DESC
-- Binder Error: Table "d" does not have a column named "country"
gq-007 with 'Tents' spelled as stored: wrong
With all four layers, 3 questions are still not right. gq-006 asks for net revenue and units sold by region. The model joined the orders mart to its lines and summed net_revenue_eur over the joined rows, so an order's revenue counts once for every line it has. The glossary's entry for units sold says to sum the lines per order first for exactly this reason, so this is a rule the context stated and the model missed. gq-007 filters on the category 'tents' where the data says 'Tents', a value no layer lists. Spelled as stored, the answer is still wrong, because the query sums only the tent lines, where the question asks for the whole revenue of the orders that included one. gq-017 asks for "our countries", which depends on the region of the regional lead asking. No layer says who is asking, and the model's query, which takes country from dim_date, doesn't run.
This is the book's measurement on one synthetic warehouse and one 4-billion-parameter model, with one sampled reply per question, not a general result about layered context. We found no peer-reviewed study from 2025 or 2026 that measures retrieved verified queries separately from other context on business data, which is why the chapter measures it here.
Working rule: Measure context one layer at a time on the dev split, with the same questions, comparison rule and model, and keep the outcomes per question, not only the totals. Treat a difference of one or two questions on a set this size as noise until repeated runs say otherwise. Read the queries that fail, because each one names a layer you don't have yet or a rule the model still skips.
test에서 선택한 계층 확인하기
ablation은 dev 분할, 즉 이 장에서 튜닝에 사용한 질문들에서 네 계층을 모두 선택했다. dev에서 가장 높은 점수를 얻은 계층 집합이 같은 질문에서 선택된 탓에 비교가 유리하게 나오는 일이 없도록, 그 집합을 따로 떼어 둔 test 분할에서 스키마만 쓴 경우와 함께 한 번 더 확인해야 한다. 녹화 파일 context-layers-test에는 answer 유형의 test 질문 17개에 대해 모델이 작성한 SQL이 들어 있으며, 그 두 계층 집합의 결과만 담겨 있다. 이어지는 문단을 위해 각 test 질문은 검증된 쿼리 중 가장 잘 맞는 것과도 비교하여 점수를 매겼다.
1"""Choose the best layer set on the dev split, then score it once on the held-out test split beside the schema alone. The2correct counts are the numbers the prose rests on, and the retrieval scores measure how close each split sits to the verified queries."""3from collections import Counter45from lib.context import LAYER_SETS, QueryIndex, grade_with_context, layer_set_name, load_verified_queries6from lib.golden import load_golden7from lib.model import RecordedModel8from lib.warehouse import connect910con = connect()11questions = [q for q in load_golden() if q.kind == "answer"] # the answer questions only, as in the ablation12dev = [q for q in questions if q.split == "dev"]13test = [q for q in questions if q.split == "test"]1415dev_model = RecordedModel("context-layers") # dev and test replies come from separate recordings; the test one plays no part in the choice16dev_correct = {layers: sum(grade_with_context(con, dev_model, q, layers) == "correct" for q in dev)17 for layers in LAYER_SETS}18best = max(LAYER_SETS, key=lambda layers: (dev_correct[layers], -len(layers))) # ties go to fewer layers19print(f"best on dev: {layer_set_name(best)}")20print(f"best on dev correct: {dev_correct[best]} of {len(dev)}")2122test_model = RecordedModel("context-layers-test")23print(f"test questions: {len(test)}")24print(f"test question IDs: {', '.join(q.question_id for q in test)}")25for name, layers in (("schema", LAYER_SETS[0]), ("best", best)):26 counts = Counter(grade_with_context(con, test_model, q, layers) for q in test)27 print(f"test {name}: correct {counts['correct']}, wrong {counts['wrong']}, error {counts['error']}")28 print(f"test {name} correct: {counts['correct']}")2930# How close each split's questions sit to the verified queries, by the retriever's own score.31index = QueryIndex(load_verified_queries())32for split, qs in (("dev", dev), ("test", test)):33 best_match = [(index.search(q.question, k=1, min_score=0.0)[0], q) for q in qs] # the best match, whatever its score34 print(f"{split} mean top retrieval score: {sum(score for (score, _), _ in best_match) / len(qs):.2f}")35 print(f"{split} questions with no verified query kept: {sum(not index.search(q.question) for q in qs)}")36 (score, query), q = max(best_match, key=lambda pair: pair[0][0])37 print(f"{split} closest pair: {q.question_id} and {query.name}")38 print(f"{split} closest score: {score:.3f}")
python best-layers-on-test.pybest on dev: schema+columns+glossary+verified
best on dev correct: 14 of 17
test questions: 17
test question IDs: gq-025, gq-026, gq-027, gq-028, gq-029, gq-030, gq-031, gq-032, gq-033, gq-034, gq-035, gq-036, gq-037, gq-038, gq-039, gq-040, gq-041
test schema: correct 4, wrong 9, error 4
test schema correct: 4
test best: correct 8, wrong 7, error 2
test best correct: 8
dev mean top retrieval score: 0.57
dev questions with no verified query kept: 2
dev closest pair: gq-001 and net_revenue_by_fiscal_quarter
dev closest score: 0.862
test mean top retrieval score: 0.63
test questions with no verified query kept: 1
test closest pair: gq-029 and new_customers_by_region
test closest score: 0.933
계층은 아무도 튜닝하지 않은 질문에서도 여전히 도움이 되며, 중요한 것은 바로 이 결과다. dev에서 가장 좋은 집합은 schema+columns+glossary+verified였고, dev에서 14 of 17개를 맞혔다. test에서는 스키마만 쓰면 4개를 맞히고, 네 계층을 모두 쓰면 8개를 맞힌다.
dev 점수와 test 점수의 차이는 또 하나의 결과이며, 이 장에서는 그 원인을 측정하지 않았다. 한 가지 설명은 검증된 쿼리가 dev에 더 잘 맞는다는 것이지만, 검색기 자체의 점수는 이를 반박한다. 각 질문에서 가장 잘 맞는 항목의 평균 점수는 dev에서 0.57, test에서 0.63이다.
골든 질문과 검증된 쿼리 사이에서 가장 가까운 한 쌍도 test에 있다. 그 쌍은 gq-029 and new_customers_by_region이고 점수는 0.933이며, 같은 질문을 2024 역년에 대해 물은 것이다. dev에서 가장 가까운 쌍의 점수는 0.862이다.
알려진 사실은 두 가지다. 첫째, dev는 집합을 고르는 것 이상의 용도로 쓰였다. 용어집 절에서 말했듯이 첫 dev 응답을 녹화하면서 용어집을 골든 SQL에 맞게 수정했는데, 이런 수정은 의도 여부와 관계없이 컨텍스트를 점수가 매겨질 질문에 맞추게 된다. 이것이 dev가 과적합되는 방식이며, dev 점수를 보고할 결과로 삼아서는 안 되는 이유다. 둘째, 두 분할은 서로 다른 것을 묻는다. test 분할은 매출총이익, 세션, 마케팅 지출을 묻는데, dev 질문 중에는 이를 다루는 것이 없다. 이 원인들을 구분하려면 이 장이 가진 것보다 더 많은 질문과 반복 실행이 필요하다.
작업 규칙: 집합은 dev에서 고르고, 그 집합을 기준선과 나란히 test 분할에서 한 번만 채점한 뒤 test 수치를 보고한다. dev 결과를 본 뒤에 컨텍스트에 가한 변경은 모두 기록해 둔다. 각각이 dev 점수를 과대평가하게 만드는 경로이기 때문이다. dev와 test가 어긋날 때는 차이를 설명하기 전에, 각 분할이 예시와 얼마나 가까운지처럼 측정할 수 있는 것부터 확인한다.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
What goes wrong in production
The layers raised the dev score well above the schema alone, and they stay correct only while the warehouse and its definitions stay put. Once people rely on them, the failures are quiet: a note or a verified query keeps teaching a rule its warehouse has since changed.
The notes drift away from the marts. A column note is true about the mart as it was built when someone wrote it. When the analytics engineers change fct_orders, for example to drop cancelled orders from the mart, the note that says to filter on status becomes wrong, and nothing fails. You notice by keeping the notes in the same repository as the SQL that builds the marts, reviewing them in the same pull requests, and checking in CI that every table and column a note names still exists.
A value the context never lists. The model wrote 'tents' for a category stored as 'Tents', and the query ran and returned nothing. Questions name values, such as categories, channels, regions and campaign names, and a model guesses their spelling. Reading the wrong answers whose result is empty or zero catches it. For columns with a few dozen distinct values, list them in the column note. For columns with many, retrieve the values that match the question's words, as you retrieve verified queries.
A rule stated in context is still ignored. With the glossary, the model dropped the cancelled-orders filter on gq-002 and left cancelled orders out of the customer count for gq-016. With the column notes, and again with the glossary, it left them out of the order count for gq-013. Both go against what the note on status and the glossary say. With all four layers, it summed order revenue over order lines on gq-006, which the glossary's entry for units sold warns against. Context makes a rule available. It doesn't make the model follow it. Only per-question golden scores reveal it. When a rule has to hold every time, move it out of the prompt and into code the model can't skip, which is what chapter 5's semantic layer does.
Retrieval offers the wrong example. Lexical retrieval matches words, so a question that shares only "most" with a verified query gets that query as its example unless a threshold stops it, and a question that means the same thing in other words gets nothing. Log which verified query each answer used, as Snowflake's Cortex Analyst reports it in the confidence field of its API (Snowflake), then read the logs for answers that went wrong.
A verified query is mistaken for a verified answer. A retrieved query guides the model, and the SQL it writes is new SQL that nobody checked. Databricks marks a Genie answer as verified only when the exact text of a parameterized example query is used (Databricks), which is the distinction to keep. Never show an answer as verified because a similar query was. You notice by checking what your interface says about answers that were built from examples.
Verified queries go stale. A query checked in October uses the definitions of October. When finance changes a definition, the old queries teach the old rule to every similar question. Keep a verified_at date on each query, re-run each against its golden counterpart when a definition changes, and remove queries nobody has checked since the change.
The context grows until it costs more than it gives. Each layer is cheap to add, and nobody removes one that stopped helping. On a CPU every prompt token costs time, and with a hosted model it costs money on every call. Recording the tokens of each call with its outcome, as the recordings here do, and re-running the ablation when a layer grows, shows which layer has stopped paying for its tokens.
Working rule: Version the context with the warehouse: keep it beside the marts, checked in CI against the tables it names, scored on golden questions with every change, and logged per answer with the verified queries it used. Move any rule that must always hold out of the context and into a definition the model can't skip.
이 부분은 아직 번역되지 않았으므로 영어로 표시된다.
Decisions this chapter made
- Context comes in four layers: the whole schema, notes on columns whose names mislead, a business glossary, and verified queries retrieved per question. Each lives in a file under
examples/data/shop/, andbuild_context()inlib/context.pyassembles them. - The schema is never trimmed while it fits; the budget applies to the other layers.
- Verified queries are retrieved lexically, by TF-IDF over the question's words with numbers, months and region codes masked, at most three, each with a score of at least 0.4. None of them is a golden question, and each names the team that verified it, never a person.
- The context is rendered stable layers first and the question last, so the question sits beside its examples and the runner can reuse the shared prefix.
- Tokens are estimated from characters, at 3.5 characters a token, which overestimated the model's count on the prompt checked, so a context that fits the estimate should fit the model; check it on your own prompts.
- Layers are measured by ablation on the dev split's answer questions, one layer at a time, and the best set is checked once on the test split beside the schema alone.
- The book keeps all four layers for the chapters that write SQL, because that set scored best on dev. The glossary stays although it didn't improve the score by itself: the verified-query layer was measured on top of it, and we didn't measure verified queries without it.
이 판의 이 장
2026.11.15판예제 통과 2026년 11월 19이 장에서 9개 예제개 중 9개가 통과한다
영어판 2026.11.15판을 번역한 것이다.