What is SCD Type 2?

How it works

A Type 2 dimension adds validity columns to each row: valid_from, valid_to (exclusive, with a far-future date such as 9999-12-31 for the current row) and is_current. The business key, such as customer_id, repeats across versions; the surrogate key is unique per version.

A load runs in one transaction:

1. Close current rows whose tracked attributes differ from the extract: set valid_to to the load date and is_current to false.
2. Insert a new current row for every business key that has no current row, which covers both changed and brand-new members.
3. Overwrite any Type 1 columns on all versions, if the dimension mixes types.

The load is idempotent: a rerun finds nothing to close and no key without a current row. A partial unique index on the business key WHERE is_current makes a buggy load fail loudly instead of creating two current rows. With MERGE, one statement cannot both close a row and insert its replacement from the same source row, so the usual trick feeds changed rows in twice, once with the key (to close) and once with a NULL key (to insert).

Facts normally store the surrogate key that was current when they were loaded. When facts carry only the business key, as in many lakehouse tables or with late-arriving data, join on the validity range instead. dbt snapshots generate this kind of table automatically, with dbt_valid_from and dbt_valid_to columns.

Example

A point-in-time join: each order finds the customer version whose validity range contains the order date, so March orders report Mumbai and the June order reports Hyderabad.

CREATE TABLE dim_customer (customer_key int, customer_id text, city text,
                           valid_from date, valid_to date, is_current boolean);
INSERT INTO dim_customer VALUES
  (1, 'C1', 'Pune',      '2026-01-01', '2026-03-10', false),
  (3, 'C1', 'Mumbai',    '2026-03-10', '2026-06-01', false),
  (5, 'C1', 'Hyderabad', '2026-06-01', '9999-12-31', true);

CREATE TABLE fact_order (order_id text, order_date date, customer_id text);
INSERT INTO fact_order VALUES
  ('O-1', '2026-03-02', 'C1'), ('O-2', '2026-03-15', 'C1'), ('O-3', '2026-06-20', 'C1');

SELECT f.order_id, f.order_date, d.customer_key, d.city AS city_at_order_time
FROM fact_order f
JOIN dim_customer d
  ON d.customer_id = f.customer_id
 AND f.order_date >= d.valid_from
 AND f.order_date <  d.valid_to
ORDER BY f.order_date;

 order_id | order_date | customer_key | city_at_order_time
----------+------------+--------------+--------------------
 O-1      | 2026-03-02 |            1 | Pune
 O-2      | 2026-03-15 |            3 | Mumbai
 O-3      | 2026-06-20 |            5 | Hyderabad

Common mistakes

• Using BETWEEN with an inclusive valid_to, so an order on the change date matches two versions.
• Leaving valid_to NULL for the current row, which breaks range joins unless every query adds COALESCE.
• Opening a new version on every load even when nothing changed, because the comparison ignores NULLs or compares untracked columns.
• Running the close and insert steps in separate transactions, so a failure leaves a customer with no current row.