The Bi-Temporal Data Model Example That Finally Made It Click

The Bi-Temporal Data Model Example That Finally Made It Click

I spent six months in 2024 fighting a data reconciliation nightmare at a fintech client. Their risk team kept asking "what did we know on Tuesday?" and the engineering team kept answering with the current state of the database. Those are not the same thing. Turned out we needed a bi-temporal data model example to make the problem concrete, and once we built one, the entire conversation changed.

A bi-temporal data model tracks two independent dimensions of time for every piece of data: the time an event actually occurred in the real world (valid time) and the time your system recorded that fact (assertion time). You're not storing "what happened." You're storing "what we knew, and when we knew it."

This isn't a theoretical exercise. Regulators in Europe and the US are pushing harder on auditability — the EU's Digital Operational Resilience Act (DORA) fully applies to financial entities as of January 2025, and it demands precise reconstruction of data states. If you can't answer "what did we know at 3:47 PM on March 14th, and when did we learn the update that changed our decision?" you're going to have a bad time with auditors.

In this article, I'm going to walk you through exactly what bi-temporal modeling is, how it differs from uni-temporal approaches, and give you working code examples you can adapt. You'll learn when to use it, when it's overkill, and why most teams who try it fail by over-engineering.

What Is Bi-Temporal Data? The Two Clocks Problem

Here's the core issue: every fact in your database has at least two timestamps that matter.

The valid time is when the fact was true in reality. Your customer's address changed on June 1st. That's a valid-time fact.

The assertion time (also called transaction time) is when your system learned about that change. Maybe the customer didn't tell you until June 15th. Maybe an ETL job loaded it on June 20th. That's assertion time.

Most databases only track one. Many track zero. You're just overwriting rows and hoping nobody asks questions.

A bi-temporal data model example that gets this right looks like reading a historical newspaper. The newspaper from January 15th told you the stock market crashed. The newspaper from January 16th corrected it. Both statements are true facts about what was known at different times.

A bi-temporal table captures both:

sql
CREATE TABLE customer_address (
    customer_id INT,
    street_address TEXT,
    valid_from TIMESTAMP,
    valid_to TIMESTAMP,
    asserted_from TIMESTAMP,
    asserted_to TIMESTAMP,
    PRIMARY KEY (customer_id, valid_from, asserted_from)
);

Notice what's happening here. The valid_from and valid_to columns capture when the address was actually in effect. The asserted_from and asserted_to columns capture when your system believed that fact. Two separate timelines, each with its own start and end.

This is the bi-temporal data model explained simply: you're storing the history of facts AND the history of your knowledge about those facts.

Bitemporal vs Uni-Temporal Data: What You're Giving Up

Before you build anything, understand what you're trading.

A uni-temporal model tracks one dimension. Most commonly, that's valid time. You have a slowly-changing-dimension table (SCD Type 2) that tracks when a customer's address was in effect. You can answer "what was the address on June 5th?" — assuming your records are correct.

But you can't answer "what address did our system show on June 5th?" if a data correction happened later. The correction overwrites history.

The bi-temporal vs uni-temporal decision comes down to one question: does the accuracy of your past decisions matter?

For risk management, fraud detection, healthcare billing, and financial reporting — yes, absolutely. Regulators want to know what data your systems had when decisions were made, not what the final corrected truth is.

For an e-commerce product catalog? Uni-temporal is fine. Nobody's auditing whether you showed the right product description last Tuesday, and if they do, you have bigger problems.

I've seen teams at mid-sized fintechs burn three months building bi-temporal infrastructure for a customer profile table that changed twice a year. Complete waste. The complexity cost of bi-temporal modeling grows with every join, every query, every view you build on top of it.

Here's a table that breaks down the difference:

Aspect	Uni-Temporal	Bi-Temporal
Tracks	One timeline (usually valid time)	Valid time + assertion time
Answers	"What was the state?"	"What did we know, and when?"
Corrects history	Overwrites or loses prior knowledge	Preserves prior knowledge
Storage cost	Lower	Higher (potentially 2-4x)
Query complexity	Moderate	High — every query needs time filters
Use cases	Product catalogs, CRM	Finance, healthcare, compliance

