Bitemporal vs Unitemporal Data Models: Field Notes
Last spring I sat in a war room with a payments company in Singapore. Their compliance team needed one answer: as of end of Q2, what did we believe this merchant's risk tier was? The data warehouse said one thing. The operational database said another. The Kafka topic replay said a third. Three systems, three truths.
That's a temporal data problem. Not a data quality problem, not a governance problem. Those systems stored facts without storing time properly. Specifically, they didn't distinguish between when a fact was true in reality and when the system knew about it. That distinction is the heart of bitemporal vs unitemporal data models.
Here's what you'll get from this: a clear breakdown of the two approaches, the exact trade-offs I've hit running production data systems, and four implementation patterns that actually work. No theory. Just what I've seen fail and what I've seen hold up.
Time Is the Hardest Thing to Model
Most data models treat a row as a single point-in-time assertion. Customer 1042 has risk tier B. That's it. But two different questions break that simple model:
- What did we think the risk tier was on June 30?
- What do we now know the risk tier actually was on June 30?
Same fact. Two different timelines. Temporal Table Usage Scenarios from Microsoft lays out these scenarios in a practical way, but it's easy to misread it. Their temporal tables handle one timeline only. The other timeline requires your own design.
Let me name the two timelines, because everything downstream depends on this.
Valid time is when a fact is true in the real world. The merchant was risk tier B from March 1 until May 14. That's valid time.
Transaction time is when your system recorded that fact. You didn't know the merchant was tier B until March 5, because the underwriter was slow. You didn't discover the July 1 downgrade until July 9, because the batch job had a retry loop.
Unitemporal models track one of these. Bitemporal tracks both. That sounds simple. The consequences aren't.
The industry calls this "slowly changing dimensions," and the ThoughtSpot guide on SCDs does a decent job covering the classic Type 0 through Type 6 ladder. But the term itself is misleading. It assumes change is slow. In streaming systems, change is constant. The TDWI piece on temporal data modeling from a few months back makes this point well: temporal modeling isn't a niche feature, it's the foundation of any system that needs to answer "what did we know and when did we know it."
Unitemporal Means You Pick One Lie
Unitemporal models are the industry default. Almost every warehouse you've worked with is unitemporal, whether it admits it or not.
A classic SCD Type 2 dimension is unitemporal valid time. You store valid_from and valid_to columns. When a customer's address changes, you close the old row and open a new one. The dbt snapshot approach and the patterns Tim Mitchell describes for SQL Server temporal tables both follow this shape. It answers "what did we think the customer's address was on January 15?"
What it can't answer: "when did we learn the address changed, and was the old address ever correct?" If you backfill a correction, you rewrite history. Your valid_from dates collapse, and your audit trail lies.
SQL Server's system-versioned temporal tables are the flip side. They track transaction time automatically. Every UPDATE creates a history row with the system timestamp. But they don't track valid time at all. If you need to record that a change is effective retroactively, SQL Server temporal tables won't do it for you. The Microsoft usage scenarios doc shows this clearly: it's great for accidental data loss prevention and auditing, but it's not a business calendar.
Both of these are unitemporal. They pick one timeline and stick to it. That's fine, until the day you need the other timeline and you have to reconstruct it from log files.
Bitemporal vs Unitemporal Data Models: The Real Difference
Here's the shortest version: unitemporal handles "what changed." Bitemporal handles "what changed, and when did we know it changed."
Let me give you a concrete failure. A healthcare client in 2024 had a claims table with effective_date and termination_date. They were proud of it. Then a payer sent a retroactive adjustment: claim C-88231 was approved for a different amount, effective two months ago. The team updated the row in place. The effective_date stayed the same, the amount changed, and every historical report that had already been run became unreconcilable. Finance caught it three weeks later, after an audit report contradicted a monthly close.
That's a unitemporal valid-time model meeting retroactive correction. It breaks. Not because the team was sloppy. Because the model can't represent "we learned on July 12 that the June 28 fact was wrong."
The SirixDB article on SCDs and temporal databases makes a sharp observation: most SCD implementations are really just temporal databases with the transaction clock amputated. You keep the valid-time columns, you lose the system-time columns, and then you're surprised when retroactive changes corrupt your history.
Bitemporal keeps both clocks running.
When I implemented a bitemporal model for a logistics client, every row had four columns: valid_from, valid_to, system_from, system_to. Updates never touched existing rows. They inserted new rows with the same valid_from/valid_to range but a new system_from. Old rows kept their system_to date. Two queries later, you could answer both questions:
sql
-- What did we think the shipment status was on Monday?
SELECT status
FROM shipment_history
WHERE shipment_id = 20456
AND system_from <= '2026-07-20'
AND system_to > '2026-07-20'
AND valid_from <= '2026-07-20'
AND valid_to > '2026-07-20';
-- What was the actual status on Monday, given what we know today?
SELECT status
FROM shipment_history
WHERE shipment_id = 20456
AND valid_from <= '2026-07-20'
AND valid_to > '2026-07-20'
ORDER BY system_from DESC
LIMIT 1;
The first query is "as of" with transaction time. The second is "as of" with valid time, using current knowledge. Same table. Two answers. Both correct.
That's the core difference, and it's why I push teams toward bitemporal the moment retroactive changes are a real scenario. Fraud detection, insurance claims, risk tiers, tax tables, pricing, identity resolution. These are not "slowly changing dimensions." They're constantly corrected dimensions.
When Unitemporal Is the Right Call
Let me push back on myself, because I've seen bitemporal implementations that were pure masochism.
If your data is append-only by nature and corrections don't happen, unitemporal valid time is plenty. Clickstream events, IoT sensor readings, order placement records. These facts are immutable. Nobody retroactively changes a sensor reading because the sensor was wrong. They insert a new reading. The TDWI overview frames this well: bitemporal adds complexity, and complexity has a maintenance cost that shows up in every query, every join, every dashboard.
The signal I look for before recommending unitemporal:
- No business process can retroactively amend facts
- Auditors don't ask "what did we know when"
- The source systems are append-only logs, not mutable operational tables
If those three hold, unitemporal valid time with effective dates is enough. I built exactly that for a fintech's transaction ledger. Every row had event_time and an as_of watermark. Queries were simple. Joins were simple. No system-time columns polluting the schema. It worked because a transaction, once settled, is never amended. It's reversed, but reversal is a new transaction.
That's the key insight. Unitemporal works when corrections are represented as new facts, not edits to old ones.
When You Can't Avoid Bitemporal
You need bitemporal when two conditions collide:
- Facts are mutable in the source system
- Auditors or regulators can ask what you knew at a past moment
Insurance is the textbook case. A policy's coverage terms can be amended retroactively. A claims adjuster can change a loss date after investigation. And state regulators absolutely will ask for a point-in-time view of every policy as it existed on their books.
The healthcare client I mentioned earlier ended up going bitemporal after that retroactive adjustment incident. Their schema looked like this:
sql
CREATE TABLE claim_facts (
claim_id UUID NOT NULL,
version_id UUID NOT NULL,
approved_amount NUMERIC(12,2) NOT NULL,
valid_from DATE NOT NULL,
valid_to DATE NOT NULL,
system_from TIMESTAMPTZ NOT NULL DEFAULT now(),
system_to TIMESTAMPTZ,
PRIMARY KEY (claim_id, version_id)
);
Every correction inserts a new version row. The system_to on the old version gets set to the moment the correction arrived. The new version carries the full valid-time range, even if that range overlaps with previous versions.
One pattern I've learned the hard way: don't use valid_to = 'infinity' if your warehouse's partitioning scheme chokes on it. Use valid_to = '9999-12-31' instead. Same for system_to. NULL works too, but some BI tools handle explicit dates better. Test it with your actual toolchain before you commit.
The query patterns are the second-hardest part. You have to version every fact and also version the relationships between facts. I've seen three separate bitemporal tables that were each correct in isolation, but the join between them was ambiguous because the version boundaries didn't line up. The fix was a bitemporal link table with its own valid and system times. Annoying to build. Necessary if you care about correctness.
Putting It In Code
Let me give you four implementations I've actually used, from simplest to most involved.
SQL Server system-versioned temporal tables are the easiest unitemporal transaction-time option. Zero application code.
sql
CREATE TABLE dbo.customer_tier (
customer_id INT NOT NULL PRIMARY KEY CLUSTERED,
risk_tier VARCHAR(20) NOT NULL,
valid_from DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
valid_to DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.customer_tier_history));
Query it with FOR SYSTEM_TIME AS OF:
sql
SELECT customer_id, risk_tier
FROM dbo.customer_tier
FOR SYSTEM_TIME AS OF '2026-06-30T23:59:59'
WHERE customer_id = 1042;
This is transaction time only. Read the SQL Server temporal usage scenarios before you pretend it's bitemporal. It isn't.
dbt snapshots give you a warehouse-native unitemporal pattern for mutable source tables.
yaml
snapshots:
- name: customer_risk_snapshot
relation: source('raw', 'customer_risk')
unique_key: customer_id
strategy: timestamp
updated_at: updated_at
dbt maintains dbt_valid_from and dbt_valid_to. It's valid-time only, and it's as good as your updated_at column. If your source system has sloppy timestamps, your snapshot history is sloppy too.
PostgreSQL without extensions is the honest path to bitemporal. You manage both clocks yourself.
sql
CREATE TABLE customer_risk (
customer_id INT NOT NULL,
risk_tier VARCHAR(20) NOT NULL,
valid_from DATE NOT NULL,
valid_to DATE NOT NULL,
system_from TIMESTAMPTZ NOT NULL DEFAULT now(),
system_to TIMESTAMPTZ,
PRIMARY KEY (customer_id, valid_from, system_from)
);
Every update is an insert plus a closure of the old row inside a transaction. Screw up the closure and you get overlapping rows. This is where most teams suffer. I use a trigger or a stored procedure for the closure logic so it can't be bypassed by an ad-hoc UPDATE.
Kafka Streams for event-time processing is the final pattern. The mapping between Kafka's timestamps and temporal models is direct: Kafka's event time maps to valid time, and Kafka's processing time maps to transaction time. The Kafka Streams documentation and community discussions around timestamp extractors keep circling this connection, and once you see it, it changes how you build stream processors.
java
StreamsBuilder builder = new StreamsBuilder();
builder.stream(
"customer-events",
Consumed.with(
Serdes.String(),
new JsonSerde<>(CustomerEvent.class)
).withTimestampExtractor((record, previousTimestamp) ->
Instant.parse(record.value().eventTime).toEpochMilli()
)
);
Processing time vs event time in Kafka streams is not a quirk of Kafka. It's the same dual-clock problem under a different name. When you build a stream processor that joins events on event time, you're building a valid-time join. When you reprocess a topic from the beginning to reconstruct what a downstream system saw, you're replaying transaction time.
What Kafka Streams Taught Me About Time
I spent most of 2025 building a fraud detection pipeline for a payments processor. Kafka topics, Flink jobs, the usual real-time stack. The first version used processing time for everything. Windowed aggregations over five-minute windows looked fine in staging. In production, a GC pause on a broker delayed a batch of events by ninety seconds. The window closed, the events arrived late, and the fraud model missed a sequence of transactions.
We switched to event time with a timestamp extractor and watermarking. The difference wasn't subtle.
Processing time is the system's clock. Event time is the real world's clock. Every team building on Kafka Streams has to make that call, and most teams I meet don't realize they're making a temporal data modeling decision. The TDWI article makes this exact argument: temporal modeling is not a database specialty anymore. It's a streaming concern.
The mapping holds up under pressure. Your Kafka event time is valid time. Your consumer offset and processing timestamp is transaction time. If your stream processor needs to handle out-of-order events, you're experiencing the same pain as a data warehouse handling retroactive corrections. Same problem. Same solution: keep both clocks.
Temporal Database vs Relational Database: The Industry Mistake
You'll still hear people talk about "temporal databases" as a separate product category. There were dedicated temporal DBMS research prototypes in the 1990s. Oracle and Teradata added temporal features. None of them changed the world.
The reason is practical: regular relational databases already do the job if you model correctly. The SirixDB analysis of SCDs and temporal databases points out that the relational model never prevented bitemporal design. It just didn't force it. As a result, the burden falls on the data modeler, which is why so many production systems are accidentally unitemporal.
Don't go shopping for a temporal database. Go shopping for a relational database with temporal features you can live with. SQL Server has system-versioned tables. Postgres has range types and exclusion constraints. Snowflake and Databricks have time travel and change tracking. All of them work, if you think in terms of clocks before you think in terms of tables.
The question I ask clients is not "do you support temporal data?" It's "do your modelers understand valid time and transaction time?" That's the bottleneck. Not the storage engine.
FAQ
What's the difference between bitemporal and unitemporal data models?
Unitemporal tracks one time dimension. Usually valid time or transaction time. Bitemporal tracks both. Valid time is when a fact is true in reality. Transaction time is when the system recorded it.
Is SQL Server temporal tables bitemporal?
No. SQL Server system-versioned temporal tables track transaction time only. The GENERATED ALWAYS AS ROW START and ROW END columns are system-generated. If you need valid time, you maintain those columns yourself.
Does PostgreSQL support bitemporal out of the box?
No, but you don't need a plugin. You can implement bitemporal with two timestamp/date columns for valid time, two for system time, and an application-level or trigger-based update pattern. Postgres range types and exclusion constraints help prevent overlapping valid-time ranges.
When should I use valid time vs transaction time?
Use valid time for business reporting: sales by month, policy coverage, pricing eligibility. Use transaction time for audit and compliance: what did we know and when did we know it. Use both when both those questions matter for the same table.
How does Kafka Streams event time relate to temporal databases?
Event time in Kafka is valid time. Processing time is transaction time. When you choose a timestamp extractor, you're choosing which clock your stream processor uses. Processing time vs event time in Kafka streams is the same dual-clock decision at the heart of bitemporal modeling.
Is a dbt snapshot a temporal table?
It's a unitemporal valid-time model. dbt snapshots maintain dbt_valid_from and dbt_valid_to for changed rows. They don't track when the snapshot process learned about the change with a separate clock.
Can I implement bitemporal in a relational database?
Yes. The pattern is a primary key that includes both a valid-time identifier and a system-time identifier, closure of old rows on update, and queries that filter on both time ranges. I've done it in Postgres and SQL Server. It works fine.
Stop Storing Facts, Store Versions
The bitemporal vs unitemporal