A Slowly Changing Dimension (SCD) is a mechanism in dimensional modeling that dictates how a data warehouse reacts to modifications in the attributes of a dimension table. Unlike static reference data, dimensional attributes like a customer's address or a product's category evolve. The SCD strategy determines whether an update overwrites history, creates a new row to preserve the old value, or adds a new column to track the change.
Glossary
Slowly Changing Dimension (SCD)
A data warehousing technique for managing and preserving historical changes to dimensional attributes over time, typically categorized into Type 0 through Type 6 strategies.
DATA WAREHOUSING
What is Slowly Changing Dimension (SCD)?
A Slowly Changing Dimension (SCD) is a data warehousing design pattern for managing and preserving historical changes to dimensional attributes over time, typically categorized into Type 0 through Type 6 strategies.
The most common implementations are Type 1 (overwrite, no history), Type 2 (add a new row with effective dates and a current flag), and Type 3 (add a new column for the previous value). This methodology is critical for maintaining point-in-time correctness in historical reporting, ensuring that a fact record can be accurately joined to the dimension state that was valid when the transaction occurred.
DIMENSIONAL MODELING STRATEGIES
SCD Type Comparison Matrix
A technical comparison of the six primary Slowly Changing Dimension types, evaluating their mechanisms for handling historical attribute changes, storage overhead, and query complexity.
| Feature | Type 0 (Fixed) | Type 1 (Overwrite) | Type 2 (Versioned) | Type 3 (Previous Value) | Type 4 (History Table) | Type 6 (Hybrid) |
|---|---|---|---|---|---|---|
Historical Preservation | ||||||
Update Mechanism | No updates permitted | UPDATE in place | INSERT new row | UPDATE column, INSERT row | INSERT into history table | UPDATE + INSERT |
Surrogate Key Strategy | Original key only | Original key only | New surrogate per version | Original key + version | Separate keys per table | Dual surrogate keys |
Storage Overhead | Minimal | Minimal | High (row multiplication) | Moderate | Moderate (table split) | High |
Query Complexity | Low | Low | Moderate (date filtering) | Moderate | High (table joins) | High |
Current Value Retrieval | Direct SELECT | Direct SELECT | WHERE is_current = true | Direct SELECT | JOIN current table | WHERE is_current = true |
As-Was Reporting | ||||||
As-Is Reporting |
DIMENSIONAL MANAGEMENT
Core Characteristics of SCD Implementation
Slowly Changing Dimensions are managed through distinct methodological types, each defining a specific trade-off between historical accuracy and storage complexity.
01
Type 0: Retain Original
The passive approach where dimensional attributes are never updated. Once a record is inserted, its values are considered permanent and immutable.
- Mechanism: No UPDATE operations are permitted on the dimension table.
- Use Case: Ideal for truly static attributes like a customer's original date of birth or a product's initial SKU assignment.
- Trade-off: Guarantees perfect historical consistency but provides zero ability to track real-world changes.
02
Type 1: Overwrite
The simplest update strategy where old attribute values are overwritten with new ones. No history is preserved.
- Mechanism: A direct SQL
UPDATEstatement replaces the existing row value. - Use Case: Correcting typographical errors or updating a customer's phone number when historical values are irrelevant.
- Trade-off: All historical fact records will retroactively reflect the new value, destroying the ability to analyze past states.
03
Type 2: Add New Row
The gold standard for full historical tracking. A new row is inserted for every change, preserving the complete history of a dimension member.
- Mechanism: Uses effective date and expiration date columns, plus a current flag indicator.
- Example: Tracking a customer's address over 5 years by inserting a new row each time they move, with
is_current = TRUEon the latest record. - Trade-off: Causes significant table growth and requires complex surrogate key management.
04
Type 3: Add New Column
A limited history strategy that stores both the previous and current values in the same row using separate columns.
- Mechanism: An
original_valuecolumn holds the first state, whilecurrent_valueholds the latest. - Use Case: Tracking a sales territory reassignment where only the immediate prior assignment matters.
- Trade-off: Cannot track more than one historical change without adding infinite columns, making it impractical for frequently changing attributes.
05
Type 4: History Table
Segregates current records in the main dimension table from historical records stored in a separate history table.
- Mechanism: The main table always contains the latest state, while a parallel table stores all previous versions.
- Use Case: High-volume dimensions where query performance on current data is critical, but an audit trail is still required.
- Trade-off: Increases ETL complexity and requires UNION operations for full historical analysis.
06
Type 6: Hybrid Approach
Combines Type 1, 2, and 3 strategies into a single dimension row. It tracks change history while maintaining a current assignment perspective.
- Mechanism: Includes surrogate keys for Type 2 tracking, a current value column for Type 1 overwrites, and historical attributes for Type 3.
- Use Case: Complex reporting scenarios requiring both the historical fact context and the current organizational hierarchy simultaneously.
- Trade-off: The most complex to implement and maintain, requiring sophisticated ETL logic.
SLOWLY CHANGING DIMENSIONS
Frequently Asked Questions
Clear, technical answers to the most common questions about implementing and managing Slowly Changing Dimensions in modern data warehousing and lakehouse architectures.
A Slowly Changing Dimension (SCD) is a data warehousing design pattern that manages and preserves historical changes to dimensional attributes over time. Unlike fact tables that capture transactional events, dimension tables describe business entities—such as customers, products, or locations—whose attributes evolve infrequently but meaningfully. The core mechanism involves detecting a change in a source system, then applying a predefined strategy (Type 0 through Type 6) to either overwrite, version, or historize the record. For example, when a customer relocates, an SCD strategy determines whether the address field is updated in place (losing history) or a new row is inserted with effective date ranges, preserving the fact that historical sales were shipped to the previous address. This ensures that historical reports remain accurate while reflecting current state for operational queries. The technique is fundamental to maintaining point-in-time correctness in analytical systems and is tightly integrated with modern table formats like Delta Lake and Apache Iceberg, which provide MERGE operations and time travel capabilities to simplify SCD logic.
Enabling Efficiency, Speed & Accuracy
Intelligent Analysis, Decision & Execution
We build AI systems for teams that need search across company data, workflow automation across tools, or AI features inside products and internal software.
Talk to Us
Search across company data
Give teams answers from docs, tickets, runbooks, and product data with sources and permissions.
Useful when people spend too long searching or get different answers from different systems.
Enterprise searchRAGPermissions
Read more
Automate internal workflows
Use AI to route work, draft outputs, trigger actions, and keep approvals and logs in place.
Useful when repetitive work moves across multiple tools and teams.
AI agentsWorkflow automationGovernance
Read more
Add AI to products and internal tools
Build assistants, guided actions, or decision support into the software your team or customers already use.
Useful when AI needs to be part of the product, not a separate tool.
AI integrationDecision supportModel routing
Read more
About the author
Prasad Kumkar
CEO & MD, Inference Systems
Prasad Kumkar is the CEO & MD of Inference Systems and writes about AI systems architecture, LLM infrastructure, model serving, evaluation, and production deployment. Over 5+ years, he has worked across computer vision models, L5 autonomous vehicle systems, and LLM research, with a focus on taking complex AI ideas into real-world engineering systems.
His work and writing cover AI systems, large language models, AI agents, multimodal systems, autonomous systems, inference optimization, RAG, evaluation, and production AI engineering.
LinkedIn
Limited slotsGet a Free AI Consultation
Partnered with leading AI, data, and software stack.
How We Work
Custom AI workflows for your Business
One-fit-all AI don't work for modern businesses. At Inferensys, we aim to understand your business & custom requirements; which we use to define most efficient agentic workflows, the data, and the tools for your business.
01
Review the use case
We understand the task, the users, and where AI can actually help.
Read more02
Pick the right approach
We define what needs search, automation, or product integration.
Read more03
Build the first useful version
We implement the part that proves the value first.
Read more04
Improve from there
We add the checks and visibility needed to keep it useful.
Read moreThe first call is a practical review of your use case and the right next step.
Talk to Us