Rolling Edition

Business Intelligence with Agents

Teaching kit

From Edition 2026.11.5, the book's current edition. It changes when the book does. Use and adapt it freely, with credit to the book.

On this page
  1. Syllabus
  2. Objectives and outlines
  3. Quiz bank

Syllabus

Reading times are for the chapter's text. Students also run its examples, in the Lab or on their own machine.

A chapter a week: 11 weeks

WeekChaptersReadingExamples
11. A data agent end to end21 min6
22. Where raw text-to-SQL gets the number wrong23 min6
33. Build golden questions from your own work23 min8
44. Give the agent layered context27 min9
55. Define metrics once in a semantic layer24 min8
66. Route questions to governed metrics first25 min6
77. Let the agent write SQL inside guards32 min11
88. Say when the answer can't be given24 min5
99. Keep the agent inside who may see what26 min8
1010. Judge answers and catch regressions26 min6
1111. Serve the semantic layer over MCP24 min5

In 5 weeks

WeekChaptersReadingExamples
11. A data agent end to end
2. Where raw text-to-SQL gets the number wrong
3. Build golden questions from your own work		67 min20
24. Give the agent layered context
5. Define metrics once in a semantic layer		51 min17
36. Route questions to governed metrics first
7. Let the agent write SQL inside guards		57 min17
48. Say when the answer can't be given
9. Keep the agent inside who may see what		50 min13
510. Judge answers and catch regressions
11. Serve the semantic layer over MCP		50 min11

Objectives and outlines

Each chapter's objectives are its first sentence. Each outline follows the chapter's sections, with the terms it defines, the predictions it asks for before an example runs, and the rule each section ends on.

1. A data agent end to end

About 21 minutes to read, 6 examples.

By the end, students can build the shop's warehouse, have a recorded model write SQL for a business question, run that SQL and catch the wrong number it returns, answer the same question from one defined metric, and score both approaches on five golden questions.

Lecture outline: 8 sections
  1. Meet the shop and its warehouse
    • Run load-the-warehouse.py
    • Working rule Build the agent against a warehouse that has your real traps in it: test rows, cancelled orders, several currencies, local business days, a fiscal calendar and at least one legacy table with a tempting name. Generate it from a seed when you can't share production data, so anyone can rebuild it. A clean demo schema hides exactly the failures you need to see.
  2. Ask a question, get SQL from a model
    • Run first-question-to-sql.py
    • Working rule Treat schema-only text-to-SQL as the baseline to beat, not as the product. Run it once on your own warehouse and keep its replies, because it shows you which of your definitions the schema doesn't carry. Record every model reply your tests depend on, pinned to a model file, a prompt hash and fixed sampling, so a failure you find can be reproduced by someone else.
  3. Run the SQL and check the number
    • Run run-the-model-sql.py
    • Working rule Never accept an agent's number because its SQL looks right. Check it against a golden result before anyone relies on it, and when the two differ, find the rows that explain the difference before you change anything. Expect the difference to be a definition, such as which orders count, more often than a syntax mistake.
  4. Answer from one defined metric
    • Run one-metric-answer.py
    • Working rule Route a question to a defined metric before you let a model write SQL. Give the model the metric's description and its dimensions, and let it return a request, which your code validates and compiles. Write SQL from a model only for questions no metric covers, and only behind guards.
  5. Score both on five golden questions
    • Run score-five-golden.py
    • Working rule Score every change on golden questions written from your own work, and report correct, wrong, error and declined separately. Include questions that should be declined from the start, or an agent that never declines will look perfect. Use a handful of questions as a smoke test while you build, and a larger set with a held-out split before you claim anything.
  6. Read the trace of one run
    • Run print-one-trace.py
    • Working rule Read traces, not only scores, and read the declines first. Check that each decline's reason is about what the agent was given, such as "no metric counts customers", and not a claim about data it never saw. Treat a decline with a false reason as a defect, even when the score counts it as declined.
  7. What goes wrong in production
    • Working rule Before anyone outside your team uses the agent, make every failure above visible: score on golden questions in CI, show provenance and data freshness with each answer, review declines in traces, replay recorded replies by message hash, and run model-written SQL only on a read-only connection. Start with golden scoring, because it is the only one of these checks that catches a plausible wrong number.
  8. Decisions this chapter made

