Summary and key takeaways
The reframe. Lower layers have an oracle (a checksum, a plan cost, a watermark). This mechanism does not. A badly modeled system works and lies. The scarce resource changes from disk access to agreement between people. Physical access cost has mostly stopped mattering (columnar storage, compression, pruning, broadcast joins), so I/O arguments about normalization are usually obsolete. The real reasons to choose a shape are semantic and organizational.
The four primitives
- Grain: what one row is. Store the finest grain the source emits; aggregation cannot be undone.
- Identity: when two references are the same thing. Always declared, never discovered.
- Time: which version of an entity a fact joins to (as-was or as-is).
- Boundary: where one model ends and another begins.
Modeling styles are positions on three choices: early or late binding, precompute or compute on read, and who pays for the join (modeler once, or every reader forever). Decide per number of consumers.
Mechanisms in one line each:
| Mechanism | One-line lesson |
|---|---|
| Grain declaration | One row per one thing; an "and" or an aggregate word means mixed grain and double counts |
| Normalization | Prevents update anomalies; in append-only stores it only protects a single point of definition |
| Star | A rate-of-change split: events versus descriptions |
| Snowflake | Only where the sub-entity has an owner; the dominant cost is path ambiguity |
| Conformed dimensions | O(N²) reconciliations become O(N) agreements; the bus matrix maps what is answerable |
| SCD types | Pick per attribute; storing both as-was and as-is costs a version chain |
| Surrogate keys | Needed because versions need an identity; hashing buys parallel loads but hard-codes trust in the business key |
| Additivity | Stocks cannot be summed over time; ratios must be recomputed from components |
| Fact types | Transaction (default), periodic snapshot (stocks), accumulating snapshot (lags, mutates), factless (absence) |
| Bridge tables | Restore additivity by asserting an allocation no data can verify |
| Vault / anchor | Late binding: resilience and audit, paid by readers; the presentation layer stays |
| OBT | A materialized view without a refresh policy |
| Semantic layer | A guard for additivity and join paths; one definition per metric |
| Entity resolution | Identity by threshold; every count restates when clusters move |
| Hierarchies | Every read-optimized encoding pays per subtree on a move; versioning is always asked late |
| Nesting | Embed only children with no life of their own; nesting keeps grain, flattening does not |
| Medallion | Replay checkpoints at trust boundaries; worthless without published guarantees |
| Event modeling | Keeps every future question open; pays in snapshots, frozen semantics, retractions |
What the layer forbids: recovering a grain you did not store; summing across a non-additive dimension; joining across grains without an unverifiable allocation; answering both as-was and as-is from one version; centralizing meaning without restating history; encoding agreement in a schema.
Laws that repeat:
- Granularity mismatch generates amplification (Type 6, OBT, closure table, nested update).
- Costs are conserved, only relocated (to definitions, readers, storage, or headcount).
- Variance costs more than volume (Type 4).
- Immutable substrate grows indirection and a collector (SCD2, event sourcing). A missing collector means unbounded growth.
- Independence is assumed and rarely true (single membership per fact; independent match features).
- Unstated meaning is re-derived per consumer, producing variants deterministically.
- Coordination cost scales with the people who must agree, not the data; shared vocabulary turns O(N²) into O(N).