Exercises: dbt

You need a working dbt project for most of these. platform/transform/kestrel_dbt/ from Increment 19 is the intended target; a scaffold you build in 19.11 also works.

Difficulty: ⭐ warm-up · ⭐⭐ standard · ⭐⭐⭐ deeper. Solutions: daggered (†) and odd-numbered problems are in appendices/answers-to-selected.md.

Part A — Warm-ups ⭐

19.1 † Name the three things dbt is, and the four things it is not. For each of the four, name the chapter that covers what does that job instead.

19.2 Why is a dbt model a SELECT rather than a CREATE TABLE? What class of bug did that eliminate?

19.3 † State the four separate consequences of replacing {{ ref('stg_orders') }} with analytics.stg_orders. Which of them disables the check that would have caught it?

19.4 Fill in the materialization table from memory: build cost, query cost, and the condition under which each of view, table, incremental, and ephemeral is correct.

19.5 † Two costs of ephemeral models. Which one is invisible in a cost report, and why?

19.6 What is the difference between dbt build and dbt run && dbt test? State the outcome difference in terms of what the dashboard shows.

19.7 † Give three ways a dbt test becomes decorative. State the one question that identifies all three.

19.8 Why does every dbt test pass when the ingestion pipeline has landed nothing? Name the three checks that catch it.

19.9 † dbt build --select "tag:a,tag:b" versus dbt build --select tag:a tag:b. Which is which, and what does the wrong one do to a scheduled job?

19.10 What do --defer and --state each do, and why is slim CI impossible without both?

Part B — Standard ⭐⭐

19.11 Build a four-model dbt project against DuckDB from scratch: one source, two staging models, one mart. Add unique and not_null to each staging model and a grain test to the mart. Run dbt build and paste the output.

19.12 † Take a mart from 19.11 and materialize it four ways — view, table, ephemeral, and (if your adapter supports it) materialized_view. For each, record: build time, the object dbt created, and the time of a query against it. Report the build-to-query crossover point — how many queries per build make table cheaper.

19.13 Introduce the §19.3 bug deliberately: replace one ref() with a hardcoded schema-qualified name. Confirm that dbt build succeeds and all tests pass. Then run dbt ls --select <upstream>+ and code/manifest_audit.py --hardcoded, and report what each shows.

19.14 † Write the volume assertion from Case Study 1 for your mart. Choose the floor and defend the number — say what it would take to make it fire falsely, and what it would fail to catch.

19.15 Configure freshness: on your source with thresholds you can trigger. Stop loading the source, run dbt source freshness, and paste the output. Then confirm that dbt build still succeeds and every test still passes.

19.16 † Write one generic test, one singular test, and one unit test against the same model. State what each can catch that the other two cannot.

19.17 Take a model with three or more CTEs and split it into three models. Then argue whether you should have — using §19.5's criterion, not taste. Which version would you defend in review?

19.18 † Write a model that uses a {% for %} loop to pivot a status column into five count columns. Then run dbt compile and read target/compiled/. Paste both, and answer §19.6's test: could a SQL developer who does not know Jinja predict the output from the source?

Part C — Deeper ⭐⭐⭐

19.19 Case Study 2 argues that every graceful-degradation mechanism creates a monitoring obligation. Find three in a system you work on — retries, defaults, COALESCE, fallbacks, circuit breakers — and for each, state the metric that would reveal it operating. How many of the three have one?

19.20 † §19.7 says a test that cannot fail should be deleted. Construct the strongest case against: when is a tautological assertion worth its runtime? Be specific about what it protects.

19.21 Measure slim CI on your own project. Build everything, record time and cost. Then modify one model and run with --select state:modified+ --defer --state. Report both, and the ratio. If the ratio is under 3×, explain what about your DAG's shape makes it so.

19.22 † Case Study 1's audit found a model reading production from developer laptops for four months with no symptom. Design a detection for the general class — "a defect that produces no observable output" — and state honestly what it cannot cover.

Part D — The Kestrel Platform ⭐⭐⭐

19.23 — Increment 19: kestrel_dbt.

(a) Convert Chapter 18's SQL files into a dbt project at platform/transform/kestrel_dbt/, with the layout from §19.5. Thirteen sources, five staging models, two intermediate, four marts.

(b) Configure freshness: on all thirteen sources with thresholds derived from each source's actual cadence — not copied. 19.24 checks that none of them is unfireable.

(c) Set materializations by directory in dbt_project.yml, and put a one-line comment on every mart's config saying why that one.

(d) Every mart gets dbt_utils.unique_combination_of_columns and a volume assertion.

(e) Add the assert_overlap_exceeds_gap macro from Chapter 18 Case Study 2 as a pre_hook on int_sessions, so widening the gap past the overlap fails the build.

(f) manifest_audit.py --all runs clean, and --hardcoded runs in CI as a failing check.

19.24 † Write audit_freshness_thresholds.py: for each source, compare the configured error_after against the source's observed load interval, and fail on any threshold that could never fire — Case Study 2's error_after: {count: 7, period: day} on an hourly source.

This is the exercise most worth doing carefully. A threshold that cannot fire appears on every audit as present, which is worse than one that is missing, and no dbt command will ever tell you.

Reflection

A. dbt's central contribution is that the dependency graph is derived from the code rather than maintained alongside it. Name two other artifacts in your systems that are maintained alongside the code and are therefore wrong. What would deriving them require?

B. Case Study 1's bug was reviewed and approved by two competent engineers, because the diff looked exactly like a correct change. How much of code review's value is conditional on defects being visible in a diff — and what fraction of the defects in this book would be?