How to Implement SCD in SQL: A Complete Type 2 Guide

Slowly Changing Dimensions Type 2 preserves historical data by creating new rows for each attribute change, using effective date columns (start_date, end_date) and a surrogate key to track versions over time.

Slowly Changing Dimensions (SCD) are essential for maintaining historical accuracy in data warehouses. This guide demonstrates how to implement SCD in SQL using practical examples from the DataExpert-io/data-engineer-handbook repository, specifically the actors_history_scd assignment detailed in intermediate-bootcamp/materials/1-dimensional-data-modeling/homework/homework.md.

Understanding SCD Type 2 Fundamentals

SCD Type 2 creates a new record whenever a dimensional attribute changes while preserving the previous state. This pattern enables point-in-time analysis and full auditability of historical changes. Unlike Type 1, which overwrites data, Type 2 maintains complete lineage by adding temporal columns that define when each version was active.

Creating the Type 2 SCD Table Schema

The foundation of any SCD implementation is the table schema. According to the Data Engineer Handbook assignment, your table must isolate historical versions using a surrogate key while tracking business attributes through time.

Required Columns for Historical Tracking

CREATE TABLE actors_history_scd (
    surrogate_id   BIGINT   GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- surrogate key
    actor_id       BIGINT   NOT NULL,                                 -- natural key (from actors)
    quality_class  STRING,                                         -- dimensional attribute
    is_active      BOOLEAN,                                         -- dimensional attribute
    start_date     DATE    NOT NULL,                                 -- when this version became effective
    end_date       DATE,                                            -- when this version ended (NULL = current)
    current_flag   BOOLEAN NOT NULL DEFAULT TRUE                  -- convenience flag for the latest row
);

• surrogate_id isolates the history from business key changes.
• start_date and end_date define the validity interval for each version.
• current_flag simplifies queries for the latest snapshot without filtering on NULL end dates.

Back-Filling Historical Data

Before implementing incremental loads, populate the SCD table with complete history from source data. The back-fill query uses window functions to establish proper date ranges by comparing each row with its successor.

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 function obtains the next row’s start_date to set the current row’s end_date. The final version for each natural key receives end_date = NULL and current_flag = TRUE, identifying it as the current record.

Processing Incremental Updates

Daily or batch updates require comparing incoming data against current records. The incremental workflow consists of two operations: inserting new versions for changed attributes and closing expired records to maintain temporal integrity.

Inserting New Dimension Versions

First, identify records where attributes differ from the current snapshot or where entirely new natural keys appear:

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,                       -- new rows are current
    TRUE
FROM new_data n
LEFT JOIN latest_snapshot l
  ON n.actor_id = l.actor_id
WHERE
    l.actor_id IS NULL                                 -- brand-new actor
    OR n.quality_class <> l.quality_class
    OR n.is_active <> l.is_active;

Closing Previous Records

After inserting new versions, update the previous records to expire them:

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);

This two-step process ensures that only one record per natural key maintains current_flag = TRUE while historical records retain accurate validity periods.

Summary

• SCD Type 2 creates new rows for changes while preserving history through effective dates, enabling point-in-time analysis.
• The surrogate key isolates dimensional history from business key volatility and provides unique identifiers for each version.
• Back-fill queries use LEAD window functions to establish initial date ranges efficiently from historical snapshots.
• Incremental processing requires both INSERT operations for new versions and UPDATE operations to close previous records and maintain temporal accuracy.
• The current_flag column optimizes query performance when retrieving the latest dimension snapshot.

Frequently Asked Questions

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

SCD Type 1 overwrites existing data with new values, permanently losing historical context. SCD Type 2 preserves complete history by inserting new rows for each change and maintaining start_date and end_date columns to track when each version was active, enabling historical trend analysis.

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

For late-arriving data, insert the new row with the appropriate start_date based on when the change actually occurred, then update any overlapping records to adjust their end_date values. You may need to split existing time periods or reorder the timeline if the late arrival falls between existing records.

Why use a surrogate key instead of the natural key in SCD tables?

The surrogate key (such as surrogate_id) ensures each historical version has a unique identifier independent of business logic. Natural keys (like actor_id) remain constant across versions, making them unsuitable for uniquely identifying individual historical records in the table.

Can you implement SCD Type 2 without a current_flag column?

Yes, though querying requires checking WHERE end_date IS NULL to find current records. The current_flag boolean is a convenience optimization that improves query readability and performance, as demonstrated in the Data Engineer Handbook examples.

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