scd type 2 sql implementation pattern

TL;DR

Type 2 keeps history by never updating in place: each change expires the current row (set end_date and is_current = false) and inserts a new row with a fresh start_date. The standard implementation hashes the tracked columns so change detection is a single comparison, then does it in three steps: insert brand-new keys, expire-and-insert changed rows, leave the rest alone. Get the hash right and the rest is bookkeeping.

The query

scd type 2 sql implementation pattern

Use this when

• a dimension (customer, product, employee) needs full history of attribute changes
• downstream needs "what was true on date X" queries
• a data agent builds dimension loads and needs a repeatable pattern

Not for

• Type 1 (overwrite, no history) or Type 3 (keep one previous value) dimensions
• fact tables (facts are insert-only; don't SCD them)
• real-time CDC (this is a batch pattern)

Steps

1. Add the Type 2 columns to the dimension: a surrogate key, start_date, end_date (use a far-future date like 9999-12-31 for current rows), is_current flag, and a row_hash over the tracked attributes.

Expected output: the dimension DDL includes all five, with the current rows flagged.

2. In staging, compute the hash over exactly the attributes you track. Exclude metadata like load timestamps or every row will look "changed".

SELECT *, MD5(CONCAT_WS('|', name, email, tier, region)) AS row_hash
FROM staging_customers;

Expected output: staging rows carry a hash; identical attribute sets produce identical hashes.

3. Insert new natural keys that don't exist in the dimension yet, with start_date = current_date and is_current = true.

Expected output: new customers appear as current rows; row counts reconcile with staging.

4. For existing keys where the hash differs, expire the old row (end_date = current_date - 1, is_current = false) and insert the new row as current. Do both in the same transaction.

Expected output: changed customers have exactly one current row and a closed history row; unchanged rows are untouched.

5. Validate: no natural key has two current rows, history chains have no date gaps, and a point-in-time query (WHERE date BETWEEN start_date AND end_date) returns exactly one row per key.

Expected output: all three checks pass, so downstream "as of" queries are trustworthy.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstKJ9oM3MIbUjNYW3zaW2ZQ