SCD Type 2 conformed customer dimension

Implementation challenges · 0/4

1. 1. Build the cross-reference

   Resolve CRM and service IDs into a single business key.

2. 2. Detect changes with a hash

   Show hint

   Hash Type 2 attributes to compare incoming vs current rows.

3. 3. Expire and insert

   Close current rows (valid_to, is_current=0) then insert new versions in one transaction.

4. 4. Add the unknown member

   Insert customer_sk = -1 so facts never drop on late dimension rows.

Expected result

52,113 dimension rows; 4,107 customers with >1 version.

Validation tests

• Exactly one is_current row per business key
• No overlapping validity ranges
• Fact join loses 0 rows

Anti-patterns reviewers look for

• Using natural keys in facts
• Type 2 on every column
• No unknown member