Types of slowly changing dimensions

Slowly changing dimensions (SCDs) are a concept in data warehousing and database management that describe how data attributes in a dimension table change over time. There are several types of slowly changing dimensions, commonly referred to as SCD types. The most commonly used SCD types are:

  1. Type 1: Overwrite
    In this type, the old data is simply overwritten with the new data. There is no historical tracking of changes, and the dimension table always contains the latest values. This approach is suitable when historical information is not important or can be obtained from other sources.
  2. Type 2: Add New Row
    In this type, a new row is added to the dimension table whenever a change occurs. This allows for the preservation of historical data by keeping multiple versions of the dimension. Each row has a different key to identify the specific version of the dimension. The latest version is determined by a flag or a date range. This approach is suitable when historical analysis is required.
  3. Type 3: Add New Attribute
    In this type, additional columns are added to the dimension table to store the new attribute values. The original attribute values are retained, allowing for limited historical analysis. Typically, a fixed number of attribute columns are used to store the changes, which means there is a limit to the number of historical versions that can be tracked.
  4. Type 4: Add New Table
    In this type, a separate table is created to store the historical changes. The original dimension table contains only the current version of the attributes, while the new table stores the historical values. This approach allows for unlimited historical tracking but can complicate queries that involve both the current and historical versions of the dimension.
  5. Type 6: Hybrid Approach
    Type 6 is a combination of Type 1 and Type 2. It involves adding a new row for each change like Type 2, but also updates the existing row with the new attribute values like Type 1. This approach provides a balance between preserving historical data and maintaining the latest values in a single row.

It's worth noting that these SCD types are not exhaustive, and variations or combinations of these types can be used based on specific requirements and constraints in a data warehousing environment.