A unified historization approach to ensure consistency, traceability, and trust in your common data platform.
Introduction
Modern data platforms must support data arriving in different shapes. Some source systems deliver full snapshots, others provide Change Data Capture (CDC) events. If handled inconsistently, this creates complexity, redundant logic, and eroded trust in the data.
The key is to normalize both delivery styles into a single historization model: bi-temporal Slowly Changing Dimension Type 2 (SCD2). This approach preserves full history and ensures that downstream consumers always work with consistent, auditable data — regardless of the ingestion format.
Why Snapshots and CDC Both Matter
- Snapshots: easy to produce but heavy to store, giving you a “point-in-time” view.
- CDC: efficient and granular, but depends on source capabilities.
In a heterogeneous data landscape, you rarely get only one. A platform must handle both gracefully.
The Role of Bi-Temporal SCD2
SCD2 keeps multiple versions of a record, instead of overwriting. Each record has valid_from and valid_to dates.
Bi-temporal adds system time:
- Business validity: when the data was true in reality.
- System validity: when the data was loaded and known by the platform.
This dual timeline allows you to answer two critical questions:
- What was true at a given point in the business process?
- What did we know, and when did we know it?
Converting Snapshots into SCD2
- Ingest the snapshot (full table).
- Compare it to the previous snapshot.
- Detect differences: new, updated, deleted rows.
- Translate them into SCD2 operations:
- Insert new records.
- Close existing rows and insert new ones for updates.
- Close rows for deletes.
Even though snapshots don’t give you deltas, the platform derives them and builds complete SCD2 history.
Converting CDC into SCD2
CDC provides explicit deltas:
- Insert → add new SCD2 row.
- Update → close current row, insert a new one.
- Delete → close the row’s validity window.
CDC maps naturally to SCD2, but now the logic is unified with snapshots.
Press enter or click to view image in full size
Benefits of a Unified Approach
- Consistency — downstream consumers always see bi-temporal SCD2.
- Auditability — both business and system time are captured.
- Flexibility — sources can evolve from snapshots to CDC without breaking downstream models.
- Trust — answers both “what happened in reality” and “what did we know back then”.
Key Takeaways
- Snapshots and CDC are just different delivery mechanisms.
- A bi-temporal SCD2 historization layer harmonizes them.
- This design creates a uniform, auditable, and trustworthy foundation for analytics, governance, and corrections.
References
- Inmon, Bill (2002). Building the Data Warehouse.
- Hultgren, Hans (2012). Modeling the Agile Data Warehouse with Data Vault.
- Slowly Changing Dimensions (Kimball Group)_