---
title: "Slowly changing dimensions (SCD)"
description: "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."
canonical: "https://motherduck.com/glossary/slowly-changing-dimensions/"
related:
  - title: "Data loading patterns | MotherDuck Docs"
    url: "https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-patterns/"
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "Storage Lifecycle and Management | MotherDuck Docs"
    url: "https://motherduck.com/docs/concepts/storage-lifecycle/"
---

# 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_current` columns. 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

```sql
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.