Multiple Daily Updates in a Full-Snapshot SCD Type 2 Load
A CRM source sends 3 million account records daily to an SCD Type 2 dimension. A single account can have up to five updates per day; the source provides LAST_MODIFIED_DATE and an INGEST_SEQ, but timestamps can be equal for different updates and occasionally arrive out of order. The current mapping uses a dynamic lookup cache on ACCOUNT_ID and an Update Strategy to insert or update; recent checks show multiple current rows per account and some older values overwriting newer ones. The dimension has 40 million rows. The team wants one current row per account, full history, and deterministic results across reruns. Source extracts are full daily snapshots, not deltas, and include future-dated modifications that should be held, not applied.
1. Explain why the current mapping produces duplicate and out-of-order versions, and design a deduplication step before the SCD2 lookup.
Read the worked answer
The dynamic lookup sees rows one at a time, so several updates to the same account in one snapshot can each be treated as a new version. If records arrive out of order, an older record can be processed after a newer one. Equal timestamps make the result dependent on arrival order, which is not deterministic across reruns.
Deduplicate the snapshot before the SCD2 lookup. Sort by ACCOUNT_ID ascending, LAST_MODIFIED_DATE descending, and INGEST_SEQ descending. Keep only the first row per ACCOUNT_ID, using an Expression with a variable port that detects a key change, or use two aggregation stages: max LAST_MODIFIED_DATE per account, join back, then max INGEST_SEQ for ties. Feed only the winner into the dynamic lookup and Update Strategy.
Use a Sequence Generator for the dimension surrogate key only on insert, not on update. Set effective dates from the winning LAST_MODIFIED_DATE and close the prior current row. Because the source is a full snapshot, accounts absent from the snapshot need separate delete handling; do not treat absence as an update without business confirmation.
2. How should equal timestamps and future-dated modifications be handled deterministically?
Read the worked answer
Equal timestamps must be resolved by INGEST_SEQ, not by arrival order. If INGEST_SEQ can also repeat, define an explicit composite tie-breaker, such as a hash of the changed business columns, and document it; otherwise no design can guarantee the same winner on every run. The sort order must include all tie-breakers, and the deduplication must run before the lookup.
Future-dated modifications should be routed to a HOLD table when LAST_MODIFIED_DATE is later than the source extract time or an agreed threshold. Do not update the current dimension row until the effective time. On the next run, the held row can be re-evaluated. This prevents a future-dated update from closing a valid current row prematurely.
Reruns must be idempotent: the same snapshot and same sort produce the same winner, and the MERGE or dynamic lookup should result in zero net changes. Test by processing the same snapshot twice and comparing row counts, current-row flags, and effective dates.
3. What validation queries would prove the SCD2 dimension is correct?
Read the worked answer
Check one current row per ACCOUNT_ID and zero duplicates among current rows. Check that effective ranges do not overlap for an account and that END_DATE is greater than or equal to START_DATE. For each account, compare the latest version's descriptive values with the deduplicated source winner. Count inserts, updates, and unchanged rows, and confirm that the sum of versions matches the expected history for a sample of accounts.
Validate that future-dated records exist only in the HOLD table, not in the current dimension. Rerun the same daily snapshot and verify zero net changes. Sample 1,000 accounts and compare their source update history with the dimension versions in order. Alert on duplicate current rows, overlapping ranges, and any account whose current row does not match the source winner. These checks catch both duplicate-version and out-of-order problems before finance or sales users see them.