Slowly Changing Dimensions: Why Historical Customer and Product Reporting Goes Wrong

Historical BI errors often begin with a simple assumption:

A customer, product, salesperson or region has one set of attributes.

In reality, those attributes change.

• A customer moves from SMB to Enterprise.
• A salesperson changes territory.
• A product moves to a different category.
• A member changes chapter.
• An account gets a new owner.

If the data warehouse stores only the latest attribute value, old transactions can appear to change history.

Last year’s revenue may suddenly move to this year’s territory.

That is the problem slowly changing dimensions are designed to solve.

The right design depends on the reporting question. Sometimes the business wants history restated using the current structure. Sometimes it wants the structure that was true when the transaction happened. Sometimes it needs both.

Actiknow’s business intelligence and data engineering work treats historical dimension behavior as a business-definition decision, not merely a warehouse implementation detail.

The Short Answer

Use Type 1 behavior when:

• Only the latest value matters.
• Historical correction is desirable.
• The old value has no reporting value.

Use Type 2 behavior when:

• Historical context matters.
• Reports must show the attribute that was true at the event date.
• Ownership, region, category or status changes must not rewrite history.

Use snapshots or fact-level attributes when:

• The state itself is a periodic business fact.
• Point-in-time reconstruction from change records would be too difficult.
• The source does not provide reliable change history.

Many real warehouses use a mix.

What Is a Slowly Changing Dimension?

A dimension contains descriptive attributes used to analyze facts.

Examples:

• Customer.
• Product.
• Employee.
• Account.
• Member.
• Supplier.
• Location.

“Slowly changing” means those descriptive attributes can change over time.

The phrase does not mean changes must literally be slow.

A customer segment could change several times in a month.

The design problem is how the warehouse should preserve or overwrite those changes.

A Simple Example

Customer 123:

January:

Region = East

July:

Region = West

There is a January sale for $1,000.

Question:

When an analyst runs the annual report in December, should that January sale appear under East or West?

There is no universally correct answer.

If reporting means “region at time of sale,” East is correct.

If reporting means “current customer portfolio by current region,” West may be correct.

The warehouse must support the intended question.

Type 1: Overwrite History

Type 1 updates the existing dimension row.

Before:

Customer 123 | Region East

After:

Customer 123 | Region West

The old value disappears.

Advantages:

• Simple.
• One row per entity.
• Easy joins.
• Lower storage.
• Good for corrections.

Disadvantage:

Historical facts now join to the current attribute.

Use Type 1 for attributes such as:

• Corrected spelling.
• Formatting.
• Non-historical classification.
• Latest contact information when old values do not matter.

Do not use Type 1 accidentally for historically meaningful fields.

Type 2: Preserve History

Type 2 creates a new dimension version when tracked attributes change.

Example:

Customer Key 1001

Customer ID 123

Region East

Valid From Jan 1

Valid To Jun 30

Current = False

Customer Key 1047

Customer ID 123

Region West

Valid From Jul 1

Valid To Open

Current = True

Facts can join to the dimension version that was valid at the fact date.

This preserves historical context.

Surrogate Keys Matter

The business key identifies the entity:

Customer ID 123.

The surrogate dimension key identifies a version:

• Customer Key 1001.
• Customer Key 1047.

A fact table can store the surrogate key corresponding to the correct historical version.

This makes historical joins efficient and explicit.

Do not confuse entity identity with dimension-version identity.

Effective Dates Need Clear Semantics

Type 2 dimensions usually include:

• Valid From.
• Valid To.
• Current flag.

Define boundaries precisely.

For example:

• Valid From inclusive.
• Valid To exclusive.

Then a join can use:

event_date >= valid_from

AND event_date < valid_to

Avoid ambiguous midnight/end-of-day rules.

Use consistent time zones.

Which Changes Should Create a New Version?

Not every changed column should trigger Type 2.

Suppose Customer has:

