Skip to content

History and slowly changing dimensions

Ways to keep the past values of data that changes over time, such as a customer's address, so that results can reflect the world as it was when an event happened.

Before reading

Dimensional modelling · Deduplication and merge

Why it matters

A clean table that holds only the current state answers "where does this customer live now?". Many questions are about the past: in which region was this sale made, how many premium customers did we have in March, what did the model know at training time. If history is overwritten, those answers change every time the data changes, and last year's report cannot be reproduced.

How it works

Dimensions change slowly compared with facts, which is where the name slowly changing dimension (SCD) comes from. The types are numbered.

Type 1 overwrites. The dimension holds only the current value. It is simple, and right for corrections, such as a misspelt name, and for attributes nobody analyses historically.

Type 2 keeps every version. A change closes the current row and adds a new one. Each row has a valid_from and a valid_to time, and often an is_current flag. Each version gets its own surrogate key, so a fact can point to the exact version that was valid when it happened. Type 2 is the standard way to keep history in dimensions.

Other types exist. Type 3 keeps the previous value in an extra column, which suits a single planned change such as a region reorganisation. Many models mix types per attribute: type 2 for region and segment, type 1 for name corrections.

flowchart LR
  O1[Order in August 2026] -->|customer_key 501| V1["dim_customer key 501<br/>c-88, Munich<br/>valid 2026-03-01 to 2026-09-15"]
  O2[Order in October 2026] -->|customer_key 774| V2["dim_customer key 774<br/>c-88, Lyon<br/>valid 2026-09-15 to open"]

Facts link to versions in one of two ways. At load time, the fact row looks up the version valid at its event time and stores that version's surrogate key; queries then use a normal join. Or the fact stores only the business key, and queries join on the business key with event time between valid_from and valid_to, a point-in-time join. The first is faster to query; the second keeps facts independent of dimension rebuilds.

Effective dates must be chosen carefully. The time a change happened in the source, taken from CDC or a change timestamp, is better than the time the pipeline noticed it, which can be hours later. Intervals should be half-open, valid_from inclusive and valid_to exclusive, so that no moment belongs to two versions.

Snapshots are a simpler way to keep history: copy the whole table every day with a snapshot date. They are easy to build and query, and they capture state as of each day, but they store much repeated data and miss changes within a day. Table formats offer time travel, but it lasts only until snapshots expire and is not a substitute for modelled history. Tools such as dbt can build type 2 tables from regular comparisons of the source.

With CDC, the raw layer already holds every change. Type 2 history can then be built, and rebuilt, from raw change events with window functions, which is more complete than comparing daily copies.

Key terms

  • Slowly changing dimension: a dimension whose attributes change over time.
  • Type 1: overwrite; no history.
  • Type 2: one row per version, with validity dates.
  • Effective dates: valid_from and valid_to of a version.
  • Point-in-time join: joining on a key and a time within the validity range.
  • Snapshot table: a full copy of a table per day or period.

Example

Customer c-88 moves from Munich to Lyon on 15 September 2026. Building versions from clean_customer_changes, one row per change event taken from raw_customers, with the source change time changed_at and source_offset, keeping only events where a tracked attribute changed:

SELECT customer_id, city, segment,
       changed_at AS valid_from,
       lead(changed_at) OVER (PARTITION BY customer_id
                              ORDER BY changed_at, source_offset) AS valid_to
FROM shop.clean_customer_changes;

The result, with a surrogate customer_key added for each version, is dim_customer. Revenue by customer city at the time of each order, using a point-in-time join:

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

The August order counts for Munich, the October order for Lyon. With a type 1 dimension both would count for Lyon, and August's regional figures would change after the move.

Pitfalls

  • Using load time as valid_from. Versions start hours or days late; use the source change time.
  • Closed intervals. A moment that falls in two versions duplicates facts in joins.
  • Type 2 on every attribute. Frequently changing attributes, such as last login, multiply versions; track only what is analysed historically.
  • Relying on time travel for history. It disappears when snapshots expire.
  • Joining facts to the current dimension row when the question is about the past.

Learn more

  • Kimball Group, slowly changing dimension techniques: https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2/
  • dbt documentation, snapshots: https://docs.getdbt.com/docs/build/snapshots

Multi-source conformance · Change data capture · Backfill and replay · The ML lifecycle and MLOps