Slowly changing dimensions (SCD Type 2)

Types 1, 2 and 3

Type 2 matters because facts must join to the dimension as it was when the fact happened. A March order from a customer who moved in May belongs to the old city in a March report.

The Type 2 columns

One MERGE, two actions

For a changed customer, the batch must do two things to the target: update the current row (close it) and insert the new version. One MERGE clause can only do one of those per source row. The standard trick stagesstage: A group of steps Spark can run without moving data between machines. A new stage starts at every shuffle. Learn more → every changed row twice:

1. 1
   Copy 1 keeps the real key as merge_key. It matches the current target row, and the MATCHED clause closes it.
2. 2
   Copy 2 has merge_key set to null, so it can never match. The NOT MATCHED clause inserts it as the new current version.
3. 3
   Brand-new customers only need copy 1: they do not match anything and are inserted.

spark.sql("""
    WITH changes AS (            -- rows that are new or differ from the current version
      SELECT s.*
      FROM updates s
      LEFT JOIN customers t ON t.customer_id = s.customer_id AND t.is_current
      WHERE t.customer_id IS NULL                  -- new customer
         OR NOT (t.city <=> s.city)                -- changed attribute (null-safe)
    ),
    staged AS (
      SELECT customer_id AS merge_key, * FROM changes          -- copy 1: closes the old row
      UNION ALL
      SELECT NULL AS merge_key, * FROM changes c                 -- copy 2: inserts the new row
      WHERE EXISTS (SELECT 1 FROM customers t
                    WHERE t.customer_id = c.customer_id AND t.is_current)
    )
    MERGE INTO customers t
    USING staged s
    ON t.customer_id = s.merge_key AND t.is_current
    WHEN MATCHED THEN UPDATE SET is_current = false, valid_to = s.changed_at
    WHEN NOT MATCHED THEN INSERT (customer_id, city, valid_from, valid_to, is_current)
      VALUES (s.customer_id, s.city, s.changed_at, NULL, true)
""")

WITH changes AS (            -- rows that are new or differ from the current version
  SELECT s.*
  FROM updates s
  LEFT JOIN customers t ON t.customer_id = s.customer_id AND t.is_current
  WHERE t.customer_id IS NULL                  -- new customer
     OR NOT (t.city <=> s.city)                -- changed attribute (null-safe)
),
staged AS (
  SELECT customer_id AS merge_key, * FROM changes          -- copy 1: closes the old row
  UNION ALL
  SELECT NULL AS merge_key, * FROM changes c                 -- copy 2: inserts the new row
  WHERE EXISTS (SELECT 1 FROM customers t
                WHERE t.customer_id = c.customer_id AND t.is_current)
)
MERGE INTO customers t
USING staged s
ON t.customer_id = s.merge_key AND t.is_current
WHEN MATCHED THEN UPDATE SET is_current = false, valid_to = s.changed_at
WHEN NOT MATCHED THEN INSERT (customer_id, city, valid_from, valid_to, is_current)
  VALUES (s.customer_id, s.city, s.changed_at, NULL, true)

Only changed rows are staged, so unchanged customers are not rewritten. With many tracked columns, compare a hash of them instead: t.row_hash <> s.row_hash, computed with null-safe concatenation (for example sha2(concat_ws('|', coalesce(city, '∅'), ...), 256)).

Querying history

spark.sql("""
    SELECT o.order_id, o.order_ts, c.city
    FROM orders o
    JOIN customers c
      ON c.customer_id = o.customer_id
     AND o.order_ts >= c.valid_from
     AND (c.valid_to IS NULL OR o.order_ts < c.valid_to)
""")

SELECT o.order_id, o.order_ts, c.city
FROM orders o
JOIN customers c
  ON c.customer_id = o.customer_id
 AND o.order_ts >= c.valid_from
 AND (c.valid_to IS NULL OR o.order_ts < c.valid_to)

Use a half-open interval (>= start, < end) so a fact exactly at a change time matches exactly one version. This is a range join: if it is slow, storing the surrogate key on the fact at load time avoids it.

Edge cases

• ✗Several changes for one key in one batch — The MERGE fails on duplicate matches, or keeps only one change. Either keep the latest per key (losing intermediate history), or apply changes in sequence order, one version per change, by building the versions with lead(changed_at) before merging.
• ✗Late, out-of-order changes — A change dated before the current version's start must be inserted in the middle of history, splitting a version. The simple MERGE assumes changes arrive in order; handle late ones separately or rebuild the key's history.
• ✗Re-running a batch — Without the change check, a rerun closes and reinserts every row again. The "differs from current" filter makes reruns no-ops.
• ✗Null attributes — t.city != s.city is null when either side is null, so a change to or from null is missed. Use <=> or compare hashes.
• ✗Deletes — Decide what a source delete means: close the current row with no new version, or insert a version flagged as deleted.

Common mistakes

• ✗Updating in place and calling it Type 2 — Without closing and inserting, history is lost.
• ✗Comparing attributes with != — Changes to or from null are missed.
• ✗Closed intervals for valid_from / valid_to — A fact at the boundary matches two versions.