• Name.
• Email.
• Region.
• Segment.
• Account Manager.
• Last Login.
• Updated At.

Creating a new version for every email or last-login change can create enormous dimensions with little analytical value.

Define tracked attributes.

Example:

Type 2:

Region.

Segment.

Account Manager.

Type 1:

Corrected Name.

Email.

Ignored operational:

Last Login.

Source Updated At.

This is a business decision.

Product Category Example

A product originally belongs to:

Category = Accessories

Later merchandising moves it to:

Category = Electronics

Historical questions:

• What was revenue by category as reported at the time?
• What would historical revenue look like under today’s category structure?

Type 2 supports the first.

Current-value restatement supports the second.

Executives may need both.

Name the reports clearly.

Sales Territory Example

A salesperson moves from North to South.

If old sales now appear under South, historical territory performance changes.

That can distort:

• Quota analysis.
• Manager performance.
• Regional trends.
• Compensation reviews.

Type 2 territory assignment can preserve the organization structure at transaction time.

But current account-ownership reporting may intentionally use today’s territory.

Different marts can support different semantics.

Customer Segment Example

Customer moves:

SMB → Mid-Market → Enterprise.

For cohort analysis, historical segment matters.

For current portfolio reporting, latest segment matters.

A robust warehouse can expose:

• Segment at transaction.
• Current segment.

Do not force analysts to infer which one they are seeing.

Membership/Status Example

A member can change:

• Chapter.
• Category.
• Status.
• Affiliation.

A current membership dashboard may want latest status.

A historical “members by chapter as of last year” report needs point-in-time history.

If the source stores only current state and the warehouse begins capturing today, you cannot reconstruct prior history perfectly.

Plan snapshots early.

Type 0: Never Change

Some dimension attributes should never change once recorded.

Examples:

• Original acquisition source.
• Initial signup date.
• Birth date, assuming corrections are handled separately.
• Original product launch category if explicitly defined as historical origin.

This is sometimes called Type 0 behavior.

The label matters less than documenting immutability.

Type 3: Store Limited Previous State

Type 3 stores current and previous values in columns.

Example:

• Current Region.
• Previous Region.

This is useful only for limited comparisons.

It does not preserve an arbitrary history of changes.

For enterprise historical reporting, Type 2 or snapshots are usually more flexible.

Type 6 and Hybrid Patterns

Dimensional modeling literature describes hybrid SCD patterns combining Type 1, 2 and 3 behavior.

In practice, many modern warehouses simply model the exact required columns and history semantics rather than focusing on type-number terminology.

The important questions remain:

• What changes?
• What history is preserved?
• What should facts see?
• What should current-state reports see?

Late-Arriving Facts

Suppose a transaction for June arrives in August.

The customer changed region in July.

If you join to the current dimension row, the transaction may incorrectly use the July region.

Instead, resolve the dimension version based on the transaction’s business-effective date.

Late data is one reason current-state joins break historical reporting.

Late-Arriving Dimension Changes

A source may tell you in August that a territory change was effective in June.

Now previously processed facts may need correction.

Define policy:

• Backdate dimension version.
• Re-key affected facts.
• Restate reports.
• Record the correction.
• Communicate material changes.

Historical truth sometimes changes because the source supplied better information.

Type 2 does not eliminate restatement.

Source Update Time vs Business Effective Time

These are different.

Source Updated At:

When the system record changed.

Business Effective From:

When the business says the change applies.

For historical reporting, business-effective time may be more important.

If the source only exposes update timestamps, document the limitation.

Bi-Temporal Requirements

Some regulated/financial systems need two timelines:

• When the fact was true in the business.
• When the system learned/recorded it.

This is bi-temporal modeling.

It is more complex than standard Type 2.

Do not introduce it unless the audit/reconstruction requirement justifies it.

Snapshot Facts

Sometimes the cleanest solution is a periodic snapshot.

Example:

