Slowly changing dimension, type 2

You meet it when#

A customer moved from Berlin to Madrid in March. Was February's revenue German or Spanish? Both answers are defensible; only one is what your stakeholder means. If the dimension was overwritten, neither is available.

The pattern#

customer_key | customer_id | city   | valid_from | valid_to   | is_current
        4471 | C8812       | Berlin | 2023-02-01 | 2026-03-14 | false
        9902 | C8812       | Madrid | 2026-03-14 | 9999-12-31 | true

customer_key changed; customer_id did not. The fact table stores the surrogate key that was current when the event happened, so a February order points at row 4471 and stays Berlin revenue forever, with no special query logic.

-- Attributed as it was at the time.
select d.city, sum(f.revenue_usd)
from fct_order_lines f
join dim_customers d on d.customer_key = f.customer_key
group by 1;

-- Re-attributed to where customers live now.
select current.city, sum(f.revenue_usd)
from fct_order_lines f
join dim_customers historical on historical.customer_key = f.customer_key
join dim_customers current
  on current.customer_id = historical.customer_id and current.is_current
group by 1;

Building it#

-- Close rows whose tracked attributes changed.
update dim_customers target
set valid_to = current_timestamp, is_current = false
from staging_customers source
where target.customer_id = source.customer_id
  and target.is_current
  and (target.city, target.plan) is distinct from (source.city, source.plan);

is distinct from, not <>. With <>, a city changing from NULL to Madrid returns unknown and the change is never detected — see anti-join for why. This is the most common bug in hand-written SCD code.

Two decisions that go wrong#

Which columns to track. Only the attributes with analytical meaning. Type-2 a column that updates on every source write — a last_seen_at — and you generate a new dimension row per customer per day until the dimension outgrows the fact table.

Late-arriving facts. An order that arrives three days late must join on the event time against the validity window, not on is_current:

join dim_customers d
  on d.customer_id = f.customer_id
 and f.ordered_at >= d.valid_from
 and f.ordered_at <  d.valid_to

Resolve the key once at load time using the event timestamp, and every downstream query goes back to a simple equality join.