Exercises: Incremental Processing and Slowly Changing Dimensions

Most of these are code, and several ask you to break something on purpose. code/scd_lab.py runs every failure in this chapter against SQLite in about a second and is the fastest way to check an answer.

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

Part A — Warm-ups ⭐

20.1 † State the rule of thumb for going incremental, and the two numbers from §20.1 that show the same technique giving opposite answers in one project.

20.2 Name the three questions every incremental model answers. Which one's wrong answer is silent?

20.3 † List the four idempotency strategies and the single question that selects between them. What does it mean if the answer is "I cannot rewrite anything completely"?

20.4 Why is append not idempotent, and why is it what you get by not specifying anything?

20.5 † What does dbt build --full-refresh actually do? Give the three consequences, and the four-step safer procedure.

20.6 Why must a lookback window be paired with a merge? What is the failure if it is not?

20.7 † Should a lookback be sized from p99, p99.9, or the observed maximum? Defend your answer in one sentence.

20.8 Which two SCD types will you actually build, and at what granularity is the choice made?

20.9 † State the three SCD2 invariants. Which one multiplies your revenue when violated?

20.10 Why is valid_to = NULL worse than a far-future sentinel? Be precise about what gets dropped.

Part B — Standard ⭐⭐

20.11 Price the incremental decision for a model in your own system. Measure the full-refresh cost and the incremental cost, both in currency and in wall-clock. Then apply §20.1's rule of thumb and state whether the current materialization is right.

20.12 † Implement all four idempotency strategies against the same table. For each: run it twice, diff the output with EXCEPT in both directions, and report the row counts. scd_lab.py --demo idempotency is the reference.

20.13 Reproduce the lost-row failure from Case Study 2. Build an incremental model with > MAX(updated_at) and no lookback, then insert a row whose timestamp is below the watermark. Confirm it is never loaded. Then add a lookback and confirm recovery. Then remove the unique_key and report what the lookback does.

20.14 † Write the anti-join test from Case Study 2 for one of your fact tables. Run it. Report the number honestly, including if it is zero, and say what you would have concluded if you had never run it.

20.15 Build an SCD2 dimension by hand — no snapshot macro — with valid_from, valid_to, is_current, and a surrogate key. Process three days of changes. Then write and run the three invariant assertions from §20.9.

20.16 † Break each invariant deliberately, one at a time, and confirm the corresponding assertion fires. For the overlap case, also run a point-in-time join and report the fan-out factor.

20.17 Take the snapshot from 20.15 and add a last_login_at column to the source that changes daily. Run it with check_cols: all for fourteen simulated days, then with four named columns. Report both row counts and the ratio. scd_lab.py --demo checkcols is the reference.

20.18 † Set incremental_predicates on a merge-strategy model and measure the difference. Report the query profile before and after — specifically, rows scanned in the target, not just elapsed time.

Part C — Deeper ⭐⭐⭐

20.19 Case Study 2 argues a reconciliation tolerance is a budget for permanent error, and puts a dollar figure on it. Do this for a tolerance in your own systems. Then decide whether to change anything, and write down why.

20.20 † §20.11 proposes a nightly rebind of unknown-member rows. Write it. Then find the case where it is wrong — where a fact should keep pointing at the unknown member — and explain how you would distinguish the two.

20.21 Design the test that catches Case Study 1's precision mismatch — a fact timestamp joined against a date-grained validity range — before version density makes it fan out. State what it costs to run on every build.

20.22 † Case Study 2 found a model whose duplication was invisible because a downstream report used COUNT(DISTINCT ...). Design a systematic way to find defects currently masked by a downstream accident. Be honest about the coverage.

Part D — The Kestrel Platform ⭐⭐⭐

20.23 — Increment 20: incremental facts and a real dimension.

(a) Make fct_order_item incremental with a merge strategy, a lookback sized from a measurement you actually take, and incremental_predicates. Write all three of §20.2's answers as a comment, including the measured basis.

(b) Build dim_customer from a snapshot with four enumerated check_cols and dbt_valid_to_current set to a sentinel.

(c) Add the three SCD2 invariants as tests, plus a dimension-growth assertion. State the threshold and the gap it sits in.

(d) Add rebind_unknown_members as a nightly post-hook and assert_no_unknown_member_backlog as a test.

(e) Add a run-twice equivalence check as a CI job: build, build again, EXCEPT both directions.

(f) Add the anti-join completeness test at zero tolerance.

20.24 † Set check_cols: all on a copy of the snapshot and run it for fourteen simulated days against a source with a last_login_at. Count the rows. Then write the paragraph explaining why every row it produced is true and the dimension is nonetheless useless.

That paragraph is the exercise. The row count takes a minute; being able to say precisely what is wrong with true data is the skill.

Reflection

A. Both case studies in this chapter were found by a check failing for an unrelated reason. Chapter 18's Case Study 2 was too, and so was Chapter 19's Case Study 1. Is that a coincidence, a property of this class of system, or an artifact of which stories get written down?

B. §20.1 argues that fct_order_item should not have been incremental, and that making it so cost two incidents to save $649 a year. How would you present that argument to a team that made the decision, without it landing as blame?