Snowflake Slowly Changing Dimensions (SCD)

Slowly Changing Dimensions (SCD) techniques manage how historical data is tracked when dimension attributes change over time.

SCD Type Definitions

SCD Type 1: Overwrite

SCD Type 1 is the simplest approach. Changes overwrite existing data without preserving history. This is ideal for correcting data errors or when only the current state matters.

Use Cases:

Correction of data entry mistakes
Attributes where history is irrelevant (e.g., phone number, email)
Small dimension tables where storage is a concern
Real-time dashboards showing only current state

Implementation with MERGE:

MERGE INTO dim_customer AS target
USING staging_customer AS source
ON target.customer_id = source.customer_id
WHEN MATCHED AND source.name <> target.name
    OR source.email <> target.email
    OR source.phone <> target.phone THEN
    UPDATE SET
        target.name = source.name,
        target.email = source.email,
        target.phone = source.phone,
        target.modified_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
    INSERT (customer_id, name, email, phone, created_at)
    VALUES (source.customer_id, source.name, source.email, source.phone, CURRENT_TIMESTAMP());

SCD Type 2: Versioning

SCD Type 2 provides full historical tracking. Each change creates a new version of the record. This is the most commonly used SCD type for analytical workloads.

Use Cases:

Regulatory compliance requiring full audit trails
Historical trend analysis and reporting
Fact table foreign key integrity across time periods
Customer dimension tracking for CRM analytics

Implementation with valid_from/valid_to:

CREATE OR REPLACE PROCEDURE sp_scd_type2_merge(p_table_name VARCHAR, p_stage_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    MERGE INTO dim_customer AS target
    USING staging_customer AS source
    ON target.customer_id = source.customer_id
        AND target.is_current = TRUE
    WHEN MATCHED AND
        HASH(target.name, target.email, target.region) <>
        HASH(source.name, source.email, source.region) THEN
        UPDATE SET
            target.valid_to = CURRENT_TIMESTAMP(),
            target.is_current = FALSE
    WHEN NOT MATCHED THEN
        INSERT (customer_id, name, email, region, valid_from, valid_to, is_current)
        VALUES (
            source.customer_id,
            source.name,
            source.email,
            source.region,
            CURRENT_TIMESTAMP(),
            NULL,
            TRUE
        );
END;
$$;

Querying Current Records:

SELECT customer_id, name, email, region
FROM dim_customer
WHERE is_current = TRUE;

Querying Historical Records:

SELECT customer_id, name, email, region,
       valid_from, valid_to,
       DATEDIFF(day, valid_from, COALESCE(valid_to, CURRENT_TIMESTAMP())) AS days_active
FROM dim_customer
WHERE customer_id = 'CUST-001'
ORDER BY valid_from;

SCD Type 3: Previous/Current Columns

SCD Type 3 maintains a fixed number of previous values through dedicated columns. This provides limited history without the row proliferation of Type 2.

Use Cases:

When only the immediate previous value is needed
Low-change dimensions (e.g., region, department)
Performance-sensitive queries requiring minimal row counts
Simple reporting scenarios

Implementation with previous and current columns:

MERGE INTO dim_employee AS target
USING staging_employee AS source
ON target.employee_id = source.employee_id
WHEN MATCHED AND source.department <> target.current_department THEN
    UPDATE SET
        target.previous_department = target.current_department,
        target.current_department = source.department,
        target.dept_changed_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
    INSERT (employee_id, name, previous_department, current_department, dept_changed_at)
    VALUES (
        source.employee_id,
        source.name,
        NULL,
        source.department,
        CURRENT_TIMESTAMP()
    );

Comparison: When to Use Each Type

Factor	SCD Type 1	SCD Type 2	SCD Type 3
History Retained	None	Full	Limited (1-2 changes)
Storage Impact	Minimal	High (row duplication)	Moderate
Query Complexity	Simple	Moderate (filters needed)	Simple
Use When	Only current state matters	Full audit trail required	Limited history is sufficient
Row Count	Same as source	Grows with changes	Same as source
Performance	Best (fewest rows)	Moderate (many rows)	Good
Common Scenario	Data correction	Regulatory compliance	Simple tracking

Snowflake Slowly Changing Dimensions (SCD)

Snowflake Slowly Changing Dimensions (SCD)

SCD Type Definitions

SCD Type 1: Overwrite

SCD Type 2: Versioning

SCD Type 3: Previous/Current Columns

Comparison: When to Use Each Type

Real-World Example: Customer Address Changes

See Also

Need Expert Snowflake Help?