Chapter 6 — Key Takeaways (Data Modeling)

The page to have open while designing a schema. The most durable material in the book.

The four steps — the order IS the method

# Step The rule
1 Select the business process Not a report, not a department. An activity that generates measurable events.
2 Declare the grain One sentence, business language, written down. Before anything else.
3 Identify the dimensions Test: can you imagine grouping by it or filtering on it?
4 Identify the facts Test: is it numeric, and does summing it across rows mean something?

The grain determines which dimensions are available and which measures are valid. At order-line grain you can attach product; at order-header grain you cannot. At line grain shipping_cost is invalid without an allocation rule.

Grain rules: one sentence in business language · choose the finest grain the source supports (you can aggregate up, never disaggregate) · one fact table, one grain · write it at the top of the model file and test it — a declared grain with no test is a comment.

Why model at all — the three reasons, ranked

Reason Weight
Performance 🟢 weakest. Modern engines join fine. Don't model for this.
Comprehensibility 🟡 put the business rules in the model once so nobody has to know them again
Semantic stability 🔴 strongest. A stable interface over an unstable source.

The refactor test: source splits a table → one file changes, forty-one downstream objects untouched. Without the layer: nine days spent finding what broke.

Normalization vs. dimensional

OLTP OLAP
Rows touched 1–100 10⁵–10⁹
Columns touched most 3–8 of 40
Optimizes write integrity read throughput
Shape normalized denormalized

Analytics does not need update integrity because it does not accept user writes. So denormalize deliberately: storage is cheap, joins are not. Prefer star to snowflake — the human reading the model is the constraint.

Fact tables

Type Shape Use
Transaction one row per event, append-only the default
Periodic snapshot one row per entity per period levels, not events — inventory, balances
Accumulating snapshot one row per entity, updated in place pipelines with known milestones

Additivity — where a model error becomes a wrong dashboard

Sums across Example
Additive everything quantity, net_revenue_cents
Semi-additive not time on_hand_units_eod — 400 Mon + 400 Tue = 400, not 800
Non-additive nothing unit_price_cents, margin_pct, any ratio

⚠️ Every BI tool defaults to SUM. Defenses: name the column so the aggregation is obvious (on_hand_units_eod) · store the additive alternative alongside (units_received, units_shipped) · set the default aggregation in the semantic layer · test it.

Fact table conventions

  • Money in integer cents, every column ending _cents.
  • Store the sign convention in a comment and test it (discount_cents >= 0).
  • Every dimension key NOT NULL, pointing at a reserved "not applicable" member. Null FKs make rows vanish from inner joins.
  • Mark non-additive measures. Someone will sum them.
  • Store the metric (net_revenue_cents) rather than making each consumer re-derive it.
  • Keep the degenerate key (order_id) for traceability back to the source.

Dimensions

Wide, denormalized, small. dim_product 47K rows × 40 columns; fct_order_item 6.48M rows × 18. Spend columns freely in dimensions, carefully in facts.

Surrogate keys, because Type 2 history makes the natural key non-unique — that is the decisive reason. Also: source keys change, multi-source identity, marginal join speed. Costs: a lookup on every load, a debugging tax, and you must carry the natural key alongside. Exception: dim_date uses YYYYMMDD — dates never change their history.

SCD Behavior Use
0 never changes birth date, signup date
1 overwrite corrections
2 new row + valid_from/valid_to/is_current default when history matters
3 add a "previous" column exactly one prior value needed
4 mini-dimension huge, fast-changing attribute sets
6 1+2+3 "as-was" and "as-is" both queryable

Type 2 only where history has a named business meaning. It costs unbounded growth, harder fact loads (resolve the historical key), and an analyst decision that can be got wrong. Keeping bronze forever is what makes this recoverable.

Conformed dimensions and the bus matrix

A conformed dimension is shared identically across fact tables — that is what lets you compare units ordered vs. shipped vs. returned in one query.

Date Customer Product Warehouse Promotion Channel
Order placement ● ● ● ● ●
Shipment ● ● ● ●
Return ● ● ● ● ●
Inventory snapshot ● ● ●

Four jobs in one table: what to build (rows = facts, columns = dimensions) · build order (most shared dimensions first) · where the integration points are · a stakeholder can read it, which prompts "why can't we break returns down by promotion?" before you build.

The seven patterns

Pattern What
Degenerate dimension A business key with no attributes. Lives in the fact. Keep it for traceability.
Junk dimension Unrelated low-cardinality flags, combined. 4 flags → ≤40 rows.
Role-playing One dimension joined several times. Alias with views.
Bridge table Genuine many-to-many. Must carry an allocation weight summing to 1.0 per fact row — and test it.
Factless fact Only keys. For counting non-events, which no ordinary fact can answer.
Periodic snapshot One row per entity per period.
Accumulating snapshot One row per entity, updated through milestones.

The eight mistakes, by symptom

Symptom Mistake
Totals will not reconcile; everyone remembers a WHERE Mixed grain
The source can answer it, the warehouse cannot Grain chosen after columns
Rows silently vanish from a report Null foreign keys
Number wrong by roughly the row count Summed a non-additive measure
Revenue doubles when a dimension is added Fan trap
Numbers inflate by orders of magnitude Chasm trap (two facts joined via one dimension)
Nine joins per query; analysts email you instead Snowflaking by default
Every source refactor breaks the warehouse A model that mirrors the source ← most common

The thing nobody warns you about

Modeling forces the business to agree with itself. You cannot declare a grain without deciding what an order is. You cannot define net_revenue_cents without resolving cancellations, partial returns, and shipping.

When a technical argument goes in circles, check whether it is a proxy for a definitional one. Schema debates are comfortable; definitional debates require someone to concede.

If you must fabricate a number (an allocation), make the fabrication visible in the data — a weight column beside the allocated amount is worth more than a paragraph nobody reads. And write the rounding remainder rule, or reconciliation to the cent fails on a third of rows.