SIVARO
Bitemporal Data Model Example: The Pattern That Saves Your Data Pipeline
We were debugging a customer refund system for a fintech client in 2024. The ledger said a user had been refunded $4,200. The user said they tried to refund the same transaction twice. Both were right. The system had stored the first refund's status as "processing," then updated it to "completed" — and the second attempt read the current state, not the state at the time of the first attempt.
That was a three-week headache. It was also the week I stopped treating bitemporal modeling as a data-warehousing nicety and started treating it as a production requirement.
Bitemporal data model example — the term gets thrown around, but most engineers I meet think it's just "keeping a history table." It's not. It's the discipline of tracking two independent time axes: when something actually happened in the world, and when your system knew about it.
I'm Nishaant Dixit, founder of SIVARO. We build data infrastructure and production AI systems. Over the last seven years, I've watched teams crash their analytics pipelines because they modeled only one dimension of time. I've also fixed enough of them to have strong opinions.
Let me show you what this pattern actually looks like — with code, with trade-offs, and with the mistakes I've made so you don't have to.
What "Bitemporal" Actually Means
Two timelines. That's it.
- Valid time: The time period during which a fact is true in the real world. The transaction happened on March 3, 2026. The customer's address changed effective July 1, 2026.
- Transaction time: The time when your system recorded that fact. You inserted the row at 14:32:07 on March 4, 2026. You corrected the address on July 15, 2026.
Most systems track one. If you're lucky, you track both. If you're doing it right, you can query the answer to two questions that sound the same but are entirely different:
- "What did we know on March 4 about the transaction that occurred March 3?"
- "What is the current record of the transaction that occurred March 3?"
For a simple example, consider a customer table. A row says status = 'active' with valid_from = '2026-01-01' and valid_to = '9999-12-31'. That's uni-temporal — it tracks effective dates. Add recorded_at and you've created a crude audit trail. Combine both, and you have bitemporal.
Look at this explanation from Martin Kleppmann's early work — he framed it as "two dimensions of time, neither of which is reducible to the other." He's right, and I'd add a practitioner's twist: most of your data-quality bugs come from conflating the two.
The Bitemporal Data Model Example You'll Actually Use
Let me give you a concrete schema. I'm using PostgreSQL since it's the most common production database we see at SIVARO. The pattern transfers to any SQL dialect.
sql
CREATE TABLE customer_events (
-- The natural/business key
customer_id UUID NOT NULL,
event_type TEXT NOT NULL, -- 'address_change', 'status_change', etc.
-- Valid time (when the fact is true in the real world)
valid_from TIMESTAMPTZ NOT NULL,
valid_to TIMESTAMPTZ NOT NULL,
-- Transaction time (when we recorded this fact)
recorded_from TIMESTAMPTZ NOT NULL,
recorded_to TIMESTAMPTZ NOT NULL DEFAULT 'infinity',
-- The actual payload
payload JSONB NOT NULL,
PRIMARY KEY (customer_id, event_type, valid_from, recorded_from)
);
The trick is the composite primary key. Four fields. That's the bitemporal part — valid_from tells you the real-world effective date, and recorded_from tells you when your system became aware.
To get the current view of all customer events valid today:
sql
SELECT *
FROM customer_events
WHERE valid_from <= NOW()
AND valid_to > NOW()
AND recorded_from <= NOW()
AND recorded_to = 'infinity';
To get the historical view as of a specific time — say, "what did we know on June 1, 2026, about events valid on April 15, 2026":
sql
SELECT *
FROM customer_events
WHERE valid_from <= '2026-04-15'
AND valid_to > '2026-04-15'
AND recorded_from <= '2026-06-01'
AND recorded_to > '2026-06-01';
This is not academic. At SIVARO, we built an audit system for a regulated commodities exchange in late 2025. Regulators asked, "Show us every correction to a trade's price, and what the price was at each point in time." The bitemporal model was the only way to answer without reconstructing from application logs — which nobody ever fully captures.
Why Most Teams Get This Wrong
Most people think bitemporal modeling in data warehousing is about storage. It's not. It's about semantics.
I've seen three patterns of failure:
Failure #1: The "just add a timestamp" fallback. Teams add a created_at and updated_at column and call it history. But updated_at overwrites, not preserves. You know when you changed the row, but not what the previous state was. You lose the ability to answer "what did we think was true then?"
Failure #2: The "we'll just use a transaction log" assumption. Some teams say, "We don't need bitemporal — we have CDC (Change Data Capture) in Kafka." I've tested this at three companies between 2023 and 2025. It doesn't work for point-in-time queries because replaying a log to reconstruct state is slow, error-prone, and hell to debug. The log is a mechanism, not a model. If your analytics queries need historical state, you want the state materialized — not a pile of deltas to replay.
Failure #3: The "temporal magic" anti-pattern. I once worked with a startup in 2024 that used a "temporal" library (think Temporal.io) for orchestration and assumed that gave them bitemporal data. It doesn't. That library handles workflow state, not business facts. They had perfect orchestration traceability and zero business data history. Two different problems.
The real reason teams fail? They treat this as an ETL problem instead of a modeling problem. You can't bolt temporal awareness onto a schema that wasn't designed for it.
How to Build Bitemporal Data the Right Way
Start with a few rules, hard-won from practice:
Rule 1: Never update. Insert.
This is non-negotiable. Every time a fact changes, you insert a new row with a new recorded_from and close the previous row's recorded_to.
sql
-- Example: customer address correction
-- Original record (valid from Jan 1, 2026):
-- customer_id = 'abc'
-- valid_from = '2026-01-01', valid_to = 'infinity'
-- recorded_from = '2026-01-01', recorded_to = 'infinity'
-- payload = {'address': '123 Old St'}
-- Correction arrives July 15, 2026; the address was actually wrong since June 1.
-- Step 1: close the original transaction-time row
UPDATE customer_events
SET recorded_to = NOW()
WHERE customer_id = 'abc'
AND valid_from = '2026-01-01'
AND recorded_to = 'infinity';
-- Step 2: insert the corrected fact
INSERT INTO customer_events
(customer_id, event_type, valid_from, valid_to, recorded_from, recorded_to, payload)
VALUES
('abc', 'address_change', '2026-01-01', 'infinity', NOW(), 'infinity',
'{"address": "123 Old St, Apt 2"}');
That's it. You now have both records. The original row (with recorded_to = NOW()) says "until July 15, we thought the address was 123 Old St." The new row says "as of July 15, we know the address is 123 Old St, Apt 2."
Rule 2: Define your "as of" queries explicitly
Most reporting doesn't need the full complexity. Create views that make the intent clear.
sql
CREATE VIEW customer_events_current AS
SELECT *
FROM customer_events
WHERE recorded_to = 'infinity'
AND valid_from <= NOW()
AND valid_to > NOW();
CREATE VIEW customer_events_as_of AS
SELECT *
FROM customer_events
WHERE recorded_from <= $1 -- the "as of transaction time" parameter
AND recorded_to > $1
AND valid_from <= NOW()
AND valid_to > NOW();
I can't tell you how many production incidents I've debugged where the "current" view was wrong because someone forgot to filter on recorded_to = 'infinity'. A view forces the discipline.
Rule 3: Use generated columns or triggers for valid_to
In PostgreSQL 18 — which is current as of late 2026 — you can use generated columns to derive time windows. We tested this pattern internally at SIVARO in mid-2026 and it works cleanly:
sql
CREATE TABLE orders_bitemporal (
order_id UUID PRIMARY KEY,
order_valid_from TIMESTAMPTZ NOT NULL,
order_valid_to TIMESTAMPTZ NOT NULL,
recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
recorded_to TIMESTAMPTZ NOT NULL DEFAULT 'infinity',
status TEXT NOT NULL,
-- generated column for convenience
is_current_record BOOLEAN GENERATED ALWAYS AS (recorded_to = 'infinity') STORED
);
CREATE INDEX idx_orders_valid ON orders_bitemporal (order_valid_from, order_valid_to);
CREATE INDEX idx_orders_recorded ON orders_bitemporal (recorded_at, recorded_to);
Generates the flag, no application logic needed. The index strategy matters — if your query patterns are heavy on "as of valid time" filters, put that index first.
The Real World: What's Changed by 2026
Here's what I've seen shift in the last 18 months.
First, AI/ML pipelines demand bitemporal data. I'm not talking about training models on historical features — I'm talking about production AI systems that need to know what the system believed at the time of a decision. In late 2025, we built a fraud-detection scoring service for a payments company. The model needed a feature that answered "what did we know about this user's velocity on January 3 at 11:42 AM?" Without bitemporal storage, the feature would have been garbage — because the model trained on data that had been corrected later. The whole point of production AI is you need the state of the world as it was, not as it is now.
Second, regulatory pressure is real. The EU's AI Act and GDPR's right to explanation both require auditable decision trails. Bitemporal modeling in data warehousing is the technical backbone of that audit trail. If you can't answer "what did the system know and when did it know it," you can't explain an AI decision in court. I've had two regulators in 2026 ask about this exact capability.
Third, statistical and streaming pipelines are converging. Apache Flink and Kafka Streams are great for processing, but their stateful stores are not bitemporal. We tested using Kafka Streams' local state with a change-log topic to reconstruct history — it's messy. The storage layer has to be explicitly temporal or you're rebuilding it yourself.
The Trade-offs Nobody Tells You About
I'll be honest. Bitemporal is not free. There are costs.
Storage bloat is the obvious one. Every correction doubles your rows. I saw a client's orders table go from 2 million rows to 14 million in six months because a legacy system was firing off corrections. We had to design a compaction strategy. Not every fact needs to be bitemporal — only the ones where "state as of" matters. I'm pragmatic: I'll happily keep a transaction's status in a simple, mutable table if the business doesn't need historical reconstruction.
Query complexity is the second cost. Your AD-hoc analytics get harder. Analysts curse you when they forget to add the recorded_to = 'infinity' filter and get duplicate facts. You mitigate this by exposing views and teaching proper query patterns.
Application complexity is third. If you're brave (or reckless, depends on your mood), you put the temporal logic in the app layer. That adds code, tests, and failure modes. If you're smart, you push it into the database with triggers and constraints.
There's also a subtle one: bitemporal is a discipline, not a feature. Every insert, every update, every import has to be done with awareness of both timelines. It takes a culture shift in the data team.
Practical Implementation Patterns
Pattern A: The "clip" pattern for corrections
When you need to correct a valid-time fact (i.e., the fact was wrong from the start), clip the valid window and insert the corrected row:
sql
-- Correct the price of order 'o1' from $100 to $120, effective June 1, 2026
-- (we previously thought $100 was correct from June 1 to June 5)
-- Step 1: close the valid-time window of the false record
UPDATE orders_bitemporal
SET order_valid_to = '2026-06-01'
WHERE order_id = 'o1'
AND order_valid_from = '2026-06-01'
AND order_valid_to = '2026-06-05'
AND recorded_at <= NOW()
AND recorded_to = 'infinity';
-- Step 2: insert the corrected fact
INSERT INTO orders_bitemporal
(order_id, order_valid_from, order_valid_to, recorded_at, recorded_to, status, price)
VALUES
('o1', '2026-06-01', '2026-06-05', NOW(), 'infinity', 'completed', 120.00);
Pattern B: The "merge" pattern for late-arriving facts
For data that arrives late, you add a new validity window rather than overwrite:
sql
-- A transaction that occurred on June 10 is discovered on June 15
INSERT INTO orders_bitemporal
(order_id, order_valid_from, order_valid_to, recorded_at, recorded_to, status, price)
VALUES
('o2', '2026-06-10', '2026-06-10', '2026-06-15', 'infinity', 'completed', 55.00);
The query "what was our revenue on June 10?" now has two answers: "as known on June 11" (missing the late fact) vs. "as known today" (includes the late fact). Both are valid. Both are different numbers.
Pattern C: The "snapshot" pattern for immutable facts
For genuinely immutable facts (a transaction ID, a user's birth date), skip the temporal complexity. Just use a regular table. We tried temporal modeling for signature hashes in 2025 — waste of time and storage.
Bitemporal and Dimensional Modeling: A Note on Kimball
If you're doing classic dimensional modeling, you'll find bitemporal fits alongside SCD (Slowly Changing Dimensions) Type 2. Most warehouses use SCD Type 2 for dimension history but rarely apply it to fact tables. I'll say this: SCD Type 2 is almost bitemporal, but the transaction-time axis is usually absent. You know the dimension changed, but you don't know when you learned about the change. If that matters (regulators, auditors, AI features), extend the pattern.
We've built this on PostgreSQL, Snowflake, and BigQuery. Snowflake's VALIDATE and TIME_TRAVEL functions help but they're not substitutes — they keep the physical data around, not the semantic history. You still need explicit modeling.
FAQ: Bitemporal Data Model Questions, Answered
Q: Is bitemporal modeling in data warehousing different from in OLTP?
Yes. OLTP systems need bitemporal for audit and correction, but they're often bounded by write throughput. In warehousing, you typically build bitemporal as a storage and query pattern, batch-loaded. The principles are identical — the implementation differs.
Q: Can I use a JSONB column for temporal data instead of structured rows?
You can, but you'll pay for it in query complexity and performance. At SIVARO, we tested JSONB-based temporal storage for a healthcare client in 2025. Fast to write, painful to query. For any dashboard or report, you'll end up unnesting — and you'll lose the ability to index on the temporal keys efficiently. Better to model it as proper columns.
Q: What about events versus facts?
Bitemporal modeling treats facts as "events" — they have a valid time and a transaction time. If your events are immutable (sensor readings), you only need valid time. If they're mutable (order status), you need both. Always default to asking: "Can this fact change after I record it?" If yes, bitemporal is the answer.
Q: How do you handle valid_to = 'infinity' in code?
It's a practical convention. In PostgreSQL, use 'infinity'::timestamptz. In other dialects, use '9999-12-31'. I've seen teams use NULL for open-ended validity — it works but makes the WHERE clause clunkier. Pick a convention and enforce it with a CHECK constraint:
sql
ALTER TABLE customer_events
ADD CONSTRAINT check_valid_window CHECK (valid_to > valid_from);
Q: What's the easiest way to introduce bitemporal to an existing system?
Don't rewrite. Create a new table with the bitemporal pattern and backfill it with current state. Use valid_from = '1970-01-01' for legacy data and recorded_from = NOW() for the initial load. Then add triggers to capture changes going forward. We used this approach for the fintech client above — took two weeks, didn't break existing queries.
Q: Is Temporal.io relevant to bitemporal data?
Confusingly named, different concept. Temporal.io is a workflow orchestration system. Bitemporal data is a data modeling technique. In 2024, I saw a company conflate the two and get burned — their workflow histories were pristine, their business data was still inconsistent. Use the right tool for the right job.
Q: How does bitemporal modeling handle deletes?
You never hard-delete in a bitemporal system. You close the transaction-time window by setting recorded_to = NOW(). That makes the record disappear from "current" queries but preserves it in "as-of" queries. This is the only way to maintain auditability. Product teams often struggle here — they want soft delete semantics, but bitemporal gives you "logical delete" plus a full trail.
Q: What are common pitfalls with valid-time and transaction-time joins?
Mostly, people forget to qualify both axes. If you join two bitemporal tables, you have to choose a point in time for both dimensions. The correct join is:
sql
SELECT *
FROM customer_events ce
JOIN orders_bitemporal ob
ON ce.customer_id = ob.customer_id
AND ob.order_valid_from < ce.valid_to
AND ob.order_valid_to > ce.valid_from
AND ob.recorded_from <= ce.recorded_from
AND ob.recorded_to > ce.recorded_from;
This is a temporal join — it's complex, but it's the only way to get semantic correctness.
The 10-Minute Test
I have a heuristic I use with every client. I ask: "Pick the three most important metrics in your business. For each one, can you answer: 'What was the value last Tuesday as known last Tuesday?'"
If they can't answer it with a single query, they're not bitemporal. They're faking it with logs or snapshots.
You might think this is overkill. For most startups? It is. A CRM for a 50-person company doesn't need bitemporal modeling. But if your customers are regulated institutions, if your ML model decides whether someone gets a loan, if your revenue numbers go into an investor update — the "as known then" question becomes existential.
I didn't design the pattern. It's half a century old, from the database research community — R. T. Snodgrass had the foundational paper in 1987, and it's still canonical. Read his work if you want the theory. But I've spent seven years applying it at SIVARO, and I can tell you: the principles translate directly to modern data infrastructure. The tech stacks change every three years, but the question "when did the world change, and when did we know it" is permanent.
I keep a bitemporal model example taped to my monitor — the customer_events table above. It's a reminder that every system is built on assumptions about time, and most systems get that assumption wrong.
We're about to see a wave of startups claiming "real-time everything." Real-time means nothing if you can't rewind the tape. Ask yourself the 10-minute test. If you can't answer, start modeling.
Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.