A customer moves from the North sales region to the South in July. When somebody reviews a sale from March, which region should the report show?
There are at least two reasonable answers. The report might use South because the organisation wants to analyse all customers according to its current structure. It might use North because that was the region responsible when the sale occurred.
Neither answer is automatically correct. They answer different questions.
The customer and sales-region example provides a simple thread through the article, but the history decision is broader. An insurer may need the coverage classification that applied when a claim occurred; a lender may need both the current and historical risk band of an account; a healthcare organisation may need the service or organisational structure in force at the time of treatment.
Should earlier results follow today's organisational structure?- Should they retain the structure that applied at the time?
- Which changes are genuine business events rather than corrections?
- What date makes a new description effective?
- What happens when the change arrives after related facts?
- Can the source provide enough evidence to support the chosen answer?
Historical reporting depends on decisions like these. The database structure comes later.
Begin with the Question, Not the SCD Type
The phrase slowly changing dimension describes a collection of techniques for handling changes to dimension attributes. It doesn’t decide which technique the business needs.
Consider a customer dimension containing name, market segment, sales region, account manager, and regulatory classification. Each attribute can change, but the reasons and reporting consequences differ:
| Change | Possible interpretation | Likely question |
|---|---|---|
| A misspelled customer name is corrected | The earlier value was wrong | Should every report show the corrected name? |
| A customer moves to another sales region | A genuine organisational relationship changed | Should earlier sales remain with the former region? |
| Marketing revises its segment definitions | The organisation adopted a new analytical view | Do users need the original and revised views? |
| A regulatory category is corrected retrospectively | A governed classification was found to be inaccurate | Which published results need restating? |
| An account manager changes | Responsibility moved on a known date | Should performance follow the manager responsible at the time? |
The history policy can therefore differ by attribute. Treating the whole dimension as either “historical” or “not historical” may be too crude.
Current-State Replacement
The simplest policy keeps one warehouse row for each customer, product, employee, or other dimension member. When a tracked value changes, the existing value is overwritten.
If customer C42 moves from North to South, the dimension row now says South. Facts already linked to that row will also appear under South when users group them by the current region.
Dimensional modelling calls this a Type 1 slowly changing dimension, often shortened to SCD Type 1.
This approach can be appropriate when:
The earlier value was an error that should no longer appear.- Only the latest description is useful to the intended consumers.
- The organisation deliberately reports all history through its current structure.
- The attribute has no meaningful historical interpretation.
- The cost and complexity of retaining versions aren't justified by a requirement.
Type 1 is sometimes described as having no history. More precisely, it doesn’t retain the overwritten attribute value in that dimension row. Audit logs, source history, load records, or another analytical structure may still retain evidence of the change.
Versioned History
A different design creates a new warehouse row when a historically significant attribute changes. The earlier row remains available, and each row records the period during which that version applied.
Customer C42 might therefore have two warehouse versions:
Earlier version
- Warehouse key
- 701
- Source customer ID
- C42
- Region
- North
- Effective period
- 1 January to 30 June
- Status
- Historical
Current version
- Warehouse key
- 846
- Source customer ID
- C42
- Region
- South
- Effective period
- From 1 July
- Status
- Current
The source customer ID still identifies the same business customer. The separate warehouse keys, also called surrogate keys, identify particular historical versions of that customer.
A March sale can store customer key 701, while an October sale stores customer key 846. Reports can then group each sale using the region that applied when it occurred.
This technique is called a Type 2 slowly changing dimension, or SCD Type 2.
Replacement supports a current view; versioning retains the description applicable to each event.
Microsoft’s dimensional-model loading guidance describes the same distinction: Type 1 updates an existing row, while Type 2 expires the existing version and inserts a new one.
Effective Periods Need Precise Rules
“Effective from 1 July” sounds clear until timestamps, time zones, overlapping records, and delayed source updates become involved.
A versioning policy should establish:
- Effective time: Which business date or timestamp determines when the new version applies?
- Boundary convention: Is the start inclusive and the end exclusive, or does the design use another unambiguous rule?
- Open-ended version: How is the current row represented without relying on an unclear magic date?
- Overlap prevention: What control ensures that two versions of the same member aren't effective at once?
- Gap handling: What happens if no known version covers the event date?
- Time-zone policy: Which time zone governs changes that occur around a reporting boundary?
An inclusive start and exclusive end is often convenient. One version might apply from 2026-01-01 up to, but not including, 2026-07-01; the next begins at 2026-07-01. The important point is consistency across loading, querying, testing, and documentation.
Facts Need the Correct Historical Version
Retaining dimension versions isn’t enough. The fact-loading process must assign each event to the version that was effective on the relevant date.
For a sale, that might be the order date, dispatch date, invoice date, or another date agreed during modelling. Different facts can legitimately use different dates, but the choice must follow the business meaning of the fact.
The matching logic normally uses:
- The source system’s business key, such as customer ID C42.
- The relevant event date or timestamp from the fact.
- The dimension version whose effective period covers that event.
The resulting warehouse key is stored on the fact row. Users shouldn’t need to repeat the effective-date join in every report.
Late-Arriving Data Changes the Problem
Warehouses rarely receive every event and description in perfect chronological order.
A fact arrives before its descriptive context
An order may arrive before the corresponding customer record. Rejecting the order indefinitely can leave reporting incomplete, while assigning it to an unrelated “unknown” member can lose the relationship.
One option is to create an inferred member: a provisional dimension row containing the known business key and placeholders for missing descriptions. When the customer details arrive, that row is completed rather than treated as an ordinary historical change.
The Microsoft loading guidance distinguishes this late-arriving dimension detail from a normal Type 2 change.
A historical change is reported late
Suppose the warehouse learns in August that the customer actually moved regions on 1 July. July facts may already point to the former customer version.
The team now needs a controlled decision. Possible responses include:
- Insert the new version with the true July effective date and reassign affected facts.
- Apply the new version only from the date the warehouse learned about it.
- Retain both the business-effective date and the warehouse-processing date for different audit needs.
- Leave published results unchanged if the reporting policy prohibits retrospective restatement.
This isn’t merely a pipeline retry. It can change previously reported totals by region, manager, segment, or other classification.
A fact itself arrives late
A late sale should normally find the dimension version that applied when the sale occurred, not whichever version is current when the file arrives. That requires retained versions and a reliable event date.
Kimball’s guidance on late-arriving facts discusses the need to locate the correct historical dimension context when facts and descriptive changes reach the warehouse out of sequence.
Correction and Change Aren’t the Same
A genuine change says the business moved from one valid state to another. A correction says an earlier representation was wrong.
If “Noth” is corrected to “North”, preserving both spellings as equally valid historical versions would usually be unhelpful. A Type 1 correction may be appropriate, even in a dimension that uses Type 2 for region reassignment.
Some corrections are more consequential. If a source supplied the wrong regulatory classification for six months, the organisation may need to:
- Record who authorised the correction and why.
- Identify the effective period that was incorrect.
- Reassign affected facts or rebuild dependent aggregates.
- Refresh semantic models and downstream extracts in a controlled order.
- Reconcile the corrected result against agreed expectations.
- Tell consumers whether previously published outputs have been restated.
Technical implementation can’t decide an organisation’s restatement policy. Finance, compliance, operations, and data owners may need to agree materiality thresholds, approval routes, publication controls, and audit evidence.
History the Source Never Retained
A warehouse can preserve history from the point at which suitable capture begins. It may also reconstruct some earlier states from audit tables, event logs, effective-dated source records, backups, or other dependable evidence.
It can’t reliably recreate every historical state from a source that stores only today’s value.
If the CRM currently says South and retains no change date, an implementation team shouldn’t invent 1 July merely because that date makes a chart look plausible. At best, it can document an assumption or use a separately governed source of evidence.
Several implementation choices can make the resulting history look more certain than the available evidence supports:
Treating the warehouse load timestamp as if it were the business-effective date.- Claiming complete history when capture began only recently.
- Reconstructing versions from unverified spreadsheets or memory.
- Allowing overlapping effective periods for the same business key.
- Updating dimension versions without considering facts already assigned to them.
- Restating published results without approval or traceable evidence.
Useful Historical-Modelling Vocabulary
Type 1 and Type 2 cover many common requirements and are the main focus here because they directly address the choice between a current view and an as-was view. Practitioners may also encounter these terms:
| Term | Plain-language meaning | Limitation or use |
|---|---|---|
| Type 0 | Retain the original value without changing it | Useful for attributes that should remain fixed, such as an original registration date |
| Type 3 | Add a separate attribute, such as PreviousRegion or OriginalRegion, while the main Region attribute holds the current value |
Supports a small number of named perspectives rather than a complete sequence of versions |
| Inferred member | Create a provisional dimension row before all descriptive details arrive | Allows a fact to retain its known business relationship while details are completed later |
| Effective date | The business date or time from which a version applies | Needs an agreed source, boundary convention, and time zone |
| Processing date | When the warehouse received or processed the change | Useful for audit, but it isn’t automatically the same as the effective date |
| Restatement | Recalculate or republish an earlier result using corrected or revised data | Requires ownership, controls, impact assessment, and communication |
The type numbers are labels for different change-handling techniques, not stages that every warehouse must implement. A single dimension can use different techniques for different attributes, depending on the reporting requirement. The Kimball Group’s dimensional-modelling techniques index provides more detailed definitions of Types 0 to 7.
Evidence the Historical Design Is Usable
A history design is more credible when the team can demonstrate:
- A documented policy for each attribute that can affect historical reporting.
- Examples showing the current view and the as-was view expected by users.
- An effective-period convention that prevents gaps and overlaps.
- Tests proving facts resolve to the correct version at boundary dates.
- A defined approach for inferred members, late facts, and retrospective changes.
- Reconciliation showing how restatement affects previously published totals.
- Clear disclosure of history that couldn't be recovered from the sources.
- Named owners for classification changes, corrections, and publication decisions.
The choice between replacement and versioning affects warehouse storage, load logic, fact assignment, reconciliation, semantic models, and user expectations. It should be visible in requirements and design artifacts rather than hidden inside a transformation.
Questions to Take into Data Transformation
Historical policy establishes what the warehouse needs to preserve. The next step is deciding where and how source data will be extracted, transformed, validated, and loaded.
- Which transformations must happen before data reaches the warehouse model?
- Which transformations benefit from the scale and controls of the target platform?
- How will new and changed rows be detected?
- Where will rejected records and quality evidence be retained?
- How will loads remain restartable without creating duplicates or gaps?
- What reconciliation proves that transformation preserved the intended meaning?
Those questions lead into Article 6, which examines ETL, ELT, and the controls needed around either transformation sequence.