Slowly Changing Dimension Type 2

Maintain a dim_customers SCD2 table from a staging snapshot. Lineage exposes both the active row and the closed-out historical row.

The lineage

The SQL

CREATE OR REPLACE TABLE stg_customers_snap (
  customer_id NUMBER,
  email STRING,
  segment STRING,
  snapshot_at TIMESTAMP
);

CREATE OR REPLACE TABLE dim_customers_scd2 (
  customer_sk NUMBER AUTOINCREMENT,
  customer_id NUMBER,
  email STRING,
  segment STRING,
  effective_from TIMESTAMP,
  effective_to TIMESTAMP,
  is_current BOOLEAN
);

MERGE INTO dim_customers_scd2 t
USING (
  SELECT customer_id, email, segment, snapshot_at
  FROM stg_customers_snap
) s
  ON t.customer_id = s.customer_id AND t.is_current = TRUE
WHEN MATCHED AND (t.email <> s.email OR t.segment <> s.segment) THEN UPDATE SET
  effective_to = s.snapshot_at,
  is_current = FALSE;

INSERT INTO dim_customers_scd2 (customer_id, email, segment, effective_from, effective_to, is_current)
SELECT s.customer_id, s.email, s.segment, s.snapshot_at, NULL, TRUE
FROM stg_customers_snap s
LEFT JOIN dim_customers_scd2 t
  ON t.customer_id = s.customer_id AND t.is_current = TRUE
WHERE t.customer_id IS NULL OR (t.email <> s.email OR t.segment <> s.segment);

What the lineage shows

• email and segment in dim_customers_scd2 are fed by stg_customers_snap of the same name.
• effective_from / effective_to derive from snapshot_at, not a stored column.
• is_current is purely derived state, easy to spot in the lineage panel.