Slowly changing dimensions: SCD types with real SQL

2024-03-01  customer 42  region = "LATAM"   -> ordered $500
2025-01-15  customer 42  region = "EMEA"    -> ordered $300

If you overwrite the region, the 2024 order retroactively becomes EMEA revenue and your historical dashboards silently change. If you keep both versions, each order joins to the region that was true when it happened.

merge into dim_customer t
using stg_customer s on t.customer_id = s.customer_id
when matched then update set
  t.region     = s.region,
  t.email      = s.email,
  t.updated_at = current_timestamp()
when not matched then insert (customer_id, region, email, updated_at)
values (s.customer_id, s.region, s.email, current_timestamp());

Correct for fixing typos, correcting bad source data, and attributes nobody reports on historically. Cheap and simple — just be sure nobody needs the old value.

create table dim_customer (
  customer_key  bigint,        -- surrogate key: unique per VERSION
  customer_id   bigint,        -- natural key: stable across versions
  region        string,
  segment       string,
  valid_from    timestamp,
  valid_to      timestamp,     -- '9999-12-31' for the live row
  is_current    boolean
);

-- 1) close out the row whose tracked attributes changed
update dim_customer t
set valid_to = current_timestamp(), is_current = false
from stg_customer s
where t.customer_id = s.customer_id
  and t.is_current
  and (t.region, t.segment) is distinct from (s.region, s.segment);

-- 2) insert the new version
insert into dim_customer
select
  hash(s.customer_id, current_timestamp()) as customer_key,
  s.customer_id, s.region, s.segment,
  current_timestamp(), timestamp '9999-12-31', true
from stg_customer s
left join dim_customer t
  on t.customer_id = s.customer_id and t.is_current
where t.customer_id is null
   or (t.region, t.segment) is distinct from (s.region, s.segment);

Then facts join on the surrogate key, not the natural key:

-- point-in-time join: the version that was active when the order happened
select d.region, sum(f.net_amount) as revenue
from fact_orders f
join dim_customer d
  on d.customer_id = f.customer_id
 and f.ordered_at >= d.valid_from
 and f.ordered_at <  d.valid_to
group by 1;

{% snapshot customers_snapshot %}
{{ config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='check',
    check_cols=['region', 'segment']
) }}
select * from {{ source('raw', 'customers') }}
{% endsnapshot %}

Run dbt snapshot and you get dbt_valid_from, dbt_valid_to and dbt_scd_id maintained automatically. This is one of the strongest arguments for dbt in a modern ELT stack.

• Joining facts on the natural key in a Type 2 dimension — you'll fan out to every version.
• Using != instead of is distinct from — nulls make change detection silently skip rows.
• Nullable valid_to — use a far-future sentinel so range joins work without coalesce.
• Tracking every column as Type 2 — one noisy column and your dimension doubles weekly.
• No unique test on (natural key, is_current) — duplicated "current" rows are the classic SCD bug.