Jaco van der Laan

Designing for Reproducibility: Dual SCD2 (Bi-Temporal) Historization in Modern Data Platforms

01/10/2025  ·  Data Architecture  ·  Data Engineering  ·  Data Modeling

‹ ›

When I wrote From Kimball to Metadata: How Dimensional Thinking Still Shapes Modern Data Architecture, a reader asked:

“Why do you focus on dual SCD2 specifically? Why not use a simpler historization pattern?”

It’s a fair question — and worth its own deep dive.
In this article I’ll explain why I advocate a dual SCD2 (bi-temporal) approach for historization in regulatory-grade data platforms where reproducibility and auditability are non-negotiable.
If your organization needs to reproduce data states across both functional and technical timelines — common in regulated industries — this pattern will likely serve you well.

The Challenge: Reproducibility in a Complex, Federated Data Landscape

Banks (and many other large enterprises) ingest data from heterogeneous organizational units — entities, subsidiaries, or functional areas. These units often deliver integrated datasets rather than raw transactions. And those deliveries:

  • Are often periodic snapshots (e.g., “End of January 2025 customer balances”).
  • Can arrive late, out of order, or even be re-delivered (e.g., “Here’s a corrected January snapshot”).
  • May include retroactive corrections for previously closed functional periods.

For regulatory reporting, model risk validation, and internal/external audits, we must:

  • Reproduce what the data looked like at any past point in time (both technically and functionally).
  • Trace why a figure or balance was what it was when a report was published.
  • Compare historical views across re-deliveries or system migrations.

A single Slowly Changing Dimension Type 2 (SCD2) history — typically capturing only technical load dates — is insufficient.

Functional vs. Technical Time

To address this, we track two independent timelines for every integrated record:

Functional Time
Represents the real-world effective period of the data — e.g., this balance is valid for January 2025.

  • Typically derived from the source system’s business key and effective dates.
  • Changes when the business meaning or state changes (e.g., new balance for the same account period).

Technical Time
Represents when the data was delivered and loaded into the platform.

  • Changes whenever a file arrives or is reprocessed — even if it’s for the same functional period.
  • Captures when we knew what we knew.

Dual SCD2 = two sets of valid_from / valid_to columns:

  • functional_valid_from / functional_valid_to
  • technical_valid_from / technical_valid_to

This is sometimes called bi-temporal modeling — but in data warehousing circles, you’ll often hear “dual SCD2.”

Why Single-Timeline Approaches Fall Short

Single SCD2 (technical only)

  • Can’t reproduce the business state for a past functional date if data was reloaded later.
  • “As of January 31, what did we think the balance was?” vs. “What should the balance have been on January 31?” — you can’t answer both with one timeline.

Snapshots Only

  • Snapshots capture what we saw at one load moment but explode storage and don’t easily support point-in-time joins or efficient incremental loads.
  • They don’t tell you when the underlying functional truth changed.

Event-only or append-only logs

  • Great for raw ingestion but hard to query and join consistently for regulatory reporting.

Dual SCD2 balances traceability and query efficiency.

It allows any of the following perspectives:

  • Functional As-Of: “Show me the world as it should have been on 2025–01–31.”
  • Technical As-Of: “Reproduce the exact dataset that fed last quarter’s report on 2025–02–07.”
  • Change Analysis: “Which accounts changed functionally vs. which were just technically re-delivered?”

How We Implement Dual SCD2 in Practice

Our pattern has evolved across several large-scale platforms (including banking):

Surrogate Keys

Two Validity Ranges

  • functional_valid_from / functional_valid_to
  • technical_valid_from / technical_valid_to
  • Both closed with a “high date” (9999-12-31) until superseded.

==Point==-in-Time (PIT) Helper Tables

Snapshots (Optional)

  • Still used at exposure layer for simpler consumers but sourced from PIT over dual SCD2.

Metadata-Driven Automation

  • All historization logic is generated from metadata (business keys, change detection rules).
  • Supports incremental pipelines and consistent historization at scale.

Why This Matters for Banks and Regulated Enterprises

  • Audit & Compliance: Regulators expect provable reproducibility — not just “we think it looked like this.”
  • Risk & Model Validation: Models must be tested against exactly what was available at training time.
  • Data Sharing & Federated Teams: Different business units may re-deliver; dual SCD2 keeps timelines clean.
  • Cost & Performance: Avoids uncontrolled snapshot explosion while retaining full history.

Related Reading

If you want to go deeper, I’ve written extensively about this pattern:

For external perspectives:

  • Dirk Lerner’s Temporal Data Series
  • Bitemporal Data on Wikipedia
  • Kimball Group on SCD2

Closing Thoughts

Dual SCD2 isn’t academic complexity — it’s a practical survival pattern for organizations where history must be both business-correct and technically reproducible.

If you’re building a model-driven, metadata-first data platform — especially in a regulated environment — dual SCD2 gives you:

  • Traceability
  • Audit confidence
  • Efficient point-in-time access
  • A foundation for self-service & AI-driven analytics without sacrificing correctness.

It’s why, even as we move beyond Kimball, the principle of capturing change cleanly continues to shape modern data architecture.