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

> Implement Slowly Changing Dimensions Type 2 in SQL. Preserve history with effective dates and point-in-time analysis for your data warehouse.

- Repository: [DataExpert.io/data-engineer-handbook](https://github.com/DataExpert-io/data-engineer-handbook)
- Tags: how-to-guide
- Published: 2026-08-12

---

**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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/intermediate-bootcamp/materials/1-dimensional-data-modeling/homework/homework.md), the target table must isolate history from the source system while maintaining referential integrity.

```sql
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.

```sql
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.

```sql
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:

```sql
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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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.