2. Where raw text-to-SQL gets the number wrong

About 23 minutes to read, 6 examples.

By the end, students 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.

Lecture outline: 8 sections
  1. Joins that fan out and inflate a sum
    • Run tent-orders-fan-out.py
    • 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.
  2. Definitions the schema doesn't hold
    • Run gross-or-net-revenue.py
    • Working rule Treat every business word in a question as a definition to look up, not a column to match. Write down who owns each definition, and give the agent the definition itself, as a governed metric or at least a glossary entry, never only the columns it could be computed from. When a word has no written definition, have the agent ask or decline instead of choosing.
  3. Time zones and the fiscal calendar
    • Run fiscal-year-and-local-day.py
    • 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.
  4. The table with the tempting name
    • Run legacy-table-answer.py
    • Working rule List the tables an agent may read, and leave legacy and staging tables off the list even when they have the friendliest names. Check every model-written query's tables against the list before it runs. When an agent never chooses a tempting table in your tests, keep the check anyway: a new question, a larger schema or a retrieved example can change that.
  5. Filters nobody wrote down
    • Run cancelled-lines-counted.py
    • 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.
  6. Count the failures by kind
    • Run classify-schema-only-errors.py
    • 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.
  7. What goes wrong in production
    • 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.
  8. Decisions this chapter made

3. Build golden questions from your own work

About 23 minutes to read, 8 examples.

By the end, students can write golden questions from the questions people in your business ask, each with its golden SQL, the role that asks it and the kind of answer it needs.

Lecture outline: 9 sections
  1. Why a public benchmark isn't your test
    • Run public-vs-own-score.py
    • Working rule Use public benchmarks to compare models on a common task, and never as evidence that an agent works on your warehouse. Cite the benchmark's version with any score you quote, since gold sets get corrected. Measure your own agent on golden questions written from your own work, with your definitions, and expect the two scores to disagree.
  2. Write questions from the work people do
    • Run load-golden-questions.py
    • Working rule Collect golden questions from real requests, keep the asker's wording, and give each one a role, a kind and a note that names the definition its gold follows. Write the golden SQL against the marts the agent will use, and have the person who owns the definition read the note. Expect to spend more time on the definitions than on the SQL.
  3. Compare results, not SQL text
    • Run results-match-rules.py
    • Working rule Score an agent by comparing its result with the golden result, never by comparing SQL text, normalized or not. Keep one comparison function for every score you report, so a change in the score is a change in the agent. When a match surprises you, read the agent's SQL, because a result can match by coincidence.
  4. Order, rounding and column names
    • Run tolerant-comparison.py
    • Working rule Ignore row order, column order and column names, allow extra columns, and require the same number of rows and numbers within a small relative tolerance. Round each gold to a stated place, and allow half a unit in that place on top. Set order_matters only when the question asks for a ranking or a sequence. Fix units and scale in the question or its note, and treat an answer in other units as wrong, because nobody reading it can tell.
  5. Check the gold before you trust it
    • Run gold-runs-and-agrees.py
    • Run reworded-golden-question.py
    • Working rule Before a golden question scores anything, run its SQL, reject empty and NULL results, compare it with an independent query where you can, and have the definition's owner read its note. Read the agent's wrong answers for unclear questions, and fix the question, not the score. When a question can be read two ways, name the reading in its text, or make it a decline.
  6. Cover declines and roles, not only answers
    • Run golden-coverage.py
    • Working rule Include declines and forbidden questions from the start, and split declines into ambiguous and unanswerable, because the right answer differs. Tag each question with the traps it tests, and count kinds, roles and traps per split before you score anything. Fix a gap by writing new questions, not by moving existing ones between splits after you've seen how the agent does on them.
  7. Hold out a test split
    • Run baseline-on-both-splits.py
    • Working rule Split your golden questions once, before you tune anything, and tune only on dev. Score the test split only when you report a result, and report both splits side by side. When the test questions have been looked at so often that people tune to them, write new test questions and retire the old ones to dev.
  8. What goes wrong in production
    • Working rule Treat the golden set as code: keep it beside the definitions, run its checks in CI, compute golden results at scoring time, add questions from real traffic to dev, and protect the test split. When a score moves, read the answers that changed before you change the set or the comparison.
  9. Decisions this chapter made

