Slowly changing dimension is a common data warehousing scenario. SCD utilizes three different method types for handling changes to columns in a data warehouse dimension table.
In Type 1 SCD, new data overwrites existing data. The existing data is lost and there is no tracking of historical changes. Type 1 SCD is easy to maintain but is useful only if you need not track the historical changes.
Example: Consider a table that keeps product information.
Key |
Name |
Price |
|---|---|---|
1 |
Notebook |
1200 |
The price of the notebook increases to 1500. The updated table simply overwrites the current record:
Key |
Name |
Price |
|---|---|---|
1 |
Notebook |
1500 |
Type 2 SCD retains the full history of values. If new data differs from the old data, an additional dimension record is created with new data values and becomes the current record. Each record contains the effective date and expiration date to identify the time period for which the record was active. Use type 2 SCD to keep a full history of dimension data in the table.
Example: See “Case study scenario”.
Type 3 SCD tracks changes using separate columns. There is one version of the dimension record that stores the previous value and current value of selected attributes. Use type 3 SCD when you need to track historical changes that occur only for a finite number of time.
Example: You have a table that keeps product information.
Key |
Name |
Price |
|---|---|---|
1 |
Notebook |
1200 |
The price of the notebook increases to 1500 on 15th July 2008. To accommodate type 3 SCD, new columns are added, Current Price and Effective Date:
Key |
Name |
Original Price |
Current Price |
Effective Date |
|---|---|---|---|---|
1 |
Notebook |
1200 |
1500 |
2008-07-15 |
Type 3 is rarely used because altering the structure
of the dimension table should be undertaken for only a very important
change.
