Legacy context. This archive preserves educational materials on data warehousing and ETL operations, with a focus on practical design patterns such as slowly changing dimension types. The content here is offered as a neutral reference for those studying data management concepts.
Key point 1. The preserved excerpts reflect an era when interactive demonstrations relied on legacy web plugins, and they document general industry practices—including trend discovery, automated reporting, and system architecture evaluation—without endorsing any specific vendor or service. No current offerings, certifications, or client relationships are implied.
Key point 2. Readers are encouraged to treat these materials as historical artifacts of evolving data engineering methods. The site does not provide medical, legal, or financial advice, nor does it represent an active commercial entity. Its purpose is solely educational: to support independent study of warehouse design, ETL workflows, and dimension handling techniques.
Data Warehouse Slowly Changing Dimensions: A Practical Comparison Guide. Slowly Changing Dimensions (SCDs) are a core concept in dimensional modeling. They address a simple but critical problem: what happens when a dimension attribute—like a customer’s address or a product’s category—changes over time? If you overwrite the old value, you lose historical context. If you keep only the new value, your reports become inconsistent. SCD types are the standard strategies for handling these changes. This guide compares the most common types (0, 1, 2, 3, and hybrid approaches), gives you decision criteria, and highlights frequent implementation mistakes.
In a star schema, fact tables store measurements (sales, clicks, orders) and dimension tables store descriptive attributes (customer name, product color, store location). A fact record references a dimension via a surrogate key. When a dimension attribute changes, you must decide how to treat the existing fact records. If you update the dimension row in place, all historical facts now point to the new attribute value, which may be incorrect for older facts. If you create a new row, you need a new surrogate key, and you must ensure new facts use the correct version. SCD types formalize these choices.
Type 0: Retain Original (No Change). Type 0 means you never change the dimension row. The original value is kept forever. This is useful for immutable attributes like a customer’s date of birth, a product’s SKU, or a user’s registration timestamp. It is not a strategy for most attributes because it ignores reality. Use Type 0 only when the attribute is definitionally permanent or when you explicitly want to preserve the original value for audit purposes. Common mistake: applying Type 0 to attributes that actually change, which silently corrupts future reports.
Type 1 is the simplest: you update the existing dimension row with the new value. The old value is lost. This is appropriate for attributes where historical accuracy is irrelevant, such as a customer’s phone number or a product’s current price (if you only care about the latest price). Type 1 keeps the dimension table small and queries fast. The main drawback is that you cannot reconstruct historical facts. For example, if a customer moves from New York to Chicago, all past sales are attributed to Chicago. Decision criteria: use Type 1 when the attribute is a “current status” field, when you have no regulatory need to track history, or when the cost of tracking history outweighs the benefit. Common mistake: using Type 1 for attributes that are later needed for trend analysis, forcing a painful reload of historical data.
Type 2: Add New Row (Full History). Type 2 is the most common for tracking history. When an attribute changes, you insert a new row with a new surrogate key, and you mark the old row as expired (e.g., with `valid_from` and `valid_to` timestamps, or a `current_flag`). The old row remains linked to historical facts; new facts use the new row. This gives you a perfect point-in-time view. For example, a customer’s address change creates two rows: one valid from 2020-01-01 to 2023-06-30, and one valid from 2023-07-01 onward. Decision criteria: use Type 2 when you need to answer questions like “How many sales were shipped to the old address?” or “What was the product category at the time of the order?”. Type 2 is mandatory for compliance in many industries (e.g., financial services, healthcare). Common mistakes: forgetting to close the old row (leaving two active rows), using natural keys instead of surrogate keys, and not adding an effective date range, which makes it impossible to know which version was active at a given time.
Type 3 stores both the current value and the previous value in the same row, using separate columns (e.g., `current_address` and `previous_address`). This supports limited history—typically just the last change. It is useful when you need to compare “before and after” for a single change, such as a product’s original category versus its current category. Type 3 is less common because it does not scale to multiple changes. Decision criteria: use Type 3 only when you have a specific business question that requires exactly one previous value, and when you are certain that no more than one change will be tracked. Common mistake: trying to use Type 3 for attributes that change frequently, which forces you to overwrite the previous value and lose older history.
Hybrid Approaches: Combining Types. Real-world dimensions often mix types. For example, a customer dimension might use Type 2 for address, Type 1 for phone number, and Type 0 for date of birth. This is called a hybrid SCD. You can also use Type 2 with a “current row” flag plus a separate “current view” table for performance. Another hybrid is Type 2 with a Type 1 override: you keep history in the dimension but also update a denormalized “current” column in the fact table for fast reporting. Decision criteria: design per attribute, not per table. Ask: “Does this attribute need history? If yes, how much?”. Common mistake: applying a single SCD type to the entire table, which either bloats the table with unnecessary history or loses critical history.
Decision Criteria: A Step-by-Step Checklist.
Common Mistakes and How to Avoid Them
Compact Actionable Reference Table
| SCD Type | Strategy | History Kept | Use Case | Key Columns | Typical Example |
|---|---|---|---|---|---|
| 0 | Retain original | None (original only) | Immutable attributes | `original_value` | Date of birth, SKU |
| 1 | Overwrite | None | Current status only | `value` (updated) | Phone number, current price |
| 2 | Add new row | Full history | Point-in-time analysis | `surrogate_key`, `valid_from`, `valid_to`, `current_flag` | Customer address, product category |
| 3 | Add new column | Limited (previous only) | Before/after comparison | `current_value`, `previous_value` | Original vs. current sales region |
| Hybrid | Mix of 0, 1, 2, 3 | Varies by attribute | Complex real-world dimensions | Varies | Customer dimension with Type 2 address, Type 1 phone |
Final Recommendations
Start with Type 2 for any attribute that drives business decisions or compliance. Use Type 1 only for low-value, high-churn attributes. Avoid Type 3 unless you have a very specific and stable requirement. Always implement surrogate keys, effective date ranges, and a `current_flag` for Type 2. Test your SCD logic with a small dataset that includes multiple changes and late-arriving facts. Finally, document every attribute’s SCD type in your data dictionary. This will save you from costly rework and ensure your reports remain trustworthy over time.
This independent educational reference summarizes general technical concepts. Verify current standards, dimensions, and manufacturer specifications before making a procurement or engineering decision.