How to Implement Dimensional Data Modeling with Slowly Changing Dimensions (SCD) in SQL

Slowly Changing Dimensions (Type 2) preserve historical changes by inserting new rows with effective dates and closing previous versions, enabling point-in-time analysis in SQL data warehouses.

Dimensional data modeling with Slowly Changing Dimensions (SCD) is essential for tracking historical attribute changes in analytics databases. The DataExpert-io/data-engineer-handbook repository demonstrates this pattern through a hands-on assignment that requires building a Type 2 SCD table for actor data. This guide walks through the exact SQL implementation found in the bootcamp materials, including table design, historical back-filling, and incremental loading patterns.

Understanding SCD Type 2 Architecture

Slowly Changing Dimensions (SCD) Type 2 is the industry-standard pattern for preserving history when dimension attributes change over time. Unlike Type 1 (which overwrites values), Type 2 creates a new row for every change while keeping the old row intact.

The pattern relies on four core structural elements:

• Surrogate key: A synthetic primary key that remains stable regardless of business key changes
• Natural key: The original business identifier (e.g., actor_id) that links all versions of the same entity
• Temporal columns: start_date and end_date columns that define the validity period of each version
• Current flag: A boolean indicator marking which row represents the latest state

This architecture enables point-in-time analysis, allowing analysts to query dimension states as they existed at any historical date.

Designing the SCD Type 2 Table

The foundation of dimensional data modeling with SCD starts with a properly structured DDL statement. Based on the assignment requirements in intermediate-bootcamp/materials/1-dimensional-data-modeling/homework/homework.md, the target table must isolate history from the source system while maintaining referential integrity.

CREATE TABLE actors_history_scd (
    surrogate_id   BIGINT   GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    actor_id       BIGINT   NOT NULL,
    quality_class  STRING,
    is_active      BOOLEAN,
    start_date     DATE    NOT NULL,
    end_date       DATE,
    current_flag   BOOLEAN NOT NULL DEFAULT TRUE
);

The surrogate_id column serves as the immutable primary key, while actor_id functions as the natural key that persists across all versions. Setting end_date to NULL and current_flag to TRUE for the active version eliminates the need for complex date arithmetic when querying current state.

Back-Filling Historical Data

When initializing an SCD table, you must load the complete historic snapshot from source data and calculate the effective date ranges for each version. The back-fill query uses window functions to determine when each version ends based on when the next version begins.

INSERT INTO actors_history_scd (
    actor_id,
    quality_class,
    is_active,
    start_date,
    end_date,
    current_flag
)
SELECT
    a.actor_id,
    a.quality_class,
    a.is_active,
    DATE_FROM_PARTS(a.year, 1, 1) AS start_date,
    LEAD(DATE_FROM_PARTS(a.year, 1, 1)) 
        OVER (PARTITION BY a.actor_id ORDER BY a.year) AS end_date,
    CASE 
        WHEN LEAD(a.year) OVER (PARTITION BY a.actor_id ORDER BY a.year) IS NULL 
        THEN TRUE 
        ELSE FALSE 
    END AS current_flag
FROM actors a
ORDER BY a.actor_id, a.year;

The LEAD window function retrieves the next chronological year's date to set the current row's end_date. For the most recent record per actor, LEAD returns NULL, which the CASE statement converts to current_flag = TRUE. This single query populates the entire history without requiring procedural code.

Implementing Incremental Loads

After the initial back-fill, new data arrives incrementally (typically by year or batch). The incremental load pattern follows a two-step process: insert new versions only when attributes change, then close the previous versions by updating their end dates and flags.

WITH new_data AS (
    SELECT
        actor_id,
        quality_class,
        is_active,
        DATE_FROM_PARTS(year, 1, 1) AS start_date
    FROM actors
    WHERE year = EXTRACT(YEAR FROM CURRENT_DATE)
),

latest_snapshot AS (
    SELECT
        actor_id,
        quality_class,
        is_active,
        start_date,
        end_date,
        current_flag
    FROM actors_history_scd
    WHERE current_flag = TRUE
)

INSERT INTO actors_history_scd (
    actor_id,
    quality_class,
    is_active,
    start_date,
    end_date,
    current_flag
)
SELECT
    n.actor_id,
    n.quality_class,
    n.is_active,
    n.start_date,
    NULL,
    TRUE
FROM new_data n
LEFT JOIN latest_snapshot l
  ON n.actor_id = l.actor_id
WHERE
    l.actor_id IS NULL
    OR n.quality_class <> l.quality_class
    OR n.is_active <> l.is_active;

Following the insert, you must expire the previous versions to maintain temporal integrity:

UPDATE actors_history_scd
SET 
    end_date = n.start_date - INTERVAL '1 DAY',
    current_flag = FALSE
FROM new_data n
WHERE actors_history_scd.actor_id = n.actor_id
  AND actors_history_scd.current_flag = TRUE
  AND (actors_history_scd.quality_class <> n.quality_class
       OR actors_history_scd.is_active <> n.is_active);

The LEFT JOIN in the insert statement identifies both brand-new actors (where l.actor_id IS NULL) and existing actors with changed attributes. The subsequent UPDATE statement closes the gap between the previous version and the new one, typically using INTERVAL '1 DAY' to avoid temporal overlap.

Summary

• SCD Type 2 creates new rows for changes rather than updating existing records, preserving full historical audit trails in dimensional data modeling.
• The table structure requires a surrogate_id for uniqueness, a natural key for entity linking, and start_date/end_date columns to define validity periods.
• Back-fill queries use window functions like LEAD to calculate end dates by examining subsequent records within the same natural key partition.
• Incremental loads compare incoming data against current_flag = TRUE rows, inserting new versions only when tracked attributes differ, then updating previous rows to close their validity window.
• The implementation follows the exact specifications found in the DataExpert-io/data-engineer-handbook assignment at intermediate-bootcamp/materials/1-dimensional-data-modeling/homework/homework.md.

Frequently Asked Questions

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

SCD Type 1 overwrites existing dimension rows when source data changes, losing all historical context. SCD Type 2 inserts new rows while retaining previous versions, using date ranges to identify which version was effective at any point in time. Choose Type 2 when auditability and historical analysis are business requirements.

Why use a surrogate key instead of relying on the natural key?

A surrogate key (such as the surrogate_id in the actors_history_scd table) provides a stable, single-column identifier for each version row. Natural keys like actor_id appear in multiple rows (one per version) and cannot serve as primary keys. Surrogate keys also insulate the warehouse from business key changes or source system mergers.

How do I handle late-arriving data in SCD Type 2?

When processing late-arriving historical data, you must insert the new version and adjust the end_date of the preceding version, potentially cascading changes to subsequent versions if the late arrival falls between existing records. Check for existing rows where start_date is less than the new record's date and end_date is greater than or null, then split or adjust the existing intervals accordingly.

Can I implement SCD Type 2 without window functions?

While window functions like LEAD provide the most elegant solution for back-filling, you can implement the pattern using self-joins or correlated subqueries to find the next chronological record per natural key. However, window functions generally offer better performance and readability for calculating end_date values across partitioned datasets.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}