Slowly Changing Dimensions: Types 0 to 7

“Customer moved from Berlin to Munich. What happens to last year’s sales by city?” That one question is what SCD types answer.

1. All types at a glance

Scenario: customer C-42 moves from Berlin to Munich on 2026-06-01.

2. SCD Type 2 in detail

customer_key | customer_id | city    | tier   | valid_from | valid_to   | is_current
-------------|-------------|---------|--------|------------|------------|-----------
101          | C-42        | Berlin  | silver | 2024-03-10 | 2026-06-01 | false
257          | C-42        | Munich  | silver | 2026-06-01 | 9999-12-31 | true

• Surrogate key per version (101, 257); natural key (C-42) repeats.
• Half-open intervals [valid_from, valid_to) avoid overlaps/gaps at boundaries.
• valid_to = 9999-12-31 (not NULL) for current rows makes range predicates simple.
• Optional: version_no, row_hash (hash of tracked columns), is_deleted, audit columns.

How facts use it

At load time (preferred): look up the version valid at the event time and store its surrogate key in the fact:

SELECT o.order_id, d.customer_key, o.amount
FROM staging_orders o
LEFT JOIN dim_customer d
  ON d.customer_id = o.customer_id
 AND o.order_ts >= d.valid_from AND o.order_ts < d.valid_to;

Then “sales by city as it was” is a simple equi-join on customer_key. “Sales by current city” joins through customer_id to is_current = true (that’s type 7).

Implementing SCD2 with MERGE (Delta / Snowflake / BigQuery)

The trick: stage each changed key twice: one row to close the old version (matched by key) and one row to insert the new version (with a NULL merge key so it never matches).

MERGE INTO dim_customer t
USING (
  -- rows that update/close existing current versions
  SELECT s.customer_id AS merge_key, s.* FROM staged s
  UNION ALL
  -- rows that insert new versions for changed customers (merge_key NULL never matches)
  SELECT NULL AS merge_key, s.*
  FROM staged s JOIN dim_customer t
    ON s.customer_id = t.customer_id AND t.is_current AND s.row_hash <> t.row_hash
) s
ON t.customer_id = s.merge_key AND t.is_current
WHEN MATCHED AND t.row_hash <> s.row_hash THEN
  UPDATE SET t.is_current = false, t.valid_to = s.effective_ts
WHEN NOT MATCHED THEN
  INSERT (customer_key, customer_id, city, tier, row_hash, valid_from, valid_to, is_current)
  VALUES (xxhash64(s.customer_id, s.effective_ts), s.customer_id, s.city, s.tier, s.row_hash,
          s.effective_ts, TIMESTAMP '9999-12-31', true);

Brand-new customers fall into NOT MATCHED via the first branch (no current row to match). Unchanged customers match with equal hashes and do nothing.

dbt snapshots

{% snapshot customers_snapshot %}
{{ config(target_schema='snapshots', unique_key='customer_id',
          strategy='check', check_cols=['city', 'tier'], invalidate_hard_deletes=True) }}
SELECT * FROM {{ source('crm', 'customers') }}
{% endsnapshot %}

strategy='timestamp' (uses updated_at) is cheaper and more reliable when the source maintains it; check compares columns.

Databricks DLT / Lakeflow

CREATE FLOW customers_scd2 AS AUTO CDC INTO silver.dim_customer
FROM STREAM(bronze.customers_cdc)
KEYS (customer_id) SEQUENCE BY lsn
APPLY AS DELETE WHEN op = 'd'
STORED AS SCD TYPE 2
TRACK HISTORY ON city, tier;

3. Edge cases interviewers love

4. SCD2 vs snapshots vs time travel

5. Interview questions