Snowflake Debug: History Erased

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.

sql/merge_dim_customer.sql

/* sql/merge_dim_customer.sql
   Incremental merge into dim_customer.
   Source: stg_customer (raw daily snapshot)
   Target: dim_customer (SCD-2)
   ENV variables: $RUN_DATE = today's run date. */

merge into dim_customer t
using stg_customer s
on t.customer_id = s.customer_id
when matched and (
    t.email <> s.email
    or t.address <> s.address
) then update set
    email = s.email,
    address = s.address,
    effective_from = $RUN_DATE,
    is_current = true
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)

/* sql/merge_dim_customer.sql
   Incremental merge into dim_customer.
   Source: stg_customer (raw daily snapshot)
   Target: dim_customer (SCD-2)
   ENV variables: $RUN_DATE = today's run date. */

merge into dim_customer t
using stg_customer s
on t.customer_id = s.customer_id
when matched and (
    t.email <> s.email
    or t.address <> s.address
) then update set
    email = s.email,
    address = s.address,
    effective_from = $RUN_DATE,
    is_current = true
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)

Active Now|Sr. Data Engineer (L5)|||5.7k Attempts|2.0k Solves|