Slowly Changing Dimensions and History

Picture a churn model that scores 0.94 AUC offline and 0.71 in production. The cause is one column: customer_segment, joined from the customer dimension, which the CRM overwrites to at_risk when an account manager flags a customer, usually weeks before they leave. The training set was built by joining March events to today's dimension, so every churner already carried the label that predicted their churn. Nobody wrote a leaky feature. The dimension table simply had no memory, and the join filled the gap with the future.

How a dimension handles change is a modelling decision with a name, the slowly changing dimension type, and it decides which questions about the past the warehouse can answer at all. The wide-table trade-off in dimensional-modelling-and-denormalisation touches this; here the subject is the history itself.

The types, by what they remember

Type 1 overwrites. The row holds the current value and nothing else. Reports re-slice all history by today's attribute, which is sometimes what the business wants ("revenue by current region") and always what training data does not want.

Type 2 adds a row. Each change closes the current version and inserts a new one with a fresh surrogate key and a validity interval \([t_{\text{from}}, t_{\text{to}})\). A fact at time \(t\) for natural key \(k\) joins to the unique version satisfying

Half-open intervals matter. With closed intervals, a change at exactly midnight matches two versions and the join silently duplicates facts.

Type 3 adds a column. A previous_region next to region keeps exactly one prior value. It suits a single planned reorganisation where reports need "old versus new" side by side, and it forgets everything before that.

Type 6 combines them. Kimball Group's hybrid is a type 2 row with a type 3 column that is overwritten as type 1, so each historical row also carries the current value; the name comes from \(1+2+3 = 6\) (Kimball Group, 2013, Design Tip #152: Slowly Changing Dimension Types 0, 4, 5, 6 and 7). A fact can then be grouped by the attribute as it was or as it is, without a second join. The price is that every change to the current value rewrites every historical row for that key.

The row count you are signing up for

Type 2 cost scales with change frequency, not entity count. Take 10 million customers where 2% change a tracked attribute each month. That is 200,000 new versions a month, 2.4 million a year, and after three years 17.2 million rows, a 72% increase. Track last_login_date in the same dimension instead, changing daily for 30% of customers, and it is 3 million versions a day, over a billion a year. Volatile attributes belong in a separate mini-dimension or in facts, not in the type 2 history.

Maxime Beauchemin argued the opposite trade in 2018: skip type 2, write a full snapshot of the dimension as a new partition every day, and accept the duplication because "storage and compute are dirt cheap compared to engineering time" (Beauchemin, 2018, Functional Data Engineering). For the same 10 million customers that is 3.65 billion rows a year, mostly identical, which columnar compression absorbs far better than the raw count suggests. The disagreement is real and unresolved. Snapshots are trivially reproducible and immune to surrogate-key bugs, while type 2 is compact, answers "when exactly did this change" directly, and loses nothing between snapshots.

Two clocks: bitemporal modelling

Type 2 records one timeline, and it is ambiguous which. If a customer moved on 1 March and the move was entered on 15 March, a row whose \(t_{\text{from}}\) is 15 March describes the database, while 1 March describes the world. Bitemporal modelling keeps both: a valid-time interval for when the fact was true and a transaction-time interval for when the database believed it. A query then fixes two coordinates, \(t_v\) for the moment being described and \(t_k\) for the state of knowledge:

SQL:2011 standardised both: application-time period tables for valid time and system-versioned tables for transaction time (Kulkarni & Michels, 2012, Temporal features in SQL:2011, SIGMOD Record 41(3)). Engine support remains uneven.

The two clocks matter most for training data. A feature for a prediction made at time \(t\) must be read with \(t_k = t\), as the system knew it then, not with \(t_v = t\) as later corrected. A retroactive correction is information the model would not have had. This is the dimension-table half of point-in-time-correctness; a feature store cannot supply as-of values that the upstream dimension never retained.

When it breaks

Late-arriving dimension changes. A change entered with a back-dated effective date splits an existing type 2 interval. Facts already loaded against the old version now point at the wrong surrogate key, and fixing them means re-keying facts, which few pipelines are built to do.

Change detection by hashing all columns. Hashing every attribute to detect change creates a new version whenever a column nobody cares about moves, and the dimension fills with versions that differ only in a timestamp.

Deletes have no natural representation. A source row that disappears must close the current version, or the dimension reports a deleted customer as current forever. CDC feeds make deletes visible; full-extract comparisons need an explicit anti-join.

History collides with erasure. Type 2 multiplies every personal attribute across versions, and snapshots multiply it across partitions, so an erasure request touches every copy. Designing history without retention-deletion-and-erasure in view creates the hardest deletion problem on the platform.