Temporal Data Modeling Best Practices
In 2024, a merchant acquiring bank called us at 2 AM from Singapore. Their reconciliation dashboard was showing balances that didn't match what customers saw. Nobody deleted data. Nobody corrupted anything. The problem was that their nightly snapshot table was being overwritten, and a late-settled payment from three days earlier had silently retro-rewritten history. The numbers looked wrong because the data model was lying about time.
That call taught me more about temporal data modeling best practices than any textbook ever did. Time is not a column. Time is an axis of your data, and if you don't model it explicitly, your system will invent a lie and present it as truth.
In this guide, I'll walk through what temporal data modeling actually means in 2026, why most SCD Type 2 implementations are fragile, and how to handle late data in streaming systems without losing your mind.
Start with semantics, not syntax
Most teams jump straight to SYSTEM_VERSIONING = ON or VALID_FROM columns and call it a day. That's backwards. Before you touch DDL, you need to answer one question: which time are you tracking?
There are three distinct concepts that teams routinely conflate:
- System time — when the database recorded a fact. Bake time. If a row was inserted at 14:03:22, that's system time.
- Business/valid time — when the fact was true in the real world. A contract signed on June 1 but entered into the system on June 5 has a valid time of June 1.
- Event time — when something actually happened in the source system, distinct from when it was processed downstream.
Your model needs to be explicit about which time each column represents. And in most production systems of substance, you need at least two of them. The SQL Server docs on Temporal Table Usage Scenarios are still the clearest practical framing of system-versioned design, especially for regulatory audit scenarios.
The moment you conflate event time with system time, you've bought yourself a debugging nightmare. I've seen this kill more projects than any other single modeling error.
Temporal data modeling best practices: the four patterns that matter
After a decade of building data systems, I've settled on four patterns that cover 95% of real-world needs. Everything else is a variation or a hack.
Pattern 1: Append-only events
You never mutate. You only insert. Every change is a new row with a monotonically increasing sequence ID and an event timestamp. This is the foundation of event sourcing and the backbone of most streaming pipelines.
Pros: simple, auditable, replayable. Cons: querying "current state" requires reconstruction, which gets expensive at scale.
Pattern 2: System-versioned snapshots
You maintain current state plus history. The database (or your application) keeps an immutable copy of every prior version. SQL Server's temporal tables do this natively, and Postgres 18's improved system-versioning support finally makes this viable without third-party extensions.
Pattern 3: Valid-time modeling
You track when a fact is/was true in the real world, independent of when it was recorded. This is what What Is Temporal Data Modeling? How Databases Track does well explaining: it's the difference between "we recorded this on Tuesday" and "this became effective on the 1st."
Pattern 4: Bitemporal
You track both system time and valid time. This is the gold standard for regulated industries, and it's a pain in the ass to build correctly. You need it when auditors will ask "what did you know and when did you know it?"
At SIVARO, we've built bitemporal systems for insurance and banking clients. The complexity is real, but the alternative is worse. When a regulator asks you to reconstruct the exact state of a policy on a specific date as you knew it then, you cannot fake that with a single timestamp column.
Temporal tables vs slowly changing dimensions: the real trade-off
There's a persistent confusion between temporal tables and slowly changing dimensions (SCDs). They overlap but aren't identical, and Slowly Changing Dimensions and Temporal Databases does a decent job of showing where the Venn diagram diverges.
SCD Type 2 is a data warehousing pattern for tracking dimension history. You add valid_from, valid_to, and an is_current flag, then you write careful update/insert logic when a dimension attribute changes.
Temporal tables are a database primitive. They automate history tracking at the storage layer.
Most people think SCD Type 2 is the right way and temporal tables are an implementation detail. That's wrong. They solve different problems.
- SCD Type 2 loses the association between business time and system time unless you build it manually.
- Temporal tables give you system time for free but say nothing about business validity periods.
- Neither alone gives you both.
Tim Mitchell's post on Using Temporal Tables for Slowly Changing Dimensions outlines a hybrid: use system-versioned temporal tables as the audit trail, then materialize SCD Type 2 views on top for your warehouse consumers. That's a pragmatic pattern. I've used it in production and it works.
The mistake is trying to bolt business time onto a system-time-only table with clever column naming. You'll end up with half the team interpreting valid_from as "when we saved the row" and the other half reading it as "when the fact became true." Both are right and that ambiguity destroys trust in the data.
How to handle late data in streaming systems
Late data is the most underestimated problem in streaming architecture. Every new engineer assumes events arrive roughly in order. Production laughs at that assumption.
In 2025, a logistics client of ours was losing millions of yen monthly due to wrong delivery-time attribution. Their tracking events from IoT sensors on delivery trucks arrived out of order — a truck passing a checkpoint in Tokyo would emit events that landed in our pipeline 40 minutes after events from the next checkpoint. The system attributed deliveries to the wrong time windows, breaking their SLA penalties with shipping partners.
There are only four serious strategies for handling late data in streaming systems, and they're all about trade-offs:
Watermark-based thresholds
You accept that events older than N seconds/minutes are "too late" and either drop them or route to a side channel. In Flink, this looks like:
java
DataStream<SensorEvent> stream = env
.addSource(kafkaSource)
.assignTimestampsAndWatermarks(
WatermarkStrategy
.<SensorEvent>forBoundedOutOfOrderness(
Duration.ofMinutes(10))
.withTimestampAssigner(
(event, ts) -> event.getEventTimestamp())
);
Ten minutes of allowed lateness is a common starting point, but the right number depends on your data. Measure the 99th percentile of event latency distribution over 30 days, then set your watermark to that. For our Tokyo client, that was 22 minutes, not the 5 minutes they'd initially chosen.
Late-data correction lane
You process late events in a separate pipeline that produces correction events rather than re-processing raw events. Downstream consumers merge these corrections into their aggregates. This is the right pattern when you can't afford to re-run stateful processing.
Event-time bumping
When a late event arrives, you bump its event time to the current processing time for aggregation purposes, but store the original event time in a separate column for traceability. Honest enough for analytics, problematic for SLAs where timing matters legally.
Two-pass aggregation with a settlement window
You do a preliminary aggregation when the watermark says "window is closed," then a final aggregation after a longer settlement period (say 24 hours). This is the pattern that financial institutions actually use for end-of-day reconciliation, and it's the most defensible when regulators are involved.
All four strategies are outlined conceptually in the TDWI piece on What Is Temporal Data Modeling? How Databases Track, which correctly identifies late-arriving facts as a temporal modeling challenge, not just a streaming infrastructure problem.
The key insight: late data is a modeling problem, not an infrastructure problem. If your storage schema doesn't support corrections and retroactive updates, no amount of Kafka configuration will save you.
Temporal data modeling best practices for schema design
Here's where I earn my keep. After building dozens of these systems, these are the rules that have survived production.
1. Never store derived time in the same column as raw time
You'll be tempted to have event_time and processed_time and ingestion_time. That's fine. What kills you is when someone uses processed_time for a business-time query because it's "close enough." Separate concerns with naming conventions so egregious that misuse is obvious: event_ts, db_ingested_ts, business_effective_ts.
2. Use closed-open intervals for validity ranges
[valid_from, valid_to) — the start is inclusive, the end is exclusive. This is the single most important convention in temporal modeling. It eliminates boundary bugs at midnight boundaries and when records expire.
sql
-- The correct pattern: valid_to is exclusive
SELECT *
FROM dim_customer
WHERE customer_id = 42
AND business_asof_date >= valid_from
AND business_asof_date < valid_to;
3. Make is_current a computed column, never a stored one
If you store an is_current flag, you will end up with two rows marked current after a failed update. Compute it instead:
sql
CREATE VIEW dim_customer_current AS
SELECT c.*
FROM dim_customer c
LEFT JOIN dim_customer c2
ON c.customer_id = c2.customer_id
AND c.valid_from < c2.valid_from
WHERE c2.customer_id IS NULL;
4. Version keys are not primary keys
A customer in an SCD Type 2 table has two identifiers: the natural key (customer_id) and the version key (customer_sk). The primary key must be the version key. Joining on the natural key alone will produce duplicate rows and silently wrong results.
5. Choose the right "end of time" sentinel
'9999-12-31' is the classic choice. It works, but it needs to be handled everywhere in your queries. Some teams prefer NULL for open-ended intervals, but that breaks closed-open range queries. Pick one and standardize. We use '9999-12-31' at SIVARO because it makes BETWEEN queries work without special-casing NULLs.
Implementation notes from production
Let me give you a concrete example. Here's the SQL Server temporal table pattern we push for clients who are on that stack — it gives you system time for free:
sql
CREATE TABLE dbo.Customer
(
CustomerId int PRIMARY KEY,
CustomerName nvarchar(100),
AccountStatus nvarchar(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
(HISTORY_TABLE = dbo.CustomerHistory));
This is elegant. Every UPDATE automatically moves the old row to CustomerHistory. You get FOR SYSTEM_TIME AS OF queries:
sql
SELECT CustomerName, AccountStatus
FROM dbo.Customer
FOR SYSTEM_TIME AS OF '2026-01-15T12:00:00'
WHERE CustomerId = 42;
That's genuinely nice. But it's system time only. The SQL Server temporal table documentation has a great scenario walkthrough for audit use cases that are satisfied by system time alone.
For finance and insurance clients, we layer business valid time on top:
sql
CREATE TABLE policy_endorsement (
policy_id INT,
endorsement_id INT PRIMARY KEY,
premium NUMERIC(12, 2),
business_valid_from DATE NOT NULL,
business_valid_to DATE NOT NULL, -- exclusive
system_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
system_retired_at TIMESTAMP NULL, -- NULL means current
CONSTRAINT no_overlap CHECK (business_valid_to > business_valid_from)
);
The business logic that manages this table needs rigor. Every insert must update the previous row's system_retired_at. Use a stored procedure or a service layer — don't let application code scatter updates-inserts in arbitrary order, because you will get overlapping validities.
Query patterns that separate pros from amateurs
Most temporal data modeling best practices guides stop at schema design. The hard part is querying.
"As of a point in time"
sql
SELECT *
FROM dim_customer
WHERE customer_id = 314
AND as_of_date >= valid_from
AND as_of_date < valid_to;
"What changed between two dates"
Compare two point-in-time snapshots. In a bitemporal system, this requires joining on both axes, and it's where most queries explode in complexity.
"Current plus history"
Most dashboards need the current snapshot with a drill-through to history. Materialize a current-state view for the fast path, keep the full history in the base table for the drill-down. The ThoughtSpot guide on slowly changing dimensions covers this well from a warehouse-consumer perspective.
Anti-patterns I've seen blow up in production
The snapshot table with no versioning. You overwrite last night's snapshot with today's, and now yesterday doesn't exist. This is how the Singapore bank ended up with phantom balances. If your team tells you "nobody queries yesterday," they're wrong.
The updated_at timestamp posing as history. A single updated_at column tells you when the row changed, not what the row was before. This is the most common fake temporal model in existence. It works for debugging but fails every audit question.
Unbounded history with no retention policy. Temporal tables grow forever. In 2020 at SIVARO, we had a client with 400 GB of ledger history that was ballooning at 15 GB a month. Fine for now, painful in three years. Define retention up front. Archive old history to cold storage after 24-36 months, or partition aggressively.
Joining on natural keys. Duplicate key join blowups are the #1 cause of "why are my numbers doubled" incidents in temporal systems.
The case for timestamps over dates
Use timestamps, not dates, for system time. Dates have a midnight problem: a spring-forward or fall-back at 2 AM creates ambiguity, and timezone-aware teams develop ulcers. If your business operates across timezones, store UTC timestamps in a standard format and convert to local time at query time using the appropriate timezone column.
This is one of those decisions where the "right answer" is almost always timestamps, and teams end up using dates because they're readable. I get it. I've been there. The '9999-12-31' sentinel is ugly, but it's less ugly than betting your audit on a date at midnight.
When not to use temporal modeling
I'll be contrarian here: temporal modeling is not always the answer.
- Analytics dashboards that only need today's numbers don't need history. Query the current snapshot.
- Chat/message logs are naturally append-only. Adding versioning on top of append-only data is redundant.
- High-volume IoT telemetry where you rarely care about "what did we know then" is better served by raw events in a columnar store like Parquet/Delta Lake, not a system-versioned relational table.
The cost of temporal modeling is complexity in every query. Every business user now has to understand validity ranges, sentinels, and version keys. If you don't have a regulatory or business reason for history, don't pay that tax. Eric Evans would call this YAGNI applied to time.
How SIVARO approaches this in practice
When we build data infrastructure for clients, we start with a simple matrix:
| Business problem | Recommended pattern |
|---|---|
| Audit compliance, regulator asks "what did you know when?" | Bitemporal, system + valid time |
| Data warehouse dimensions | SCD Type 2, or SCD Type 2 over temporal tables |
| Event streams with late arrivals | Event-time modeling with watermark + correction lane |
| Operational "current state" only | No temporal modeling, just current snapshot |
This is a starting point, not a gospel. But it's saved us months of overengineering on projects where teams wanted full bitemporality for what was really a simple inventory lookup.
FAQ
What's the difference between temporal tables and slowly changing dimensions?
Temporal tables vs slowly changing dimensions is the most common search I see. Short version: temporal tables are a database primitive that gives you system-time history automatically. SCD Type 2 is a warehouse-level pattern for tracking dimension changes with business-influenced validity. They overlap but aren't interchangeable. You can build SCD Type 2 on top of temporal tables.
When should I use bitemporal modeling?
When auditors or regulators ask "what did we know, and when did we know it?" — insurance, banking, healthcare, logistics contracts. If you only need to answer "what changed and when," system time alone is fine. Validate whether you actually need both axes before committing to bitemporal complexity. In our experience, it trips up teams less if they start primed for retroactive corrections.
How do I handle late data in streaming systems without breaking downstream reconciliation?
Use watermarks for initial processing and a correction lane for anything later. Have the correction lane emit compensation events, and let downstream consumers merge them into their aggregates. We use this pattern with Flink and Kafka clients regularly. Follow the temporal data modeling best practices for aggregations by measuring the 99th percentile of event-to-processing latency over 30 days before setting your bound.
Should I store is_current as a column?
No. Compute it. Storing is_current invites bugs when updates fail mid-transaction and leave two "current" rows. A derived view that picks the latest valid_from per natural key is safer and always correct.
How long should I keep temporal history?
Define a retention policy up front. For most OLTP workloads, 24-36 months of online history is plenty; archive older rows to cold storage as Parquet or compressed text. Reconcile the archived data back into your temporal store if you need full "as-of" queries across all time, but that's expensive.
Do temporal tables hurt performance?
They add storage overhead and some write cost, but modern databases handle this well. SQL Server's in-memory temporal tables are fast enough for high-throughput OLTP, and Postgres 18 made substantial improvements to system-versioning overhead. The bigger risk is bad query patterns, not the table structure. Always test with your real data volume, not a 10K-row sample.
When should I avoid temporal modeling entirely?
When you don't need history at all and every query is about current state. High-Velocity appending event logs rarely need versioning. Don't pay the complexity tax unless a business requirement demands rolling back the clock, whether for audit, replay, or policy retroactivity.
The engine room view
I was wrong about one thing early on. When we started at SIVARO, I believed temporal modeling was a storage problem. Add the right columns, enable versioning, done. It turned out to be a semantic problem. The hard part isn't storing history. It's agreeing on what time means for each fact, getting the team to align on closed-open intervals, and making sure every query knows which axis it's operating on.
If your ingest pipeline peaks at 200K events per second with 30% late-arrival rates, the bottleneck is never disk space. It's the consistency between what your streaming layer thinks is "now" and what your temporal model defines as "as of."
Teams that get temporal modeling right treat time like a first-class citizen of their schema: an axis, not an annotation. They keep valid-from/valid-to, system-versioning, and event-time semantics explicit, and they never make the database guess what as of means.
That alignment is what turns a data infrastructure crisis into a solved problem. And it's what turns a 2 AM phone call.
Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.