Model a dimension and its history
A dimension describes the things facts are about: customers, products, stores. Its attributes change, and every change forces a choice, because a fact can be reported under the attributes that were true when it happened (as-was) or under today's (as-is). No single stored version can answer both. Most dimensions get that choice made once, for the whole table, by whoever writes the loader. Make it per attribute, by whoever reads the report.
You will end with a dimension whose grain, history type per attribute and keys are decided on purpose, with the cost of each written down.
Work through these in order.
Work through it
Declare the grain of the facts it serves
Write one sentence: one row equals one what. If it needs an "and" joining two different things, or an aggregate word ("plus totals"), the grain is mixed and sums will double count. Default to the finest grain the source emits: grain is lossy downward only, and storing a billion 100-byte events costs about $2.30 a month.
Reuse a conformed dimension before building a new one
If the entity already has a shared dimension, point at it. Two fact tables can be compared along a dimension only if it means the same thing in both, and conformance turns N(N−1)/2 pairwise reconciliations into N agreements. Check the bus matrix: an empty cell is a question that will cost a project.
For each attribute, ask which question the business needs
As-was (revenue by the region the customer was in when they bought), as-is (by the region they are in today), or both. Answer it per attribute, not per table: one customer dimension can hold a Type 1 email, a Type 2 territory and a Type 4 credit band at the same time.
Pick the type that answers it, and price it
- Type 1, overwrite: only as-is survives, and every historical report restates on the next load. Fine for a corrected typo; catastrophic for a reorganized territory.
- Type 2, a new row per change: the join on the stored key gives as-was, the current row gives as-is. Versions grow without bound, and nothing collects them.
- Type 3, a previous-value column: one step of history, for reporting both ways during a transition.
- Type 4, a mini-dimension: for attributes that change often. Type 2 grows as members × change rate: 5 million customers × 6 credit-band changes a year is 30 million new rows a year; a mini-dimension of the banded values has 20 rows.
- Type 6, both answers on every row: trivial reads both ways, and a backfill of every historical row of a member at each change.
Key the versions
Versions need an identity that the business key cannot carry: a surrogate key. A key lookup is a serialization point in an otherwise parallel load. A deterministic hash of the natural key removes it and makes loads order-independent, but writes into every downstream row the assumption that the natural key is stable and unique.
Load late facts against the version that was true then
A fact for event time T that arrives three days late must join to the version effective at T, so the loader range-searches the effective interval instead of taking the current row.
Worked example
A retailer's customer dimension: 5 million customers. Email is Type 1 (history does not matter). Sales territory is Type 2 (finance reports as-was). Credit band changes about six times a year, so it goes into a Type 4 mini-dimension instead of adding 30 million Type 2 rows a year. Keys are surrogate keys from a lookup, because two source systems reuse customer ids. Late orders join by effective date.