Writing SQL for Slowly Changing Dimensions: Type 1, Type 2, and Type 6 Merge Patterns Using Window Functions and CTEs

Introduction

Picture this: your sales team wants to know which region a customer belonged to when they made a purchase two years ago. The customer has since moved from the Midwest to the Pacific Northwest. Your customers table shows the current region — Pacific Northwest — because someone ran an UPDATE last quarter. The historical sales data is still there, but you've lost the context that made it meaningful. Every report that joins orders to customers will now silently lie about the past.

This is the central problem that Slowly Changing Dimensions (SCDs) were designed to solve. In a dimensional data warehouse, dimension tables hold the descriptive context — customer profiles, product categories, sales territory assignments, employee roles — that gives raw transactional facts their meaning. The challenge is that this context changes over time, and how you handle those changes determines whether your historical analysis is trustworthy or subtly corrupted.

By the end of this lesson, you'll be able to design, implement, and maintain SCD Type 1, Type 2, and Type 6 tables using production-grade SQL patterns. You'll understand not just the mechanics of each type, but the performance trade-offs, common failure modes, and architectural decisions that separate a fragile weekend implementation from something you'd trust to run nightly in a production pipeline.

What you'll learn:

• The conceptual model behind SCD Type 1, Type 2, and Type 6, and when to choose each
• How to write MERGE statements and CTE-based upsert patterns for Type 1 changes
• How to implement Type 2 history tracking using window functions to detect and close out expired rows
• How to build the hybrid Type 6 "current flag + effective dates + current value" pattern that bridges Type 1 and Type 2 analysis
• How to handle late-arriving data, corrections, and edge cases that break naive implementations
• Performance tuning strategies for large SCD tables, including indexing and partition pruning

---

Prerequisites

You should be comfortable with:

