Slowly Changing Dimensions: Keep History Without Breaking Analytics
A customer updates her address before a Diwali sale, a product moves from “premium beauty” to “mass beauty,” and a store gets reassigned to a new region. If your warehouse simply overwrites the old value, last year’s sales can quietly start telling this year’s story.
- Slowly Changing Dimensions handle descriptive data that changes over time - customer city, product category, sales territory, employee manager.
- Type 1 overwrites history; use it for corrections where the old value is not analytically useful.
- Type 2 creates a new row with a surrogate key and validity dates; use it when historical reporting must remain true.
- Type 3 stores limited previous values in extra columns; use it for one-step comparisons such as current region versus previous region.
- Type 0 never changes, Type 4 keeps a separate history table, and Type 6 combines Type 1 + Type 2 + Type 3 behaviour.
- The key design question is not “Which SCD type is best?” It is “Which attributes need history, and at what grain?”
- Interview-safe answer: define SCD, explain the major types, show a customer/product example, then justify the trade-off between accuracy, complexity and storage.
Big Picture: SCD Is a Choice About Memory
A data warehouse is not just a place to store data; it is a place to make past decisions explainable. Slowly Changing Dimensions are the rules that decide how much “memory” the warehouse keeps when a business attribute changes.
Core Explanation: What Actually Changes in an SCD?
A dimension is the “who, what, where, when, why” context around a measurable business event. Sales amount is a fact; customer, product, store and campaign are dimensions.
A slowly changing dimension is a dimension whose descriptive attributes change over time and need controlled handling in analytics.
The word “slowly” is relative. Customer city may change a few times in a lifetime, product category may change once a year, and sales territory may change every quarter. The point is not speed; the point is whether a changed label should rewrite history.
A customer lives in Mumbai in April and moves to Bengaluru in June. If August sales are analysed by current city, Type 1 may be fine. If April sales must remain Mumbai sales, you need Type 2.
The SCD Decision Flow
When an incoming dimension record differs from the existing warehouse record, follow one clean sequence: detect the change, classify the attribute, apply the SCD rule, and validate history.
The Main SCD Types You Must Know
Most interview questions focus on Type 1 and Type 2, but a strong answer briefly places them inside the full family.
Type 1 vs Type 2: The Difference That Usually Gets Tested
Here is the core contrast: Type 1 changes the past; Type 2 preserves the past. That one sentence solves half the confusion.
A Small Worked Example: Why History Changes the Answer
Assume customer C101 lived in Mumbai until May and moved to Bengaluru in June. The same customer purchased ₹1,000 in April and ₹1,500 in August.
This is the business reason SCD matters: a wrong history model can move revenue, margin, churn or risk from one segment to another without any real-world change.
How to Design a Type 2 Dimension
Type 2 is the most important SCD pattern because it preserves complete row-level history. It normally uses a business key to identify the real-world entity and a surrogate key to identify each historical version.
Definitions You Can Say in One Breath
Dimension: A descriptive business entity used to slice facts, such as customer, product, store or employee.
Slowly Changing Dimension: A dimension whose descriptive attributes change over time and require controlled history handling.
Surrogate Key: A warehouse-generated key that uniquely identifies one dimension row or version.
Business Key: A source-system identifier that represents the real-world entity across versions.
Operational Checks: How to Know Your SCD Is Working
History design is only useful if the pipeline stays trustworthy. Track these checks in production, especially for customer, product, pricing, territory and regulatory dimensions.
Mini Case Study: Nykaa and the Need to Preserve Category History
Nykaa shows why product and customer attributes cannot always be overwritten when a business expands across categories, channels and brands.
Situation: Nykaa began as a digital-first beauty and personal care platform and later expanded its business across owned brands, fashion, physical retail and omnichannel experiences. In such a business, attributes like product category, brand ownership, customer segment, city tier and fulfilment channel can change as the company’s strategy evolves.
The data problem: Suppose a product originally sat under one beauty category and was later reclassified after merchandising changes. If the warehouse uses Type 1 for that category, old sales are restated under the new category. That may be fine for a cleaned-up current catalogue view, but it can distort historical category growth, margin mix and campaign ROI.
The SCD move: A robust analytics model would use mixed SCD treatment - Type 1 for genuine data corrections, Type 2 for historical product category or customer segment changes, and possibly Type 3 for “previous segment” comparisons. The primary driver is preserving decision-grade historical reporting; supporting drivers include clearer campaign measurement, stable cohort analysis and better merchandising governance.
Outcome or lesson: The lesson is not that every Nykaa attribute must be Type 2. The lesson is that omnichannel and multi-category businesses need attribute-level SCD rules, because growth analysis depends on whether yesterday’s classification remains visible tomorrow.
How AI Changes Slowly Changing Dimensions & Keeping History
AI does not remove SCD logic; it makes the detection, documentation and monitoring of history rules smarter.
- Better change detection: ML-based entity resolution can flag that “A. Sharma,” “Ananya Sharma” and a changed phone/email combination may represent the same customer, reducing duplicate dimension entities. Human review is still needed for high-risk merges.
- Faster SCD model generation: LLMs can draft dbt SQL, merge logic, test cases and documentation for Type 2 dimensions. The analyst must still validate business keys, effective-date logic and late-arriving data treatment.
- Anomaly monitoring: AI-assisted observability tools can flag unusual spikes in changed rows, duplicate current records or category churn that may indicate a bad source release rather than real business change.
Use ChatGPT or Claude to practise: paste a small dimension table and ask, “Design Type 1 and Type 2 handling for these attributes, generate sample rows after a change, and list edge cases.” Then check whether the answer correctly uses surrogate keys, validity dates and one current row per business key.
Interview Relevance
“Explain Slowly Changing Dimensions. How would you decide between Type 1 and Type 2 for a customer dimension?”
If you want to sound strong, say: “I would define SCD treatment at the attribute level, not table level. The same customer dimension can have Type 1 phone correction and Type 2 city history.”
Common Mistake
The biggest mistake is saying “Type 2 is always better because it keeps history.” That sounds safe but misses the design trade-off: unnecessary Type 2 creates row bloat, confusing joins and noisy history. Fix: choose the SCD type based on the business question each attribute must answer.
What to Revise Next
Once SCD is clear, revise the pipeline that creates and maintains these dimension tables. Go next to Extract-Transform-Load versus Extract-Load-Transform Pipelines, then zoom out to The Modern Data Stack: Ingestion, Storage, Transformation & Serving. Together, these explain how raw source changes become trusted analytical history.