What are Slowly Changing Dimensions (SCD)?

A slowly changing dimension is a type of dimension in a data warehouse that changes over time but not rapidly. For example, a customer dimension might include attributes such as name, address, and phone number, which may change slowly over time. In a data warehouse, slowly changing dimensions are used to track changes to the attributes of a dimension over time so that historical data can be analyzed and compared to more recent data. There are several different types of slowly changing dimensions, including Type 1, Type 2, and Type 3, which represent different strategies for handling changes to dimension attributes.

Type 1 Slowly Changing Dimensions: Type 1 slowly changing dimensions overwrite the previous data when an attribute changes. This means that if an attribute of a dimension changes, the new value will replace the old value, and any historical data will be lost. This is the most straightforward approach to handling changes to dimension attributes, but it does not allow for tracking the changes over time.

Type 2 Slowly Changing Dimensions: Type 2 slowly changing dimensions create a new record when an attribute changes. If an attribute of a dimension changes, a new record is created to reflect the new value, and the old record is preserved. This allows for tracking the changes to dimension attributes over time. Still, it can result in many records for a single entity, which can make it more difficult to analyze the data.

Type 3 Slowly Changing Dimensions: Type 3 slowly changing dimensions create a new record when an attribute changes and add column to the record to store the previous value. This attribute of a dimension changes, a new record is created with a new column to store the old value, and the old record is preserved. This allows for tracking the changes to dimension attributes over time, and. It more efficient approach than Type 2 slowly changing dimensions, as it does not result in multiple records for a single entity. However, it does require adding new columns to the record, which can make the data model more complex.