Slowly Changing Dimensions

Overview

Slowly changing dimensions, from Kimball dimensional modeling, define how a warehouse records changes to descriptive attributes. The most used variant is Type 2, which preserves full history by versioning rows.

How it works

Each dimension row has a surrogate key, the business key, tracked attributes, valid_from, valid_to and is_current. On a change, the pipeline expires the old row and inserts a new one.

-- close old version
UPDATE dim_customer SET valid_to = :ts, is_current = false
WHERE customer_id = :id AND is_current AND region <> :new_region;
-- open new version
INSERT INTO dim_customer
  (customer_id, region, valid_from, valid_to, is_current)
VALUES (:id, :new_region, :ts, '9999-12-31', true);

When loading facts, look up the surrogate key whose validity range contains the event timestamp. Reports grouped by region then attribute each sale to the region at the time of sale. For current-state views, filter on is_current.

Other types: Type 0 never changes, Type 1 overwrites, Type 3 stores one prior value, Type 4 splits fast-changing attributes into a mini-dimension, Type 6 combines 1, 2 and 3.

Pitfalls

• Detecting change by comparing every column is costly; compute a hash of tracked attributes and compare hashes.
• Gaps or overlaps in validity ranges. Add tests that each business key has contiguous, non-overlapping ranges.
• Late-arriving updates: a change dated in the past requires splitting a row and re-keying affected facts.
• Tracking attributes that rarely matter inflates history; choose tracked columns deliberately.
• Using the business key in fact tables, which loses the historical link.

Key takeaways

• Decide per attribute: overwrite, version, or keep previous value.
• Type 2 uses surrogate keys plus effective date ranges.
• Test range continuity and uniqueness of the current row.
• Use mini-dimensions for fast-changing attributes.