What Are Type 2 Slowly Changing Dimensions?
Type 2 Slowly Changing Dimensions (SCD Type 2) are a data warehouse modeling technique used to track changes to data over time by preserving historical values rather than overwriting them. This is achieved by creating a new record in the dimension whenever a value in the set of key columns is modified, while maintaining start and end dates for each record. The current record is identified either by querying for rows that are not end-dated, or by maintaining a flag (e.g., current_flg) to easily distinguish it from historical versions.
Key Takeaways
- SCD Type 2 can’t be validated with a simple source-to-target comparison — because historical records accumulate over time, testing needs to account for both current and past versions of each row.
- Query Compare validates current records — comparing the source table against only the rows flagged
CURRENT_FLG = 'Y'in the dimension confirms the active data matches the source. - Duplicate Check Rule verifies key-column uniqueness — grouping by all key columns and checking for
COUNT(*) > 1confirms no unintended duplicate “current” records exist. - Baseline and Compare confirms history is preserved correctly — snapshotting the dimension before a source change, then comparing it after the ETL run, verifies that changed records got a new row while the old one was end-dated as expected.
Why Testing SCD Type 2 Dimensions Is Different?
Testing SCD Type 2 Dimensions is tricky because it cannot be achieved by a simple comparison of the source and target data. In this article we will examine different aspects of Type 2 SCD that can be tested using ETL Validator.
For the sake of this article, let’s consider an Employee dimension (EMPLOYEE_D) of SCD Type 2 which is sourced from a table called EMPLOYEE in the source system.
Sample Table Structure: EMPLOYEE and EMPLOYEE_DIM
| Table | Columns |
|---|---|
| EMPLOYEE | ROW_ID EMP_NO FIRST_NAME LAST_NAME SSN DOB JOB_TITLE SALARY |
| EMPLOYEE_DIM (SCD Type 2 Dimension) | ROW_WID EMP_NO FIRST_NAME LAST_NAME SSN DOB JOB_TITLE SALARY START_DT END_DT CURRENT_FLG |
Three Tests to Validate SCD Type 2 Dimensions
| Test | ETL Validator Feature | What It Validates |
|---|---|---|
| Test 1: Current Data Accuracy | Query Compare Test Case | Verifies that current records (CURRENT_FLG = 'Y') match the source system. |
| Test 2: Key-Column Uniqueness | Duplicate Check Rule (Data Rules Test Plan) | Ensures no unintended duplicate “current” records exist across key columns. |
| Test 3: Historical Preservation | Component Test Case with Baseline and Compare | Confirms that changed records create a new row while the previous row is correctly end-dated. |
Test 1: Verifying the Current Data
Use a Query Compare test case in ETL Validator to compare the current data records in the SCD Type 2 Employee_Dim with the data in the source Employee table.
Source Query : select ROW_ID, EMP_NO, FIRST_NAME, LAST_NAME, SSN, DOB, JOB_TITLE, SALARY from EMPLOYEE
Target Query : select ROW_ID, EMP_NO, FIRST_NAME, LAST_NAME, SSN, DOB, JOB_TITLE, SALARY from EMPLOYEE_DIM where CURRENT_FLG = ‘Y’
Test 2: Verifying the uniqueness of the key columns in the SCD
The combination of the key columns in the SCD should be Unique— a core requirement for dimension tables. For the above example, the columns EMP_NO, FIRST_NAME, LAST_NAME, SSN, DOB, JOB_TITLE, SALARY comprise of an unique key in the EMPLOYEE_DIM dimension. This can be easily verified using the Duplicate Check Rule in the Data Rules test plan of ETL Validator. The query generated by ETL Validator using the Duplicate Check Rule should be something like below:
Select EMP_NO, FIRST_NAME, LAST_NAME, SSN, DOB, JOB_TITLE, SALARY, COUNT(*) CNT from EMPLOYEE_DIM group by EMP_NO, FIRST_NAME, LAST_NAME, SSN, DOB, JOB_TITLE, SALARY having COUNT(*)>1
This query should not return any rows.
Test 3: Verifying that historical data is preserved and new records are getting created
Recommended approach: whenever a change occurs to the values in the key columns, a new record should be inserted into EMPLOYEE_DIM and the old record should be end-dated. ETL Validator’s Component Test Case — specifically its Baseline and Compare feature — can be used to verify this behavior by snapshotting the dimension before a source change and comparing it against the dimension after the ETL run.
Below are the steps:
- Create a Component test case and take a snapshot of the current values in the EMPLOYEE_DIM (called Baseline).
- Modify a few records in the source EMPLOYEE table by updating the values in the key columns such as SALARY, LAST_NAME.
- Execute the ETL process so the the EMPLOYEE_DIM has the latest data.
- Run the Component test case to compare the Baseline data with the Result table and identify the differences. Verify that the differences are as expected.
ETL Validator thus provides a complete framework for automating the testing of SCD Type 2 dimensions.
Conclusion:
SCD Type 2 testing can’t rely on a simple source-to-target comparison, since history accumulates instead of being overwritten. Validating current records, key uniqueness, and historical preservation separately — as ETL Validator does with Query Compare, Duplicate Check Rule, and Baseline & Compare — closes the gaps a basic comparison would miss. This turns SCD Type 2 validation into a repeatable framework instead of a manual, error-prone process.
Frequently Asked Questions: Testing SCD Type 2 Dimensions
1) Why is testing Type 2 Slowly Changing Dimensions difficult?
Because SCD Type 2 preserves history by inserting new records rather than overwriting old ones, a straightforward source-to-target row comparison won’t work — testing needs to separately validate current data, key uniqueness, and historical preservation.
2) How do you verify current records in an SCD Type 2 dimension?
Use ETL Validator’s Query Compare test case to compare the source table against only the dimension rows
where the current-record flag (e.g. CURRENT_FLG = 'Y') is set, confirming the active data
matches the source system.
3) How do you check for duplicate key columns in an SCD Type 2 dimension?
ETL Validator’s Duplicate Check Rule groups the dimension by its key columns and flags any group with a count greater than one, since the combination of key columns should always be unique among current records.
4) How do you confirm historical records are preserved correctly?
Use ETL Validator’s Component Test Case with Baseline and Compare: take a snapshot of the dimension before a source change, run the ETL process, then compare the results to confirm the old record was end-dated and a new one was inserted as expected.
Rajesh Kumar A
Digital Marketing Manager, Datagaps
Digital Marketing Manager at Datagaps. Drives data-driven growth through content, performance campaigns, and marketing technology.
Subrahmanya Narayana Chirravuri
Senior Director, Technology, Datagaps
Senior Director of Technology at Datagaps. Leads engineering for the ETL, BI, and data-quality validation platforms.