4. Give the agent layered context

About 27 minutes to read, 9 examples.

By the end, students 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
  • verified queries retrieved for each question
Lecture outline: 9 sections
  1. What the schema says, and what it doesn't
    • Run schema-digest.py
    • 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.
  2. Describe columns where names mislead
    • Run column-descriptions.py
    • 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.
  3. Add the business glossary
    • Run glossary-context.py
    • 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.
  4. Retrieve verified queries as examples
    • Run retrieve-similar-queries.py
    • 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.
  5. Fit the context to a budget
    • Run context-token-budget.py
    • Working rule Set a token budget well inside the model's window, and fit each question's context to it by a priority you have measured, not by the order you wrote the layers in. Never drop the schema when it fits. Render the stable layers first and the question last, whatever was dropped. Check your token estimate against the model's real count once, and keep it on the high side.
  6. Measure what each layer adds
    • Run layer-ablation-dev.py
    • Run missing-columns-schema-only.py
    • Run what-stays-wrong.py
    • 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.
  7. Check the chosen layers on test
    • Run best-layers-on-test.py
    • Working rule Choose the set on dev, then score it once on the test split beside the baseline, and report the test number. Write down every change you made to the context after reading a dev result, because each one is a way the dev score can overstate. When dev and test disagree, check what you can measure, such as how close each split sits to your examples, before you explain the gap.
  8. What goes wrong in production
    • Working rule Treat context as code that ships with the warehouse: versioned 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.
  9. Decisions this chapter made

5. Define metrics once in a semantic layer

About 24 minutes to read, 8 examples.

By the end, students can write the shop's semantic models and metrics in MetricFlow's YAML spec, compile a metric request to SQL with the book's own compiler, show that its entity joins can't fan out, group by the fiscal calendar, and confirm that MetricFlow compiles the same definitions to the same results.

Lecture outline: 9 sections
  1. Describe each table as a semantic model
    • Run load-semantic-models.py
    • Working rule Declare one semantic model per table, mark an entity primary only where it's unique, and check the declarations against the data whenever the warehouse or the YAML changes. Write simple metrics without measures, naming their model and aggregation, unless your MetricFlow version or your dbt project still requires measures.
  2. Compile a metric request to SQL
    • Run compile-net-revenue.py
    • Working rule Let the agent, and every other client, send metric requests, never SQL fragments. Resolve every name in a request against the definitions, build the query as a tree with values as literals, and show the SQL with every answer so a person can read what ran. Accept a raw SQL filter only from the people who maintain the layer, never from a model.
  3. Join through entities so sums can't fan out
    • Run entity-join-no-fanout.py
    • Working rule Declare entities so that every join the layer makes is many rows to one, and let the layer refuse the rest. When a question needs a number on a finer table, such as revenue by product, define the metric on that table rather than reaching it through a join. Check that primary entities are unique in the data, because a wrong declaration turns the protection off.
  4. Build ratio and derived metrics from simple ones
    • Run refund-rate-metric.py
    • Working rule Define every rate as a ratio of two metrics, so each side is aggregated on its own and divided per group. Write down which time dimension each side is counted on, and make sure each side applies its filter, in its own definition or as an input filter, rather than relying on readers to know. Use a derived metric when the expression is more than one division, and keep its inputs simple metrics you can query on their own.
  5. Put the fiscal calendar in the time spine
    • Run fiscal-quarter-grain.py
    • Working rule Put every fiscal and business calendar in the time spine as custom grains whose values each name one period, and let requests group by them. Build local business days in the marts, before the layer. When you need a comparison across fiscal periods, run one request per period and compare the results in code, until your layer supports offsets at custom grains.
  6. Check the definitions against MetricFlow
    • Run metricflow-agrees.py
    • Run metricflow-needs-a-date.py
    • Working rule Compare your layer with MetricFlow, or whichever tool serves it in production, on every request shape you rely on, and run the comparison whenever either side changes. Pin MetricFlow's version exactly and keep its engine behind one module, because its Python API can change in any release.
  7. One definition, any warehouse's dialect
    • Run transpile-metric-sql.py
    • Working rule Keep one definition and transpile it, but treat each dialect's SQL as untested until it runs on that warehouse against a known answer. Cast dates and divisions explicitly in the compiler, so the translation has less to guess.
  8. What goes wrong in production
    • Working rule Treat the semantic layer as code with tests: check entity uniqueness against the data, run every metric's SQL in CI, compare against MetricFlow and the golden questions on every change, and accept only structured requests from anything that isn't a person who maintains the layer.
  9. Decisions this chapter made

