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

TL;DR: SCD Type 2 preserves history by refusing to overwrite: when an attribute shifts, you retire the current row (set is_current=false and end_date) and add a fresh current row. On Delta Lake that becomes one MERGE INTO using the staged-double-row trick, so closing the old and opening the new commit together atomically and re-running the job changes nothing.

How to approach it. Explain what SCD2 gives you (an auditable trail of dimension changes) against SCD1 (overwrite, no history). Lay out the bookkeeping columns, then write the MERGE and say why it has to run as a single atomic transaction instead of a separate UPDATE followed by 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.