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

The ways a warehouse handles a change to a descriptive record, such as a customer's address: type 1 overwrites it, type 2 adds a new dated version, and type 3 keeps the previous value in an extra column.

- Page: https://tecyfy.com/concepts/slowly-changing-dimensions
- Subject: Data engineering in the AI era (https://tecyfy.com/concepts)
- Level: intermediate
- Also called: SCD, SCD type 1, SCD type 2, History tracking

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.

(An interactive demo goes here on the page: https://tecyfy.com/concepts/slowly-changing-dimensions)

## 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 capture](https://tecyfy.com/concepts/change-data-capture) 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 [warehouse](https://tecyfy.com/concepts/data-warehouse) can still answer truthfully later.

## Related

- [Change Data Capture](https://tecyfy.com/concepts/change-data-capture): Copying 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.
- [Data Warehouse](https://tecyfy.com/concepts/data-warehouse): A database built for analysis: cleaned, structured data from many systems, kept in one place and shaped for fast reports and dashboards.
- [Medallion Architecture](https://tecyfy.com/concepts/medallion-architecture): Organising a lakehouse in 3 layers of rising quality: bronze for raw data, silver for cleaned and joined data, and gold for tables shaped for reports and AI.
- [Semantic Layer](https://tecyfy.com/concepts/semantic-layer): One shared definition of business terms and metrics, such as revenue or active customer, that reports, dashboards and AI assistants all read instead of writing their own.

## Sources

- [Type 1: Overwrite](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-1/)
- [Type 2: Add New Row](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2/)
- [Type 3: Add New Attribute](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-3/)
- [The AUTO CDC APIs: Simplify change data capture with pipelines](https://docs.databricks.com/aws/en/ldp/cdc)
- [Enable history tracking (SCD type 2)](https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/scd)