• Standard SQL JOINs and subqueries — if you need a refresher, Joining Multiple Tables in SQL: INNER, LEFT, RIGHT, and FULL OUTER JOIN Explained covers the foundation
• Window functions, particularly ROW_NUMBER(), LAG(), and LEAD() — the Window Functions: RANK, ROW_NUMBER, and LAG article is the essential background
• CTEs and how the optimizer handles them — Common Table Expressions (CTEs) for Cleaner SQL will get you there
• Basic understanding of MERGE / UPSERT syntax for your database platform (PostgreSQL's INSERT ... ON CONFLICT, SQL Server's MERGE, BigQuery's MERGE)

The examples in this lesson use standard ANSI SQL with notes for PostgreSQL and SQL Server divergences. The concepts apply universally.

---

The Conceptual Foundation: What Changes, and What Should You Do About It?

Before writing a single line of SQL, you need to answer a design question: when an attribute of a dimension record changes, what should happen to history?

Think about a dim_customer table. A customer changes their email address. Does the old email address matter? Almost certainly not for any analysis you'd ever run — nobody is segmenting customers by the Yahoo address they used in 2019. But if that same customer moves from one sales territory to the other, suddenly it matters enormously. Revenue attribution, quota calculations, regional performance reports — all of these depend on territory assignments being historically accurate.

This is the distinction between Type 1 and Type 2 changes:

• Type 1 (Overwrite): The new value replaces the old value everywhere. History is not preserved. Use this for corrections, low-cardinality lookups that don't affect analysis, and attributes nobody will ever analyze by historical value.
• Type 2 (Versioned History): A new row is inserted with the new value. The old row is "closed out" with an end date. Every fact row that referenced the dimension during the old period still correctly resolves to the old attribute values. Use this for any attribute that contextualizes historical analysis.
• Type 6 (Hybrid): A superset of Type 2 that adds a "current value" column to every historical row, allowing you to answer both "what was the value then?" and "what is the value now?" in a single join without a subquery.

There's also Type 3 (keep only current and previous value) and Type 4 (mini-dimension), but Type 6 is the practical choice when you need the power of Type 2 with the query simplicity of Type 1, which is why we'll focus on 1, 2, and 6 here.

---

Your Sample Schema and Source Data

We'll work with a realistic scenario throughout: an e-commerce company that tracks customer profiles in a data warehouse. Customers have a customer_segment (Bronze, Silver, Gold) and a sales_territory that drive reporting. Both can change over time, and both matter for historical analysis.

The source system is a transactional database. Nightly, a staging table receives a snapshot of changed customer records:

-- Staging table: new or changed customer records arriving from the source system
CREATE TABLE stg_customer_updates (
    customer_id        INTEGER,
    full_name          VARCHAR(200),
    email              VARCHAR(200),
    customer_segment   VARCHAR(50),
    sales_territory    VARCHAR(100),
    source_updated_at  TIMESTAMP,  -- when the source system last modified this record
    load_batch_id      INTEGER     -- identifies which ETL batch loaded this row
);

-- Our dimension table (Type 2 version — we'll build to this)
CREATE TABLE dim_customer (
    customer_key       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- surrogate key
    customer_id        INTEGER NOT NULL,   -- natural/business key
    full_name          VARCHAR(200),
    email              VARCHAR(200),
    customer_segment   VARCHAR(50),
    sales_territory    VARCHAR(100),
    effective_from     DATE NOT NULL,
    effective_to       DATE,              -- NULL means currently active
    is_current         BOOLEAN NOT NULL DEFAULT TRUE,
    load_batch_id      INTEGER
);

A few design decisions worth noting:

• The surrogate key (customer_key) is separate from the business key (customer_id). Fact tables reference customer_key, which means they'll always resolve to the historically correct version of a customer.
• effective_to being NULL indicates the current record. Some teams prefer 9999-12-31 as a sentinel date to avoid NULL handling; we'll discuss the trade-offs shortly.
• is_current is technically redundant with effective_to IS NULL, but it enables very fast bitmap index lookups for current-record queries.

---

Type 1: Overwriting Stale Data with MERGE and CTEs

Type 1 is the simplest pattern, but "simple" doesn't mean "easy to get right at scale." The goal is: if a record in staging matches an existing record in the dimension by business key, update the dimension row in place. If no match exists, insert a new row.

This is a classic upsert pattern. Let's build it in layers.

The Naive Approach (and Why It Fails)

A developer new to warehouse loading might write something like this:

-- DON'T DO THIS in production
UPDATE dim_customer
SET    full_name        = s.full_name,
       email            = s.email,
       customer_segment = s.customer_segment,
       sales_territory  = s.sales_territory
FROM   stg_customer_updates s
WHERE  dim_customer.customer_id = s.customer_id;

INSERT INTO dim_customer (customer_id, full_name, email, customer_segment, sales_territory, effective_from, is_current)
SELECT s.customer_id, s.full_name, s.email, s.customer_segment, s.sales_territory, CURRENT_DATE, TRUE
FROM   stg_customer_updates s
WHERE  NOT EXISTS (
    SELECT 1 FROM dim_customer d WHERE d.customer_id = s.customer_id
);

This has a classic race condition: another transaction could INSERT the row between your UPDATE and your INSERT. In a nightly batch this is unlikely, but if you ever parallelize your loads or run ad-hoc corrections concurrently, you'll get duplicates. The correct approach wraps this in a single atomic statement.

The MERGE Pattern (SQL Server / BigQuery / Snowflake)

MERGE INTO dim_customer AS target
USING (
    -- Deduplicate staging: if a customer_id appears multiple times in staging,
    -- take only the most recently updated version
    WITH deduped_staging AS (
        SELECT *,
               ROW_NUMBER() OVER (
                   PARTITION BY customer_id
                   ORDER BY source_updated_at DESC, load_batch_id DESC
               ) AS rn
        FROM   stg_customer_updates
    )
    SELECT customer_id, full_name, email, customer_segment,
           sales_territory, source_updated_at, load_batch_id
    FROM   deduped_staging
    WHERE  rn = 1
) AS source
ON target.customer_id = source.customer_id
   AND target.is_current = TRUE  -- Only match against the active row

WHEN MATCHED AND (
    target.customer_segment <> source.customer_segment OR
    target.sales_territory  <> source.sales_territory  OR
    target.full_name        <> source.full_name        OR
    target.email            <> source.email
) THEN
    UPDATE SET
        target.full_name        = source.full_name,
        target.email            = source.email,
        target.customer_segment = source.customer_segment,
        target.sales_territory  = source.sales_territory,
        target.load_batch_id    = source.load_batch_id

WHEN NOT MATCHED BY TARGET THEN
    INSERT (customer_id, full_name, email, customer_segment,
            sales_territory, effective_from, is_current, load_batch_id)
    VALUES (source.customer_id, source.full_name, source.email,
            source.customer_segment, source.sales_territory,
            CURRENT_DATE, TRUE, source.load_batch_id);

Notice the ROW_NUMBER() window function inside the USING clause. This is a critical guard — source systems often send multiple change events for the same record in the same batch window, and without deduplication, your MERGE will fail with a non-deterministic match error (SQL Server raises an error if more than one source row matches a target row in MERGE).

The PostgreSQL Equivalent: INSERT ... ON CONFLICT

PostgreSQL doesn't have MERGE in older versions (it was added in PG15). The idiomatic pattern uses ON CONFLICT DO UPDATE:

WITH deduped_staging AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY source_updated_at DESC
           ) AS rn
    FROM   stg_customer_updates
)
INSERT INTO dim_customer
    (customer_id, full_name, email, customer_segment,
     sales_territory, effective_from, is_current, load_batch_id)
SELECT
    customer_id, full_name, email, customer_segment,
    sales_territory, CURRENT_DATE, TRUE, load_batch_id
