Exercises: Data Modeling

Modeling is learned by modeling. Most of these ask you to design something and then find the flaw in your own design, which is the actual skill.

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

Part A — Warm-ups ⭐

6.1 † State Kimball's four steps in order. For each, say what goes wrong if you do it before the step that precedes it.

6.2 Give the three reasons to build a dimensional model rather than copying source tables, and rank them by importance.

6.3 † Classify each measure as additive, semi-additive, or non-additive: quantity, unit_price_cents, on_hand_units_eod, net_revenue_cents, gross_margin_pct, account_balance_cents, discount_cents, conversion_rate.

6.4 Why must promotion_key be NOT NULL and point at a "none" row rather than being nullable? Name the specific bug the nullable version produces.

6.5 † Give the four reasons to use a surrogate key for a dimension, and say which one is decisive. Then state what surrogate keys cost.

6.6 For each, name the SCD type: (a) correcting a misspelled customer name; (b) tracking that a customer moved from Colorado to Oregon so past orders still report as Colorado; (c) a birth date; (d) needing the immediately previous product category and no earlier ones.

6.7 † What is a degenerate dimension? Give a Kestrel example and say why it is kept.

6.8 Explain the difference between the fan trap and the chasm trap in one sentence each, and give the symptom of each.

Part B — Standard ⭐⭐

6.9 Complete the 🧪 Try It from §6.3 for Kestrel's returns process, in full: business process, grain (one sentence), at least seven dimensions, and all facts. Then answer the trap question — is the refunded amount always the amount originally paid for that line? Write down the three business questions you would have to ask, and who at Kestrel you would ask each one.

6.10 † fct_inventory_snapshot has one row per product per warehouse per day. At Kestrel's 47,000 SKUs and 3 warehouses: (a) How many rows per day? Per year? (b) At roughly 90 bytes per row as Parquet, what is the annual storage, and what does it cost at the frozen S3 rate? (c) Someone proposes storing only rows where the quantity changed. State two things that becomes harder, and decide.

6.11 Design the junk dimension for Kestrel's orders. The flags are: is_gift (2 values), is_expedited (2), is_first_order (2), payment_method (5), is_wholesale (2). How many rows at most? How many would you actually expect to observe, and why is the difference interesting?

6.12 † A dashboard shows revenue by promotion. When the promotion dimension is added, total revenue rises 31%. Diagnose it, name the pattern from §6.9, and write the fix as SQL — including the allocation weight and the test that the weights sum to 1.0 per fact row.

6.13 Kestrel keeps both fct_order_item (line grain) and fct_order (header grain). Write the argument for collapsing them into one table with allocated shipping and order-level discount. Then write the argument against. Which would you choose, and what would change your mind?

6.14 † Build the bus matrix for a business you know — a gym, a library, a hospital ward, an airline. At least five processes and seven dimensions. Then identify: the dimension used by the most processes, the two processes that share the fewest dimensions, and one comparison the business would want that your matrix says is currently impossible.

6.15 Write the dim_date table definition for Kestrel, covering 2016–2035. Include at least fifteen columns. Then explain why it uses an intelligent YYYYMMDD key when §6.5 says to use surrogate keys — and name the property of dates that makes the exception safe.

Part C — Deeper ⭐⭐⭐

6.16 §6.6 says a bus matrix "reveals integration points." Take your matrix from 6.14 and find a missing conformed dimension: a comparison the business wants that no shared dimension supports. Design the dimension that would enable it, and estimate what it would cost to add to the existing fact tables retroactively.

6.17 † The 🔐 callout notes that a Type 2 dim_customer complicates erasure, because prior versions of a person's attributes are also personal data. Design the erasure procedure for Kestrel's dim_customer: what gets deleted, what gets nulled, what must be preserved for referential integrity in the fact tables, and how you verify completeness. Then state which of your choices you would want a lawyer to confirm.

6.18 Construct the strongest possible case for Inmon's normalized enterprise warehouse over Kimball's approach, using Kestrel as the example — not in the abstract. Then name the specific property of Kestrel that makes the case fail, and describe the company for which it would succeed.

6.19 † §6.4 argues for storing net_revenue_cents even though it is derivable from other stored columns. This is deliberate redundancy. Find the argument against it — what can go wrong with a stored derived column that cannot go wrong with a computed one? Design the test that closes the gap.

Part D — The Kestrel Platform ⭐⭐⭐

6.20 — Increment 6: the model on paper.

Three documents in platform/docs/model/. No code this chapter; Chapter 19 builds these in dbt.

(a) bus-matrix.md. Build Kestrel's matrix yourself from Chapter 1's business description — at least six processes and eight dimensions. Mark, in a separate list, every cell you are unsure about. There should be several, and they are the questions you take to the business.

(b) grain-declarations.md. One sentence per fact table, in business language, six to eight of them. For each, list two questions it can answer and one it cannot. That second list is what tells you whether the grain is right.

(c) dim-customer.md. Every attribute, its SCD type, and — the part that matters — one sentence of justification for every Type 2 attribute naming the business question that requires history. Any attribute you cannot justify becomes Type 1.

6.21 † Add platform/docs/model/definitions.md, resolving the five ambiguities from §6.8 in your own words: what counts as an order, what net_revenue_cents means, which region a customer belongs to, whether promotions apply per order or per line, and when a session ends.

For each, write the definition, the alternative you rejected, and who at Kestrel would have to agree. This file is the artifact Chapter 1's Case Study 1 says survived three technology migrations; treat it accordingly.

Reflection

A. §6.1 argues that modeling forces the business to agree with itself, and that this is uncomfortable. Have you seen an organization avoid a definitional disagreement by keeping two systems? What did it cost?

B. Mistake 8 in §6.9 — a model that mirrors the source — is called the hardest to argue against because the copy works. What evidence would persuade a skeptical team to invest in a modeling layer before the refactor that breaks everything?