• Customer status as of each month-end.
• Employee organization as of each day.
• Inventory balance each night.
• Membership state at month-end.

Snapshots make “as of” reporting straightforward.

Tradeoff:

• More storage.
• Scheduled capture.
• Potentially large tables.

But storage can be cheaper than reconstructing state from incomplete changes.

Accumulating Snapshots

For lifecycle processes, an accumulating snapshot can track milestones.

Example order:

• Created date.
• Approved date.
• Shipped date.
• Delivered date.

This is a different modeling problem from dimension history but is often confused with SCD.

Choose the pattern based on the business process.

Fact Attributes Can Preserve Context

Sometimes a historically important attribute belongs directly on the fact.

Example:

• Order currency.
• Contracted price.
• Sales channel at purchase.
• Tax jurisdiction.

If the attribute describes the transaction itself, storing it on the fact may be more correct than resolving it through a changing dimension.

Avoid overusing dimensions for transactional context.

Current vs Historical Reporting Should Be Explicit

A semantic model can expose:

• Customer Current Region.
• Customer Region at Sale.
• Product Current Category.
• Product Category at Sale.
• Account Current Owner.
• Account Owner at Opportunity Close.

Clear naming prevents silent analytical disagreement.

Do not call both fields “Region.”

Power BI Modeling

For Type 2 dimensions, a Power BI model typically joins facts to the surrogate dimension key already resolved in the warehouse.

Avoid implementing complex effective-date range joins dynamically in Power BI when the warehouse can resolve them during transformation.

This improves:

• Performance.
• Consistency.
• Testability.

The semantic model can separately expose a current-state dimension if required.

Source Systems Often Do Not Preserve Enough History

CRM and SaaS systems may overwrite attributes.

If historical reporting might matter later, start capturing changes early.

Options:

• CDC.
• Snapshots.
• Audit/history objects.
• Warehouse ingestion history.

You cannot reliably recreate history that was never stored.

Fivetran/Connector History Is Not Automatically Business History

Some ingestion tools can retain change history.

That is useful.

But raw technical history may include:

• System updates.
• Non-business fields.
• Sync behavior.
• Deleted rows.
• API artifacts.

Transform it into a deliberate business-effective dimension.

Do not expose connector history directly as the semantic model.

Data Quality Tests for Type 2

Test:

• One current row per business key.
• No overlapping validity ranges.
• Valid From < Valid To.
• Surrogate key unique.
• Business key not null.
• Tracked changes create expected versions.
• Untracked changes do not create unnecessary versions.
• Facts resolve to one valid dimension row.

Overlapping ranges can double-count facts.

Gaps can leave facts unmatched.

Reconciliation

Compare:

• Current entity count.
• Distinct business keys.
• Version count.
• Changed entities.
• Facts with missing dimension keys.

Facts assigned to unexpected default members.

For major dimensions, manually test known change examples.

A row-count match alone does not validate history.

Unknown Dimension Members

A fact may arrive before its dimension.

A common pattern uses an Unknown or Not Yet Available member.

Later processing can re-key the fact when the dimension arrives.

This avoids dropping the transaction.

Monitor unknown-key volume.

High levels usually indicate pipeline ordering or source-quality problems.

Deletes

What does a source deletion mean?

• Entity truly deleted?
• Soft delete?
• Merged record?
• Privacy deletion?
• Source cleanup?

Define whether historical facts should retain the dimension description.

For many analytical use cases, deleting the historical dimension row would break history.

Privacy requirements may override that.

Coordinate governance and legal requirements.

Corrections vs Genuine Changes

This distinction is critical.

Customer region changed because they moved:

Historical change.

Customer region was wrong and corrected:

Maybe Type 1 correction.

If the source does not distinguish them, define a practical policy.

Not every update deserves a new historical truth.

Historical Hierarchies

Dimensions often have hierarchies:

• Product → Category → Division.
• Employee → Manager → Region.
• Account → Territory → Area.

Changes at higher levels can rewrite historical rollups.