FROM deduped_staging
WHERE rn = 1
ON CONFLICT (customer_id)
    WHERE is_current = TRUE  -- Partial index target
DO UPDATE SET
    full_name        = EXCLUDED.full_name,
    email            = EXCLUDED.email,
    customer_segment = EXCLUDED.customer_segment,
    sales_territory  = EXCLUDED.sales_territory,
    load_batch_id    = EXCLUDED.load_batch_id;

For this to work, you need a unique index that the ON CONFLICT clause targets:

CREATE UNIQUE INDEX uq_dim_customer_current
ON dim_customer (customer_id)
WHERE is_current = TRUE;

This partial unique index enforces exactly one current row per customer_id, and it's the constraint that ON CONFLICT resolves against. It's also a performance asset — queries filtering on is_current = TRUE will hit this index directly.

---

Type 2: Versioned History with Window Functions

Type 2 is where SCD implementations get genuinely complex. The contract is: every time a tracked attribute changes, you:

1. Close out the existing current row (set effective_to and clear is_current)
2. Insert a new row with the new attribute values, effective_from set to today, effective_to as NULL, and is_current as TRUE

The deceptively hard part is reliably detecting what changed in an efficient, scalable way.

Step 1: Identify What Changed

-- Find staging records that represent genuine changes to tracked attributes
WITH current_dimension AS (
    SELECT customer_id,
           customer_key,
           customer_segment,
           sales_territory,
           full_name,
           email
    FROM   dim_customer
    WHERE  is_current = TRUE
),
incoming_changes AS (
    SELECT s.customer_id,
           s.full_name,
           s.email,
           s.customer_segment,
           s.sales_territory,
           s.source_updated_at,
           s.load_batch_id,
           ROW_NUMBER() OVER (
               PARTITION BY s.customer_id
               ORDER BY s.source_updated_at DESC
           ) AS rn
    FROM   stg_customer_updates s
),
deduped_incoming AS (
    SELECT * FROM incoming_changes WHERE rn = 1
),
change_detection AS (
    SELECT
        d.customer_key,
        d.customer_id,
        i.full_name          AS new_full_name,
        i.email              AS new_email,
        i.customer_segment   AS new_customer_segment,
        i.sales_territory    AS new_sales_territory,
        i.source_updated_at,
        i.load_batch_id,
        -- Detect which attributes changed
        CASE WHEN d.customer_segment <> i.customer_segment THEN TRUE ELSE FALSE END AS segment_changed,
        CASE WHEN d.sales_territory  <> i.sales_territory  THEN TRUE ELSE FALSE END AS territory_changed,
        CASE WHEN d.full_name        <> i.full_name        THEN TRUE ELSE FALSE END AS name_changed,
        CASE WHEN d.email            <> i.email            THEN TRUE ELSE FALSE END AS email_changed,
        -- Is this a brand-new customer with no existing record?
        CASE WHEN d.customer_id IS NULL THEN TRUE ELSE FALSE END AS is_new_customer
    FROM       deduped_incoming i
    LEFT JOIN  current_dimension d ON d.customer_id = i.customer_id
)
SELECT * FROM change_detection
WHERE  is_new_customer = TRUE
    OR segment_changed = TRUE
    OR territory_changed = TRUE
    OR name_changed = TRUE
    OR email_changed = TRUE;

Step 2: Close Out Expiring Rows

Now that you know which records need versioning, expire the current rows:

UPDATE dim_customer
SET    effective_to = CURRENT_DATE - INTERVAL '1 day',
       is_current   = FALSE
WHERE  customer_key IN (
    SELECT d.customer_key
    FROM   dim_customer d
    INNER JOIN stg_customer_updates s ON s.customer_id = d.customer_id
    WHERE  d.is_current = TRUE
    AND    (
        d.customer_segment <> s.customer_segment OR
        d.sales_territory  <> s.sales_territory  OR
        d.full_name        <> s.full_name        OR
        d.email            <> s.email
    )
);

Notice the effective_to = CURRENT_DATE - 1 day pattern. The convention is that a row was valid through its effective_to date, and the new row becomes valid from the day after. This creates clean, non-overlapping date ranges: row A is valid from Jan 1 through Jan 14, row B is valid from Jan 15 onward. Querying for "what was the territory on Jan 14?" will correctly return row A.

Step 3: Insert the New Version Rows

INSERT INTO dim_customer
    (customer_id, full_name, email, customer_segment,
     sales_territory, effective_from, effective_to, is_current, load_batch_id)
SELECT
    s.customer_id,
    s.full_name,
    s.email,
    s.customer_segment,
    s.sales_territory,
    CURRENT_DATE       AS effective_from,
    NULL               AS effective_to,
    TRUE               AS is_current,
    s.load_batch_id
