How Does Temporal Work in Data Engineering?
I spent three days in 2024 chasing a ghost in our event pipeline. The marketing team at a fintech client kept asking why their dashboard showed a customer churning twice. We dug through logs, checked the ingestion code, even blamed the database. It wasn't a bug. It was a time problem. We had overwritten a fact instead of preserving a history. That's when I stopped treating temporal data modeling like a database feature and started treating it like the core of the engineering problem.
Temporal data modeling is the practice of tracking how data changes over time, so you can answer questions about what the data was, not just what it is. It separates the "current truth" from the "historical truth" and forces you to decide which one your systems actually need.
In this guide, I'll show you how temporal works in data engineering, how to pick between temporal and wall-clock time, and why bitemporal data models are the only way to keep your sanity when the source system lies to you.
The State vs. Event Distinction Is Everything
Most people think temporal data is just about timestamps. Wrong. It's about the difference between state and events.
A state is a snapshot. Your user's profile. Their subscription tier. The price of a product. States get overwritten. Events, on the other hand, are immutable facts. "User upgraded from Basic to Pro at 14:32:01." "Price changed from $10 to $12 on Tuesday."
When you model temporal data, you're choosing how to handle the transition between states. If you just keep the current state, you lose the history. If you keep every event, you can reconstruct any state — but you have to do the math every time you query.
The SQL Server team has a great breakdown of Temporal Table Usage Scenarios that shows exactly why this matters. They walk through scenarios like auditing changes, point-in-time analysis, and repairing corrupted data. The key insight is that temporal tables give you both the current state and the history without forcing you to choose upfront.
Let me show you what this looks like in practice. A system-versioned temporal table in SQL Server:
sql
CREATE TABLE dbo.Customers
(
CustomerId INT PRIMARY KEY,
Tier VARCHAR(20),
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);
Every update creates a new version. The old version gets its ValidTo set to the current time, and the new version gets its ValidFrom set to the same time. You get a complete audit trail for free.
But here's the thing. That's the easy part. The hard part is deciding which clock to use.
How Does Temporal Work in Data Engineering? It Depends on Your State
The fundamental question in temporal data engineering is this: what clock are you trusting?
There are two clocks in every system. The first is transaction time, also called system time or load time. This is when your database recorded the change. It's wall-clock time at the moment of insertion. The second is valid time, also called application time or business time. This is when the change is true in the real world.
Tim Mitchell covers this distinction well in his post on Using Temporal Tables for Slowly Changing Dimensions. He notes that temporal tables in SQL Server track transaction time, not valid time. That's fine for auditing, but it's a trap if you're trying to do business analysis.
Here's the scenario that breaks people. A customer changes their address on June 15. The source system sends that change to your data warehouse on June 16 because of a batch job. If you're using transaction time, the change appears as June 16. But the customer actually moved on June 15.
If you're reporting on "customers who moved in June," you need valid time. If you're reporting on "what data did we have as of June 16," you need transaction time.
The TDWI article on What Is Temporal Data Modeling? makes a crucial point: most organizations default to transaction time because it's what the database gives you automatically. But that's backwards. You should be modeling valid time because that's what the business actually cares about.
I've seen this play out in a supply chain system we built for a logistics company in 2025. They had a shipment that was delayed by three days due to a port strike. Their operational database updated the delivery date in real time. But their analytics warehouse loaded data nightly. Every report for a week showed the old delivery date, and every operations person lost faith in the data.
The fix wasn't a faster pipeline. It was a bitemporal model.
Bitemporal vs Unitemporal Data: The Tradeoff
Let me be direct. Most data warehouses are unitemporal. They track one time dimension, usually transaction time. This is simpler, faster, and easier to reason about. It's also wrong for a surprisingly large number of use cases.
Bitemporal data tracks both valid time and transaction time. This gives you four combinations:
- You know what the data is (current state)
- You know what the data was (historical state)
- You know when you learned about a change (transaction time)
- You know when the change occurred in the real world (valid time)
The dev.to article on Slowly Changing Dimensions and Temporal Databases has a great example. Imagine a customer service rep corrects a customer's phone number. The correction is valid time — the customer's phone number changed at some point in the past. But the correction is also transaction time — the rep made the change at a specific moment.
If you only track transaction time, you lose the fact that the phone number was wrong for a week before the rep corrected it. If you only track valid time, you can't answer "what did we think the phone number was on Tuesday?"
Here's what a bitemporal table looks like:
sql
CREATE TABLE dbo.Customers_Bitemporal
(
CustomerId INT NOT NULL,
Tier VARCHAR(20) NOT NULL,
ValidFrom DATETIME2 NOT NULL, -- Business time
ValidTo DATETIME2 NOT NULL, -- Business time
SystemFrom DATETIME2 NOT NULL, -- System time
SystemTo DATETIME2 NOT NULL, -- System time
PRIMARY KEY (CustomerId, ValidFrom, SystemFrom)
);
Every change creates a new row with the current system time. If the source system sends a correction to a past valid time, you insert a new row with the updated valid time range. You never update the old row's business time.
Is this worth the complexity? Not always. But when the source system makes mistakes, bitemporal data is the only way to reconcile what happened with what you recorded.
Here's a rule of thumb from the systems I've built: if your data feeds regulatory reporting, legal disputes, or customer-facing financial records, go bitemporal. If you're building a recommendation engine, save yourself the pain and stay unitemporal.
Slowly Changing Dimensions: The Operational Reality
The concept of slowly changing dimensions (SCD) has been around since Ralph Kimball. It's how data warehouses have historically handled temporal data. There are six types, but you'll mostly encounter Type 1 and Type 2.
Type 1 overwrites the old value. Simple. Fast. No history. Type 2 keeps history by adding a new row with start and end dates. The ThoughtSpot guide to Slowly Changing Dimensions has a clear table of all the types Tee says "Type 2 SCDs are the most common approach to handling historical data."
We used Type 2 extensively in a customer 360 project for a retail bank in 2024. Every time a customer changed their address or their risk profile, we inserted a new row and closed the old one. The query to get the current address was a filter on ValidTo = '9999-12-31'. The query to get the address as of a specific date was a range join.
sql
SELECT c.CustomerId, c.Address, c.ValidFrom, c.ValidTo
FROM Customer_History c
WHERE c.CustomerId = 12345
AND '2024-06-01' BETWEEN c.ValidFrom AND c.ValidTo;
This worked beautifully for the "as of" queries. It was a disaster for the "what changed" queries. Every address change created a new row. Every risk score recalculation created a new row. The history table grew by millions of rows a day, and every query needed to filter by the latest version.
Tim Mitchell's post on Using Temporal Tables for Slowly Changing Dimensions explains why this pattern is so common: it's simple to implement, it's easy to understand, and it handles the 80% case. The remaining 20% — the corrections, the backdated changes, the source system mistakes — is where it falls apart.
How to Choose Temporal vs Wall Clock Time
This is the question I get asked most by engineering teams. Should we use event time (the timestamp on the event) or processing time (the timestamp when we processed the event)?
My answer is usually: both. But if you can only pick one, pick event time.
Here's why. Processing time is what your system records. It's deterministic assured. But it doesn't reflect reality. Event time is what the source system claims happened. It reflects the business reality, but it can be wrong, delayed, or out of order.
I built a fraud detection system in 2023 for a payment company that processed 200,000 events per second. The data arrived in Kafka with producer timestamps. Our first version used processing time for all aggregations. It worked in production. And then a network partition caused a five-minute backlog, and every fraud alert for that window was wrong because the time window shifted.
We switched to event time for the core analytics and kept processing time for operational monitoring. This is the standard approach in stream processing — you've seen it in Flink and Spark Structured Streaming — but it has a huge implication for temporal data modeling. You can't just track the event timestamp. You have to track both the event timestamp and the processing timestamp, because you need to know when you learned about the event.
This is the exact same problem as bitemporal data, just in streaming form. The event time is valid time. The processing time is transaction time. And you need both if you want to handle out-of-order data gracefully.
The Time Join Problem Nobody Warns You About
Let me talk about the hardest practical problem in temporal data engineering: joins.
If you have a fact table with event timestamps and a dimension table with valid time ranges, how do you join them? The naive approach is to join on the exact timestamp. That fails because the dimension might not have a row that exactly matches the event time.
The correct approach is a range join. You join the fact to the dimension where the event time falls between the dimension's valid start and valid end.
sql
SELECT f.EventId, f.EventTime, d.Attribute
FROM Facts f
JOIN Dimension_History d
ON f.DimensionKey = d.DimensionKey
AND f.EventTime >= d.ValidFrom
AND f.EventTime < d.ValidTo;
This query is correct, but it's also brutally expensive on large tables. The range join can't use a simple hash join because the join condition isn't equality. You end up with a nested loop join or a sort-merge join, which is slow on billions of rows.
At SIVARO, we solved this for a telecom client in 2025 by pre-computing the temporal join. Every time a dimension changes, we re-write the affected facts with the dimension attributes. This is technically denormalization, and it feels wrong. But it turned a 40-second query into a 200-millisecond query.
The lesson: temporal data modeling is not just about schema design. It's about query performance. The correct model is the one that answers your questions fast enough.
The Storage Problem: History Is Expensive
Every temporal model multiplies your storage by the number of time versions you keep. A Type 2 SCD can easily create 100 rows for a single customer over a year. A bitemporal model can create twice that.
This is the trade-off nobody talks about. Temporal data is valuable, but it's also expensive. You have to decide how much history you actually need.
We worked with a healthcare company in 2024 that had to keep seven years of claim history for regulatory reasons. Their warehouse was 80% temporal data and 20% everything else. We solved this with tiered storage: hot data in the warehouse, warm data in Parquet files on S3, cold data in compressed archives.
The key insight is that not all time versions are equally valuable. The recent history is queried constantly. The old history is rarely accessed. You can archive the old history without losing the ability to reconstruct it.
The TDWI article on What Is Temporal Data Modeling? makes the point that temporal data models need to be designed with the entire lifecycle in mind, not just the schema. That's exactly right. Storage costs, query patterns, and retention policies are all part of the model.
Failure Modes: When Temporal Data Goes Wrong
I've seen five common failure modes in temporal systems. Here they are.
Clock skew. Your source system's clock is ahead of your warehouse's clock. A change gets recorded with a future timestamp, and it disappears from "as of now" queries until the wall clock catches up. Solution: use a monotonic timestamp from your database, not from the application server.
Late-arriving data. The source system sends a change that happened yesterday, but you've already loaded today's snapshot. If you're using a Type 2 SCD, you have to close the current row and insert a new row with a valid start time of yesterday. This is the most common failure mode, and it's why bitemporal models exist.
Incorrect valid time. The source system sends a valid time that's wrong. Maybe the engineer set it to the batch processing time instead of the business event time. This is a data quality issue that temporal modeling can't fix. You need to validate the valid time against known business rules.
Semantic time vs. technical time. The event happened at 10:00 AM, the system processed it at 10:05 AM, and the business person sees it in the dashboard at 10:10 AM. Which time is "when it happened"? This is a semantic problem, not a technical one. You have to define what time means to your business.
The "as of" query trap. You build a temporal model that tracks valid time. Then someone asks "what did the data look like last week?" and you run a valid-time query. But what they really want is "what did the system think last week?" which is a transaction-time query. You built the right model for the wrong question.
The Engineering Checklist for Temporal Systems
After building these systems for years, here's my checklist.
Decide your source of truth for time. Is it the database, the application, or the external source? Whatever you pick, make sure every system in the pipeline uses the same clock.
Model both valid time and transaction time. Even if you don't implement a full bitemporal schema, keep the columns around. You'll need them when something goes wrong.
Use system-versioned temporal tables where available. SQL Server, PostgreSQL (with extensions), and Databricks all have temporal features. They're cheaper than building your own.
Test with out-of-order data. Your temporal model will fail when events arrive late. Write tests that simulate this.
Plan for storage growth. Temporal data grows linearly with time versions. Build the archive strategy before you need it.
Time to Fail Differently
I've been building data infrastructure since 2018, and I've made almost every mistake in this article. The systems that worked had one thing in common: they treated time as a first-class citizen, not an afterthought.
How does temporal work in data engineering? It works when you respect the difference between what happened and when you learned about it. It works when you model both valid time and transaction time. And it works when you accept that your source systems will be wrong.
The temporal data problem is not a database feature problem. It's a system design problem. Your schema, your pipelines, and your queries all need to be built around the fact that time is not a point. It's a relationship between the world and your understanding of it.
The sooner you accept that, the fewer ghosts you'll chase in your event pipelines.
FAQ: Temporal Data Engineering Questions
What is the difference between valid time and transaction time?
Valid time is when a fact is true in the real world. Transaction time is when the database records that fact. A customer's address change is valid on the day they moved, but it might be recorded in the database a day later.
What is a unitemporal data model?
A unitemporal model tracks one time dimension, usually transaction time. It answers "what did the system know as of time X?" It cannot answer "what happened in the real world as of time X?"
What is a bitemporal data model?
A bitemporal model tracks both valid time and transaction time. It can answer both questions: what happened in reality and when the system learned about it.
What is a slowly changing dimension Type 2?
A Type 2 SCD keeps history by adding new rows for each change. Each row has a valid start and end date. The current version has an end date of "infinity" (like '9999-12-31').
When should I use temporal tables vs. manual audit columns?
Use temporal tables when you need to query historical states. Use manual audit columns when you only need to know who made the change and when.
Does temporal data modeling affect query performance?
Yes. Range joins are slower than equality joins. You may need to pre-compute temporal joins or use tiered storage to keep queries fast.
Is temporal data modeling worth the storage cost?
It depends on your use case. If you need to answer "as of" questions, yes. If you only need current state, no.
Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.