Slowly Changing Dimensions (SCD) Explained: Types 1, 2, and 3

Slowly changing dimensions (SCDs) are the techniques a data warehouse uses to handle attributes that change over time, such as a customer's address or a product's category. The three classic approaches are Type 1, which overwrites the old value; Type 2, which keeps full history by adding a new row for each change; and Type 3, which stores the previous value in an extra column. Choosing between them decides whether your reports show the world as it is now or as it was when each event happened.

What is a slowly changing dimension?

In dimensional modeling, fact tables hold measurable events like orders or page views, and dimension tables hold the descriptive context: customers, products, stores. If you are new to this layout, star schema vs snowflake schema covers the basics.

Dimension attributes are mostly stable, but not permanently. A customer moves from Denver to Austin. A product is recategorized from "Accessories" to "Audio". A sales rep changes territory. These changes are slow compared with the stream of facts, hence the name, but they still raise a real question.

Suppose a customer placed orders while living in Denver, then moved. If you report revenue by city for last year, should those orders count toward Denver or Austin? There is no universally correct answer. It depends on what the business wants to measure, and the SCD type is how you encode that decision.

SCD Type 1: overwrite the value

Type 1 simply updates the existing row. No history is kept.

After the update, every historical order for C42 appears under Austin. This is appropriate when:

• The old value was a mistake, such as a typo in a name.
• History has no analytical value, such as a phone number format.
• The business explicitly wants current-state reporting.

Type 1 is simple and keeps tables small, but it silently rewrites history in every report that uses the attribute, so apply it deliberately.

SCD Type 2: add a new row for each change

Type 2 preserves full history. When a tracked attribute changes, the current row is closed and a new row is inserted. The dimension ends up with one row per version of each entity.

Three pieces make this work.

Surrogate keys

The business key customer_id is no longer unique, because C42 now has two rows. A surrogate key (customer_sk) is a warehouse-generated identifier, typically an identity column or sequence, that uniquely identifies each version. Fact tables store the surrogate key that was current when the event occurred, so an order from 2025 points to row 1001 and keeps reporting under Denver forever.

Effective dates

valid_from and valid_to define the period during which each version was true. Many teams leave valid_to null for the current row, while others use a far-future date like 9999-12-31 so range filters work without null handling. Pick one convention and use it everywhere. Also decide whether the interval is inclusive or exclusive at the end; a half-open interval (valid_from <= t < valid_to) avoids overlaps.

Current flag

is_current is technically redundant with valid_to, but it makes the most common query, "give me the current version", trivial and fast. It also simplifies the merge logic below.

Loading SCD Type 2 with SQL MERGE

A single MERGE cannot both update and insert for the same matched source row. The standard workaround is to feed each changed record into the merge twice: once with its business key, so it matches and closes the old row, and once with a null key, so it falls through to the insert branch as a new version.

MERGE INTO dim_customer AS tgt
USING (
  -- All incoming rows, keyed by business key
  SELECT s.customer_id AS merge_key, s.customer_id, s.name, s.city, s.segment, s.updated_at
  FROM stg_customer AS s

  UNION ALL

  -- Changed rows again with a NULL key so they are inserted as new versions
  SELECT NULL AS merge_key, s.customer_id, s.name, s.city, s.segment, s.updated_at
  FROM stg_customer AS s
  JOIN dim_customer AS d
    ON d.customer_id = s.customer_id
   AND d.is_current = TRUE
  WHERE d.city IS DISTINCT FROM s.city
     OR d.segment IS DISTINCT FROM s.segment
) AS src
ON tgt.customer_id = src.merge_key
   AND tgt.is_current = TRUE

-- Close the current version when a tracked attribute changed
WHEN MATCHED AND (tgt.city IS DISTINCT FROM src.city
               OR tgt.segment IS DISTINCT FROM src.segment) THEN
  UPDATE SET valid_to = src.updated_at,
             is_current = FALSE

