Slowly Changing Dimensions Type 2 in SQL (Full Implementation)

Why Type 1 isn't enough

The simplest way to handle a changed dimension value is to just UPDATE it in place — that's SCD Type 1. It's fine for corrections (fixing a misspelled name), but it destroys history: if a customer's plan_tier changes from "Free" to "Pro" and the old row is overwritten, every historical fact that joined to that customer now reports "Pro" retroactively, even for orders placed while they were still on the free plan. SCD Type 2 exists specifically to prevent that — every version of a dimension row stays queryable forever.

The SCD Type 2 table shape

CREATE TABLE dim_customer (
  customer_sk         INT PRIMARY KEY,
  customer_id         INT NOT NULL,
  plan_tier           VARCHAR(50),
  region               VARCHAR(50),
  effective_start_date DATE NOT NULL,
  effective_end_date   DATE,
  is_current           BOOLEAN NOT NULL DEFAULT TRUE
);

Detecting a change

Compare the incoming source row to the current dimension row for that business key. Any tracked column that differs means a new version is needed:

SELECT s.customer_id, s.plan_tier, s.region
FROM staging_customer s
JOIN dim_customer d
  ON d.customer_id = s.customer_id
 AND d.is_current = TRUE
WHERE s.plan_tier IS DISTINCT FROM d.plan_tier
   OR s.region     IS DISTINCT FROM d.region;

IS DISTINCT FROM is worth knowing here over a plain <>: it treats NULL vs. NULL as "not different," where a normal <> comparison against NULL evaluates to unknown and silently skips the row. On databases without IS DISTINCT FROM (older MySQL), NOT (a <=> b) or an explicit COALESCE comparison covers the same case.

Expiring the old row

For every changed customer, close out their current row:

UPDATE dim_customer
SET effective_end_date = CURRENT_DATE - INTERVAL '1 day',
    is_current = FALSE
WHERE customer_id IN (-- the changed customer_ids from the detection query
  SELECT s.customer_id
  FROM staging_customer s
  JOIN dim_customer d ON d.customer_id = s.customer_id AND d.is_current = TRUE
  WHERE s.plan_tier IS DISTINCT FROM d.plan_tier
     OR s.region     IS DISTINCT FROM d.region
)
AND is_current = TRUE;

Inserting the new version

INSERT INTO dim_customer
  (customer_sk, customer_id, plan_tier, region, effective_start_date, effective_end_date, is_current)
SELECT
  NEXT VALUE FOR customer_sk_seq,   -- or your surrogate key generator
  s.customer_id,
  s.plan_tier,
  s.region,
  CURRENT_DATE,
  NULL,
  TRUE
FROM staging_customer s
JOIN dim_customer d
  ON d.customer_id = s.customer_id AND d.is_current = FALSE AND d.effective_end_date = CURRENT_DATE - INTERVAL '1 day';

Run in this order — UPDATE then INSERT — inside a single transaction, so a mid-load failure never leaves a customer with two rows both marked current, or none at all.

Doing it in one statement with MERGE

Databases with a full MERGE implementation (SQL Server, Snowflake, Oracle, Databricks) can combine detection and expiry into one statement, though the insert of the brand-new version is usually still handled as a separate step, since a single MERGE can't both close an old row and insert an unrelated new row for the same match in every dialect:

MERGE INTO dim_customer AS tgt
USING staging_customer AS src
  ON tgt.customer_id = src.customer_id AND tgt.is_current = TRUE
WHEN MATCHED AND (
     tgt.plan_tier IS DISTINCT FROM src.plan_tier
  OR tgt.region     IS DISTINCT FROM src.region
)
THEN UPDATE SET
  effective_end_date = CURRENT_DATE - INTERVAL '1 day',
  is_current = FALSE;
-- followed by a separate INSERT ... SELECT for the new versions, as above

Querying an SCD Type 2 table

Two very different queries, both trivial once the table is built correctly:

-- Just the latest version of every customer
SELECT * FROM dim_customer WHERE is_current = TRUE;

-- What was true on a specific historical date
SELECT * FROM dim_customer
WHERE customer_id = 42
  AND '2026-03-15' BETWEEN effective_start_date AND COALESCE(effective_end_date, '9999-12-31');

Fact tables join on customer_sk, not customer_id — that's the whole mechanism that pins a historical order to the exact plan tier and region that were true the moment the order happened, regardless of how many times the customer's attributes have changed since.

Type 1 vs Type 2 vs Type 3

Common mistakes

• Joining fact tables to the business key instead of the surrogate key. This silently defeats the entire purpose of SCD Type 2 — every historical fact ends up reporting whatever the current dimension state is.
• Using <> instead of IS DISTINCT FROM for change detection when tracked columns can be NULL — a change from NULL to a real value, or vice versa, gets silently missed.
• Not wrapping the expire-then-insert in a transaction. A failure between the two steps can leave a customer with zero current rows or two.
• Tracking every column as SCD Type 2 when only a few actually matter for historical reporting — this multiplies row counts for changes nobody will ever query historically.
• Forgetting an index on (business_key, is_current) — every load and every "current state" query filters on exactly that pair.