SCD type 2: keep history in a dimension, with MERGE, dbt and AUTO CDC

SCD type 2 keeps history in a dimension table by adding a new row each time a tracked value changes, instead of overwriting it. Each row carries a surrogate key, the date it became valid, the date it stopped being valid and a current flag, so an order placed last year is still reported against the city, segment or price list that was true last year.

It is one of the Slowly Changing DimensionsSlowly Changing DimensionsThe ways a warehouse handles a change to a descriptive record, such as a customer's address: type 1 overwrites it, type 2 adds a new dated version, and type 3 keeps the previous value in an extra column. Kimball named: type 1 overwrites, type 2 adds a row, type 3 keeps one previous value in an extra column. Type 2 is the one most warehouses use for anything the business reports on.

A worked example

Customer 42 orders once a month: £120, £80, £200, £150, £90 and £60. In month 3 they move from Leeds to York; in month 5 they move from the Retail to the Business segment.

With type 1, the dimension has one row that now says York and Business. Revenue by city reports York £700, including the £200 they spent while living in Leeds. Anyone reconciling with last quarter's report finds the numbers moved.

With type 2, the dimension holds 3 rows:

Revenue by city reports Leeds £200 and York £500, the same as it did at the time.

Try itOne customer, 6 months, 3 ways to store the change

Type 1Type 2Type 3

Month

6

key	city	segment	valid from	valid to	current
1	Leeds	Retail	month 1	month 3	false
2	York	Retail	month 3	month 5	false
3	York	Business	month 5	null	true

Leeds£200

York£500

History kept

The columns

• Surrogate key (customer_sk): one per row, because the business key (customer_id) is no longer unique. Kimball has facts store the surrogate key in force when they were loaded.
• valid_from and valid_to: the period the row describes. Leave valid_to null on the current row, or set a far-future date such as 9999-12-31 so range filters need no null check.
• is_current: a flag for the latest row, so "current state" queries need no date logic.

Track only columns whose history matters. A corrected typo in a name should overwrite (type 1) rather than add a version.

Type 2 with MERGE on Delta Lake

One MERGE can close the old row and insert the new one. The trick: send each changed customer twice, once with its key (to match and close the current row) and once with a null key (which never matches, so it inserts).

CREATE TABLE IF NOT EXISTS dim_customer (
  customer_sk BIGINT GENERATED ALWAYS AS IDENTITY,
  customer_id BIGINT,
  city STRING,
  segment STRING,
  valid_from TIMESTAMP,
  valid_to TIMESTAMP,
  is_current BOOLEAN
);

MERGE INTO dim_customer AS t
USING (
  -- every incoming row, keyed: closes a changed current row or inserts a new customer
  SELECT s.customer_id AS merge_key, s.* FROM staged_customers AS s
  UNION ALL
  -- changed customers again, with a null key: inserts their new version
  SELECT NULL AS merge_key, s.*
  FROM staged_customers AS s
  JOIN dim_customer AS d
    ON s.customer_id = d.customer_id AND d.is_current
  WHERE NOT (s.city <=> d.city AND s.segment <=> d.segment)
) AS u
ON t.customer_id = u.merge_key AND t.is_current
WHEN MATCHED AND NOT (t.city <=> u.city AND t.segment <=> u.segment) THEN
  UPDATE SET valid_to = u.updated_at, is_current = false
WHEN NOT MATCHED THEN
  INSERT (customer_id, city, segment, valid_from, valid_to, is_current)
  VALUES (u.customer_id, u.city, u.segment, u.updated_at, NULL, true);

staged_customers holds one row per customer with its updated_at. Unchanged customers match but fail the change test, so nothing happens to them. The <=> operator compares nulls as equal, so a null city does not count as a change every run.

Two cautions. If a batch holds 2 changes for the same customer, the merge fails, because several source rows would update one target row; keep only the latest per key, or process the batch in order. And rows that arrive late are applied as if they were new; for out-of-order change feeds, use AUTO CDC below.

Type 2 with AUTO CDC on Databricks

In a Lakeflow pipeline, AUTO CDC does all of this, including ordering:

from pyspark import pipelines as dp
from pyspark.sql.functions import col, expr

dp.create_streaming_table("dim_customer")

dp.create_auto_cdc_flow(
    target="dim_customer",
    source="customer_changes",
    keys=["customer_id"],
    sequence_by=col("updated_at"),
    apply_as_deletes=expr("operation = 'DELETE'"),
    except_column_list=["operation"],
    stored_as_scd_type="2",
)

The table gets __START_AT and __END_AT columns; __END_AT is null on the current version. sequence_by puts late changes in the right place. To keep history for some columns only, add track_history_except_column_list; changes to the listed columns then update the current row in place. Open-source Spark 4.2's Auto CDC does type 1 only.

Type 2 with a dbt snapshot

# snapshots/customers_snapshot.yml
snapshots:
  - name: customers_snapshot
    relation: source('crm', 'customers')
    config:
      unique_key: customer_id
      strategy: timestamp
      updated_at: updated_at
      hard_deletes: invalidate

dbt snapshot adds dbt_valid_from and dbt_valid_to. dbt recommends the timestamp strategy; use check with a list of check_cols when the source has no reliable updated_at. Since dbt 1.9, dbt_valid_to_current sets a far-future date instead of null.

Reading it: the point-in-time join

When facts store only the business key, join on the date as well:

SELECT d.city, SUM(f.amount) AS revenue
FROM fact_orders AS f
JOIN dim_customer AS d
  ON f.customer_id = d.customer_id
 AND f.order_ts >= d.valid_from
 AND (f.order_ts < d.valid_to OR d.valid_to IS NULL)
GROUP BY d.city;

Use >= on one side and < on the other, so an order placed at the exact moment of a change matches one row, not 2. For current-state reports, filter is_current instead.

What to do

1. Decide per column: history (type 2) or overwrite (type 1).
2. On Databricks pipelines, use AUTO CDC; in dbt, use snapshots; otherwise the MERGE above.
3. Deduplicate each batch to one row per key before a MERGE.
4. Test with a customer who changes twice in one day and with one who comes back to an old value.
5. For where this sits in a pipeline, see the medallion pipelines module of our data engineering course.