SCD Type 1 vs 2 vs 3 vs 4 Compared (With SQL)

Type 1 — Overwrite

UPDATE dim_customer
SET email = 'new.email@example.com'
WHERE customer_id = 1042;
-- The old email is gone permanently — no way to recover it from this table

Simplest possible approach: the row is updated, the old value is gone. Correct for attributes where history genuinely has no value — fixing a typo, updating a phone number where no report ever needs to know the old one.

Type 2 — New row per change

UPDATE dim_customer SET expiration_date = CURRENT_DATE, is_current = FALSE
WHERE customer_id = 1042 AND is_current = TRUE;

INSERT INTO dim_customer (customer_id, region, effective_date, expiration_date, is_current)
VALUES (1042, 'EMEA', CURRENT_DATE, NULL, TRUE);
-- Old fact rows still join correctly to the historical 'is_current = FALSE' version

Full history preserved, and any historical fact still joins to the dimension state that was actually true when the fact occurred. This is the most detailed treatment covered here — see the dedicated SCD Type 2 guide for surrogate key design and a full MERGE-based implementation.

Type 3 — Previous-value column

ALTER TABLE dim_customer ADD COLUMN previous_region VARCHAR(50);

UPDATE dim_customer
SET previous_region = current_region,
    current_region  = 'EMEA'
WHERE customer_id = 1042;
-- Only one prior state is retained — a second change overwrites previous_region too

A middle ground: one "before" value is kept alongside the current one, enough for a before/after comparison, but a second change to the same attribute discards whatever was in previous_region — there's no way to represent three or more historical states this way.

Type 4 — Separate history table

CREATE TABLE dim_customer (            -- current values only, stays small
  customer_id INT PRIMARY KEY,
  region      VARCHAR(50)
);

CREATE TABLE dim_customer_history (    -- every version, grows over time
  history_id     BIGINT PRIMARY KEY,
  customer_id    INT,
  region         VARCHAR(50),
  effective_date DATE,
  expiration_date DATE
);

-- On a change: UPDATE dim_customer with the new value,
-- INSERT the outgoing state into dim_customer_history

The main dimension table stays lean — every query that only needs current values avoids scanning historical rows entirely. The tradeoff is an extra table to maintain and an extra join whenever a report genuinely needs point-in-time history.

Side-by-side comparison

Choosing the right type per attribute

SCD type is a per-attribute decision, not a whole-table one — a single customer dimension might reasonably use Type 1 for a corrected email typo, Type 2 for a customer-tier change that affects historical revenue attribution, and Type 3 for a simple current-vs-previous comparison on a different field. Ask, for each attribute: does a historical fact need to see the value that was true at the time, or only the value as it is now?

Common mistakes

• Defaulting to Type 1 for an attribute that actually needs audit history — a compliance requirement or a "what did this customer's tier look like last quarter" report can't be answered after the fact.
• Applying Type 2 to an attribute that changes extremely frequently, causing the dimension table to grow far faster than expected — Type 4 is usually the better fit there.
• Forgetting to chain Type 3's previous-value column correctly across more than one change, silently losing the value that was in "previous" before the newest update.
• Skipping an index on the history table's foreign key in Type 4, making point-in-time history lookups far slower than they need to be.