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

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

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.

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.

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:

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:

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.

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:

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

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