Imagine you’re managing a customer database, and a customer updates their address. Do you overwrite the old one? Keep both? Track all past addresses? This decision falls under Slowly Changing Dimensions (SCD)—a method for handling changes in dimension tables in a data warehouse.
SCD Type 1: Overwrite the Old Data
The old value is replaced with the new one, and no history is kept.
📌 Example: A customer changes their email. In Type 1, you simply update the email field, losing the old one.
🔹 Pros: Simple and storage-efficient.
🔹 Cons: No history tracking.
SCD Type 2: Keep History with New Rows
A new record is inserted for each change, keeping the old one with an inactive status.
📌 Example: A customer moves from New York to Los Angeles. Instead of updating the city, a new row is added for Los Angeles, while the New York record is marked inactive.
🔹 Pros: Full historical tracking.
🔹 Cons: Table grows over time.
SCD Type 3: Keep History in the Same Row
Stores limited history in additional columns like Previous_Value and Current_Value.
📌 Example: If a company tracks only an employee’s last department, they add Previous_Department alongside Current_Department.
🔹 Pros: Saves space compared to Type 2.
🔹 Cons: Can’t track multiple historical changes.
Choosing the Right SCD Type
- If history isn’t needed → Type 1
- If full history is required → Type 2
- If limited history is enough → Type 3
SCDs ensure data integrity while keeping track of changes based on business needs. Always ask: Do we need to track past values? That will guide your approach!
Top comments (0)