Validating Historical Accuracy via Data Profiling

In this lesson, you will learn how to use automated data profiling to ensure that your Type 2 Slowly Changing Dimension (SCD) timelines remain logically sound and free of historical gaps or overlaps.

When we use SCD Type 2 to track history, we rely heavily on effective_date and expiration_date columns to define when a specific row version was "active." If our ETL logic fails, we might end up with overlapping dates (where two versions claim to be active at once) or gaps (where a record suddenly vanishes from the timeline). Manually checking millions of rows is impossible, so we use automated profiling to validate these rules.

The Logic of Validation

To verify historical accuracy, your profiling queries should check for three common "integrity breaks":

1. Overlaps: A row’s effective_date must be strictly greater than the expiration_date of the previous version for the same natural key.
2. Gaps: The effective_date of a new version should typically align exactly with the expiration_date of the previous version.
3. Chronological Integrity: The expiration_date must always be greater than or equal to the effective_date.

Profiling with SQL

You can perform these checks during your data quality pipeline using window functions. Here is a simple validation query to identify overlapping versions for a specific natural_key:

SELECT natural_key, effective_date,
       LAG(expiration_date) OVER (PARTITION BY natural_key ORDER BY effective_date) as prev_expiration
FROM dim_customer
WHERE effective_date < prev_expiration;

If this query returns any results, your historical timeline is corrupted. By automating this check, you catch errors where the business logic—such as an out-of-order update or a bad source system timestamp—has compromised your SCD history.

Profiling isn't just about finding nulls; in the context of SCDs, it is about verifying the continuity of time. By treating your dates as a continuous sequence, you ensure that every point in your analytical history has exactly one version of the truth, preventing skewed reporting in your downstream fact tables.

Key Takeaway: Automated profiling for SCDs must verify that date ranges for the same natural key are contiguous and non-overlapping to ensure historical data integrity.