# How to Implement Slowly Changing Dimensions Type 2 in SQL: A Complete Guide

> Learn to implement Slowly Changing Dimensions Type 2 in SQL. This guide shows how to preserve historical data for accurate reporting using new records for attribute changes.

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

---

**Slowly Changing Dimensions Type 2 (SCD 2) preserves full historical data by creating new records when tracked attributes change, enabling accurate "as-of" reporting in data warehouses.**

SCD 2 is the industry-standard pattern for tracking dimensional history in analytics databases. The DataExpert-io/data-engineer-handbook repository provides a production-ready SQL implementation for managing player dimension history across seasons. This guide walks through the exact CTE-based approach used in their intermediate bootcamp materials.

---

## What Is SCD 2 and Why It Matters

**Slowly Changing Dimensions Type 2** maintains complete audit trails by versioning rows rather than overwriting them. Each dimensional record carries **validity periods** (typically `start_date`/`end_date` or `start_season`/`end_season`) that define when that version of the data was active.

This pattern solves critical analytics requirements:

- **Point-in-time analysis** – query "who was the active player on this date?"
- **Historical accuracy** – reports remain consistent even after source data changes
- **Regulatory compliance** – complete audit history for data lineage

The implementation in [`incremental_scd_query.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/incremental_scd_query.sql) tracks `scoring_class` and `is_active` across `start_season` and `end_season` boundaries.

---

## The Five-Step SCD 2 Pattern

The repository's SQL implementation follows a proven five-stage pipeline. Each stage isolates a specific transformation concern using **Common Table Expressions (CTEs)**.

### Step 1: Capture the Current Snapshot

Isolate the "live" dimension records that represent the current processing period's starting state.

```sql
WITH last_season_scd AS (
    SELECT * FROM players_scd
    WHERE current_season = 2021
      AND end_season = 2021  -- Active records only
),
historical_scd AS (
    SELECT player_name, scoring_class, is_active,
           start_season, end_season
    FROM players_scd
    WHERE current_season = 2021
      AND end_season < 2021  -- Already closed records
)

```

These two CTEs split prior history into **active candidates** (may need closure) and **immutable history** (already finalized).

### Step 2: Ingest Fresh Source Data

Load the incoming dimensional data for the new processing period.

```sql
this_season_data AS (
    SELECT * FROM players
    WHERE current_season = 2022
)

```

### Step 3: Detect Unchanged Records

Join incoming data against the active snapshot. When all tracked attributes match, simply **extend the validity period** rather than creating new rows.

```sql
unchanged_records AS (
    SELECT ts.player_name,
           ts.scoring_class,
           ts.is_active,
           ls.start_season,           -- Keep original start
           ts.current_season AS end_season  -- Extend end date
    FROM this_season_data ts
    JOIN last_season_scd ls
      ON ls.player_name = ts.player_name
    WHERE ts.scoring_class = ls.scoring_class
      AND ts.is_active = ls.is_active
)

```

This optimization prevents unnecessary version proliferation.

### Step 4: Handle Changed Records with Array Unnesting

When tracked attributes differ, **close the old record** and **open a new record** simultaneously. The implementation uses PostgreSQL's `UNNEST` with an **array of two ROW constructors**:

```sql
changed_records AS (
    SELECT ts.player_name,
           UNNEST(ARRAY[
               ROW(ls.scoring_class, ls.is_active,
                   ls.start_season, ls.end_season)::scd_type,  -- Old record
               ROW(ts.scoring_class, ts.is_active,
                   ts.current_season, ts.current_season)::scd_type  -- New record
           ]) AS records
    FROM this_season_data ts
    LEFT JOIN last_season_scd ls
      ON ls.player_name = ts.player_name
    WHERE (ts.scoring_class <> ls.scoring_class
        OR ts.is_active <> ls.is_active)
)

```

The `::scd_type` cast requires a pre-defined **composite type** matching the dimension structure.

Then unnest to flatten the array into individual rows:

```sql
unnested_changed_records AS (
    SELECT player_name,
           (records::scd_type).scoring_class,
           (records::scd_type).is_active,
           (records::scd_type).start_season,
           (records::scd_type).end_season
    FROM changed_records
)

```

### Step 5: Identify Brand-New Dimensions

Detect source keys absent from the historical snapshot—these require **initial records** with both dates set to the current period.

```sql
new_records AS (
    SELECT ts.player_name,
           ts.scoring_class,
           ts.is_active,
           ts.current_season AS start_season,
           ts.current_season AS end_season
    FROM this_season_data ts
    LEFT JOIN last_season_scd ls
      ON ts.player_name = ls.player_name
    WHERE ls.player_name IS NULL
)

```

---

## Final Assembly with UNION ALL

The complete SCD 2 dimension emerges from stacking all partial results:

```sql
SELECT *, 2022 AS current_season
FROM (
    SELECT * FROM historical_scd        -- Immutable past
    UNION ALL
    SELECT * FROM unchanged_records     -- Extended current
    UNION ALL
    SELECT * FROM unnested_changed_records  -- Closed + new versions
    UNION ALL
    SELECT * FROM new_records           -- First-time dimensions
) a;

```

This structure is **idempotent**—re-running for the same `current_season` produces identical results, a critical property for reliable data pipelines.

---

## Key SQL Techniques in the Implementation

| Technique | Purpose |
|-----------|---------|
| **CTE pipeline** | Isolates logical stages for readability and debugging |
| `UNNEST(ARRAY[ROW(...)])` | Generates multiple rows from a single changed record |
| Composite types (`::scd_type`) | Enables structured data in arrays |
| `LEFT JOIN ... IS NULL` | Identifies new dimension keys |
| `UNION ALL` | Combines disjoint row sets without deduplication overhead |

---

## Adapting to Other Database Platforms

The core logic is **ANSI-SQL compatible**, though the array unnesting syntax varies:

- **SQL Server**: Replace `UNNEST` with `CROSS APPLY` against a table-valued constructor
- **BigQuery**: Use `UNNEST` with `STRUCT` types instead of `ROW`
- **Snowflake**: Leverage `FLATTEN` or `SPLIT_TO_TABLE` for row generation
- **Hive/Spark**: Apply `LATERAL VIEW explode()` on an array of structs

Preserve the five-stage CTE structure regardless of platform—the separation of concerns remains sound.

---

## Summary

- **SCD 2 preserves history** by versioning dimensional rows with validity periods rather than updating in-place
- The DataExpert-io implementation uses **five CTE stages**: current snapshot, historical rows, unchanged records, changed records (with array unnesting), and new records
- **Changed rows generate two outputs**: the closed old version and the opened new version
- The pattern is **idempotent** and adapts to most SQL dialects with minor syntax adjustments

---

## Frequently Asked Questions

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

**SCD 1 overwrites existing data** when attributes change, losing historical context. **SCD 2 preserves all versions** with validity periods, enabling historical analysis. Choose SCD 2 when audit trails and point-in-time reporting matter; use SCD 1 for dimensions where only current state is relevant.

### When should I use the array unnesting technique versus separate INSERT/UPDATE statements?

The **array unnesting approach** (shown in [`incremental_scd_query.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/incremental_scd_query.sql)) is ideal for **set-based, declarative pipelines** in modern cloud warehouses. It processes all changes in a single query, maximizing parallelization. Use separate INSERT/UPDATE statements in traditional OLTP systems or when row-level locking and transaction control are required.

### How do I handle deletes in SCD 2?

The repository's pattern does not explicitly cover deletions. Typical approaches include: adding an `is_deleted` flag with a type 2 row showing when deletion occurred; implementing **SCD 3** to track previous value; or using a **soft-delete pattern** where the `end_season` is set but the record remains. The homework in [`homework.md`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/homework.md) prompts extending this pattern for additional scenarios.

### What composite type definition is needed for the scd_type cast?

You must create a matching type before running the query: `CREATE TYPE scd_type AS (scoring_class TEXT, is_active BOOLEAN, start_season INT, end_season INT);`. This type must align exactly with the columns selected in the `ROW` constructors. The repository assumes this prerequisite exists in the database environment.