Decide whether hierarchy history matters.

Sometimes a separate bridge/hierarchy history model is required.

Performance

Type 2 dimensions contain more rows.

Usually this is manageable.

Optimize:

• Surrogate-key joins.
• Clustering/partitioning where appropriate.
• Current-row filtering.
• Incremental processing.

Do not sacrifice correct history prematurely for a smaller dimension.

Implementation Pattern

For each source entity:

1. Identify business key.
2. Identify tracked attributes.
3. Hash/compare tracked values.
4. Detect changed rows.
5. Close prior version.
6. Insert new version.
7. Maintain current flag.
8. Resolve fact surrogate keys.
9. Test validity ranges.
10. Reconcile.

Make the process idempotent so retries do not create duplicate versions.

Idempotency Matters

If the pipeline reruns the same source batch, it should not create another identical dimension version.

Use:

• Deterministic comparisons.
• MERGE patterns.
• Stable business-effective timestamps.
• Batch IDs.
• Deduplication.

Test reruns explicitly.

When Type 1 Is Better

Do not preserve history by default.

Type 1 is better when:

• The field is corrected frequently.
• Old values have no analytical value.
• History would confuse users.
• The dimension would explode unnecessarily.

A warehouse is not an archive of every source mutation.

Preserve history with purpose.

When Type 2 Is Better

Use Type 2 when users ask:

“What was true then?”

Examples:

• Region at sale.
• Owner at close.
• Customer tier at renewal.
• Product category at purchase.
• Member chapter at reporting date.

If the answer can affect a historical KPI, preserve it.

When Snapshots Are Better

Use snapshots when users ask:

“What did the whole population look like at a particular date?”

Examples:

• Month-end customer status.
• Daily inventory.
• Weekly pipeline.
• Annual membership census.

Snapshots can be easier to reason about than reconstructing thousands of independent changes.

A Decision Checklist

For every changing attribute ask:

• Does old value matter?
• Should old facts retain old value?
• Should reports restate history?
• Does source provide effective date?
• Do we need current and historical views?
• How often does it change?
• Can we reconstruct it later?
• Is it sensitive?
• Who owns the definition?

Document the answer.

Frequently Asked Questions

What is a slowly changing dimension?

It is a dimensional-modeling approach for handling descriptive attributes that change over time.

What is SCD Type 1?

Type 1 overwrites the previous attribute value, so historical reports use the latest value.

What is SCD Type 2?

Type 2 creates a new dimension version and preserves effective dates, allowing facts to retain historical context.

Should every changing field use Type 2?

No. Preserve only history that has analytical value. Corrections and low-value operational fields often use Type 1 or are ignored.

Can Power BI handle Type 2 dimensions?

Yes. It is usually best to resolve the appropriate surrogate key in the warehouse and expose the resulting dimensional model to Power BI.

What if the source does not store historical changes?

Begin capturing CDC or snapshots. Earlier history may not be reconstructable unless audit data exists elsewhere.

Are snapshots the same as Type 2?

No. Type 2 records attribute changes as versions. Snapshots capture the state of a population at regular points in time.

Why do historical totals change after dimension updates?

Often because facts are joined to current dimension attributes instead of the historically correct version, or because hierarchy/business rules were intentionally restated.

Conclusion

Slowly changing dimensions are not primarily a technical pattern.

They are a decision about what history means.

Should last year’s sale follow the customer’s old region or current region?

Should a product’s old revenue remain in its old category?

Should an opportunity belong to the salesperson who closed it or today’s owner?

Answer those questions first.

Then implement Type 1, Type 2, snapshots or fact-level attributes accordingly.

A warehouse that preserves the wrong history can be perfectly engineered and still produce misleading reports.

If historical customer, product, membership or territory reporting is changing unexpectedly, Actiknow can help trace the issue from source history through warehouse dimensions and BI semantic models. Discuss your data warehouse modeling with Actiknow.