6. Route questions to governed metrics first

About 25 minutes to read, 6 examples.

By the end, students can give the agent the semantic layer's metrics as tools instead of the warehouse's tables, have it return a structured metric request that's validated before anything runs, have it ask the person back when a question could mean several metrics, and measure the change on the golden questions.

Lecture outline: 8 sections
  1. Give the agent metrics, not tables
    • Run metric-tools.py
    • Working rule Give the agent the metrics, their descriptions and the dimensions each can use, and keep tables out of its tools until a question needs them. Put the catalog in the prompt while it fits, so a run doesn't spend a call listing metrics, and let a describe tool return dimension values from the warehouse. When the catalog outgrows the prompt, keep names and descriptions there and move the dimensions behind the describe tool.
  2. Ask for a request, not SQL
    • Run model-returns-a-request.py
    • Working rule Have the model return a metric request as structured arguments to a tool, and parse it into a typed request before anything else happens. Keep the reply format simple enough for your own code to parse, and keep that parser in one place. Accept SQL from a model only on a separate, guarded path, for questions no metric covers.
  3. Validate the request against the layer
    • Run reject-unknown-dimension.py
    • Working rule Validate every request before it compiles: names against the layer, categorical values against the warehouse, dates against their grain. Refuse with a message that names the allowed alternatives, and send it back to the model, which can usually correct itself in one call. Treat any request that would compile into an empty or partial result as invalid, not as an answer of zero.
  4. Ask back when a question is ambiguous
    • Run clarify-best-customers.py
    • Working rule Give the agent a tool to ask back, with options that name the layer's metrics, and show the person each option's definition. Ask back only when every option leads to a request the layer can answer; when none does, decline instead. Read clarifications in traces as you read declines, because a score counts a useless question as correct.
  5. Measure metrics-first on the golden questions
    • Run metrics-first-score.py
    • Working rule Score metrics-first beside your baseline on the same golden questions, and tune on dev only. Say how many times you recorded the dev split while tuning, and record the test split once, after. Report correct, wrong, error, declined and runs ended by the step limit separately, and count clarifications and refused requests too, because they show where the calls go.
  6. Read the runs that weren't correct
    • Run metrics-first-misses.py
    • Working rule Read every run that wasn't correct, call by call, and sort it by what it needs: a near-miss metric means the descriptions need sharpening or the question needs asking back, a missing metric means the layer needs one or the question needs guarded SQL, and an answer that depends on the role means access rules, not prompts.
  7. What goes wrong in production
    • Working rule Before you widen the agent's audience, keep one metric per business term with descriptions that say when not to use it, review validation messages as you review prompts, read dimension values when requests run, enforce roles below the agent, and count clarifications and refusals beside the score. Start with the near-duplicate metrics, because they produce valid requests that return wrong numbers.
  8. Decisions this chapter made

7. Let the agent write SQL inside guards

About 32 minutes to read, 11 examples.

By the end, students can handle the questions no metric covers by letting the agent write SQL that runs read-only on a database holding only the rows and columns its role may read, and that is parsed, checked against an allowlist of tables, bounded in rows, size, cost and time, repaired once from an error, and transpiled to your warehouse's dialect.