FROM  stg_customer_updates s
-- Use ROW_NUMBER to handle duplicate customer_ids in staging
INNER JOIN (
    SELECT customer_id,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY source_updated_at DESC) AS rn
    FROM   stg_customer_updates
) ranked ON ranked.customer_id = s.customer_id AND ranked.rn = 1
-- Only insert if there's an existing closed row (i.e., we just expired it)
-- OR if it's a brand new customer
WHERE EXISTS (
    SELECT 1 FROM dim_customer d
    WHERE  d.customer_id = s.customer_id
    AND    d.is_current = FALSE
    AND    d.effective_to = CURRENT_DATE - INTERVAL '1 day'
)
OR NOT EXISTS (
    SELECT 1 FROM dim_customer d WHERE d.customer_id = s.customer_id
);

Wrapping the Three Steps in a Transaction

These three operations — detect changes, close out rows, insert new rows — must run atomically. If your INSERT succeeds but the UPDATE didn't, you'll have two current rows for the same customer. Always wrap them in an explicit transaction:

BEGIN;

-- Step 1: Expire changed rows
UPDATE dim_customer
SET    effective_to = CURRENT_DATE - INTERVAL '1 day',
       is_current   = FALSE
WHERE  customer_key IN (
    SELECT d.customer_key
    FROM   dim_customer d
    WHERE  d.is_current = TRUE
      AND  EXISTS (
          SELECT 1
          FROM   stg_customer_updates s
          WHERE  s.customer_id = d.customer_id
            AND  (
                s.customer_segment <> d.customer_segment OR
                s.sales_territory  <> d.sales_territory  OR
                s.full_name        <> d.full_name        OR
                s.email            <> d.email
            )
      )
);

-- Step 2: Insert new versions and net-new customers
INSERT INTO dim_customer
    (customer_id, full_name, email, customer_segment,
     sales_territory, effective_from, effective_to, is_current, load_batch_id)
WITH ranked_staging AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id ORDER BY source_updated_at DESC
           ) AS rn
    FROM   stg_customer_updates
)
SELECT
    r.customer_id, r.full_name, r.email,
    r.customer_segment, r.sales_territory,
    CURRENT_DATE, NULL, TRUE, r.load_batch_id
FROM ranked_staging r
WHERE r.rn = 1
  AND (
      -- New customer: no existing record at all
      NOT EXISTS (SELECT 1 FROM dim_customer d WHERE d.customer_id = r.customer_id)
      OR
      -- Changed customer: we just closed out their active row
      EXISTS (
          SELECT 1 FROM dim_customer d
          WHERE  d.customer_id = r.customer_id
            AND  d.is_current  = FALSE
            AND  d.effective_to = CURRENT_DATE - INTERVAL '1 day'
      )
  );

COMMIT;

---

Querying Type 2 History: The Date Range Join

Once your Type 2 dimension is loaded, the pattern for historical queries is a range join:

SELECT
    f.order_id,
    f.order_date,
    f.revenue,
    c.customer_segment,
    c.sales_territory
FROM   fact_orders f
INNER JOIN dim_customer c
    ON  c.customer_id    = f.customer_id
    AND f.order_date     >= c.effective_from
    AND f.order_date     <  COALESCE(c.effective_to + INTERVAL '1 day', '9999-12-31'::DATE)
    AND c.is_current IS NOT FALSE  -- optional hint for partial index
;

The COALESCE trick handles the NULL effective_to for current rows. The + INTERVAL '1 day' is because our effective_to is an inclusive end date. If you store effective_to as exclusive (the day after the last valid day), you'd drop the COALESCE arithmetic.

---

Type 6: The Hybrid Pattern

Type 2 gives you historical accuracy, but it has an ergonomic problem: answering "show me all orders by current customer segment" requires either a self-join or knowing to filter to is_current = TRUE and then join back. Business analysts writing ad-hoc queries in tools like Looker or Tableau often get this wrong, joining on the fact's date range but accidentally picking up historical rows.

Type 6 solves this by adding a current_customer_segment and current_sales_territory column to every row — including historical ones. Every time a new version is created, you also update these columns on all rows for that customer.

-- Extended dim_customer for Type 6
CREATE TABLE dim_customer_t6 (
    customer_key              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id               INTEGER NOT NULL,
    full_name                 VARCHAR(200),
    email                     VARCHAR(200),
    -- Type 2 attributes: historical values at time of record creation
    customer_segment          VARCHAR(50),
    sales_territory           VARCHAR(100),
    -- Type 6 additions: always reflect the CURRENT value
    current_customer_segment  VARCHAR(50),
    current_sales_territory   VARCHAR(100),
    -- Version control
    effective_from            DATE NOT NULL,
    effective_to              DATE,
    is_current                BOOLEAN NOT NULL DEFAULT TRUE,
    load_batch_id             INTEGER
);

The Type 6 Load Pattern

The Type 6 load is a three-phase operation: the same expire-and-insert as Type 2, plus a back-fill of current_* columns across all versions.

