Skip to main content

Guide: SCD Type 1, 2, 3, 4

Slowly Changing Dimensions is the term Ralph Kimball used to describe one of the most nuanced challenges in dimensional modeling: what do you do when a dimension attribute changes? The answer is not uniform — it depends on the attribute, the business meaning of the change, and whether historical accuracy is required for that attribute.

Every data warehouse architect must understand SCD Type 1, Type 2, and Type 3. Choosing the wrong type for a critical attribute produces analytics that silently lie — reporting historical revenue against a customer's current segment, or crediting current-state carbon tiers to past shipments.

TalkingSchema's AI copilot generates SCD-ready dimension tables on request and explains the analytics implications of each choice.

SCD Type 0: Retain Original​

The dimension attribute never changes — it captures the state at the time the entity was first created and is immutable thereafter.

Use for: Creation date, original onboarding source, founding country of a supplier, first purchase date.

-- Type 0: onboarding_source never changes after initial load
CREATE TABLE dim_customer (
    customer_key        INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_bk         UUID NOT NULL,
    company_name        VARCHAR(200) NOT NULL,       -- Type 1 (correctable)
    customer_tier       VARCHAR(20) NOT NULL,        -- Type 2 (historical)
    onboarding_source   VARCHAR(50) NOT NULL,        -- Type 0 (immutable)
    created_date        DATE NOT NULL                -- Type 0 (immutable)
);

SCD Type 1: Overwrite (No History)​

When the attribute changes, the existing row is updated in-place. No historical record is kept. All fact rows — past and future — reflect the current attribute value.

Use for: Corrections and typo fixes, attributes with no analytical meaning in historical context (e.g., company_name spelling corrections, address updates for mailing purposes).

Risk: Any historical report sliced by this attribute will use the current value, not the value at the time of the transaction. For non-analytical attributes this is acceptable; for analytical attributes (tier, segment, classification) it is dangerous.

-- SCD Type 1: Update in place
-- No history retained — all historical analyses now see the updated value
UPDATE dim_supplier
SET certification = 'ISO 14001:2015'
WHERE supplier_bk = '8f4e2c1a-...'
  AND is_current_row = TRUE;

SCD Type 2: Add New Row (Full History)​

When the attribute changes, a new row is inserted with the new attribute value. The previous row's effective_to date is set. The new row gets effective_from = change date, effective_to = NULL (indicating it is the current record).

This is the gold standard for analytical accuracy. It means a sales report from Q3 2024 reflects the supplier's carbon tier as it was in Q3 2024 — not the tier after a reclassification in 2025.

-- SCD Type 2 dimension table structure
CREATE TABLE dim_supplier (
    supplier_key        INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    supplier_bk         UUID NOT NULL,               -- Natural key (same across versions)
    company_name        VARCHAR(200) NOT NULL,        -- Type 1
    country             VARCHAR(100) NOT NULL,        -- Type 1
    carbon_tier         VARCHAR(1) NOT NULL,          -- Type 2 (analytically meaningful)
    carbon_tier_label   VARCHAR(50) NOT NULL,         -- Type 2
    is_active           BOOLEAN NOT NULL,             -- Type 2
    -- SCD Type 2 tracking columns
    effective_from      DATE NOT NULL,
    effective_to        DATE,                         -- NULL = current row
    is_current_row      BOOLEAN NOT NULL DEFAULT TRUE
);

-- ── SCD Type 2 Load Logic ─────────────────────────────────────

-- Step 1: Expire the current row when carbon_tier changes
UPDATE dim_supplier
SET
    effective_to   = CURRENT_DATE - INTERVAL '1 day',
    is_current_row = FALSE
WHERE supplier_bk   = '8f4e2c1a-...'
  AND is_current_row = TRUE
  AND carbon_tier   != 'A';  -- Only if the value actually changed

-- Step 2: Insert new current row with updated carbon_tier
INSERT INTO dim_supplier (
    supplier_bk, company_name, country,
    carbon_tier, carbon_tier_label, is_active,
    effective_from, effective_to, is_current_row
)
SELECT
    supplier_bk, company_name, country,
    'A',                    -- New carbon tier (upgraded)
    'Tier A — Gold Certified',
    is_active,
    CURRENT_DATE,           -- Effective from today
    NULL,                   -- No expiry = current record
    TRUE
FROM dim_supplier
WHERE supplier_bk = '8f4e2c1a-...'
  AND is_current_row = TRUE;

Querying SCD Type 2 dimensions​

To get the historically accurate view — fact records joined to the dimension state that was current at the time of the transaction:

-- Point-in-time accurate query
-- "What was each supplier's carbon tier when each sale occurred?"
SELECT
    dd.year,
    dd.quarter_label,
    ds.carbon_tier,
    ds.company_name,
    SUM(fs.line_revenue_usd) AS revenue
