How to Implement Incremental Slowly Changing Dimensions Type 2 Queries in SQL

Implementing incremental Slowly Changing Dimensions Type 2 logic requires separating incoming data into unchanged, changed, and new record groups using CTEs, then leveraging composite types with UNNEST to generate both historical and current versions for changed entities before merging everything into the target dimension table.

Slowly Changing Dimensions Type 2 is the industry standard for tracking historical attribute changes in dimensional data warehouses. This guide walks through a production-ready incremental SCD Type 2 pattern using pure SQL and Apache Spark, as implemented in the DataExpert-io/data-engineer-handbook repository.

Understanding Incremental SCD Type 2 Architecture

Incremental SCD Type 2 processing differs from full-table reloads by touching only the current season's data and the previous season's SCD snapshot. This approach minimizes I/O and enables fast nightly loads without scanning entire history.

The Four Logical Data Slices

The query architecture decomposes the problem into four distinct slices using Common Table Expressions (CTEs):

• Historical records: Already closed dimensions where end_season < current_season
• Unchanged records: Current entities with identical attributes to the previous season
• Changed records: Entities with modified attributes requiring versioning
• New records: Entities appearing for the first time with no prior SCD history

The UNNEST Pattern for Bi-Temporal Emission

For every changed record, the query must emit two rows: one closing the old version (preserving its original start and end dates) and one opening the new version (with current season as both start and end). The implementation uses a composite user-defined type (scd_type) to bundle the four SCD columns, then leverages UNNEST(ARRAY[...]) to expand these into separate rows in a single pass.

Step-by-Step SQL Implementation

Dimension Table Schema

First, define the target table that stores the historical dimension data. The schema in players_scd_table.sql establishes the structure:

CREATE TABLE players_scd_table (
    player_name      TEXT,
    scoring_class    scoring_class,
    is_active        BOOLEAN,
    start_season     INTEGER,
    end_season       INTEGER,
    current_season   INTEGER
);

Isolating Data with CTEs

The query begins by isolating the relevant datasets to minimize the working set:

CREATE TYPE scd_type AS (
    scoring_class scoring_class,
    is_active     BOOLEAN,
    start_season  INTEGER,
    end_season    INTEGER
);

WITH last_season AS (
    SELECT * FROM players_scd_table
    WHERE current_season = 2021 AND end_season = 2021
),
historical AS (
    SELECT * FROM players_scd_table
    WHERE current_season = 2021 AND end_season < 2021
),
this_season AS (
    SELECT * FROM players_staging
    WHERE current_season = 2022
)

Processing Unchanged Rows

Unchanged records keep their original start_season while receiving the current season as their new end_season:

unchanged AS (
    SELECT
        ts.player_name,
        ts.scoring_class,
        ts.is_active,
        ls.start_season,
        ts.current_season AS end_season
    FROM this_season ts
    JOIN last_season ls USING (player_name)
    WHERE ts.scoring_class = ls.scoring_class
      AND ts.is_active = ls.is_active
)

Handling Changed Attributes

The critical logic uses UNNEST to generate both versions of changed records simultaneously, as shown in incremental_scd_query.sql:

changed AS (
    SELECT
        ts.player_name,
        UNNEST(ARRAY[
            ROW(ls.scoring_class, ls.is_active, ls.start_season, ls.end_season)::scd_type,
            ROW(ts.scoring_class, ts.is_active, ts.current_season, ts.current_season)::scd_type
        ]) AS rec
    FROM this_season ts
    LEFT JOIN last_season ls USING (player_name)
    WHERE ts.scoring_class <> ls.scoring_class
       OR ts.is_active <> ls.is_active
),
changed_unnested AS (
    SELECT
        player_name,
        (rec).scoring_class,
        (rec).is_active,
        (rec).start_season,
        (rec).end_season
    FROM changed
)

Appending New Entities

New records that have no prior history in the SCD table get both start and end dates set to the current season:

new 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 ts
    LEFT JOIN last_season ls USING (player_name)
    WHERE ls.player_name IS NULL
)

Final Assembly

The query concludes by unioning all slices and tagging with the current season, producing an idempotent result set:

SELECT *, 2022 AS current_season
FROM (
    SELECT * FROM historical
    UNION ALL 
    SELECT * FROM unchanged
    UNION ALL 
    SELECT * FROM changed_unnested
    UNION ALL 
    SELECT * FROM new
) AS final_scd;

Production Deployment with Apache Spark

For data lakes and large-scale warehouses, wrap the SQL logic in a Spark job. The implementation in players_scd_job.py demonstrates this pattern:

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("IncrementalSCD").getOrCreate()

# Load incremental source and existing SCD table

staging_df = spark.read.parquet("s3://warehouse/players/2022/")
scd_df = spark.read.parquet("s3://warehouse/players_scd/")

staging_df.createOrReplaceTempView("players")
scd_df.createOrReplaceTempView("players_scd_table")

# Execute incremental SCD Type 2 logic

result = spark.sql("""
    -- [Full query from incremental_scd_query.sql]
""")

# Overwrite partition for idempotent writes

result.write.mode("overwrite").insertInto("players_scd_table")

Testing Your Implementation

Validate correctness using the PyTest suite in test_player_scd.py. These tests verify that:

• Changed records generate exactly two history rows
• Unchanged records extend their end_date
• New records appear with identical start and end seasons
• Historical records remain untouched

Summary

• Incremental filtering on current_season ensures the query only scans the latest data and previous SCD snapshot, not the entire history.
• CTE decomposition separates logical concerns (historical, unchanged, changed, new), improving readability and enabling optimizer efficiency.
• Composite types with UNNEST efficiently generate both closed and open versions for changed attributes in a single transformation.
• UNION ALL assembly merges all record types idempotently, supporting pipeline retries without duplicates.
• Engine-agnostic SQL runs on Postgres, BigQuery, Snowflake, and Spark, making the pattern portable across data platforms.

Frequently Asked Questions

What makes this approach incremental rather than a full reload?

The query filters the source data to only the current_season (e.g., 2022) and joins against the previous season's SCD records (where end_season = previous_year). This restricts the working set to new and changed records only, avoiding a full table scan of the entire dimension history.

Why use UNNEST instead of inserting changed rows in separate statements?

Using UNNEST(ARRAY[...]) with a composite type allows the database to process both the historical closure and the new version insertion in a single pass through the data. This set-based approach is more efficient than row-by-row operations and maintains atomicity within the query block.

How does the composite type scd_type improve the query structure?

The scd_type bundles the four slowly changing columns (scoring_class, is_active, start_season, end_season) into a single object. When unnested, this generates properly typed rows without repetitive column aliasing, making the SQL concise and easier to maintain when tracking additional attributes.

Can this pattern handle late-arriving data or backfills?

Yes. Because the logic is idempotent and filters on specific season values, you can re-run the query for any historical current_season value to process late-arriving records. The UNION ALL structure ensures that reprocessing a season produces identical results without creating duplicates, provided the current_season column is used correctly in the target table's partitioning or filtering logic.

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