Architecting Two Truths: A Guide to Bi-Temporal Modeling

How data systems track both reality and what they believed at the time.

Most data engineers are familiar with Slowly Changing Dimensions (SCD Type 2). It introduces the idea of tracking history using:

• Effective From
• Effective To

This works well for dimensions, but it hides a deeper problem:

To solve this properly, we need bi-temporal modeling.

The Core Idea of Bi-Temporal Modeling

• Valid Time (Business Reality) When the data was true in the real world
• System Time (System Knowledge) When the system knew or recorded a version of the data

Both are stored as intervals:

• from → to

So every record has:

• valid_from, valid_to
• system_known_from, system_known_to

Why SCD Type 2 Is Not Enough

SCD Type 2 only tracks one timeline (effective dates). But in real systems, two different questions arise:

1. What was true in the real world?
2. What did the system believe at a given time?

These are not the same.

This becomes obvious when:

• Data is corrected
• Late-arriving data is received
• Facts are restated

Example 1: Fact Data (Monthly Return Correction)

Let’s take a real-world KPI: May Return %

Initial Load (June 1)

We receive:

• May Return = 12%

We store:

Correction Received (June 15)

We receive corrected data:

• May Return = 12.5%

We insert a new version:

Example 2: Dimension Data (Customer Address Change and Correction)

This example shows two independent things:

1. Customer moves (valid time change)
2. ZIP code correction (system time change)

🧭 Step 1: Initial State (Customer lives at old address)

Customer initially lives at an old address.

Meaning:

• Customer is currently at Address X
• We believe this is correct
• No known future end yet

🏠 Step 2: Business Event — Customer Moves (June 1)

On June 1, customer moves to Address A.

We now update VALID TIME only (business reality changes).

Meaning:

• Business truth changed → customer moved
• System belief is still correct (no correction yet)
• ZIP 90004 is what we believe at this time

🔧 Step 3: Data Correction — ZIP was wrong (June 20)

We learn that:

ZIP 90004 was incorrect → correct ZIP is 90020

Now we are NOT changing business reality.

We are only correcting system knowledge.

So we:

• Close old system-known version
• Insert corrected version

✅ Final Bi-Temporal State

Key Insight: What Changes and What Does Not

Valid Time Changes When:

• Business reality changes (customer moves, event happens)

System Time Changes When:

• We correct or update what we knew about data

How Do We Answer Questions?

Answer:

Question 2: What do we now believe is correct?

What is the corrected May return?

Answer:

• 12.5%

Question 3: What was the customer’s ZIP on June 10?

Answer:

• 90004

Answer:

• 90020

Why Two Timelines Are Necessary

If we store only one timeline (like SCD Type 2), we lose one of these truths:

• Either we lose historical system knowledge
• Or we lose corrected reality

Bi-temporal modeling preserves both.

Final Thought

Bi-temporal modeling is not just an advanced version of SCD Type 2.

It is a more complete representation of reality in data systems.

Because in real-world data systems, there are always two truths:

• What actually happened (valid time)
• What the system knew when it happened (system time)

Bi-temporal modeling ensures we never lose either.

References

• https://www.youtube.com/watch?v=D3KUMBEapyw 28:30
• LLM