Data Platforms: Warehouses, Delta Lake & Snowflake

-- Production Delta Lake / Snowflake MERGE for SCD Type-2
MERGE INTO curated_lake.dim_customer_scd2 AS target
USING (
    -- Staging updates unioned with dummy records to trigger inserts
    SELECT 
        s.customer_id, s.tier, s.address, s.update_timestamp,
        s.customer_id AS merge_key
    FROM staging_customer_updates s
    UNION ALL
    -- NULL merge_key bypasses MATCHED clause and forces an INSERT
    SELECT 
        s.customer_id, s.tier, s.address, s.update_timestamp,
        NULL AS merge_key
    FROM staging_customer_updates s
    JOIN curated_lake.dim_customer_scd2 t ON s.customer_id = t.customer_id
    WHERE t.is_current = TRUE 
      AND (s.tier <> t.tier OR s.address <> t.address)
) AS source
ON target.customer_id = source.merge_key AND target.is_current = TRUE

-- 1. Expire current record
WHEN MATCHED AND (target.tier <> source.tier OR target.address <> source.address) THEN
    UPDATE SET 
        target.end_date = CAST(source.update_timestamp AS DATE),
        target.is_current = FALSE

-- 2. Insert new current record
WHEN NOT MATCHED THEN
    INSERT (customer_sk, customer_id, tier, address, start_date, end_date, is_current)
    VALUES (
        UUID_NUMERIC(), 
        source.customer_id, 
        source.tier, 
        source.address, 
        CAST(source.update_timestamp AS DATE), 
        '9999-12-31', 
        TRUE
    );