Lecture outline: 12 sections
  1. Give the agent a database with only what it may read
    • Run agent-warehouse.py
    • Working rule Run the agent's SQL on a connection that can reach only what the agent may query: a role with only those grants, or, in DuckDB, a read-only database file that holds only the allowed tables, with file access and extension loading off and its settings locked. Name the allowed tables one by one. Then list what the database can't hide, such as its catalog, and refuse those reads in the guard.
  2. Give each role a database of views
    • Run role-database.py
    • Working rule Make the database the boundary: connect the agent read-only, as a role that can read only views limiting the rows and columns that role may see, through secure views, row access policies and masking policies where the warehouse has them. In DuckDB, give each role its own read-only database of those views. Generate the views from the same policy the rest of your stack uses, and test them with queries the parser accepts.
  3. Parse the SQL before it runs
    • Run parse-agent-sql.py
    • Working rule Parse the agent's SQL into a tree before anything runs, in the dialect of the warehouse that will run it, and accept exactly one statement. Check nodes, never text. Treat a query that parses as a query whose shape you can now inspect, not as one that is valid, and resolve its columns against the tables it may read before you send it to the warehouse.
  4. Allow only read-only queries on known tables
    • Run block-a-delete.py
    • Run block-raw-tables.py
    • Working rule Accept only a query at the root, walk every node for a statement that writes, and allowlist tables by schema and name, with nothing but tables and subqueries in FROM. Resolve a name without a schema as a CTE only where a CTE of that name is visible, refuse the database's catalog by name, and allow only the functions your analytics need. Check the same list the agent's database holds. When a golden question needs a table off the list, give it a mart or a metric, not a wider list.
  5. Bound the rows and the cost
    • Run explain-before-running.py
    • Working rule Bound every query four ways: a row limit that keeps a smaller one the agent wrote, a limit on the result's size, a plan-size limit from EXPLAIN checked before the query runs, and one deadline over every step of the guard, planning included. Refuse a result over either limit rather than cut it, and refuse a plan with a step that has no estimate. Set the plan limit from your own known-good queries, never use EXPLAIN ANALYZE, which runs the query, and rely on the deadline and the result limits, not the estimate.
  6. Enforce the deadline by killing a process
    • Run stop-a-slow-plan.py
    • Working rule Start the deadline when the agent's SQL arrives, and run EXPLAIN under it as well as the query. Enforce it by killing a separate process with its own read-only connection, not with an interrupt. Check on your own database that a stopped plan and a stopped query really stop.
  7. Replay the attacks on the guard
    • Run replay-guard-bypasses.py
    • Working rule Rely on the database's limits, and treat the parser as defence in depth. Keep every attack anyone finds as a test: replay it against the agent's database alone and through the guard, and check that each is refused by the layer you expect. When an attack gets past the parser, ask first why the database let it reach anything, and then fix the parser.
  8. Repair once from the error
    • Run repair-from-error.py
    • Working rule Repair at most once, and only from an outside signal: the guard's refusal or the warehouse's error, passed to the model with its own SQL and a prompt that doesn't assume the SQL is wrong. Let the model decline in the repair. Score repaired answers on their own, apart from first-time answers, and if your golden questions show they are rarely right, decline on a refusal instead of repairing.
  9. Transpile to the warehouse's dialect
    • Run transpile-to-snowflake.py
    • Working rule Let the agent write the dialect your golden questions are checked in, guard that SQL, and transpile the guard's output, not the agent's text, with errors raised for what the transpiler knows it can't translate. Drop the guard's quotes for warehouses that fold unquoted names to upper case, refuse functions the transpiler passed through, and run the golden questions on the target warehouse before you trust a transpiled answer, because a function with the same name can return different values.
  10. When to prefer a new metric over SQL
    • Run long-tail-score.py
    • Working rule Send a question to guarded SQL only when no metric covers it, and score that path on its own long tail of golden questions. Promote a question to the semantic layer when it recurs, when its answer goes into a report, or when guarded SQL gets it wrong on a definition, as with what counts as an order. Leave one-off lookups on the guarded path. When a question needs a table off the allowlist, build the mart and its metric rather than widening the list.
  11. What goes wrong in production
    • Working rule Treat the role's read-only database of views as the control, and the guard as a layer that turns mistakes into refusals and bounds the cost. Check the agent's grants and test each role's views, run every golden query through the guard in CI, set the time limit in the guard and on the warehouse, log every refusal, estimate and repair with its answer, and score the path on its own long tail of golden questions after every change.
  12. Decisions this chapter made

