Data Warehouse (part 6): Managing DW history through Slowly Changing Dimensions (SCDs)

Slowly Changing Dimensions (SCDs):

• Techniques to manage history within data warehouse
• Multiple techniques based on various historical data policies
• Enables data warehouse to appropriately manage history regardless of policies in transactional applications.

Three main policies for historical data that matches 3 most common types of SCD accordingly besides type 0 (always retain original value), type 4 (min-dimension), type 5,6,7 (various hybrid techniques):

• Overwrite old data; no history retention: Type 1 (overwrite)
• Maintain unlimited history: Type 2 (new row)
• Maintain limited history: Type 3 (new column)

Type 1 SCD

• Replace old value with new value
• Same row and column in database table
• Common use case: correcting errors