Implement Slowly Changing Dimensions (SCDs)

This skill implements Slowly Changing Dimensions (SCD) in data warehouses. It covers all core SCD types: Type 0 (retain original, no changes), Type 1 (overwrite, no history), Type 2 (add new record with effective/expiration dates and an is_current flag - the most common approach), and Type 3 (add a previous-value column for one level of history). It implements a hybrid strategy combining SCD types per attribute within one dimension, Type 2 with version numbers, hash-based change detection comparing staging data against the current dimension via MD5 hashing, bulk SCD Type 2 processing using SQL MERGE, indexing and partitioning strategies for large dimension tables, SCD integrity checks (detecting duplicate current records or gaps in effective dating), late-arriving dimension handling, and mini-dimensions for rapidly changing attributes.

Use this skill when designing or maintaining a dimensional data warehouse - choosing which SCD type fits each dimension attribute, implementing Type 2 history tracking with effective/expiration dates, writing a hash-based change detection query for ETL, using MERGE for bulk SCD processing, indexing an SCD Type 2 table for query performance, validating SCD integrity (no overlapping current records, no date gaps), handling a late-arriving historical record, or splitting rapidly changing attributes into a mini-dimension.

It does not cover fact table design or general star-schema modeling beyond dimensions - it is focused specifically on how dimension attributes change over time and are tracked.

Inputs are typically a dimension table and a stream of source changes. Outputs include SQL implementations, for example a Type 2 SCD table with effective dating:

CREATE TABLE dim_customer (
    customer_key BIGINT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT NOT NULL,
    customer_name VARCHAR(100),
    address VARCHAR(200),
    effective_date DATE NOT NULL,
    expiration_date DATE,
    is_current BOOLEAN DEFAULT TRUE
);

UPDATE dim_customer SET expiration_date = CURRENT_DATE - 1, is_current = FALSE
WHERE customer_id = 12345 AND is_current = TRUE;
INSERT INTO dim_customer (customer_id, customer_name, address, effective_date)
VALUES (12345, 'John Doe', 'New Address', CURRENT_DATE);

Other outputs include a hash-based change detection query, a MERGE-based bulk SCD Type 2 ETL statement, indexing and range-partitioning DDL, integrity check queries for duplicate current records and date gaps, a late-arriving dimension insert pattern, and a mini-dimension design for rapidly changing attributes referenced from the fact table.

Data engineers and warehouse architects designing dimension tables who need to track historical changes correctly and efficiently rather than losing history on every update.