8. Say when the answer can't be given

About 24 minutes to read, 5 examples.

By the end, students can make declining a first-class answer:

  • tell the questions the warehouse can't answer from the ones it can
  • decline with a reason of a known kind
  • check that the data for the period asked about has arrived
  • show the provenance behind every answer
  • measure declines with precision and recall
  • so the agent neither guesses nor refuses too often
Lecture outline: 7 sections
  1. Kinds of questions the warehouse can't answer
    • Run unanswerable-cases.py
    • Working rule Sort every question your golden set marks as a decline into ambiguous or unanswerable, and name its category, so you know which reply it needs. Ask back on an ambiguous question only when every reading is answerable, and decline an unanswerable one with what's missing. For each unanswerable question, find the column or value nearby that a model could mistake for the answer, because that's the wrong number you're guarding against.
  2. Decline with a reason
    • Run cannot-answer-tool.py
    • Working rule Make declining a tool that ends the run with a kind from a fixed list and a sentence for the person. Let the model choose among the kinds it can judge, such as missing data, a forecast or an undefined term, and let code decide the kinds code can check, such as stale data or a forbidden role. Count declines by kind, and read the reasons in traces, because a decline of the right kind can still give the wrong reason.
  3. Check freshness before answering
    • Run stale-refunds.py
    • Working rule Check freshness in code before every answer that names a period: compare the newest load of each feed the answer reads with the end of the period plus the metric's settling window, and decline as stale when the feed stops short, whether its rows haven't loaded or haven't happened. Measure each window from your own data, as the delay that covers the events you've seen. Read load times from your own load columns or a load log, never from table metadata, and map each table to the feed that decides its freshness. Answer a question with no period, and show its data as of in the provenance.
  4. Show the provenance with every answer
    • Run answer-with-provenance.py
    • Working rule Show every answer with its provenance, and put the metric's name and definition first, since that's where a near-miss metric shows. Then show the filters, the period, the role and the data as of, and keep the SQL one step away for the people who check it. Show declines the same way, with their kind, reason and role.
  5. Measure declines, not only accuracy
    • Run decline-precision-recall.py
    • Working rule Score an agent on declines as well as answers: report decline precision and recall, coverage and selective risk, and a penalty score with a c your stakeholders agree to, stated as a judgement. Compare agents on the same questions, and list the wrong numbers each gave, because a change that removes one wrong number can add another. When the decline questions number in single digits, read the runs, not only the rates.
  6. What goes wrong in production
    • Working rule Before you widen the agent's audience, give the freshness check a load log, measure each metric's settling window again on a schedule, check every time series' last period, count declines by kind and feed, and read a sample of reasons and questions back. Start with the not_in_data declines on answerable questions, because each one names a metric to write.
  7. Decisions this chapter made

9. Keep the agent inside who may see what

About 26 minutes to read, 8 examples.

By the end, students can enforce each role's row filters, column masks and minimum group size below the agent, in the role's own database, with the rewrite in the semantic layer and the SQL guard as defence in depth, so that no prompt can widen access.

