Analytics often fails in a quiet way: the totals are correct, but the context is wrong. A sales report might show stable revenue, yet the “top region” flips simply because territories were redefined. A customer might move from “SMB” to “Enterprise,” and suddenly last year’s performance looks different when you rerun the same query. Slowly Changing Dimensions (SCDs) exist to prevent this kind of accidental rewriting of history. In dimensional modelling, an SCD is a method for handling changes to descriptive data (dimension data) so reports can reflect what was true at the time of the transaction, not just what is true today. Kimball’s work popularised the SCD “types” that teams still use as a shared language for these choices.
This topic is often introduced early in a Data Analytics Course because it connects modelling decisions to real reporting trust: if dimensions change, your results can change even when facts do not.
1) The core problem: dimensions change, but facts should keep their meaning
Dimension tables store descriptive attributes, customer segment, product category, employee department, store region. These are not transactions, but they influence how transactions get grouped. Over time, those attributes change naturally: people relocate, products get reclassified, sales territories are redrawn.
If you overwrite dimension values every time something changes, you may improve “current accuracy” but you can lose historical accuracy. Microsoft’s Power BI modelling guidance notes that a Type 2 SCD supports versioning of dimension members and typically requires a surrogate key (an internal ID) so each version is uniquely referenced.
A practical rule: if a change could alter how the business interprets past performance, you need a history-aware approach rather than simple overwrites.
2) The main SCD types in plain English (and when each fits)
SCD “types” are just patterns, ways to store change.
Type 1: Overwrite (no history kept)
You update the dimension record in place. This is useful when history is not needed or the old value was wrong. Example: fixing spelling, correcting an invalid postcode, standardising a city name. Kimball highlights that Type 1 changes affect history because they overwrite what reports will see for past periods.
Type 2: Add a new row (full history kept)
When an attribute changes, you insert a new row for the same business entity and keep the old row. The new row gets a new surrogate key; you often add “start date/end date” and/or a “current flag.” Microsoft Fabric’s SCD Type 2 pattern describes creating a new record when a value changes while preserving the original record so you can see what data looked like at any time.
Use Type 2 when historical reporting must remain stable: customer segment changes, territory reassignment, product hierarchy changes, employee department changes.
Type 3: Add a new column (limited history kept)
You keep “current value” and “previous value” (or a small number of prior states). This is helpful when you only need a short comparison (e.g., “current vs previous region”) and don’t need full version history. (Type 3 is commonly described alongside other SCD approaches in standard SCD references.)
A balanced point for learners in a Data Analytics Course in Hyderabad: Type 2 is the most important to master because it forces you to think about time-aware correctness, not just joins.
3) A real-world example: why “as-of” reporting needs Type 2
Imagine an ecommerce company where “Customer Segment” changes based on annual spend. A customer moves from SMB to Enterprise in July. If you run a “Q1 revenue by segment” report in October:
-
With Type 1, that customer’s Q1 revenue may now appear under “Enterprise,” even though in Q1 they were still “SMB.”
-
With Type 2, Q1 facts join to the customer dimension version that was valid in Q1, preserving the original grouping.
This is why Type 2 is often implemented using effective dates and a current flag, so the warehouse can answer “what was true on that date?” reliably.
It also reduces rework. Analysts frequently cite that data preparation consumes the majority of time; one widely referenced survey summary reported that data scientists spend around 80% of their time preparing and managing data rather than analysing it. Getting SCD handling right upstream prevents repeated “why did this number change?” investigations downstream.
4) Implementation patterns that keep SCDs accurate
SCDs are less about clever SQL and more about consistent rules.
Key design elements for Type 2
-
Surrogate key: an internal key so each version of a dimension row is distinct.
-
Change detection: decide which fields trigger a new version (e.g., segment, region, manager).
-
Validity fields: start_date, end_date, and/or is_current make “as-of” logic explicit.
-
Modern tooling example
If you work in ELT workflows, dbt snapshots are explicitly built to implement Type 2 SCD behaviour over mutable source tables (tables that get updated). That makes the “track changes over time” pattern repeatable rather than custom-built each time.
For anyone taking a Data Analytics Course in Hyderabad, it’s useful to connect the concept to tooling: the modelling idea (Type 2) is stable, while the implementation can vary by stack.
Concluding note
Slowly Changing Dimensions are about protecting meaning over time. When dimension values evolve, a warehouse must decide whether to overwrite, partially retain, or fully preserve history. Type 1 is fast but rewrites the past; Type 2 preserves “as-of” truth through versioned rows and surrogate keys; Type 3 keeps a limited history for focused comparisons. Learning these patterns in a Data Analytics Course helps you build reports that stay consistent across reruns, audits, and organisational changes. And when you practise SCD choices using realistic business scenarios, a Data Analytics Course in Hyderabad naturally becomes a way to turn modelling theory into reporting accuracy that people can rely on.
Business Name: Data Science, Data Analyst and Business Analyst
Address: 8th Floor, Quadrant-2, Cyber Towers, Phase 2, HITEC City, Hyderabad, Telangana 500081
Phone: 095132 58911
