Slowly changing dimensions (SCD)
Slowly changing dimensions (SCD) are techniques for handling changes to dimension attributes over time — such as a customer moving to a new region — while preserving or overwriting historical values as needed.
Overview
Dimension attributes aren't static: a customer relocates, a product gets reclassified, an employee changes departments. Slowly changing dimension (SCD) techniques, popularized by Ralph Kimball, define how a warehouse should react when that happens. The three most common types:
- Type 0: never change the value once loaded — used for genuinely immutable attributes.
- Type 1: overwrite the old value with the new one. Simple, but history is lost — old facts now appear to have always had the new attribute value.
- Type 2: insert a new dimension row for the change and keep the old row, tracking validity with
effective_date/end_date/is_currentcolumns. This preserves full history and is the most common approach in mature warehouses. - Type 3: add a new column (e.g.,
previous_region) to track just the prior value alongside the current one — a lightweight compromise when you only need one level of history.
Example: SCD Type 2
Copy code
CREATE TABLE dim_customer (
customer_key INTEGER PRIMARY KEY,
customer_id VARCHAR, -- natural key, repeats across versions
region VARCHAR,
effective_date DATE,
end_date DATE,
is_current BOOLEAN
);
-- Close out the old row when a customer's region changes
UPDATE dim_customer
SET end_date = CURRENT_DATE, is_current = false
WHERE customer_id = 'C123' AND is_current;
-- Insert the new version
INSERT INTO dim_customer
VALUES (nextval('seq_customer_key'), 'C123', 'EMEA', CURRENT_DATE, NULL, true);
Fact rows loaded before the change keep referencing the old customer_key, so historical reports still show the customer's region as it was at the time of the sale — that's the entire point of Type 2.
DuckDB angle
As of DuckDB 1.4, the MERGE INTO statement can express an SCD Type 2 upsert (matching, updating, and inserting) in a single statement, which DuckDB's own documentation covers as a guide. On earlier versions, or for portability, the explicit UPDATE + INSERT pattern above works everywhere and is easy to reason about.
Related terms
A dimension table stores the descriptive, mostly-textual attributes — like customer name, product category, or region — that you use to filter, group, and label the measures in a fact table.
Conformed dimension →A conformed dimension is a dimension table — like a shared date or customer dimension — that is defined identically and reused across multiple fact tables or data marts, so metrics stay comparable across the warehouse.
Surrogate key →A surrogate key is a system-generated identifier — typically a sequential integer or UUID — assigned to a row that has no business meaning of its own, used as the primary key in dimension and fact tables.
Natural key →A natural key is an identifier that comes from real-world business data — like an email address, SSN, or product SKU — as opposed to a surrogate key generated purely for database use.
Degenerate dimension →A degenerate dimension is a dimension attribute — like an invoice or order number — that's stored directly as a column in the fact table, with no corresponding dimension table, because it has no other descriptive attributes.
Junk dimension →A junk dimension is a single dimension table that groups together several low-cardinality, otherwise-unrelated flags and indicators, to avoid cluttering a fact table with many tiny separate dimension keys.
FAQS
Type 1 overwrites the old attribute value, losing history. Type 2 inserts a new dimension row and keeps the old one, so historical facts still join to the attribute value that was true at the time — preserving full history at the cost of more rows.
Type 2 is the most common default in mature warehouses because it preserves history without extra columns per attribute. Use Type 1 for attributes where history genuinely doesn't matter (e.g., correcting a typo), and Type 0 for attributes that should never change.
