Slow Changing Data types

August 5, 2024

SCD Type 0 (Retain Original):

• No changes are applied once data is initially loaded. The original data remains unchanged, which means historical changes are not tracked.
• Use Case: Used when the history does not matter, or the data should not change once recorded.

SCD Type 1 (Overwrite):

• Updates the record with the new data, overwriting the old data. There is no historical record of changes.
• Use Case: Used when corrections are needed, and historical tracking is unnecessary, such as in cases of data entry errors.

SCD Type 2 (Add New Record):

• Creates a new record for each change, preserving the history of changes. A new surrogate key is generated for each version.
• Use Case: Used when historical tracking is necessary, such as tracking changes in customer addresses.

SCD Type 3 (Add New Attribute):

• Stores both the previous and current values within the same record. Typically, this involves adding additional columns to capture the historical data.
• Use Case: Used when there is a need to compare old and new values but not for full historical tracking, such as tracking the last two addresses of a customer.

SCD Type 4 (History Table):

• Uses separate tables to store historical data. The main table contains only the current data, while a history table maintains the historical records.
• Use Case: Used when separating current and historical data for performance or organizational reasons, such as in systems with large datasets.

SCD Type 6 (Hybrid):

• Combines elements of Types 1, 2, and 3. Typically, it involves keeping the current and historical data in the same table with additional attributes and/or a separate historical table.
• Use Case: Used when there is a need to maintain a complete history while also allowing easy access to current and previous values, such as tracking both current and previous states of an account.

SCD Type 7 (Hybrid with Soft Deletes):

• Similar to Type 6, but includes soft deletes to mark records as logically deleted without physically removing them from the table. This can involve setting an "is_deleted" flag.
• Use Case: Useful in scenarios where regulatory compliance requires retention of deleted records or when records should not be physically removed from the database.

SCD Type N (Custom Solutions):

• Custom solutions that combine aspects of the above types or implement unique strategies tailored to specific business requirements.
• Use Case: Applied in complex systems where a standard approach doesn't meet all business needs, requiring a mix of retention, auditability, and flexibility.