Slowly changing dimension
The rules a data warehouse uses when descriptive data, like a customer's city, changes.
In a star schema, dimension tables describe the who, what and where of each event: the customer, the product, the store. Most of that barely changes, but it does change now and then. A customer moves, a product switches category, a store gets a new manager. A slowly changing dimension, or SCD, is a dimension that has to deal with these occasional changes, and the term usually refers to the techniques for handling them.
The choice matters because it changes history. Say Ana moved from Córdoba to Rosario in March. With Type 1 you simply overwrite the city; it is easy, but now all her purchases from last year show up as Rosario sales. With Type 2 you keep the old row, close it with an end date, and add a new row for Rosario with its own surrogate key, valid from March and marked as current. Old sales stay attached to Córdoba, new ones to Rosario. Type 3 adds a "previous city" column, which keeps only one step of history.
Type 2 is the most common when history matters, and it is why many dimension tables carry columns like valid_from, valid_to and is_current. The type numbers come from Ralph Kimball's dimensional modeling, and teams often mix them in one table: overwrite a typo fix, keep history for the address. Modern tools such as dbt snapshots and Microsoft Fabric pipelines can build Type 2 tables automatically.
Keep in mind
- Type 1 overwrites the old value and loses history.
- Type 2 adds a new row with validity dates, keeping full history.
- Type 3 keeps only the previous value in an extra column.
- The choice decides whether past reports change when a description changes.
Go deeper
- Slowly changing dimension on Wikipedia
- Type 2: add new row (Kimball Group)
- Slowly changing dimension type 2 (Microsoft Fabric docs)
