Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 36 ยท Modeling and Warehousing

Slowly Changing Dimensions (SCD Types 1, 2, 3)

Handle changing attributes in dimension tables: overwrite with SCD Type 1, keep history with Type 2 effective dates, or store the previous value with Type 3.

A customer moves from Berlin to Madrid. A product moves from the "Toys" category to "Games". If your dimension table simply overwrites the old value, last year's sales suddenly report under the new city or category, and history changes retroactively. If you keep both versions, you can answer both "where do customers live now?" and "where did they live when they bought?".

These changing attributes are called slowly changing dimensions (SCDs). The approach you choose per attribute is called its SCD type. This lesson implements the three most common ones with pandas.

The setup

We have a customer dimension and a fresh extract from the source system, in which Bruno moved and Chen is new.

Python The starting data

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Type 0 and Type 1: no history

Type 0 keeps the original value forever (for example the original signup date). Type 1 overwrites the old value with the new one. It is simple and right when history does not matter, such as fixing a typo in a name.

Python SCD Type 1: overwrite

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The downside: every past fact joined to customer 2 now says "Sao Paulo", even sales made while Bruno lived in Rio.

Type 2: keep full history

Type 2 adds a new row each time a tracked attribute changes. The old row is closed with an end date; the new row gets a new surrogate key and is marked current. Facts loaded before the change keep pointing to the old key, so history stays correct.

Typical Type 2 columns:

Column Meaning
valid_from Date this version became true
valid_to Date it stopped being true (a far-future date such as 9999-12-31 for current)
is_current Convenience flag for the latest version
flowchart LR
  A["key 2: Bruno, Rio<br/>2025-01-01 to 2026-03-01"] --> B["key 4: Bruno, Sao Paulo<br/>2026-03-01 to 9999-12-31 (current)"]
Python SCD Type 2: close and insert

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Bruno now has two rows: the Rio version closed on 2026-03-01 and the Sao Paulo version current. New facts look up the current key; old facts keep the old key.

Point-in-time lookups

To load a late-arriving fact (an event from February arriving in March) you must pick the version that was valid at the event date:

Python Which version was valid on the event date?

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The half-open interval (>= valid_from and < valid_to) avoids a date matching two versions.

Type 3: keep the previous value

Type 3 adds columns such as previous_city. It tracks exactly one prior value, which is enough when you only need "current versus original" (a one-time reorganization of sales regions). It does not scale to repeated changes.

Other types and practical advice

  • Type 4: current values in the main dimension, history in a separate history table.
  • Type 6: a hybrid of 1, 2 and 3: history rows plus a "current value" column updated on every version.
  • Choose the type per attribute. A name correction can be Type 1 in the same table where city is Type 2.
  • Use a hash of tracked columns to detect changes cheaply, instead of comparing column by column.
  • In SQL warehouses, Type 2 is implemented with MERGE; dbt calls this feature snapshots.
  • Beware of noise: if a source updates a timestamp column daily and you track it, you create a new version every day.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which SCD type keeps the full history of a changing attribute?

    Type 2 adds a new row per version with validity dates, so every historical state remains.
  2. 2. What is the main downside of SCD Type 1?

    Overwriting changes how past facts appear in reports.
  3. 3. In a Type 2 table, why is valid_to usually set to a far-future date for current rows?

    A far-future end date lets range predicates work uniformly for current and historical versions.
  4. 4. A tracked attribute is a column that the source rewrites with the load timestamp every night. What happens in Type 2?

    Any change to a tracked column creates a version, so noisy columns should be excluded from tracking.
  5. 5. Type 3 is best when

    Type 3 stores one prior value in an extra column.

Summary

  • SCD types define how dimension attributes react to change: Type 1 overwrites, Type 2 versions rows, Type 3 stores the previous value.
  • Type 2 uses valid_from, valid_to and is_current; facts use the surrogate key valid at the event time.
  • Choose the type per attribute and ignore noisy columns.

Next: Normalization, Denormalization and Data Vault