Implement Slowly Changing Dimension Type 2 history tracking in a Delta lakehouse.

TL;DR: SCD Type 2 keeps history by never updating in place: when an attribute changes, you expire the current row (set is_current=false and end_date) and insert a new current row. In Delta Lake that is a single MERGE INTO with the staged-double-row trick, so the close and the open commit atomically and the job is idempotent on re-run.

How to approach it. State what SCD2 buys you (auditable history of dimension changes) versus SCD1 (overwrite, no history). Define the bookkeeping columns, then write the MERGE and explain why it must be one atomic transaction rather than a separate UPDATE then INSERT.

A strong answer. The dimension carries effective_date, end_date (NULL while current), and is_current. A change produces two writes: expire the old version, insert the new one. Doing that as two statements opens a window where a reader sees zero or two current rows. Delta's MERGE gives ACID atomicity, and a small staging trick lets one MERGE both close and open a row.

MERGE INTO dim_customer AS t
USING (
  -- row that matches an existing key -> will CLOSE the current version
  SELECT s.customer_id AS merge_key, s.*  FROM updates s
  UNION ALL
  -- same payload with NULL key -> can only INSERT the new version
  SELECT NULL AS merge_key, s.*
  FROM updates s
  JOIN dim_customer t ON s.customer_id = t.customer_id
  WHERE t.is_current = true AND s.attrib <> t.attrib
) staged
ON t.customer_id = staged.merge_key AND t.is_current = true
WHEN MATCHED AND t.attrib <> staged.attrib THEN
  UPDATE SET t.is_current = false, t.end_date = staged.effective_date
WHEN NOT MATCHED THEN
  INSERT (customer_id, attrib, effective_date, end_date, is_current)
  VALUES (staged.customer_id, staged.attrib, staged.effective_date, NULL, true);

The trick: a changed key appears twice in staged, once with its real merge_key (which MATCHES and closes the old row) and once with merge_key = NULL (which can never match, so it INSERTs the new current row). The s.attrib <> t.attrib guard is what makes it idempotent: re-running with unchanged data matches nothing and writes nothing, which matters because pipelines retry. Delta's optimistic concurrency and transaction log keep the whole MERGE atomic, so a reader never sees a moment with two current rows or zero.

Key takeaways

• One atomic MERGE, never UPDATE-then-INSERT: the staged NULL-key row opens the new version while the matched row closes the old.
• The <> change guard is the idempotency lever: re-runs on unchanged data are no-ops.
• As-of queries fall out for free: effective_date <= :d AND (end_date > :d OR end_date IS NULL).
• Dedupe to the latest change per key per batch before the MERGE, or you get overlapping validity windows.

What interviewers probe next.

• "Why not UPDATE then INSERT?" Two statements are not atomic; a failure between them leaves the key with no current row or two. MERGE is one transaction.
• "How do you query as-of a date?" WHERE effective_date <= :d AND (end_date > :d OR end_date IS NULL).
• "Delta vs Iceberg vs Hudi for this?" All give ACID table semantics; Delta uses a JSON-then-Parquet transaction log with optimistic concurrency, Iceberg uses a manifest tree that eases schema/partition evolution, Hudi is tuned for streaming upserts (copy-on-write vs merge-on-read).
• "Late-arriving or out-of-order updates?" Order the staged set by event time and dedupe to the latest change per key per batch before the MERGE.

Common mistakes.

• Updating the dimension in place, destroying history (that is SCD1).
• Two separate statements, opening a non-atomic window.
• Omitting the <> change guard, so every run rewrites unchanged rows and breaks idempotency.
• Forgetting to dedupe multiple changes to the same key within one batch, producing overlapping validity windows.