Lecture outline: 10 sections
  1. A rule in the prompt is not a control
    • Run prompt-rule-bypassed.py
    • Working rule Never enforce access in a prompt. Decide what a role may see in code, from the role your sign-in system gives you, and apply it to the SQL that runs. Keep access rules out of the prompt, so nobody mistakes it for the control, and test the control with questions that try every way around it: other regions by name, totals, rankings, and questions that claim an exception.
  2. Apply row filters in the compiler
    • Run regional-lead-revenue.py
    • Working rule Attach row filters to tables, not to questions, and apply them by replacing each table a query reads with a filtered subquery, after the semantic layer compiles and after the guard checks. Write a filter for every table a filtered role can reach, reach the filter's column through a key where a table has none, and refuse tables with no filter. Refuse a filter value outside the role's rows, and record the row filter in every answer's provenance.
  3. Mask columns in every path
    • Run masked-emails.py
    • Working rule Mask a column by replacing its table, with a view in the role's database and a subquery in the rewrite, that returns the mask in the column's place, never by rewriting the columns a query selects. Keep personal columns out of the semantic layer. Deny a column a role may not know exists, and mask one it may know exists but not read.
  4. Suppress groups small enough to identify someone
    • Run small-group-suppressed.py
    • Working rule Set a minimum group size for every role that may see aggregates over people but not the people, and record it as a policy choice with its reason: the statistical offices use 3 for tables of amounts, and a larger number doesn't stop subtraction across answers. Count distinct people per group with the metric's own filters, suppress the values of every group below the minimum, and refuse metrics whose rows can't be traced to a person.
  5. Subtracting two answers recovers a group
    • Ask first Before you run it, predict whether the policy lets the second answer through, and what subtracting the first answer from it gives.
    • Run difference-two-answers.py
    • Working rule Treat small-group suppression as protection for one answer, never for a person across answers. Where small groups matter, limit the agent to breakdowns planned together, or perturb the values, and review the audit log for overlapping questions from one person.
  6. Treat text in the data as data
    • Run injected-product-name.py
    • Working rule Treat every value the model reads as untrusted, and never let a model's prose be the only form of an answer. Return the table with its provenance, show a summary only after code has checked it against the table, and keep the role and the policy out of anything the model can write. Marking data as data in the prompt is not a control.
  7. Measure access on the golden questions
    • Run role-scope-score.py
    • Working rule Score every policy change on golden questions that name their asker's role, with forbidden questions in both splits, and read how each was declined, not only whether. Decide whether a role should be told that a rule applies or only that no answer exists, and make every decline say it the same way.
  8. Log who asked what, and what ran
    • Run audit-entries.py
    • Working rule Write one audit entry per question, from code, with the principal from your sign-in system, what the agent asked for, the SQL that ran after the policy, and the outcome. Keep values out of the log, protect its questions as personal data, and review it for overlapping questions and for declines a role keeps hitting.
  9. What goes wrong in production
    • Working rule Bind the role from the sign-in system, never from a request's arguments, test the policy file against the warehouse's tables in CI, keep one source of truth for policies, list dimension values through the policy, and read the audit log for refusals, single-group answers and overlapping questions after every policy change.
  10. Decisions this chapter made

10. Judge answers and catch regressions

About 26 minutes to read, 6 examples.

By the end, students can score a run of the agent on several measures at once, use a model as a judge only where results can't be compared directly, calibrate that judge against reference labels anyone can recompute, see how much a score moves from one run to the next, and gate a change to prompts, context or metrics in CI on recorded runs.

Lecture outline: 8 sections
  1. Score a run on more than accuracy
    • Run scorecard.py
    • Working rule Report every run as a scorecard: correct with a Wilson interval, wrong numbers, declines and their recall and precision, failures by kind, and calls and tokens per question. Compare agents on the same questions in the same table. Score the test split once, when you report a final number, and say that you did; when two agents' intervals overlap, don't call either better from the totals.
  2. Judge only what can't be compared
    • Run judge-an-explanation.py
    • Working rule Use a judge only for what can't be compared with a result, such as reasons, explanations and questions back, and keep results_match() for every number. Judge each pair in both orders and count the verdicts that change, record every verdict, and read a sample of the judge's reasons, not only its labels. Don't let a judge's preference between two texts decide a release until you have measured that judge on cases with known answers.
  3. Calibrate the judge against reference labels
    • Run judge-agreement.py
    • Working rule Before you use a judge on text, measure it on cases whose answers a check can label, with labels anyone can recompute. Report observed agreement with Cohen's kappa and Scott's pi, split by the kinds of answer you'll judge, list the errors in each direction, and say how far one case moves kappa when the cases number a few dozen. Thresholds such as 0.8 are conventions, not results (Artstein and Poesio); at a kappa near 0.5, use the judge to choose what a person reads, never to pass or fail a release on its own.
  4. Repeat runs to see the variance
    • Run seed-variance.py
    • Working rule Run each golden question several times at the sampling settings you serve, with seeds pinned in the recording's manifest, and confirm once that a seed replays exactly. Report per-question flips, not only the spread of the total, because a total can stay put while questions trade places. Never lower the temperature to make runs agree, unless that is the setting you will serve.
  5. Gate a change in CI
    • Run regression-gate.py
    • Working rule Gate a change to prompts, context or metrics on paired, per-question differences over repeated runs, never on a difference of totals. Fail on any stable break and on a mean difference whose interval lies below zero, write both rules down as your choice, and compute the smallest change they can detect on your set. When the gate fails, read the questions that moved before you touch the rules.
  6. Catch a schema change before users do
    • Run renamed-column-caught.py
    • Working rule Run the golden SQL, every metric and every recorded replay in CI on changes to the warehouse as well as to the agent, and treat MissingRecording as a failure, never as a reason to skip a question. When a check fails after a schema change, fix the definition in the semantic layer before you record anything again.
  7. What goes wrong in production
    • Working rule Before a gate guards a production agent, keep its judge's calibration cases beside the judge prompt and re-measure on every change to either, show the judge what it can check, record repeated runs at the settings you serve, agree the gate's rules with the people who answer for the numbers, and count in each manifest how many times a split was recorded.
  8. Decisions this chapter made