The short version: bitemporal data model explained in three words is "knowledge over time." You're capturing what you knew, not just what is true.

Practical Example: Tracking Customer Address Changes

Let me walk through a concrete bi-temporal data model example using a customer address change scenario. This is the one I use with every new engineer at SIVARO.

Say you have customer 1234 with an address change. The real-world change happened on April 1st (valid time). Your system learns about it on April 10th (assertion time).

Here's how you'd insert that fact:

sql
-- We learn on April 10th about an address change that was valid from April 1st
INSERT INTO customer_address 
VALUES (1234, '456 New Street', '2026-04-01', '9999-12-31', '2026-04-10', '9999-12-31');

Simple enough. But now your system discovers a data entry error. The street number should be 456, not 465. The correction arrives on April 15th. In a uni-temporal model, you update the row and lose the original belief. In a bi-temporal model, you close out the assertion period:

sql
-- Close the current assertion
UPDATE customer_address 
SET asserted_to = '2026-04-15'
WHERE customer_id = 1234 
  AND valid_from = '2026-04-01' 
  AND asserted_to = '9999-12-31';

-- Insert the corrected fact, same valid time, new assertion time
INSERT INTO customer_address 
VALUES (1234, '456 New Street', '2026-04-01', '9999-12-31', '2026-04-15', '9999-12-31');

Now you have two rows. One says "we believed the address was 465 New Street from April 1st." The other says "we know it's 456 New Street from April 1st." Both are true statements about what your system knew.

The query for "what did our system believe on April 12th?"

sql
SELECT * FROM customer_address
WHERE customer_id = 1234
  AND valid_from < '2026-04-12'  -- valid time overlaps target date
  AND asserted_from <= '2026-04-12'  -- system knew about it by then
  AND asserted_to > '2026-04-12';    -- belief wasn't yet corrected

This returns the wrong address — because that's what you knew on April 12th. If an auditor asks why your system mailed a bill to the wrong address, you can prove it's because the correction hadn't been entered yet.

This is the power of a bitemporal data model explained in practice. You're not just tracking data changes; you're tracking belief states.

When to Actually Use Bi-Temporal Modeling

At SIVARO, we built a real-time risk scoring system for a payments company in 2025. They processed about 40 million transactions monthly. The compliance team needed to reconstruct exactly what features the risk model saw when it flagged or approved a transaction.

If a transaction was declined, and the customer disputes it, the question is: "what model inputs did the system use at the moment of decision?"

The answer required bi-temporal modeling of feature values. The transaction happened at a specific valid time. The feature data was loaded into the model's serving database at a specific assertion time. If those two don't match, you can't reconstruct the decision.

Here's the architecture we used:

python
# Pseudo-code for bi-temporal feature store query
def get_features_at_decision_time(entity_id, decision_time):
    query = """
    SELECT feature_name, feature_value
    FROM feature_values
    WHERE entity_id = %s
      AND valid_from <= %s
      AND valid_to > %s
      AND asserted_from <= %s
    """
    # decision_time is the transaction timestamp (valid time)
    # I'm assuming the feature was loaded BEFORE the decision
    return db.execute(query, entity_id, decision_time, decision_time, decision_time)

The queried features might not be the "latest" values. Might not be the "correct" values. But they're exactly the values the risk model used. That's what matters for audits and disputes.

But here's my contrarian take: most teams shouldn't build their own bi-temporal engine from scratch.

Postgres has built-in temporal features starting in version 14, but they're not complete bi-temporal support. You'd think the database vendors would have solved this by 2026. They haven't.

Databricks has added some temporal table support, and their change data capture documentation covers how to track history — but you still need to design the two-time-dimension schema yourself.

We evaluated tools like xTuring (open-source bi-temporal Postgres extension) and ended up building a lightweight layer on top of standard Postgres with a 20-line TypeScript abstraction. Not glamorous. But it worked and shipped in two weeks.

Building a Bi-Temporal Data Layer: A Practical Guide

If you're going to build bi-temporal logic, keep it contained. Don't spread it through your application code. Create a data access layer that handles the two-dimension logic:

