Bitemporal Data Modeling: The Hard Truth Nobody Tells You
Look, I get it. You're here because you've got a production system that's rewriting history, and it's driving you insane. Your reports don't match your operational data. Your auditors are asking uncomfortable questions. Your data team is recreating the same broken patterns every six months.
I've been there. In 2019, we lost a client's entire order history at SIVARO because their "temporal" model only tracked valid time, not assertion time. When their ETL pipeline re-ran after a failed batch, it silently overwrote corrected records. Sales figures changed retroactively with zero audit trail.
That incident cost us the account. It also taught me the difference between doing temporal modeling and doing it right.
A quick definition: temporal data modeling tracks when something was true in reality. Bitemporal data modeling tracks when something was true in reality and when your system actually knew about it. Two separate timelines. Both matter.
This article covers what I've learned building data infrastructure for everything from fintech to logistics companies. You'll learn when temporal tables are sufficient, when bitemporal models are non-negotiable, and how to handle the messy reality of out-of-order events in Kafka.
Let's get into it.
Most People Confuse Temporal Tables with SCDs. That's a Problem
The industry has spent two decades talking about slowly changing dimensions. Type 1, Type 2, Type 3 — the alphabet soup of data warehousing. Tim Mitchell's work on using temporal tables for slowly changing dimensions is particularly good because it shows how SQL Server's system-versioned tables can replace manual SCD implementations.
But here's the thing that trips everyone up:
Temporal tables and SCDs are not the same thing.
| Capability | Temporal Table | SCD Type 2 | Bitemporal Model |
|---|---|---|---|
| Tracks valid time | Yes | Yes | Yes |
| Tracks assertion time | No | No | Yes |
| Handles corrections | Poorly | Poorly | Cleanly |
| Requires manual ETL logic | No | Yes | Sometimes |
SQL Server's temporal table documentation shows the typical use cases: auditing changes, point-in-time analysis, reconstructing historical states. These are genuinely useful features. But notice what's missing from their scenarios — handling data that arrives late or out of order isn't a use case they address.
Why? Because temporal tables assume your data arrives in the right sequence. Production systems violate that assumption daily.
The TDWI piece on temporal data modeling makes this distinction clear: tracking what happened is different from tracking what you knew. It's the difference between a record and a memory of that record. Understanding this distinction is the foundation of building systems that don't fall apart when data arrives late, gets corrected, or comes from unreliable sources.
Most teams think they need temporal tables when they actually need bitemporal modeling. The distinction becomes critical when answering simple questions like "what did we know on March 15th?" versus "what was true on March 15th?" These are different questions with different answers.
The Two Timelines Explained (Without the Academic Fog)
Let me break this down with a concrete example from a logistics client we worked with in 2022.
A shipment contains 2,500 units of electronics. The tracking system says it departed the warehouse Tuesday at 09:00. That's valid time — when the event actually occurred in reality.
But the tracking system's API was down for six hours that morning. The "departed" event didn't hit our database until Tuesday at 15:30. That's assertion time — when our system first knew about it.
Most data models only track valid time. If you're building a real-time dashboard showing shipment status, that's probably fine. If you're building a customer-facing tracking page, it's fine too.
But the moment you need to answer "how many shipments did we show as departed at 10:00 AM on Tuesday?" you're screwed with a valid-time-only model. You literally can't answer it. The data didn't exist in your system at that point.
This is where "temporal vs bitemporal data modeling" stops being academic and starts being business-critical.
Bitemporal modeling captures both timelines:
valid_fromandvalid_tofor when events occurred in realityasserted_atfor when your system recorded them
Here's a detailed discussion of slowly changing dimensions and temporal databases that digs into this question from the database-nostalgia angle. The author's point about vector time is worth reading.
The Three Times Problem You Didn't Know You Had
Bitemporal modeling introduces a third time dimension that most people don't think about: transaction time.
Yes, I said three, not two.
- Valid time: When the event occurred in reality
- Assertion time: When your system recorded it
- Transaction time: When the record physically changed in your database
OK, technical implementation details matter here. Let me show you a concrete example.
sql
-- Bitemporal table structure we've used in production
CREATE TABLE shipment_events (
shipment_id BIGINT NOT NULL,
event_type VARCHAR(32) NOT NULL,
location_code VARCHAR(10),
valid_from TIMESTAMPTZ NOT NULL,
valid_to TIMESTAMPTZ,
asserted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
row_version BIGINT NOT NULL DEFAULT 1,
PRIMARY KEY (shipment_id, event_type, valid_from, row_version)
);
See that row_version column? That's critical. You can't just update records in a bitemporal model — you need to insert new versions. The version number ensures that multiple assertions about the same event can coexist without primary key collisions.
It also means we can answer questions that break most systems:
sql
-- What did we believe about shipments at 10:00 AM Tuesday?
SELECT shipment_id, event_type, location_code
FROM shipment_events
WHERE asserted_at <= '2026-08-02 10:00:00' -- we knew this information by then
AND valid_from >= '2026-08-02 09:00:00'
AND valid_to <= '2026-08-02 10:00:00';
This query gives you the state of the system as it appeared at a specific moment — not the state of reality, but the state of knowledge.
When Bitemporal Costs You More Than It Saves
I'm going to say something that might be controversial:
You don't need bitemporal modeling for most analytics use cases.
There. I said it.
If you're building a data warehouse for business intelligence, and your data comes from systems you control, temporal modeling might be enough. Tracking valid_from and valid_to gives you historical snapshots. That's what most people need.
But if you're building:
- A financial system that handles regulatory compliance
- An insurance platform that processes claims after the fact
- A risk management system that needs to know what was known when
- Any system where data corrections must be auditable
Bitemporal modeling isn't optional — it's the only safe choice.
We've built trading systems for a market maker in Amsterdam. For them, the question "what did we see on the exchange at 09:45:21.100?" is regulatory. They MUST know their exact system state at any point in time. That's bitemporal modeling.
The cost? Storage grows roughly linearly with version count. Query complexity increases. Staff need to understand what they're querying. In 2023, we estimated that a client's bitemporal implementation added about 40% to their storage costs and slowed point-in-time reconstruction queries by 2-3x compared to a simple valid_from/valid_to model.
But the alternative was regulatory fines in the millions. So the tradeoff made sense.
How to Handle Out-of-Order Events in Kafka
Earlier I called out Kafka as a particular pain point. Out-of-order events in Kafka are the single biggest chaos generator I see in data systems. Here's the pattern I've observed repeatedly: an event for order #12345 arrives, gets written to the database. Twenty minutes later, a retry event for the same order arrives with a different timestamp, different payload, and no version marker.
Your temporal table gets corrupted. Your valid time goes backwards.
The fix is the Kafka streams architecture that handles partial event streams, but the real solution is designing your event schema with bitemporal awareness.
sql
CREATE TABLE orders (
order_id BIGINT NOT NULL,
event_id UUID NOT NULL,
stated_at TIMESTAMPTZ NOT NULL, -- when the sender claims it happened
received_at TIMESTAMPTZ NOT NULL, -- when we actually received it
payload JSONB NOT NULL,
PRIMARY KEY (order_id, event_id)
);
Now your system can make a choice: trust stated_at or trust received_at. In our trading systems, we use both and reconcile on a separate pipeline.
For Kafka specifically, the critical insight: event time and processing time are different. Kafka consumers that sort by event time on arrival will produce wrong results. You need to handle late events deliberately.
What we build at SIVARO typically includes:
- An ingestion layer that timestamps everything on arrival (assertion time)
- An event store that preserves event-provided timestamps (valid time)
- A reconciliation job that identifies out-of-order events and reprocesses affected queries
Querying Bitemporal Data Without Losing Your Mind
Once you've built the bitemporal model, you still have to query it. This is where most teams give up and fall back to temporal models.
The fundamental challenge is: how do you find the correct record for a given point in time?
A naive query looks like this:
sql
SELECT *
FROM shipment_events
WHERE shipment_id = ?
AND valid_from <= '2026-08-01 00:00:00'
AND (valid_to IS NULL OR valid_to > '2026-08-01 00:00:00');
This gets you the state of the shipment at the given valid time. But it does NOT get you:
- What the system knew at that time (requires filtering on
asserted_at) - The latest version of the event (requires a version rank)
A correct bitemporal query uses both filtered constraints and version ranking:
sql
WITH ordered_events AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY shipment_id, event_type, valid_from
ORDER BY asserted_at DESC) AS rn
FROM shipment_events
WHERE valid_from <= '2026-08-01 00:00:00'
AND (valid_to IS NULL OR valid_to > '2026-08-01 00:00:00')
AND asserted_at <= '2026-08-01 00:00:00'
)
SELECT * FROM ordered_events WHERE rn = 1;
This gives you the latest version of the event as of the query time, but ONLY if that version was known by the assertion time. It's a subtle but critical difference.
Performance skyrockets when you pre-compute some of this. We usually build a "current facts" table that mirrors the bitemporal data but only stores the latest version, plus a "point-in-time facts" table for historical queries. The current facts table serves real-time requests. The history table serves analytical and reporting needs. Each serves its purpose without the other's query complexity.
The Event Sourcing Angle (And Why We Almost Never Use It)
If you're building microservices, you've probably come across event sourcing. The idea is elegant: instead of storing state, store events. Rebuild state by replaying events. Bitemporal modeling feels like a natural fit.
It feels that way. Until you try to implement it.
Event sourcing suffers from a fundamental problem when applied to bitemporal data: events themselves have timestamps, but those timestamps only reflect valid time. The event's creation time in your system is assertion time. If events are replayed, the assertion time changes — which means the bitemporal model corrupts.
We tried event sourcing for a project in 2021. The premise was solid: build a trading audit trail from event logs. What we discovered was that replaying events always creates new assertion times, which means the audit trail isn't actually an audit trail. It's a reconstruction.
The solution we eventually landed on: store events with their original assertion times as asserted_at fields. Don't overwrite that timestamp during replay. Instead, add a replay_occurred_at column that tracks when the event was re-processed.
This way, the bitemporal model preserves two truths simultaneously:
- When the event actually occurred (valid time)
- When the system first saw it (assertion time)
- When the replay occurred (transaction time)
Three timelines, all visible in the schema.
When Temporal vs Bitemporal Is a Compliance Question
The SQL Server temporal table documentation lists reporting and compliance as the main drivers for temporal modeling. It's not wrong, but it's incomplete. For compliance, you need to answer questions the temporal model can't touch.
Consider HIPAA in healthcare. When a patient record is corrected, you need to know:
- What the original record said (valid time history)
- When the correction was made (assertion time)
- Who made the correction and why (transaction time)
A temporal model gives you #1. It's missing #2 and #3 entirely. A bitemporal model captures all three.
This is exactly the kind of gap that surprises teams when auditors start asking questions. No, your temporal table doesn't show when a correction was made. You need to explain that in your compliance documentation or risk the consequences.
The two-timeline nature of bitemporal modeling matches the two-timeline nature of compliance: reality and knowledge. The distance between them is what auditors care about, because it's where mistakes happen.
In finance, regulations like MiFID II require preserving the exact sequence of events as they were seen by your system. This is impossible with temporal-only data. You can't reconstruct what you saw at 09:45 if you overwrite the data you saw at 09:45.
The Cost Calculation That Convinces Skeptical Teams
Hot take: the cost of bitemporal modeling is usually overstated.
The storage overhead is manageable with columnar compression. The query complexity is manageable with thoughtful schema design. The real cost — the one that breaks teams — is the cultural shift. It's getting everyone to think in terms of "what did we know" instead of "what happened."
The ThoughtSpot guide on slowly changing dimensions correctly identifies the question "how much history?" as foundational. But here's where I disagree with the mainstream approach: don't ask "how much history." Ask "who questions the semantics?"
If your compliance officer asks "why did this number change?" you need bitemporal modeling. If your CFO asks "how many shipments were in transit at noon?" you might not. The difference isn't the amount of history — it's the nature of the questions.
The calculation I run for clients:
Temporal model:
- Storage: 1x
- Query complexity: 1x
- Compliance exposure: HIGH (can't prove what was known)
Bitemporal model:
- Storage: 2.2x (industry average from [this analysis](https://dev.to/sirixdb/slowly-changing-dimensions-and-temporal-databases-58p2))
- Query complexity: 1.5x
- Compliance exposure: NONE
When regulatory fines can reach $1M+ per violation, the extra 120% storage cost becomes negligible.
Why I Now Default to Bitemporal (With Exceptions)
Here's my honest view after building systems for the last decade:
Default to bitemporal modeling. Assume you need both timelines unless you can prove otherwise.
The reason is simple: retrofitting bitemporal modeling onto a production system is exponentially harder than building it in from day one. We've done both at SIVARO. Building fresh is a week of work. Retrofit is a quarter of work, with constant risk of data corruption. The extra design effort at the start is small compared to the hidden costs of rework.
That said, we do make exceptions. Specific cases where temporal-only modeling is sufficient:
- Read-only reporting systems that never need to know what the system knew
- Low-criticality operational dashboards where hard-coded valid time assumptions are acceptable
- Prototype systems that will be replaced within 12 months
But for anything with an external user, a regulatory overlay, or a financial component, bitemporal is non-negotiable.
The Future Is Bitemporal Everything
In 2026, data platforms are moving toward built-in bitemporal support. PostgreSQL's ongoing temporal features research and SQL Server's system-versioned temporal tables are early steps, but the industry is converging on bitemporal as the standard. The rise of event-driven architectures, CDC pipelines, and real-time analytics has forced the issue. Everyone's data arrives out of order. Everyone has late events. Everyone's reporting needs to reflect what was known when.
Bitemporal modeling isn't a nice-to-have anymore. It's table stakes for serious data infrastructure.
FAQ
Q: What's the difference between temporal tables and slowly changing dimensions?
A: Temporal tables automatically track record history via system-versioned tables. SCDs are manual implementations that require ETL logic. Tim Mitchell's breakdown shows how SQL Server temporal tables can replace manual SCD Type 2 implementations, but neither approach handles assertion time.
Q: When should I use bitemporal data modeling?
A: Use it when you need to know what your system knew at any point in time. Financial, healthcare, logistics, and compliance-heavy domains typically require it. For simple analytics, temporal modeling might be enough.
Q: How do you handle out-of-order events in Kafka?
A: Include both stated_at (event time) and received_at (processing time) in your event schema. Track both separately. Don't sort by event time on arrival — use a reconciliation job to handle late events.
Q: Is bitemporal modeling more expensive?
A: Yes, storage and query complexity go up. But if compliance or audit requirements are present, the cost is justified. The industry average storage overhead is around 2.2x.
Q: Can I retrofit bitemporal modeling onto an existing system?
A: You can, but it's significantly harder than building it in from the start. Expect a quarter of work for moderate complexity systems. If you're designing a new system, build bitemporal modeling in from day one.
Q: Does Kafka support bitemporal modeling natively?
A: Kafka supports both event time and processing time. But the bitemporal model must be implemented in the consumer logic, not the broker. Your event schema and consumption logic handle the bitemporal semantics.
Q: What's the difference between valid time and assertion time?
A: Valid time is when an event occurred in reality. Assertion time is when your system first recorded it. Both are critical for accurate historical queries.
Q: Why is temporal-vs-bitemporal modeling such a topic in 2026?
A: Because data architectures have shifted toward event-driven, real-time processing, where data arrives out of order and must be combined with historical snapshots. The gap between reality and system knowledge has become a compliance issue across industries.
Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.