11. Serve the semantic layer over MCP

About 24 minutes to read, 5 examples.

By the end, students can serve the shop's governed metrics as MCP tools that answer for the role the server was built for, return each answer with its provenance as structured data, test the server in memory, and run the book's agent through it as a host.

Lecture outline: 7 sections
  1. Turn metrics into tools
    • Run list-metric-tools.py
    • Working rule Serve the tools your agent was measured with, each one a thin call into the same code the in-process agent uses, so that the server can't drift from what you tested. List them in a fixed order and mark them read-only. Let the role change what a tool returns, and shorten the tool list only when a role can't use a tool at all.
  2. Carry the caller's role into every query
    • Run role-bound-at-build.py
    • Working rule Bind the role where the person was authenticated, never in a tool's arguments. For a stdio server, the host passes the role in the environment, and the server opens that role's read-only database and builds its policy once. For an HTTP server, take the role from a verified token issued for this server, on every request, and enforce the role's rows in the database and the policy whatever the token's scopes say. Leave role out of every input schema, so there's nothing for a model or a question to fill in, and log any argument a tool ignores, role first, because one is a sign that text the model read is steering it (treat ticket text as untrusted data).
  3. Return answers with their provenance
    • Run tool-result-with-provenance.py
    • Working rule Return a pydantic model from every tool, so that each answer travels as structured content with its rows, its role, its filters and its data's freshness, and a host's code can check them. Raise a tool error for a request the model can fix, with a message that says how. Return a refusal for access as an answer that declines, with its reason, so the host ends the run instead of letting the model try another way in.
  4. Test the server in memory
    • Run semantic-server-tests.py
    • Working rule Test each role's rules through an in-memory client, with one server per role and assertions on the structured answer, and include a check that fails when the binding is wrong. Use the golden questions' results as the expected values, so the tests and the scores agree on what correct means. Add tests over HTTP for whatever an in-memory client can't send, tokens first.
  5. Answer questions through a host
    • Run host-asks-the-server.py
    • Working rule Put the server under the host you already measured, and score it on the golden questions before anyone uses it, because the transport can change what the model reads. Compare outcomes question by question with the in-process runs, and read every run that changed. Record what the new text produces rather than editing the server's messages to match old recordings, and show people the structured answer with its provenance.
  6. What goes wrong in production
    • Working rule Run one stdio server per person, or HTTP with the role from each request's token, and fail closed on any claim the policy file doesn't know. Query with the role's own read-only database, not a shared one. Pin the SDK and rerun the golden questions through the host whenever it changes. Keep one server per semantic layer in each host, record an audit entry for every answer, and read it for a principal that more than one person seems to share.
  7. Decisions this chapter made

Quiz bank

Questions on every chapter, with answer keys, for verified teachers with the Educator plan. Each answer is tied to its section. Ask for the answer keys