BEGIN;

-- Phase 1: Expire changed current rows (identical to Type 2)
UPDATE dim_customer_t6
SET    effective_to = CURRENT_DATE - INTERVAL '1 day',
       is_current   = FALSE
WHERE  customer_key IN (
    SELECT d.customer_key
    FROM   dim_customer_t6 d
    INNER JOIN stg_customer_updates s ON s.customer_id = d.customer_id
    WHERE  d.is_current = TRUE
      AND  (
          s.customer_segment <> d.customer_segment OR
          s.sales_territory  <> d.sales_territory
      )
);

-- Phase 2: Insert new versions with both historical and current values
WITH ranked_staging AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY source_updated_at DESC) AS rn
    FROM   stg_customer_updates
)
INSERT INTO dim_customer_t6
    (customer_id, full_name, email,
     customer_segment, sales_territory,
     current_customer_segment, current_sales_territory,
     effective_from, effective_to, is_current, load_batch_id)
SELECT
    r.customer_id, r.full_name, r.email,
    r.customer_segment,               -- historical value = new value (just inserted)
    r.sales_territory,
    r.customer_segment,               -- current value = new value
    r.sales_territory,
    CURRENT_DATE, NULL, TRUE, r.load_batch_id
FROM ranked_staging r
WHERE r.rn = 1
  AND (
      NOT EXISTS (SELECT 1 FROM dim_customer_t6 d WHERE d.customer_id = r.customer_id)
      OR EXISTS (
          SELECT 1 FROM dim_customer_t6 d
          WHERE  d.customer_id = r.customer_id
            AND  d.is_current  = FALSE
            AND  d.effective_to = CURRENT_DATE - INTERVAL '1 day'
      )
  );

-- Phase 3: Back-fill current_* values on ALL historical rows for changed customers
-- This is the key Type 6 operation
UPDATE dim_customer_t6 AS hist
SET    current_customer_segment = curr.customer_segment,
       current_sales_territory  = curr.sales_territory
FROM   dim_customer_t6 AS curr
WHERE  curr.customer_id  = hist.customer_id
  AND  curr.is_current   = TRUE
  AND  hist.is_current   = FALSE;  -- Only update historical (non-current) rows

COMMIT;

Phase 3 is what makes Type 6 unique and powerful. After a territory change, every historical row for that customer will now carry the new territory in current_sales_territory, while keeping the territory-at-time-of-event in sales_territory. This means:

• Historical query: JOIN dim_customer_t6 ON customer_id AND order_date BETWEEN effective_from AND COALESCE(effective_to, '9999-12-31') and use sales_territory — you get the territory at time of purchase.
• Current segment query: JOIN dim_customer_t6 ON customer_id AND is_current = TRUE and use current_sales_territory — you get today's territory for all time.

---

Handling Late-Arriving Data and Corrections

Real-world SCD loads don't always receive changes in order. A source system might batch-process updates and send yesterday's change today, or a data entry correction might retroactively change a value that was effective three months ago.

Late-Arriving Changes

If a change arrives with a source_updated_at that is earlier than your most recent effective_from in the dimension, you have a history-insertion problem: you need to split an existing row.

