Slowly changing dimension (SCD)

A slowly changing dimension (SCD) is a dimension table whose values change now and then. Its type says how the change is kept: overwrite the value (type 1) or keep every version (type 2).

Select any words here to ask your Maestro about them.

In July, cold brew moves from the Coffee category to a new one, Ready to drink. Your dim_product is type 1: it overwrites the category. In October you rerun the January to June report you sent in June. What happens to Coffee's revenue?

A coffee chain. In July, cold brew moves from Coffee to a new Ready to drink category. Example data.

What it is: the dimension tables in a star schema describe customers, products or stores, and those descriptions change: someone moves, a product changes category. A slowly changing dimension is one of those tables, and its type is the rule for keeping the changes.

The types: type 1 overwrites the value and loses the history. Type 2 adds a row per version, with start and end dates, and keeps the history. Type 3 keeps the previous value in another column.

How it's built: in a data warehouse, with SQL in the ETL job. In dbt, snapshots build type 2 tables for you.

Model one in the course Data Modeling for AI Agents.

How it's used

One row per version

dim_product (type 2)

product_key | product_id | category | valid_from | valid_to
1041 | P-88 | Coffee | 2024-03-01 | 2026-06-30The old version: it ends the day before the change.
marked: 2213 | P-88 | Ready to drink | 2026-07-01 | 9999-12-31The current version; 9999-12-31 means "still current".

Each sale stores the product_key of the version current that day, so sales before July point to 1041 and stay in Coffee.

When to look closer

Joining on the wrong key

Sales joined to dim_product

marked: dim_product has two rows for P-88, cold brew

Your type 2 dim_product has two rows for cold brew. You join sales on product_id. What happens to cold brew's revenue?

Ask your Maestro about Slowly changing dimension

It answers from the free course page closest to this word, the way it answers while you study.

Example

You: What does "Slowly changing dimension" mean, in plain words?

Your Maestro: I'll explain it with a case of yours: it sticks better than a definition. Which detail about your customers or products changes over time?

Asking about: Answers from the page "What should I check in a table before cleaning it? Build a problem inventory"

Only this browser keeps what you ask here. How it works

Learn more

Star schema
A star schema arranges data for analysis: a fact table of events, like sales, in the center, joined by keys to dimension tables (customer, product, date) that describe them.
Data warehouse
A data warehouse is a database built for analysis: it brings data from many systems into one place, keeps its history and organizes it for queries.
dbt (data build tool)
dbt (data build tool) turns SQL SELECT queries into tables or views in your data warehouse, runs them in order and tests the results: dbt run builds the models, dbt test checks them.
ETL (extract, transform, load)
ETL (extract, transform, load) copies data out of the systems where it's created, cleans and reshapes it, and loads it into a data warehouse so it can be analyzed together.

The whole glossary