Data Engineering Medium Asked at dbt LabsAsked at SnowflakeAsked at DatabricksAsked at Amazon

What are slowly changing dimensions, and how do Type 1 and Type 2 differ?

The short answer

Slowly changing dimensions (SCDs) handle attributes that change over time — a customer moving cities, a product changing category. Type 1 overwrites the old value and loses history. Type 2 inserts a new row with effective and expiry dates, preserving the full history of what was true at any point in time. Type 2 is the standard when accurate historical reporting matters.

How to think about it

The SCD problem: source systems update a record in place. Your warehouse dimension must decide whether that update should silently rewrite history (Type 1) or create a new historical record (Type 2).

Type 1 — overwrite

The dimension row is updated in place. No history is retained. Use when the old value was simply wrong (a typo in a name) and historical accuracy against that attribute does not matter.

-- Type 1: just update the row
UPDATE dim_customer
SET city = 'Austin'
WHERE customer_key = 1001;
-- All historical fact rows joining to customer 1001 now show Austin, even past orders.

Type 2 — new row with effective dates

A new dimension row is inserted. The old row gets an effective_end date. The surrogate key changes; the natural key (customer_id) stays the same.

-- Current row: customer 1001 lives in Boston
-- customer_key=1001, customer_id=C42, city='Boston', eff_start='2024-01-01', eff_end='2026-06-04', is_current=TRUE

-- New row after move to Austin
INSERT INTO dim_customer VALUES
  (9999, 'C42', 'Austin', '2026-06-05', '9999-12-31', TRUE);

UPDATE dim_customer
SET eff_end = '2026-06-04', is_current = FALSE
WHERE customer_key = 1001;

Fact rows that occurred while the customer lived in Boston still join to customer_key=1001 (Boston). New fact rows join to customer_key=9999 (Austin). Historical revenue-by-city reports remain accurate.

Querying the current record

SELECT c.city, SUM(f.revenue)
FROM fact_orders f
JOIN dim_customer c ON f.customer_key = c.customer_key
WHERE c.is_current = TRUE        -- or: eff_end = '9999-12-31'
GROUP BY 1;

Type 3 — current and previous value columns

Type 3 adds a previous_city column alongside current_city. Cheap to implement but only retains one prior value — rarely used in practice.

dbt native SCD support

dbt’s snapshot command implements Type 2 automatically using a unique_key and strategy: timestamp or strategy: check. Changes are detected on each snapshot run and new rows are inserted.

Learn it properly Slowly Changing Dimensions