-- Example: Customer 1042's territory changed on March 5, but we're loading it on March 20.
-- Currently in dim_customer: one row effective_from March 1 (because we processed March 1's batch then)
-- We need to split that row: March 1–March 4 (old territory), March 5–present (new territory)

WITH late_change AS (
    SELECT
        customer_id      = 1042,
        new_territory    = 'Pacific Northwest',
        change_date      = DATE '2024-03-05'
),
row_to_split AS (
    SELECT d.customer_key, d.effective_from, d.effective_to, d.customer_segment,
           d.sales_territory, d.full_name, d.email
    FROM   dim_customer d
    CROSS JOIN late_change lc
    WHERE  d.customer_id   = lc.customer_id
      AND  d.effective_from < lc.change_date
      AND  (d.effective_to IS NULL OR d.effective_to >= lc.change_date)
)
-- Step 1: Shrink the existing row's end date
UPDATE dim_customer
SET    effective_to = (SELECT change_date - INTERVAL '1 day' FROM late_change),
       is_current   = FALSE
WHERE  customer_key IN (SELECT customer_key FROM row_to_split);

-- Step 2: Insert the new row starting at change_date
INSERT INTO dim_customer
    (customer_id, full_name, email, customer_segment, sales_territory,
     effective_from, effective_to, is_current, load_batch_id)
SELECT
    r.customer_id,  -- you'd pull these from the original row or from staging
    r.full_name,
    r.email,
    r.customer_segment,
    lc.new_territory,
    lc.change_date,
    r.effective_to,    -- preserve the original end date (NULL if it was current)
    r.effective_to IS NULL,  -- is_current only if the row we split was itself current
    -1  -- special load_batch_id for manual corrections
FROM  row_to_split r
CROSS JOIN late_change lc;

This pattern is complex enough that most teams formalize it in a stored procedure or dbt macro. The Stored Procedures and User-Defined Functions: Building Reusable SQL Logic article covers exactly how to encapsulate this kind of stateful logic safely.

Detecting Phantom Changes

A common source of noise in SCD pipelines is phantom changes — records where the source system sends a row even though none of the tracked attributes have actually changed. This happens when source systems fire update triggers on any field modification, even irrelevant ones (like a last-login timestamp).

Window functions are your best defense here:

WITH staged_with_previous AS (
    SELECT
        s.customer_id,
        s.customer_segment,
        s.sales_territory,
        s.source_updated_at,
        -- Compare to what we currently have in the dimension
        d.customer_segment AS dim_customer_segment,
        d.sales_territory  AS dim_sales_territory,
        -- Hash comparison for efficiency at scale
        MD5(s.customer_segment || '|' || s.sales_territory) AS incoming_hash,
        MD5(d.customer_segment || '|' || d.sales_territory) AS current_hash
    FROM       stg_customer_updates s
    LEFT JOIN  dim_customer d
        ON  d.customer_id = s.customer_id
        AND d.is_current  = TRUE
)
SELECT *
FROM   staged_with_previous
WHERE  incoming_hash <> current_hash   -- real change
    OR current_hash IS NULL;           -- new customer (no existing dim row)

The MD5 hash comparison is a pragmatic shortcut for multi-column equality checks. It's not cryptographically meaningful here — you just want a fast fingerprint of the relevant column set. For truly wide rows with many Type 2-tracked columns, hashing the concatenated values can simplify the WHERE clause dramatically. Just make sure your concatenation separator ('|' in the example) can't appear in the actual data, or use a proper row hash function if your platform supports one (Snowflake has HASH(), SQL Server has HASHBYTES()).

---

Using Window Functions to Audit and Repair SCD Tables

Even well-maintained SCD tables accumulate anomalies over time. Window functions are the right tool for auditing them.

Finding Overlapping Date Ranges

-- Detect rows for the same customer_id where date ranges overlap
WITH ordered_versions AS (
    SELECT
        customer_key,
        customer_id,
        effective_from,
        effective_to,
        LEAD(effective_from) OVER (
            PARTITION BY customer_id
            ORDER BY effective_from
        ) AS next_version_start
    FROM dim_customer
),
overlaps AS (
    SELECT *
    FROM ordered_versions
    WHERE effective_to IS NOT NULL
      AND effective_to >= next_version_start  -- end of this row >= start of next row
)
SELECT * FROM overlaps ORDER BY customer_id, effective_from;

Finding Gap Periods

-- Detect customers who have a gap in coverage (no active row for some period)
WITH ordered_versions AS (
    SELECT
        customer_id,
        effective_from,
        effective_to,
        LAG(effective_to) OVER (
            PARTITION BY customer_id
            ORDER BY effective_from
        ) AS prev_effective_to
    FROM dim_customer
)
SELECT
    customer_id,
    prev_effective_to + INTERVAL '1 day' AS gap_start,
    effective_from - INTERVAL '1 day'    AS gap_end
FROM   ordered_versions
WHERE  prev_effective_to IS NOT NULL
  AND  effective_from > prev_effective_to + INTERVAL '1 day';

These audit queries are invaluable during development and should be incorporated into your pipeline's data quality checks. For a comprehensive approach to testing your SCD logic, the Writing Effective SQL Unit Tests: Validating Query Logic, Edge Cases, and Data Contracts in CI/CD Pipelines article shows how to encode these as automated assertions.

Finding Duplicate Current Rows (the Most Dangerous Bug)

SELECT
    customer_id,
    COUNT(*) AS current_row_count
FROM   dim_customer
WHERE  is_current = TRUE
GROUP BY customer_id
HAVING COUNT(*) > 1;

If this query returns anything, you have a critical data integrity problem. Fact tables joining to this dimension will produce fan-out (duplicate rows in query results). Trace back through your load logs using load_batch_id to find which batch created the duplicates and reprocess from that point.

---

Performance Tuning SCD Tables at Scale

A dimension table with years of Type 2 history for millions of customers can grow to hundreds of millions of rows. The query and load patterns need to be designed for this from the start.

Indexing Strategy

For a Type 2 dim_customer table, you want at minimum:

-- Partial unique index for fast current-record lookups and ON CONFLICT targeting
CREATE UNIQUE INDEX uq_dim_customer_active
    ON dim_customer (customer_id)
    WHERE is_current = TRUE;

-- Composite index for range joins from fact tables
CREATE INDEX ix_dim_customer_id_dates
    ON dim_customer (customer_id, effective_from, effective_to);

-- Covering index if most queries also pull segment and territory
CREATE INDEX ix_dim_customer_id_dates_attrs
    ON dim_customer (customer_id, effective_from, effective_to)
    INCLUDE (customer_segment, sales_territory);

The INCLUDE clause (available in PostgreSQL 11+ and SQL Server) allows index-only scans for queries that only need these columns, avoiding a heap fetch entirely.

Partitioning by Load Date

For very large tables, partition by effective_from year or quarter. This enables partition pruning when your date-range join predicates align with partition boundaries:

CREATE TABLE dim_customer_partitioned (
    -- ... same columns ...
) PARTITION BY RANGE (effective_from);

CREATE TABLE dim_customer_2022 PARTITION OF dim_customer_partitioned
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

CREATE TABLE dim_customer_2023 PARTITION OF dim_customer_partitioned
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE dim_customer_2024 PARTITION OF dim_customer_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

When a fact table query includes WHERE order_date BETWEEN '2023-03-01' AND '2023-09-30', the planner can eliminate the 2022 and 2024 partitions entirely during the join. See Partitioning Strategies in SQL: Using Table Partitioning and Partition Pruning to Accelerate Queries on Large Datasets for a thorough treatment of when and how to implement this.

The Sentinel Date vs. NULL Debate

Many teams use 9999-12-31 as the effective_to value for current rows instead of NULL:

Arguments for 9999-12-31:

• Avoids COALESCE in every query
• Enables standard BETWEEN syntax in range joins: order_date BETWEEN effective_from AND effective_to
• Easier to partition on effective_to if needed

Arguments for NULL:

• Semantically accurate: NULL means "no end date" (still valid)
• PostgreSQL's partial unique index WHERE is_current = TRUE is cleaner than WHERE effective_to = '9999-12-31'
• Avoids edge cases when someone runs the code in late December 9999

In practice, both work. Pick one convention and enforce it with a CHECK constraint:

ALTER TABLE dim_customer
ADD CONSTRAINT chk_dim_customer_date_logic
CHECK (
    (is_current = TRUE  AND effective_to IS NULL) OR
    (is_current = FALSE AND effective_to IS NOT NULL AND effective_to >= effective_from)
);

This constraint will catch any load logic that incorrectly sets these fields, surfacing bugs at insert time rather than silently corrupting data.

---

Hands-On Exercise

Work through this scenario independently. A healthcare company tracks which insurance plan a patient is enrolled in. Plan changes affect cost calculations historically, so the team needs a Type 2 dimension.

Setup:

CREATE TABLE stg_patient_plan_updates (
    patient_id         INTEGER,
    patient_name       VARCHAR(200),
    insurance_plan     VARCHAR(100),   -- e.g., 'Blue Shield PPO', 'Aetna HMO'
    plan_tier          VARCHAR(50),    -- 'Individual', 'Family', 'COBRA'
    enrollment_date    DATE,           -- date the plan became effective in the source
    source_updated_at  TIMESTAMP,
    batch_id           INTEGER
);

CREATE TABLE dim_patient_plan (
    patient_plan_key  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id        INTEGER NOT NULL,
    patient_name      VARCHAR(200),
    insurance_plan    VARCHAR(100),
    plan_tier         VARCHAR(50),
    effective_from    DATE NOT NULL,
    effective_to      DATE,
    is_current        BOOLEAN NOT NULL DEFAULT TRUE,
    batch_id          INTEGER
);

-- Seed some existing data
INSERT INTO dim_patient_plan
    (patient_id, patient_name, insurance_plan, plan_tier, effective_from, is_current)
VALUES
    (101, 'Jane Doe',     'Blue Shield PPO', 'Family',     '2023-01-01', TRUE),
    (102, 'Mark Chen',    'Aetna HMO',       'Individual', '2022-06-15', TRUE),
    (103, 'Sarah Park',   'Cigna EPO',       'Family',     '2023-09-01', TRUE);

-- Incoming staging data
INSERT INTO stg_patient_plan_updates VALUES
    (101, 'Jane Doe',   'Aetna HMO',       'Family',     '2024-01-01', NOW(), 1),  -- plan change
    (102, 'Mark Chen',  'Aetna HMO',       'Individual', '2024-01-01', NOW(), 1),  -- no change
    (104, 'Tom Rivera', 'Blue Shield PPO', 'Individual', '2024-01-01', NOW(), 1);  -- new patient

Your Tasks:

1. Write a query that identifies which staging records represent genuine changes (excluding patient 102 who has the same plan).
2. Write the Type 2 load: expire changed rows and insert new versions in a transaction.
3. Add a current_insurance_plan column to the table and write the Phase 3 Type 6 back-fill that updates all historical rows for changed patients with their new current plan.
4. Write an audit query using LAG() to verify there are no gaps in coverage for any patient after your load completes.
5. Bonus: Patient 101 (Jane Doe) actually switched to Aetna HMO on December 15, 2023 — not January 1, 2024. Her current row in staging has enrollment_date = '2023-12-15' but you just processed it with effective_from = '2024-01-01'. Write the late-arriving data correction that splits her current row at the correct date.

---

Common Mistakes & Troubleshooting

Mistake 1: Not Deduplicating Staging Before MERGE

Symptom: MERGE throws a non-deterministic match error, or inserts duplicate rows.
Cause: Multiple staging rows with the same business key.
Fix: Always apply ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY source_updated_at DESC) in a CTE before the MERGE, and filter to rn = 1.

