What is a Slowly Changing Dimension (SCD)?

How it works

When a source attribute changes, the warehouse must decide what happens to the old value. The type is chosen per attribute, not per table, and most real dimensions mix several:

Type	Keeps	Typical use
0	The original value, never updated	Signup date, original acquisition channel
1	Only the latest value (overwrite)	Corrections, email, phone
2	Every version as its own row	Region, segment, anything reported historically
3	Current value plus one previous value in extra columns	A planned reorganisation compared before and after
4	Current rows plus a separate history table (or a mini-dimension)	Fast current lookups
6	Type 2 history plus a current-value column on every row	“As it was” and “as it is” from one join

Type 1 is the simplest: one row per member, history is lost, and every past fact now reports the new value. That is right for a misspelt name and wrong for “revenue by the city the customer lived in at the time”. Type 2 keeps that history by closing the current row and inserting a new version with a new surrogate key, so old facts keep pointing at the old version.

Whatever the type, the load should only touch rows that really changed. Compare with IS DISTINCT FROM rather than <>, because <> returns NULL, and therefore skips the row, when either side is NULL. A load written that way is also idempotent: running it again with the same extract changes nothing.

Example

A Type 1 load with MERGE: city and email are overwritten, the signup date (Type 0) is left out of the update, and unchanged rows are not rewritten (PostgreSQL 16).

CREATE TABLE dim_customer (customer_id text PRIMARY KEY, email text, city text, signup_date date);
INSERT INTO dim_customer VALUES
  ('C1', '[email protected]', 'Pune', '2025-11-20'),
  ('C2', '[email protected]', 'Delhi', '2025-12-05');

CREATE TABLE stg_customer (customer_id text, email text, city text, signup_date date);
INSERT INTO stg_customer VALUES
  ('C1', '[email protected]', 'Mumbai', '2026-03-10'),  -- signup date is a source bug
  ('C2', '[email protected]', 'Delhi', '2025-12-05');

MERGE INTO dim_customer d
USING stg_customer s ON d.customer_id = s.customer_id
WHEN MATCHED AND (d.email, d.city) IS DISTINCT FROM (s.email, s.city) THEN
  UPDATE SET email = s.email, city = s.city
WHEN NOT MATCHED THEN
  INSERT VALUES (s.customer_id, s.email, s.city, s.signup_date);

SELECT * FROM dim_customer ORDER BY customer_id;

 customer_id |        email         |  city  | signup_date
-------------+----------------------+--------+-------------
 C1          | [email protected] | Mumbai | 2025-11-20
 C2          | [email protected]     | Delhi  | 2025-12-05

Common mistakes

• Choosing one type for the whole table instead of per attribute.
• Using Type 1 for an attribute the business reports historically, which silently restates past reports.
• Detecting changes with <>, which misses changes to or from NULL.
• Using a natural key as the primary key, which makes Type 2 history impossible later.