What are slowly changing dimensions (SCD type 1, 2 and 3)?

Slowly changing dimensions are the rules for what a data warehouse does when a descriptive record changes: a customer moves, a product changes category, a sales rep changes region. There are 3 common answers, numbered in the Kimball Group’s dimensional modelling techniques. Type 1 overwrites the old value, type 2 adds a new dated version of the row, and type 3 keeps the previous value in an extra column.

Why a change is a problem

A warehouse keeps 2 kinds of table. Facts record events with amounts, such as orders. Dimensions describe who and what the events were about, such as customers and products, and reports group facts by them: sales by city, by tier, by category. These records change slowly, which is where the name comes from, but they do change. When a customer moves from York to Bristol, what should happen to the orders she placed while she lived in York? The answer decides what every report about cities says.

STORED AS SCD TYPE 2

Type 1: overwrite

The customer's row is updated in place: the city becomes Bristol. It is simple and the table stays small, one row per customer. But history is gone. Every past order now reports under Bristol, so last spring's sales for York shrink after the fact, and any summary already built from them is wrong until it is rebuilt. Type 1 suits corrections, such as a misspelt name, and attributes nobody reports history by.

Type 2: add a version

The current row is closed with an end date, and a new row is added for the new values, with a start date and no end. The customer now has 2 rows, and each order joins to the row that was current on the day it was placed. York keeps its spring sales, and Bristol gets only what came after the move. This is the type most teams mean by keeping history.

It costs rows, since a customer who changes often has many versions, and every join has to pick the right version. Kimball's way gives each version its own surrogate key and writes that key on each fact row as it loads. The other common way joins on dates: an order matches the version whose start is on or before the order date and whose end is after it, or null.

You can also choose which columns make a new version. If only the city matters for history, a change of loyalty tier can overwrite the current version instead. The cost appears in the demo above: orders from before the change of tier now report under the new tier.

Type 3: keep the previous value

A second column holds the value before the last change, such as previous_city beside city. Reports can group by either. It handles one change well, such as a sales territory redrawn once, but the second move overwrites the first, so it is used less often than the other 2.

On Databricks

A Lakeflow pipeline does types 1 and 2 with AUTO CDC ... STORED AS SCD TYPE 1 or TYPE 2, from a change data captureChange Data CaptureCopying only the rows that were added, changed or deleted in a source database, read from its change log, instead of copying the whole table again. feed or from snapshots. It orders changes by a SEQUENCE BY column, so an update that arrives late cannot overwrite a newer one, and marks each type 2 version with __START_AT and __END_AT. TRACK HISTORY ON * EXCEPT (tier) leaves a column out of the history. The managed database connectors offer the same history tracking. One warning from the docs: a full refresh replaces the table and its earlier versions with it. Type 3 has no built-in form; it is a MERGE you write.

Why it matters for AI

An assistant asked "how did York do last spring?" queries the same dimension tables a dashboard does. With type 1, it gives a confident answer that is quietly wrong, because York's history was overwritten. Choosing the type is a decision about which questions the warehouseData WarehouseA database built for analysis: cleaned, structured data from many systems, kept in one place and shaped for fast reports and dashboards. can still answer truthfully later.