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

> Learn how to implement SCD Type 2 in SQL to preserve historical data. This guide details using effective dates and surrogate keys for version tracking, ensuring data integrity over time.

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

---

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

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

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

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

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

```

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.