FROM fact_sales fs
JOIN dim_date dd     ON fs.order_date_key = dd.date_key
JOIN dim_supplier ds ON fs.supplier_key = ds.supplier_key
    -- The supplier_key in fact_sales already points to the correct
    -- SCD2 row — no date range filter needed if ETL was correct
GROUP BY dd.year, dd.quarter_label, ds.carbon_tier, ds.company_name;

-- To get the current-state view (today's tier for all historical sales):
JOIN dim_supplier ds ON ds.supplier_bk = (
    SELECT supplier_bk FROM dim_supplier WHERE supplier_key = fs.supplier_key
) AND ds.is_current_row = TRUE

SCD Type 3: Add Prior Column​

Instead of a new row, add a new column to store the previous value. The dimension table retains the current value and the immediately prior value — but no older history.

Use for: Business scenarios where exactly two states matter: current and previous. Not suitable for attributes that change frequently.

-- SCD Type 3: Add a 'prior_carbon_tier' column
ALTER TABLE dim_supplier
  ADD COLUMN prior_carbon_tier       VARCHAR(1),
  ADD COLUMN prior_tier_change_date  DATE;

-- Update logic on tier change:
UPDATE dim_supplier
SET
    prior_carbon_tier      = carbon_tier,          -- Preserve previous value
    prior_tier_change_date = CURRENT_DATE,
    carbon_tier            = 'A'                   -- Apply new value
WHERE supplier_bk = '8f4e2c1a-...'
  AND is_current_row = TRUE;
-- Query: "How did carbon tier changes affect revenue?" (current vs prior state)
SELECT
    ds.company_name,
    ds.prior_carbon_tier AS tier_before,
    ds.carbon_tier AS tier_after,
    ds.prior_tier_change_date AS change_date,
    SUM(fs.line_revenue_usd) AS revenue_since_upgrade
FROM fact_sales fs
JOIN dim_supplier ds ON fs.supplier_key = ds.supplier_key
WHERE fs.order_date_key >= (
    SELECT date_key FROM dim_date WHERE full_date = ds.prior_tier_change_date
)
GROUP BY ds.company_name, ds.prior_carbon_tier, ds.carbon_tier, ds.prior_tier_change_date;

SCD Type 4: History Table​

Separate current-state records (in the main dimension table) from historical records (in a history table). The main table always has one row per entity with current values. The history table records every prior version.

-- Main dimension: always current values (Type 1 behavior)
CREATE TABLE dim_supplier (
    supplier_key    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    supplier_bk     UUID NOT NULL UNIQUE,
    company_name    VARCHAR(200) NOT NULL,
    carbon_tier     VARCHAR(1) NOT NULL,
    is_active       BOOLEAN NOT NULL
);

-- History table: all past versions
CREATE TABLE dim_supplier_history (
    history_key     INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    supplier_bk     UUID NOT NULL,
    company_name    VARCHAR(200) NOT NULL,
    carbon_tier     VARCHAR(1) NOT NULL,
    is_active       BOOLEAN NOT NULL,
    effective_from  DATE NOT NULL,
    effective_to    DATE NOT NULL     -- Always has an end date (historical only)
);

Generating SCD Dimensions with TalkingSchema​

Generate a dim_supplier dimension table with the following SCD strategy:

- company_name: SCD Type 1 (overwrite — corrections only)
- carbon_tier: SCD Type 2 (full history — analytically meaningful)
- certification: SCD Type 2 (full history)
- is_active: SCD Type 2 (full history — deactivation is a business event)
- country: SCD Type 1 (correction only — suppliers don't move countries)

Include: surrogate key, business key, all columns,
SCD Type 2 columns (effective_from, effective_to, is_current_row).
Also generate: ETL logic to apply a new version of a supplier record
when carbon_tier changes, and the SQL to expire the old row.

Frequently Asked Questions​

Can one dimension table mix SCD types for different columns?​

Yes — and this is common. dim_supplier might be SCD Type 2 for carbon_tier (analytically meaningful) and SCD Type 1 for company_name (correction only). Each column has its own SCD type assignment, but the row-level tracking (effective dates, is_current) is shared across all Type 2 columns in the same table.

Does SCD Type 2 affect fact table design?​

Significantly. With SCD Type 2, the ETL process must resolve the correct dimension surrogate key at the time of the transaction — not just the current surrogate key. This means joining to the dimension table with a date range predicate during ETL loading, or using the effective_from/effective_to range to find the correct row. Tools like dbt handle this with snapshot models.

What SCD type should I use for status/flag columns?​

Status columns with analytical meaning (carbon_tier, customer_tier, is_active) should be SCD Type 2. Status flags used only for filtering current operations (is_deleted) should be SCD Type 1.