Slowly Changing Dimensions in Master Data Management (SCD Types Explained)

Slowly Changing Dimensions in Master Data Management

Last week, we unpacked When and Why to Build a Master Data API. We covered the tipping points, like when systems compete for the latest values or downstream teams need just-in-time access to clean attributes. If your golden record isn’t easily accessible, you’re inviting shadow pipelines.

This week, we’re going deeper into what makes that golden record trustworthy over time: how you manage change.

Welcome to the real work: slowly changing dimensions (SCDs) in Master Data Management.

What Are Slowly Changing Dimensions (SCDs)?

SCDs describe how systems track changes to dimensional data (like customers, products, or vendors) over time. While originally a data warehousing concept, SCD patterns are essential for any MDM platform that needs to manage historical changes, track lineage, or serve analytics and operational systems alike.

In MDM, SCDs control:

  • Which changes overwrite the current state
  • Which changes preserve history
  • How consumers know which record is current
  • How downstream systems interpret these changes

The 7 Types of SCDs (0–6)

TypeDescriptionMDM UseAnalytics Use
Type 0No changes allowedGood for tax IDs, immutable keysSnapshot accuracy
Type 1Overwrite with new valueCleanups, corrected dataNo history
Type 2Add new row with versioningTrack full historyTime-based reporting
Type 3Add new column for previous valueUseful for current + previousLimited history
Type 4Store changes in a history tableSeparate audit storeClean golden record
Type 5Combine Type 1 and 4Overwrite + audit traceHybrid design
Type 6Combine 1, 2, 3Full lineage, flexible viewsTime-aware dashboards

In practice, Type 1 and Type 2 dominate the field, but a hybrid (Type 6) is often needed when your MDM hub feeds both ops and analytics.

Operational vs Analytical SCDs

Operational MDM favors current values. Users care about “what is true now” (e.g., the active customer address or product price).

Analytical systems prioritize lineage and visibility. BI teams ask, “What was true on this date?” or “How has this changed over time?”

SystemPriorityCommon SCD Types
MDM HubCurrent state, survivorshipType 1, Type 4
Data WarehouseChange tracking, trend analysisType 2, Type 6
APIsLight payloadsType 1, Type 3

A smart MDM strategy separates storage (store full history) from serving (serve the right view).

MDM and Change Management

In MDM, changes come from many sources:

  • Upstream systems (CRM, ERP)
  • Manual stewardship
  • External enrichments

Each must obey field-level survivorship logic. You decide:

  • Which source wins?
  • When do nulls override values?
  • What counts as a change?

Key fields to define SCD policy for:

  • Status (active/inactive)
  • Address and contact details
  • Classification codes (customer tier, product category)
  • Hierarchies (store → region)

And yes, you need different policies for each.

Best Practices for Managing SCDs in MDM

  1. Use semantic defaults. Avoid nulls. Use values like UNKNOWN, NOT_PROVIDED, or PENDING_APPROVAL.
  2. Log every change. Even if your golden record overwrites values, track the delta in a side table.
  3. Version records. Use EffectiveStartDate and EffectiveEndDate or a VersionNumber.
  4. Mark current rows. Use a IsCurrent = 1 flag for querying ease.
  5. Validate source trust. Don’t let low-trust sources trigger high-impact changes.
  6. Design for purpose-built views. APIs and analytics consumers don’t need the full record.

Tool-Specific Implementation Tips

SQL Server (T-SQL):

  • Use MERGE statements with versioning logic
  • Add system-versioned temporal tables for audit
MERGE dbo.Customer AS Target
USING staging.Customer AS Source
ON Target.CustomerID = Source.CustomerID
WHEN MATCHED AND Target.Email <> Source.Email
    THEN UPDATE SET Target.Email = Source.Email;

Informatica MDM:

  • Leverage trust scores per source system
  • Use the Change Tracking feature for Type 2 behavior

Azure Data Factory:

  • Use Data Flow SCD transformations (Type 1, Type 2 built-in)
  • Connect to Delta Lake or SQL DB for history tables

Real-World Use Cases

Type 1: Correct a customer’s name typo. No need to keep the old misspelling.

Type 2: Keep historical product category assignments for reporting periods.

Type 3: Store both current and previous region for active store routing.

Type 6: Track changes to customer status and tier while preserving contact details.

MDM Architecture Patterns for SCDs

Store once, serve many.

  • Keep a core record with full history
  • Serve different views to consumers

Split history storage from serving layer.

  • Analytics pulls Type 2-like history
  • APIs get flattened Type 1 or 3

Audit-first design.

  • Log deltas before applying updates
  • Use triggers or pipelines to populate audit tables

Final Thought

SCDs aren’t just a BI topic, they’re central to whether your master data earns trust. The moment your MDM platform stops respecting time, it stops being useful for analytics.

Define your SCD rules. Apply them intentionally. And serve data that reflects reality, both past and present.

Related Posts