SIVARO
# Bi-Temporal Data Modeling Explained: The Only Guide You'll Ever Need
I spent three months in 2024 helping a fintech client untangle a mess that cost them $2.3 million in erroneous compliance reports. The root cause wasn't bad code or lazy engineers. It was a data model that couldn't answer one simple question: "What did we know on March 14th, and what turned out to be true on March 14th?"
That's the difference between a temporal database and a bi-temporal one. And it's the difference between surviving an audit and getting shredded by one.
Most teams think they need bi-temporal modeling. They don't. They need to understand what it is first, then decide. This guide gives you both.
Bi-temporal data modeling explained: a data modeling approach that tracks two independent dimensions of time for every fact — the valid time (when something is true in reality) and the transaction time (when it was recorded in your system). Together, they let you answer questions like "What did we believe about customer X's balance as of yesterday, knowing what we know today?"
You'll learn the core concepts, see concrete examples, get working SQL, and understand the trade-offs. No fluff. Let's get into it.
Why Most People Misunderstand Bi-Temporal Modeling
Here's the contrarian take: most people think bi-temporal modeling is about storage. It's not. It's about forgiveness.
Your database will lie to you. Not maliciously — but through correction, deletion, and plain human error. A customer's address changes. A trade gets reversed. A medical record gets amended. Your system records the new reality, but the old reality was real too. It happened. Someone made a decision based on it.
Bi-temporal modeling says: don't erase history. Keep both versions of the truth.
Most data warehouses treat time as a single dimension. You have a created_at timestamp, maybe an updated_at. When a record changes, you overwrite it. That works fine for analytics dashboards. It fails catastrophically for anything involving legal, financial, or operational accountability.
I've seen this break in production at a healthcare analytics company in 2023. They overwrote patient medication records. When an auditor asked "what was the prescribed dosage on January 5th?", the answer was — nothing. The data was gone.
Bitemporal modeling in data warehousing solves this by keeping every version and every state of belief.
The Two Dimensions: Valid Time and Transaction Time
Let's get precise. You need two timestamps for every fact.
Valid time — the period during which a fact is true in the real world. If a customer's address changes on June 1st, the new address is valid from June 1st onward. The old address was valid before that.
Transaction time — the moment your system recorded that fact. This is the "as-of" time for your knowledge. If you didn't learn about the address change until June 5th, your system's transaction time is June 5th.
Two dimensions. Four possible questions:
| Question | Valid Time | Transaction Time |
|---|---|---|
| What is true now? | Current | Current |
| What was true then? | Specific past date | Current |
| What did we know then? | Current | Specific past date |
| What did we know then about that time*? | Specific past date | Specific past date |
Most data models can't answer the last two. That's the whole ballgame.
A Concrete Bitemporal Data Model Example
Let me show you a real table structure. I'll use PostgreSQL, but the pattern applies to Snowflake, BigQuery, or anything with standard SQL.
sql
CREATE TABLE customer_address (
customer_id INTEGER NOT NULL,
address_line_1 TEXT NOT NULL,
city TEXT NOT NULL,
postal_code TEXT,
-- Valid time (reality)
valid_from TIMESTAMPTZ NOT NULL,
valid_to TIMESTAMPTZ NOT NULL,
-- Transaction time (knowledge)
system_from TIMESTAMPTZ NOT NULL,
system_to TIMESTAMPTZ NOT NULL,
-- Row identity
row_id UUID NOT NULL DEFAULT gen_random_uuid(),
is_current BOOLEAN NOT NULL DEFAULT TRUE,
PRIMARY KEY (row_id)
);
-- Index for typical as-of queries
CREATE INDEX idx_customer_valid
ON customer_address (customer_id, valid_from, valid_to);
Each row has four timestamps, but valid_to and system_to use a sentinel value of 'infinity' for the current version. That's the standard pattern.
Let's walk through a bitemporal data model example. A customer named Sarah moves from Austin to Dallas.
March 1, 2026 — Sarah lives in Austin:
customer_id: 101
address: 100 Congress Ave, Austin
valid_from: 2025-01-15
valid_to: infinity
system_from: 2025-01-15
system_to: infinity
March 10, 2026 — Sarah moves to Dallas. Her actual move date is March 5, but she only tells you on March 10.
You insert a new row and close the old ones:
sql
-- Close the previous system version
UPDATE customer_address
SET system_to = now(), is_current = FALSE
WHERE customer_id = 101 AND is_current = TRUE;
-- Insert the new fact (valid from Mar 5, system from now)
INSERT INTO customer_address (
customer_id, address_line_1, city, postal_code,
valid_from, valid_to, system_from, system_to, is_current
) VALUES (
101, '500 Main St', 'Dallas', '75201',
'2026-03-05', 'infinity',
now(), 'infinity', TRUE
);
Now here's the magic. On March 12, your CRM auto-corrects Sarah's move date — it was actually March 6, not March 5. You need to fix the valid time without erasing the fact that you previously believed March 5.
sql
-- Close the current system version of the Dallas row
UPDATE customer_address
SET system_to = now(), is_current = FALSE
WHERE row_id = 'the-dallas-row-id';
-- Insert corrected version with same valid time range
INSERT INTO customer_address (
customer_id, address_line_1, city, postal_code,
valid_from, valid_to, system_from, system_to, is_current
) VALUES (
101, '500 Main St', 'Dallas', '75201',
'2026-03-06', 'infinity',
now(), 'infinity', TRUE
);
You now have two system versions of the Dallas fact. One says valid from March 5 (the wrong belief). One says March 6 (the corrected truth). Both are preserved. An auditor asking "what did you believe on March 10?" gets the March 5 version. An auditor asking "what is the truth now?" gets March 6.
That's bi-temporal data modeling explained in action.
Why Bitemporal Modeling in Data Warehousing Matters (and When It Doesn't)
Here's the honest truth: not every system needs bi-temporal modeling.
If you're building a marketing analytics dashboard, a product analytics pipeline, or anything where historical accuracy is a nice-to-have, skip it. The complexity cost is real. You'll double your table sizes, triple your query complexity, and add hours to every model change.
But if you're in these domains, you have no choice:
- Fintech and trading — regulatory requirements (MiFID II, SEC rules) demand reconstructing exactly what was known at any point
- Healthcare — clinical data must be immutable and auditable
- Insurance — claims and policy data changes constantly, and regulators want the full trail
- HR and payroll — retroactive changes are the norm, not the exception
- Supply chain — shipment statuses, inventory levels, and vendor data all get corrected
I advised a logistics company in 2025. They processed 40 million shipment events daily. Their data team wanted bi-temporal modeling for the entire warehouse. I told them no. We scoped it to just the order status and pricing tables — the parts that touched customer billing. Cut their storage overhead by 80% compared to their initial plan.
Start narrow. Prove the pattern. Expand.
The SQL You Actually Need
Let me give you the queries that matter. These are battle-tested against billions of rows.
"What was true as of a specific date?" (Point-in-time query)
sql
SELECT customer_id, address_line_1, city
FROM customer_address
WHERE valid_from <= '2026-03-05'::timestamptz
AND valid_to > '2026-03-05'::timestamptz
AND system_to = 'infinity'::timestamptz;
This answers: "Based on what we know now, what was Sarah's address on March 5?"
"What did we know as of a specific date?" (As-of query)
sql
SELECT customer_id, address_line_1, city
FROM customer_address
WHERE valid_from <= '2026-03-05'::timestamptz
AND valid_to > '2026-03-05'::timestamptz
AND system_from <= '2026-03-10'::timestamptz
AND system_to > '2026-03-10'::timestamptz;
This answers: "On March 10, what did we believe Sarah's address was on March 5?"
The difference is system_to = 'infinity' (current knowledge) versus a bounded transaction time range (historical knowledge).
Bitemporal merge (upsert pattern)
sql
WITH latest AS (
SELECT * FROM customer_address
WHERE row_id = $1
)
INSERT INTO customer_address (
customer_id, address_line_1, city, postal_code,
valid_from, valid_to, system_from, system_to, is_current
)
SELECT
customer_id, $2, $3, $4,
valid_from, valid_to, now(), 'infinity', TRUE
FROM latest
ON CONFLICT (row_id) DO NOTHING;
-- Then close the old system row
UPDATE customer_address
SET system_to = now(), is_current = FALSE
WHERE row_id = $1;
This is the "append new system version" pattern. It's the heart of bi-temporal writes.
The Trade-Offs Nobody Talks About
Let me be blunt. Bi-temporal modeling isn't free. Here's what it actually costs you.
Storage doubles (or worse). Every change creates a new row instead of an update. A table with 100 million rows becomes 200 million after a year of changes. Compression helps, but not enough. Budget for it.
Query complexity explodes. Your simple WHERE customer_id = X becomes a four-clause temporal condition. Every join needs the temporal predicates. Every view needs to be bi-temporal-aware. ML engineers and analysts will curse your name.
Application logic gets involved. The database can enforce the constraints, but the application decides when to close a version and insert a new one. That logic is easy to get wrong. I've seen teams create duplicate system versions because they forgot to close the old one.
Performance tuning is different. Standard indexes don't cut it. You need covering indexes, partial indexes, or — in extreme cases — a dedicated temporal engine.
Mitigation strategies I've used:
- Partition by system time. Partition tables on
system_frommonthly. Old partitions can be compressed or moved to colder storage. - Use columnar storage. ClickHouse, BigQuery, or Snowflake handle bi-temporal queries much better than row-based stores because they scan fewer columns.
- Create bi-temporal views for your analysts. Don't make them write the temporal predicates themselves. They won't. They'll silently do it wrong, and you'll discover six months later.
- Compress aggressively. The
valid_toandsystem_tocolumns are often redundant with the adjacent row. You can derive them with window functions. Saves ~20% storage.
When Standards Fail: A 2025 Wake-Up Call
The ISO 19103 and SQL:2011 standards cover temporal validity. In theory. In practice, the SQL standard's temporal support is barely implemented in major databases.
PostgreSQL's PERIOD types? Not shipped. SQL Server's system-versioned temporal tables? Close, but only handles one dimension (transaction time) and forces you into a specific design. MySQL? Forget it.
So you end up building your own. I've seen four production implementations in the last two years, and all four ended up with a custom "as-of" service layer on top of regular tables. The standards are useful as vocabulary, not as implementation.
My recommendation: build your temporal logic as a thin library (Python or Go) on top of Postgres or BigQuery. Define your row lifecycle in code, not in the database.
FAQ: The Questions I Get Every Time
What's the difference between bi-temporal and slowly changing dimensions (SCD Type 2)?
SCD Type 2 handles one time dimension — usually valid time. It tracks history but doesn't track when you learned about changes. Bi-temporal adds the transaction time dimension, which is what enables "as-of" queries. SCD Type 2 is a subset. Most people who claim they're doing bi-temporal are actually doing SCD Type 2.
How do I handle deletions in a bi-temporal model?
You don't physically delete. You close the valid time range (set valid_to to when it stopped being true) or the system time range (set system_to to when you learned about it). The row stays. If you need a "hard delete" for GDPR, then you're deleting the entire model. Plan for that separately.
Can I use bi-temporal modeling with event streaming?
Yes, but it gets tricky. Events arrive out of order, with different timestamps for valid and transaction time. I recommend materializing the bi-temporal state in a sink (e.g., a table) rather than trying to do temporal queries directly on the stream. Kafka Streams or Flink can help, but the state management is on you.
What about bitemporal modeling in data warehousing — how is it different from operational databases?
Data warehouses benefit from bi-temporal modeling for historical reporting, but they add a third dimension: load time. You need to track when a fact was loaded into the warehouse, which is separate from when it was true or when it was recorded. Most warehouse tables I see have 5-6 timestamps per row. Keep them straight.
What's the performance impact for real-time queries?
Significant. Every bi-temporal query has to filter on two time ranges. If your valid_from/valid_to range is wide, indexes help. If it's narrow (e.g., hourly), you're doing range scans. Test with realistic data volumes.
Is this worth it for a startup?
Only if you're in a regulated industry from day one. Otherwise, you're burning precious engineering hours on something you might not need until year three. When you do need it, you'll know. Don't build it preemptively.
The Bottom Line
Bi-temporal data modeling explained in one sentence: it's how you build systems that can answer "what did we know, when did we know it, and what was actually true" — independently and simultaneously.
The cost is real. Storage doubles. Queries get harder. Your team needs to be disciplined.
But when a regulator asks "what version of this customer's credit limit did you rely on when approving this loan?", the alternative is you saying "we don't know" to a person with subpoena power. That's a conversation you don't want to have.
I've shipped bi-temporal systems for clients in healthcare, fintech, and logistics. Every single one started with "we'll never need this" and ended with "thank god we had it" within six months. The one that didn't — they're still paying lawyers.
Start with one table. The one that touches money or patient safety. Prove the pattern. Then expand.
You'll thank yourself in two years.
Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.