SCD Testing: How to Test Slowly Changing Dimensions (Type 1, 2, 3)

Slowly changing dimensions (SCDs) are where data warehouse bugs like to hide. A customer moves city, and suddenly last year's sales are reported against the new city, or the customer appears twice in every report. SCD testing proves that history is handled exactly as the design says. This guide covers test scenarios and SQL for SCD Type 1, 2 and 3.

What Is a Slowly Changing Dimension?

A dimension attribute such as a customer's city or a product's category changes occasionally. The SCD type decides what the warehouse does when it changes:

The mapping document should say which type applies to each column. A single dimension can mix types, for example Type 1 for phone number and Type 2 for city.

Testing SCD Type 1

Scenarios: change a Type 1 attribute in the source, run the load, and verify:

• The target row is updated with the new value
• No new row is created (row count per business key stays 1)
• The surrogate key does not change
• Unchanged rows are not touched (their update timestamp stays the same)

-- Type 1: target value must equal the latest source value
SELECT s.customer_id, s.phone AS source_phone, d.phone AS target_phone
FROM src.customers s
JOIN dw.dim_customer d ON d.customer_id = s.customer_id
WHERE COALESCE(s.phone, '') <> COALESCE(d.phone, '');
-- Expected: 0 rows

Testing SCD Type 2

Type 2 needs the most test cases because it creates rows, closes rows and manages dates and flags. A typical Type 2 table has customer_sk (surrogate key), customer_id (business key), the tracked attributes, effective_from, effective_to and is_current.

SCD Type 2 SQL Validation Queries

-- 1. Exactly one current row per business key
SELECT customer_id, COUNT(*) AS current_rows
FROM dw.dim_customer
WHERE is_current = 1
GROUP BY customer_id
HAVING COUNT(*) <> 1;

-- 2. No overlapping effective date ranges
SELECT a.customer_id, a.customer_sk, b.customer_sk
FROM dw.dim_customer a
JOIN dw.dim_customer b
  ON a.customer_id = b.customer_id
 AND a.customer_sk < b.customer_sk
 AND a.effective_from <= b.effective_to
 AND b.effective_from <= a.effective_to;

-- 3. No gaps: each version starts the day after the previous one ends
SELECT customer_id, effective_from, prev_to
FROM (
  SELECT customer_id, effective_from,
         LAG(effective_to) OVER (PARTITION BY customer_id ORDER BY effective_from) AS prev_to
  FROM dw.dim_customer
) x
WHERE prev_to IS NOT NULL
  AND effective_from <> prev_to + INTERVAL '1' DAY;

-- 4. Current row matches the latest source values
SELECT s.customer_id
FROM src.customers s
JOIN dw.dim_customer d ON d.customer_id = s.customer_id AND d.is_current = 1
WHERE s.city <> d.city;

-- All four: expected 0 rows

Date arithmetic syntax differs between databases (for example DATEADD in SQL Server), so adjust query 3 for your platform. Also check facts: a sale made before the change should still join to the old version's surrogate key.

-- Facts must point to the version that was current on the transaction date
SELECT f.order_id
FROM dw.fact_orders f
JOIN dw.dim_customer d ON d.customer_sk = f.customer_sk
WHERE f.order_date NOT BETWEEN d.effective_from AND d.effective_to;
-- Expected: 0 rows

Testing SCD Type 3

• After a change, current_city holds the new value and previous_city holds the old one
• A second change shifts values again: the first value is lost, as designed
• No new row is created
• For new records, previous_city is null (or the agreed default)

SELECT d.customer_id, d.previous_city, d.current_city, s.city AS source_city
FROM dw.dim_customer_t3 d
JOIN src.customers s ON s.customer_id = d.customer_id
WHERE d.current_city <> s.city;
-- Expected: 0 rows

Common SCD Defects Testers Find

SCD scenarios are also a favourite interview topic. See scenario-based ETL testing interview questions.

ETL testing process & templates: ETL testing process · ETL test plan template · ETL testing checklist · data profiling · metadata testing · ETL test cases

Frequently Asked Questions

What is SCD testing?

SCD testing verifies that a slowly changing dimension handles attribute changes as designed: overwriting for Type 1, creating and closing versions for Type 2, and keeping a previous value for Type 3.

How do you test SCD Type 2?

Change a tracked attribute in the source, run the load and check that the old row is closed, a new current row is created with correct effective dates, there is exactly one current row per key, dates do not overlap and facts point to the correct version.

What is the difference between SCD Type 1 and Type 2?

Type 1 overwrites the old value and keeps no history. Type 2 keeps full history by closing the current row and inserting a new version with effective dates and a current flag.

What is the most common SCD Type 2 defect?

Failing to close the previous version, which leaves two current rows for the same business key and duplicates the customer in reports. The one-current-row-per-key query catches it.

AN

Written by

Asim Noaman Lodhi

Certified Google Partner · QA Consultant · 12+ Years IT

QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.

4.5 Rating 913+ Students Google Partner 79 Lectures