Mistake 2: Using `effective_to = CURRENT_DATE` Instead of `CURRENT_DATE - 1`

Symptom: Date range joins miss events on the last day of a period because the order date equals the effective_to date of the expired row AND the effective_from date of the new row.
Cause: Ambiguous boundary when both the old and new rows share a boundary date.
Fix: Standardize on inclusive effective_to: expired row's effective_to = CURRENT_DATE - 1, new row's effective_from = CURRENT_DATE. Or use exclusive dates consistently throughout (new row effective_from equals old row effective_to, and your join is order_date >= effective_from AND order_date < effective_to).

Mistake 3: Forgetting the Type 6 Back-Fill (Phase 3)

Symptom: current_* columns in old rows don't reflect the latest values.
Cause: Phase 3 UPDATE was omitted or ran against only new rows.
Fix: Ensure Phase 3 updates WHERE is_current = FALSE (historical rows) using the newly inserted current row as the source of truth. Always run after Phase 2 within the same transaction.

Mistake 4: Missing the Partial Unique Index

Symptom: Duplicate current rows in the dimension table; ON CONFLICT doesn't fire.
Cause: The unique constraint targets all rows rather than WHERE is_current = TRUE, so the constraint allows multiple rows with the same customer_id (because historical rows are also present).
Fix: Use a partial unique index: CREATE UNIQUE INDEX ... ON dim_customer (customer_id) WHERE is_current = TRUE.

