Slowly changing dimensions, and the "as of when" problem

Dimensions change. Customers move, products get recategorised, sales reps change territory, plans get renamed. The question is what your warehouse remembers.

This is the slowly changing dimension problem, and it has a small number of standard answers with unglamorous names.

Type 1: overwrite#

Update the row. Keep only the current value.

Simple, cheap, and it silently rewrites history: every historical report now attributes that customer's entire lifetime to Madrid. Run a report from last year today and it will not match the copy you exported last year.

Correct when the old value has no analytical meaning — a corrected typo, a fixed email address. Wrong for anything you slice revenue by.

Type 2: new row per version#

Close the old row, insert a new one, and keep both. This is the workhorse.

Note that customer_key changed and customer_id did not. That is the mechanism: the fact table stores the surrogate key that was current when the event happened, so a February order points at row 4471 and is Berlin revenue forever. History is preserved automatically, with no special query logic.

-- Revenue as it was attributed 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   -- version-specific
group by 1;

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

Both are legitimate. They answer different questions, and a stakeholder asking "revenue by city" has no idea they just asked one of them. Ask.

Type 3: previous value column#

Keep city and previous_city side by side. Cheap, and only remembers one step back. Genuinely useful in one narrow case: when a reorganisation happened and everyone wants to see both cuts for a quarter. Otherwise, use type 2.

Type 6: all of the above#

A type 2 table that also carries the current value on every row.

Now both questions are answerable from one join, by choosing a column instead of writing a second join. Slightly redundant, considerably kinder to the analyst. This is what I reach for on the dimensions people actually slice by.

Building type 2 in practice#

You need a snapshot of the source over time. If you have CDC, you already have the change log. If you only have nightly full extracts, snapshot them — dbt snapshot does exactly this, and doing it by hand is about forty lines of merge.

-- Close any row whose tracked attributes changed since last run.
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);

-- Then insert the new versions.
insert into dim_customers (customer_key, customer_id, city, plan, valid_from, valid_to, is_current)
select md5(source.customer_id || current_timestamp::text),
       source.customer_id, source.city, source.plan,
       current_timestamp, timestamp '9999-12-31', true
from staging_customers source
left join dim_customers target
  on target.customer_id = source.customer_id and target.is_current
where target.customer_id is null
   or (target.city, target.plan) is distinct from (source.city, source.plan);

is distinct from rather than <> is deliberate: <> returns NULL when either side is NULL, so a city changing from NULL to Madrid would not be detected. This is the most common bug in hand-rolled SCD code.

The trap: late-arriving facts#

An order arrives three days late, but the customer's dimension row already moved on. Joining on is_current attributes it wrongly. The correct join uses the event time against the validity window:

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

This is what surrogate keys exist to avoid — resolve the key once at load time, using the event timestamp, and every downstream query becomes a simple equality join.