Essential Guide to SEO: Boost Your Online Visibility

Most data models answer a single question: what is true right now? That is fine until someone asks what was true last Tuesday, or what you believed was true last Tuesday before a correction landed. The moment those questions appear, you need temporal data modeling: designing your tables so that time is a first-class citizen rather than something you bolt on after the fact.

This post explains the two kinds of time you actually care about, the patterns for modeling them, and the traps that catch almost everyone the first time.

The “Current State” Trap

The default way we build tables is destructive. A customer changes their address, we run an UPDATE, and the old address is gone forever:

			
UPDATE customer
   SET address = '14 New Street'
 WHERE customer_id = 1001;

The row now reflects reality, but the database has quietly thrown away history. You can no longer reproduce last quarter’s tax report, you cannot audit when the change happened, and if the update was wrong you have nothing to roll back to. For transactional systems that is often acceptable. For analytics, finance, and anything touching compliance, it is a slow-motion disaster.

Temporal modeling fixes this by refusing to overwrite. Instead of mutating a row, we record a new version and keep the old one.

Two Dimensions of Time

The key insight — and the thing most people miss — is that there are two independent timelines in any dataset:

Valid time — when a fact is true in the real world. The customer lived at the old address from January to June, then moved.
Transaction time (also called system time) — when the database learned about the fact. You might not have recorded the move until July.

These two clocks rarely agree. Data arrives late, corrections are backdated, and someone always enters last month’s figures next week. A model that tracks both timelines is called bitemporal, and it lets you answer the question that auditors love: “What did we believe the value was on date X, as of what we knew on date Y?”

Most teams start by modeling one timeline and add the second only when they get burned.

Pattern 1: Slowly Changing Dimensions (Valid Time)

In the warehouse world, valid time is usually handled with a Slowly Changing Dimension Type 2 (SCD2). Every version of a row gets an effective range and a current flag:

			
CREATE TABLE dim_customer (
    customer_key   BIGINT,       -- surrogate key, one per version
    customer_id    BIGINT,       -- natural/business key
    address        VARCHAR,
    valid_from     DATE,
    valid_to       DATE,
    is_current     BOOLEAN
);

		

A change closes the old row and opens a new one:

			
-- close the previous version
UPDATE dim_customer
   SET valid_to = '2024-06-30', is_current = FALSE
 WHERE customer_id = 1001 AND is_current = TRUE;
-- insert the new version
INSERT INTO dim_customer
VALUES (..., 1001, '14 New Street', '2024-07-01', '9999-12-31', TRUE);

		

Now a point-in-time query is trivial — you simply ask for the version whose range contains the date you care about:

			
SELECT address
  FROM dim_customer
 WHERE customer_id = 1001
   AND '2024-03-15' BETWEEN valid_from AND valid_to;

This is the workhorse pattern. It is simple, it is portable, and it powers most “as-of” reporting in the wild.

Pattern 2: System-Versioned Tables (Transaction Time)

For transaction time, the SQL standard defines system-versioned temporal tables, supported natively by SQL Server, MariaDB, DB2, and others. The database maintains the timeline for you:

			
SELECT * FROM account
FOR SYSTEM_TIME AS OF '2024-03-15 09:00:00';

You never write the period columns by hand — every UPDATE and DELETE automatically archives the prior row into a history table. This is perfect for audit and forensic queries (“show me exactly what this account looked like before the incident”), but note what it does not give you: it only tracks when the database changed, not when the fact was true in the real world. That distinction is the whole game.

Pattern 3: Bitemporal Tables

When you need both, you carry two pairs of period columns:

			
CREATE TABLE price_bitemporal (
    product_id    BIGINT,
    price         DECIMAL(12,2),
    valid_from    DATE,      valid_to    DATE,     -- real-world timeline
    system_from   TIMESTAMP, system_to   TIMESTAMP -- knowledge timeline
);

		

A retroactive correction — “the June price was actually wrong” — becomes a new row with the same valid range but a later system range. Nothing is destroyed; you can now reconstruct both what was true and what you thought was true at any combination of the two clocks. It is more machinery than most tables need, but for pricing, risk, insurance, and regulatory reporting it is non-negotiable.

The Traps Nobody Warns You About

Temporal modeling is conceptually clean and operationally fiddly. The recurring mistakes:

Closed vs half-open intervals. Use half-open ranges [valid_from, valid_to) so consecutive versions never overlap on the boundary day. Mixing inclusive and exclusive ends produces double-counting that is brutal to debug.
NULL vs sentinel end dates. Pick one convention for “still open” — either NULL or a sentinel like 9999-12-31 — and enforce it everywhere. Inconsistency here breaks every BETWEEN.
Overlapping versions. Without a constraint, a bad load can leave two “current” rows for the same key. Add an exclusion constraint or a uniqueness check on (business_key, is_current).
Time zones. Store temporal columns in UTC. A report that straddles midnight in two regions will otherwise disagree with itself.
Late-arriving and retroactive data. This is precisely why transaction time exists. If you only model valid time, a backdated correction silently rewrites history and your old reports stop reproducing.

Isn’t This Just Time Travel?

If you have read about lakehouse formats, you might be thinking that Iceberg snapshots already solve this. They do not — at least not completely. Table-level time travel is transaction time at the snapshot granularity: it tells you what the table looked like at a point in your load history. It says nothing about valid time, and it cannot answer “what was the customer’s address in March” unless you have explicitly modeled that. Snapshots and temporal modeling are complementary: one versions the table, the other versions the facts.