Mistake 5: NULL Propagation in Hash-Based Change Detection

Symptom: MD5 hash comparison treats NULL values as always "changed," causing phantom Type 2 inserts.
Cause: NULL || 'anything' evaluates to NULL in SQL, making every hash NULL and every comparison unequal.
Fix: Use COALESCE to replace NULLs with a sentinel string before hashing:

MD5(COALESCE(customer_segment, '') || '|' || COALESCE(sales_territory, ''))

For more on NULL behavior traps, see Understanding SQL NULL Handling: COALESCE, NULLIF, and IS NULL for Reliable Data Queries.

Mistake 6: The Fan-Out Join

Symptom: Fact table query returns 2x or 3x the expected row count.
Cause: The fact table join matched multiple dimension rows for the same customer (either duplicate current rows, or the date-range condition wasn't restrictive enough).
Fix: First, run the duplicate current row check query above. If no duplicates, verify your date range join condition covers all possible orders — particularly orders with timestamps exactly on boundary dates. Consider using order_date::DATE to normalize timestamps to dates before comparing with date-type effective_from/effective_to columns.

---

Summary & Next Steps

You now have a complete, production-oriented framework for implementing SCD patterns in SQL.

The core concepts to internalize:

• Type 1 is an overwrite upsert. Use MERGE or INSERT ... ON CONFLICT DO UPDATE with window-function deduplication on the staging side. A partial unique index on the current row is the structural keystone.

• Type 2 is a versioned history pattern. Three operations — expire, insert, audit — must run atomically in a transaction. Window functions (ROW_NUMBER, LAG, LEAD) are essential for deduplication, change detection, and post-load auditing.

• Type 6 extends Type 2 by back-filling current_* attribute columns on all historical rows. This makes BI tools and analyst queries naturally correct for both historical and current-state analysis without requiring the analyst to understand effective dates.

• Edge cases — late-arriving data, phantom changes, NULL propagation, boundary date ambiguity — are where naive implementations break. Build change detection with hashing, enforce structural constraints at the DDL level, and write audit queries that you run after every load.

Where to go next:

To deepen your understanding of time-based query patterns that work on top of these SCD structures, Temporal Data Mastery: Writing Queries for Time-Series, Date Ranges, and Slowly Changing Dimensions extends these concepts into full time-series analysis. For optimizing the range joins that SCD tables create at scale, SQL Query Optimization: Reading Execution Plans - Advanced Performance Analysis will teach you how to read execution plans and identify where these multi-stage CTEs are losing performance. And if you're encapsulating your SCD logic into reusable procedures — which you should be — Stored Procedures and User-Defined Functions: Building Reusable SQL Logic covers the right patterns for parameterizing and testing that kind of stateful load logic.

SCDs are one of those topics where the theory is clean but production reality is messy. The patterns in this lesson are the ones that hold up under that messiness.