History Erased

A medium AI Coding interview practice problem on DataDriven.

Domain

AI Coding

Difficulty

medium

Seniority

L5

Asked in

Snowflake

Problem

dim_customer is supposed to be SCD-2: every change to address/email creates a new row with effective_from / effective_to and is_current. After today's incremental run, the table has exactly one row per customer , history wiped. Run tests/test_scd2_history.py to reproduce. Fix sql/merge_dim_customer.sql so changes append a new version and close the old one, instead of overwriting.

Yesterday never happened.

Worked solution and explanation

The trap

This is SCD-2 history preservation wearing an incremental merge costume. The real question: when a customer's email or address changes, can you APPEND a new version and CLOSE the old one instead of overwriting it? Anyone can write a MERGE. The trap is when matched then update set , it mutates the current row in place, and the row it mutates is the exact history SCD-2 exists to keep. Run it once and every customer collapses to a single row; yesterday's address is gone, and because stg_customer is a snapshot and not a log, it is gone for good.

Starter: in-place update

when matched ... then update set email = s.email, effective_from = $RUN_DATE rewrites the live row. One row per customer, forever. The prior email/address and its date range are overwritten with no trace.

SCD-2: close then append

when matched first closes the old row (effective_to, is_current = false); a second matched branch inserts the new version. Two rows survive: the historical range and the current one.

The fix, branch by branch

merge into dim_customer t
using stg_customer s
  on t.customer_id = s.customer_id and t.is_current = true
/* 1. change detected: close the current row */
when matched and (t.email <> s.email or t.address <> s.address)
  then update set
    effective_to = $RUN_DATE,
    is_current   = false
/* 2. change detected: append the new version */
when matched and (t.email <> s.email or t.address <> s.address)
  then insert (customer_id, email, address, effective_from, effective_to, is_current)
  values (s.customer_id, s.email, s.address, $RUN_DATE, null, true)
/* 3. brand-new customer: first version */
when not matched
  then insert (customer_id, email, address, effective_from, effective_to, is_current)
  values (s.customer_id, s.email, s.address, $RUN_DATE, null, true)

Step 1: Scope the match to the current row

Add and t.is_current = true to the on clause so staging only ever compares against the live version. Without it, a customer with closed history rows matches multiple times and the change predicate fires against stale versions.

Step 2: Close the old version on change

when matched and (t.email <> s.email or t.address <> s.address) then update set effective_to = $RUN_DATE, is_current = false. This is a real update, but it only writes the bookkeeping columns , it never touches email or address, so the historical values stay intact.

Step 3: Append the new version

A second when matched branch inserts the changed row with effective_from = $RUN_DATE, effective_to = null, is_current = true. when not matched handles a brand-new customer identically. The customer now has two rows: one closed, one current.

Common follow-up questions

• What changes if `stg_customer` can arrive with `email` as `NULL`? Does `t.email <> s.email` still detect the change? (`<>` is unknown for NULLs, so a value going to or from NULL is silently missed without `is distinct from`.)
• How would you port this to Postgres, whose `MERGE` rejects `insert` in a matched branch? (Dialect portability: close in the MERGE, append with a separate INSERT.)
• How do you handle a hard delete , a customer who vanishes from `stg_customer`? (SCD-2 close-out on disappearance is not covered by a MERGE that only acts on-match.)