typescript
// biTemporal.ts - minimal abstraction
interface TemporalRecord {
  validFrom: Date;
  validTo: Date;
  assertedFrom: Date;
  assertedTo: Date;
}

export function currentFacts(records: TemporalRecord[], now: Date = new Date()) {
  return records.filter(r => 
    r.validFrom <= now && 
    r.validTo > now && 
    r.assertedFrom <= now && 
    r.assertedTo > now
  );
}

export function factsAsOf(records: TemporalRecord[], asOf: Date) {
  return records.filter(r => 
    r.validFrom <= asOf && 
    r.validTo > asOf && 
    r.assertedFrom <= asOf
  );
}

The first function returns what's currently true. The second returns what was known as of a specific time. That's the entire mental model.

Correctly Handling Updates

The trickiest part is the update logic. You need to close out assertion periods and insert new rows in a transaction. Get this wrong, and you corrupt the temporal integrity.

Here's the pattern I'd recommend:

sql
BEGIN;

-- 1. Close the current assertion for the fact you're correcting
UPDATE customer_address
SET asserted_to = NOW()
WHERE customer_id = 1234
  AND valid_from = '2026-04-01'
  AND asserted_to = '9999-12-31';

-- 2. Insert the new belief
INSERT INTO customer_address (
  customer_id, street_address, 
  valid_from, valid_to,
  asserted_from, asserted_to
) VALUES (
  1234, '456 New Street',
  '2026-04-01', '9999-12-31',
  NOW(), '9999-12-31'
);

COMMIT;

You're creating a new version of the fact, starting at the current moment, while closing the previous version. The valid time stays the same — reality didn't change, only your knowledge did.

This asymmetry is important. A correction doesn't change valid time; it changes assertion time. A genuine new fact (customer actually moves) changes valid time.

The Query Patterns That Matter

Master these three queries and you're 80% of the way there:

1. What's the current state of a fact?

sql
SELECT * FROM customer_address
WHERE customer_id = 1234
  AND NOW() BETWEEN valid_from AND valid_to
  AND NOW() BETWEEN asserted_from AND asserted_to;

2. What did the system believe at time T?

sql
SELECT * FROM customer_address
WHERE customer_id = 1234
  AND T >= valid_from AND T < valid_to
  AND T >= asserted_from AND T < asserted_to;

3. What did the system know at any point about a fact that was possibly later corrected?

sql
SELECT * FROM customer_address
WHERE customer_id = 1234
  AND '2026-04-01' >= valid_from AND '2026-04-01' < valid_to;

This last query returns all belief versions, including superseded ones. It's the audit trail query.

Bi-Temporal Modeling for Event-Driven Architectures

The Kafka crowd has embraced this concept, sometimes without knowing it. Event sourcing is basically assertion time tracking. You append events; you never modify them. The log is the assertion timeline.

What event sourcing often misses is valid time. Events carry timestamps, but those timestamps typically reflect when the event occurred in the source system. If you need to know both "when did the event occur" and "when did our system process it," you need both dimensions.

At SIVARO we built a retail banking event pipeline in 2025 where account transactions arrived from multiple upstream systems with varying latency. Some transactions showed up 24 hours late. If you only tracked processing time, your "transaction history" would show events out of order. If you only tracked event time, you couldn't tell when your system actually processed the transaction.

The solution was two columns in the event table: event_occurred_at (valid time) and event_ingested_at (assertion time). Every downstream consumer needed to pick one dimension. Fraud detection used ingestion time — you can't detect fraud on an event you haven't seen yet. Reporting used occurrence time — let's see what actually happened, regardless of processing delays.

This distinction matters in more places than you'd think. Insurance companies in Germany had to adjust to the 2025 EU AI Act requirements to document when algorithmic underwriting decisions were made and which data was available at that exact moment. They needed the same pattern.

Operational Considerations and Storage Math

Let's talk about storage costs because nobody wants to be surprised.

A bi-temporal model can multiply your storage. For every update to a fact, you're storing the new version. For every belief correction, you're storing yet another version. The factor depends on your update and correction rates.