-- Insert brand-new customers and new versions of changed customers
WHEN NOT MATCHED THEN
  INSERT (customer_id, name, city, segment, valid_from, valid_to, is_current)
  VALUES (src.customer_id, src.name, src.city, src.segment, src.updated_at, NULL, TRUE);

How it behaves for each case:

1. New customer: the first branch has no match, so it is inserted as current. The second branch produces nothing because there is no existing row to join.
2. Unchanged customer: the first branch matches but fails the change condition, so nothing happens.
3. Changed customer: the first branch matches and closes the old row. The null-keyed copy never matches and is inserted as the new current version.

A few details matter in production. IS DISTINCT FROM treats nulls as comparable values; a plain <> returns unknown when either side is null and would miss changes to or from null. The customer_sk column is omitted from the insert so the identity column fills it. The staging table must contain at most one row per customer_id per run, or the merge will fail or produce duplicates, so deduplicate first. MERGE with IS DISTINCT FROM is supported in engines such as PostgreSQL 15 and later, Snowflake, and Databricks, though minor syntax may differ.

If you build models with dbt, its snapshot feature implements this same Type 2 pattern declaratively; see dbt explained.

Joining facts to a Type 2 dimension

When facts already store the surrogate key, the join is a plain equality on customer_sk. When they only store the business key, join on the business key and the event time:

SELECT d.city, SUM(f.amount) AS revenue
FROM fct_orders AS f
JOIN dim_customer AS d
  ON d.customer_id = f.customer_id
 AND f.ordered_at >= d.valid_from
 AND (f.ordered_at < d.valid_to OR d.valid_to IS NULL)
GROUP BY d.city;

Resolving surrogate keys at load time is usually preferred, because it makes downstream queries simpler and faster.

SCD Type 3: keep the previous value in a column

Type 3 adds columns for a limited amount of history, usually just the prior value.

This supports "before and after" comparisons, such as reporting under both the old and new sales territories during a reorganization. It cannot represent more than one prior value, so a second move overwrites Denver. Type 3 is used less often, typically for planned, one-off transitions.

SCD Type 1 vs Type 2 vs Type 3

You can mix types within one dimension. A common design treats email as Type 1, since only the current value matters, while city and segment are Type 2, since analysts need historical accuracy.

Other SCD types

The literature also describes Type 0 (never change the value after first load), Type 4 (keep history in a separate table), and hybrid Type 6, which combines Types 1, 2, and 3 so each row carries both historical and current values. They are useful to know by name, but Types 1 and 2 cover most real-world needs.

Key takeaways

• Slowly changing dimensions define how a warehouse handles attributes that change over time.
• Type 1 overwrites and loses history; use it for corrections and attributes with no analytical history.
• Type 2 adds a row per change using surrogate keys, effective dates, and a current flag, preserving accurate history.
• Type 3 stores one previous value in a column, useful for planned transitions.
• Load Type 2 with a MERGE that feeds changed rows twice, and use null-safe comparisons.
• Mix types per attribute based on what the business needs to report.

Frequently asked questions

Which SCD type is most common?

Type 2 is the most widely used when history matters, because it answers questions accurately as of any point in time. Type 1 is common for attributes where only the current value is relevant. Many dimensions use both for different columns.

Why do SCD Type 2 tables need surrogate keys?

Each change adds a new row for the same business key, so the business key is no longer unique. A surrogate key uniquely identifies each version and lets fact rows point to the exact version that was current when the event occurred.

Should valid_to be null or a far-future date?

Both work. A null clearly means the row is current but requires null handling in range filters, while a far-future date like 9999-12-31 simplifies range queries. Consistency across the warehouse matters more than which convention you pick.

How do I handle late-arriving dimension changes?

If a change arrives with an effective date earlier than the current version, you may need to split an existing version and repoint affected facts. Many teams restrict this to a reprocessing job rather than the regular merge, since it is rare and more complex.