A slowly changing dimension (SCD) is the data warehousing pattern for handling attribute changes in dimension tables over time — managing the tension between the need to see historical data in the context of how it was classified at the time (what account type was this account when that transaction was posted?) and the need to see all data in the context of current classifications (what does our total OPEX look like under the current account structure?). In finance BI, this tension is omnipresent: accounts are reclassified, cost centres are restructured, entities are renamed, legal entity hierarchies are reorganised. The SCD design determines whether those changes corrupt historical comparisons or are managed transparently.
How It Works
Three SCD types cover the majority of finance dimension attribute scenarios:
| SCD Type | Behaviour | History Preserved? | Finance Use Case |
|---|---|---|---|
| Type 1 | Overwrite the existing value | No | Corrections to errors (wrong account code, typo in name) — when the old value was wrong, not just different |
| Type 2 | Insert a new row with the new value; mark the old row as expired | Yes — via separate dimension rows | Accounting reclassifications, cost centre restructuring, entity reporting line changes — when historical transactions should be reported under the old classification |
| Type 3 | Add a new column for the new value; retain the old value in the original column | Yes — side by side in same row | Parallel reporting structures (current vs prior year account mapping) — when both structures are needed simultaneously |
Type 2 is the most common and most complex: each change to a tracked attribute creates a new surrogate key, and fact rows retain the surrogate key of the dimension version active at the time of the transaction. BI queries using the current dimension see current-classification totals; queries using date-bounded dimension versions see historically accurate totals.
Design Decisions and Trade-offs
The SCD strategy must be decided at the attribute level, not at the table level. Within the Account dimension, the account code and description may be Type 1 (corrections only), the management reporting line may be Type 2 (reclassifications affect historical comparisons), and the current year vs prior year mapping may be Type 3. Applying a single SCD type to an entire dimension is a simplification that produces incorrect historical analysis for the attributes where a different type was appropriate.
Common Implementation Errors
The most specific SCD implementation error in finance BI is applying Type 1 to management reporting hierarchy assignments because “it’s simpler” — overwriting the old hierarchy assignment when an account is moved to a different reporting line. The consequence is that all historical fact rows referencing that account now aggregate under the new reporting line, even for periods before the reclassification occurred. A year-over-year comparison that should show a like-for-like performance comparison instead reflects the reclassification, making prior year figures incomparable to current year. The finance team attributes the discrepancy to a data error and raises a support ticket; the root cause is an SCD Type 1 applied to an attribute that required Type 2.
How Loop Wise Solutions Designs for This
We produce an SCD attribute register for each dimension table — listing every tracked attribute, its SCD type, the rationale for that type, and the ETL mechanism used to detect and process changes. The register is reviewed with the finance team before ETL development begins, because the correct SCD type for management reporting hierarchy attributes is a finance reporting governance decision, not a technical choice.
Answers before you ask.
How to handle changes to dimension attributes over time — a cost centre renamed, an entity reassigned — so that historical analysis stays consistent while current reporting reflects the latest values. The SCD pattern defines, per attribute, whether history is preserved or overwritten, which governs how reports behave when source master data changes.
Type 0 retains the original value and ignores changes; Type 1 overwrites with the new value, keeping no history; Type 2 adds a new row for each change, preserving full history with effective dates; Type 3 adds a column to hold a limited prior value. Each handles the history-versus-current trade-off differently.
Type 1 (overwrite) suits corrections or attributes where history does not matter and reports should always reflect the current value. Type 2 (add row) suits attributes where historical accuracy is essential — so a past report shows the entity structure as it was then. The choice depends on whether history must be preserved for that attribute.
Directly. With Type 2, a historical report stays stable because the version of the attribute in force at the time is preserved; with Type 1, the same report can change when the attribute is overwritten, since only the current value exists. Choosing the wrong type silently alters or freezes history in ways that mislead analysis.