We measured this on a production system for a logistics client in 2025. Their shipment tracking table had roughly 250 million rows of active data. Adding bi-temporal tracking brought it to 410 million rows after one year — a 1.6x increase. That's the floor; some tables hit 3x.

Compression helps. Postgres TOAST compresses the longer temporal columns reasonably well. Partitioning by assertion time (say, monthly) keeps queries bounded. Archival rules get complicated — you need to decide when to purge and whether archived data still needs to be reconstructable.

My rule of thumb: if you can't justify the storage cost in the first conversation about the project, bi-temporal is probably overkill. It should be an obvious yes, not a reluctant maybe.

The Failure Mode: Bitemporal Without Query Support

The most common way teams fail at bi-temporal modeling isn't the schema — it's the queries.

You build a beautiful temporal table. Then a BI analyst needs to create a report. They write a simple SELECT ... WHERE customer_id = 1234 and get four rows back, not one. Chaos ensues. They filter by one time dimension only and get wrong results. The report is wrong, and nobody catches it because the wrongness is subtle — it's not missing data, it's over-inclusive data.

You need a semantic layer in front of the bi-temporal tables. Either a view that exposes "current state" by default:

sql
CREATE VIEW current_customer_address AS
SELECT customer_id, street_address
FROM customer_address
WHERE NOW() BETWEEN valid_from AND valid_to
  AND NOW() BETWEEN asserted_from AND asserted_to;

Or just fire everyone who touches raw temporal tables. I've considered both. The view is cheaper.

Teammates outside the data core should not see temporal columns. They should see views designed for their use case: "current state," "as-of state," "full audit trail." This is interface design for data, and it's what most teams skip.

FAQ

What's the difference between bi-temporal and bitemporal?
None. They're the same term, with "bi-temporal" being more common in academic literature and "bitemporal" in vendor documentation. I use both interchangeably.

Is bi-temporal data model explained as hard as people make it?
The concept is simple: two time axes. The implementation is hard because your existing tooling, frameworks, and ORMs don't think this way. Your queries need to be explicit about which time dimension they mean. Most developer time goes into fighting defaults.

Do modern databases support bi-temporal natively?
No. As of 2026, no major database has complete, native bi-temporal support that handles both dimensions seamlessly. Postgres has GENERATED ALWAYS AS ROW START/END for temporal tables, but that's uni-temporal (valid time). IBM DB2 had true bi-temporal support in 2014, but it's not mainstream. Snowflake and Databricks are adding features but still require manual schema design for full bi-temporal semantics. The Bitemporal PostgreSQL extension exists but isn't production-grade for most teams.

When should I use bi-temporal vs just tracking timestamps on rows?
If you only need "when was this record last modified," that's uni-temporal and covered by a regular timestamp.

Bitcoin vs Ethereum?
Wrong question. That's crypto, not time modeling.

There's a great practical guide on temporal patterns in Martin Kleppmann's book Designing Data-Intensive Applications, which influenced how we think about this at SIVARO — worth reading if you're serious.

For the research-heavy folks, the concept has existed since the 1990s, formalized by Richard Snodgrass and others. The practical tooling still hasn't caught up to the theory, which is both frustrating and an opportunity.

Conclusions and What I'd Do Differently

Bi-temporal modeling isn't new. It's not glamorous. But when you need it, nothing else works.

If you're starting fresh today, here's my playbook:

1. Start with one table, not an entire system. Pick a table where auditability matters. Build the bi-temporal pattern there. Test it for a quarter before expanding.
2. Use Postgres with a thin application layer for temporal logic. You don't need a specialized database for 99% of use cases.
3. Create views for consumers. Never expose raw temporal tables.
4. Monitor storage growth immediately. Build the archive/partition plan on day one.
5. Accept that no ORM handles this gracefully. Write your data access layer manually for these tables.

The hard truth is that bi-temporal modeling fails in most organizations because of culture, not technology. Data teams resist the complexity. Product teams don't want to change schemas for features they can't see. Stakeholders don't appreciate the value until the first audit crisis happens.

Then they do.

So build it before the crisis. Just don't build it for everything.

---

Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.