Temporal & Historical Modeling

In Slowly Changing Dimensions (SCD), Asha moved from IN to DE on 2026-01-08. Type 2 kept both versions, each with valid_from and valid_to.

This chapter asks a harder question. What if the warehouse learns about the move four days late?

Then one pair of dates is not enough. You need a second pair. Senior interviewers ask about exactly this.

What you will learn

1. Valid time and recorded time, the two kinds of time.
2. Effective dating, and how an as-of join finds the right row.
3. Bitemporal rows, and how to ask “as known” and “as true”.
4. Changes against corrections, and how to restate a report.
5. Snapshots, event tables, and rows that arrive late.
6. Time travel in engines, and how to choose a design.
7. Three common mistakes.

Valid time is when a fact is true in the real world. Recorded time is when the warehouse learns the fact.

Take Asha’s move. She moved on 2026-01-08. The support team told the warehouse on 2026-01-12.

For four days, the warehouse says IN while Asha lives in DE. Order 102 is dated 2026-01-10, inside those four days.

Two different questions need two different dates:

• What was true on 2026-01-10? This uses valid time.
• What did we believe on 2026-01-10? This uses recorded time.

Other books call recorded time “transaction time” or “system time”. Valid time is also called application time or business time. The ideas are the same.

Effective dating stores valid time on each row. You know it from Type 2. Here are Asha’s two rows again:

Each row is true from valid_from up to, but not including, valid_to. We write this as [valid_from, valid_to).

The square bracket includes the start day. The round bracket leaves out the end day.

Why leave out the end day? Test the day of the move, 2026-01-08. First use BETWEEN, which includes both ends:

SELECT customer_key, country
FROM dim_customer
WHERE customer_id = 1
  AND DATE '2026-01-08' BETWEEN valid_from AND valid_to;

A join on that day would return two rows, and an order would count twice. Now use the half-open test:

SELECT customer_key, country
FROM dim_customer
WHERE customer_id = 1
  AND DATE '2026-01-08' >= valid_from
  AND DATE '2026-01-08' <  valid_to;

An as-of join uses this test for every order. Slowly Changing Dimensions called it a point-in-time join. It is the same join.

This query joins Asha’s two orders:

SELECT o.order_id, o.order_date, c.country
FROM orders AS o
JOIN dim_customer AS c
  ON  c.customer_id = o.customer_id
  AND o.order_date >= c.valid_from
  AND o.order_date <  c.valid_to
WHERE o.customer_id = 1
ORDER BY o.order_id;

A bitemporal table keeps both kinds of time on every row. It has four date columns:

• valid_from: the first day the fact is true.
• valid_to: the day it stops being true. This day is not included.
• recorded_from: the day we started to believe the row.
• recorded_to: the day we stopped believing it. This day is not included.

Use 9999-12-31 for an end that is still open. We never delete a row.

We change only one column on an old row: recorded_to. Setting it closes what we believed before.

Start on 2026-01-01. We load Asha as IN, with no end date. We believe this row now:

On 2026-01-12 we learn about the move. We do three things:

1. Close the old belief.
2. Add an IN row for the days before the move.
3. Add a DE row from the move.

Why a new IN row? Row 1001 says IN with no end. That was our old belief, so we keep its country and valid dates.

The new belief is IN only until 2026-01-08, so it needs its own row.

These statements do it:

UPDATE dim_customer_bt
SET recorded_to = DATE '2026-01-12'
WHERE customer_key = 1001;

INSERT INTO dim_customer_bt
  (customer_key, customer_id, country,
   valid_from, valid_to, recorded_from, recorded_to)
VALUES
  (1008, 1, 'IN', DATE '2026-01-01', DATE '2026-01-08',
   DATE '2026-01-12', DATE '9999-12-31'),
  (1006, 1, 'DE', DATE '2026-01-08', DATE '9999-12-31',
   DATE '2026-01-12', DATE '9999-12-31');

Now you can ask Asha’s country in two ways. Each query tests a valid date and a recorded date.

“As known on 2026-01-10” means: use only what the warehouse believed on that day. A report run that day said IN for order 102:

SELECT country
FROM dim_customer_bt
WHERE customer_id = 1
  AND DATE '2026-01-10' >= valid_from
  AND DATE '2026-01-10' <  valid_to
  AND DATE '2026-01-10' >= recorded_from
  AND DATE '2026-01-10' <  recorded_to;

“As true on 2026-01-10” means: use what we know today. Today is any day on or after 2026-01-12. We use 2026-01-31 for the recorded date:

SELECT country
FROM dim_customer_bt
WHERE customer_id = 1
  AND DATE '2026-01-10' >= valid_from
  AND DATE '2026-01-10' <  valid_to
  AND DATE '2026-01-31' >= recorded_from
  AND DATE '2026-01-31' <  recorded_to;

Both answers are correct. They answer different questions.

The first is the old report. The second is the truth we found later.

A change means the world changed. A correction means our data was wrong or late. They need different edits.

A change starts on the day you hear about it. A correction fixes the past: its valid date is earlier than its recorded date.

In a bitemporal table, both set recorded_to on the old row. Only a correction changes what we believed about days before the recorded date.

The late move is a correction. Its valid date is 2026-01-08, and its recorded date is 2026-01-12.

To restate a report means to publish new numbers for a past period. The 2026-01-10 report counted Asha’s paid orders under IN. We now know order 102 belongs to DE.

The query below runs the as-of join twice. WITH makes a small table, report_dates, with two “known on” dates. We assume both orders were loaded by 2026-01-10.

WITH report_dates(known_on) AS (
  VALUES (DATE '2026-01-10'), (DATE '2026-01-31')
)
SELECT r.known_on, c.country, SUM(o.amount) AS revenue
FROM report_dates AS r
CROSS JOIN orders AS o
JOIN dim_customer_bt AS c
  ON  c.customer_id = o.customer_id
  AND o.order_date >= c.valid_from
  AND o.order_date <  c.valid_to
  AND r.known_on  >= c.recorded_from
  AND r.known_on  <  c.recorded_to
WHERE o.customer_id = 1
  AND o.status = 'paid'
GROUP BY r.known_on, c.country
ORDER BY r.known_on, c.country;

You can still rebuild the old report. It is the first row.

The new report moves 25.00 from IN to DE. The total is 65.00 both times.

Publish the new numbers with their known date, and keep the old report. A reader can then see why the number moved.

A snapshot is a full copy of a table, taken on a schedule. A nightly job copies the current customer rows into a table with a snapshot_date column. It stores no ranges.

Here are Asha’s rows for five nights. The 2026-01-12 copy is taken after the late news arrives:

To ask about one night, use one filter with =:

SELECT country
FROM dim_customer_snapshot
WHERE customer_id = 1
  AND snapshot_date = DATE '2026-01-10';

A snapshot answers “as known”. It cannot answer “as true on 2026-01-10”. The real answer is DE, but no snapshot dated 2026-01-10 says so.

Use snapshots when you need exact copies at a few moments, such as month end. Use history rows when values change rarely and you need valid dates.

An event is one thing that happened. In event sourcing, you only add events and never update a row.

You work out the state from the events. The state is the values at one moment, such as status and amount.

Order 105 has two events. The second one is made up for this story. It happened on 2026-01-25, but it reached the warehouse on 2026-01-27.

The state on a date is the last event on or before that date. Ask for the state as true on 2026-01-26:

SELECT status, amount
FROM order_events
WHERE order_id = 105
  AND event_time <= DATE '2026-01-26'
ORDER BY event_time DESC
LIMIT 1;

Now ask what we knew on 2026-01-26. Add a test on recorded_at:

SELECT status, amount
FROM order_events
WHERE order_id = 105
  AND event_time  <= DATE '2026-01-26'
  AND recorded_at <= DATE '2026-01-26'
ORDER BY event_time DESC
LIMIT 1;

LIMIT 1 keeps the first row after sorting. It works in PostgreSQL, MySQL and Snowflake. Other engines use TOP 1 or FETCH FIRST 1 ROW ONLY.

A late fact is loaded after its day, like order 106 in Fact Table Design. You look up the dimension by the order date, not the load date.

A late dimension version is harder. Old fact rows may already point at the wrong version.

On 2026-01-10, order 102 was loaded. Asha had one row then, key 1001. So the fact stored key 1001.

On 2026-01-12, the move arrives. In the Type 2 table, you close row 1001 at 2026-01-08 and add row 1006. Now order 102 points at the wrong row:

Find the wrong keys with a check query. It joins each fact to its stored row, then to the row that is valid on the order date:

SELECT f.order_id,
       f.customer_key AS stored_key,
       v.customer_key AS right_key
FROM fact_orders AS f
JOIN dim_customer AS s ON s.customer_key = f.customer_key
JOIN dim_customer AS v
  ON  v.customer_id = s.customer_id
  AND f.order_date >= v.valid_from
  AND f.order_date <  v.valid_to
WHERE v.customer_key <> f.customer_key;

This check skips a fact that matches no version. That is a gap, rule 1 in Slowly Changing Dimensions. Check for gaps too.

Then repair the fact row. You change its key and nothing else:

UPDATE fact_orders
SET customer_key = 1006
WHERE order_id = 102;

Some engines keep old row versions for you. This is called time travel. It stores recorded time only.

Check your engine’s documents for the exact limits. They change by edition and by setting. Here is a system-versioned table in SQL Server style:

SELECT country
FROM dim_customer
  FOR SYSTEM_TIME AS OF '2026-01-10'
WHERE customer_id = 1;

The engine does not know the move was on 2026-01-08. So it cannot answer “as true on 2026-01-10”.

SQL:2011 also has application-time periods for valid time. Db2 and MariaDB support them; SQL Server does not. In most warehouses, you model valid time yourself.

Start from the question people will ask. Each question points at one design:

Bitemporal rows cost the most. They have four dates, and every query tests two ranges. Use them for the few columns that audits or restated reports need.

Do not use them for every column. Most columns need only Type 1 or Type 2.

Three mistakes break most temporal tables:

1. Overlapping ranges. Two rows are true on the same day, so a join returns two rows and counts twice.
2. Inclusive end dates. A BETWEEN test matches the old and the new row on the day of the move.
3. Current values for old facts. The join uses is_current, so old orders show today’s value.

You saw mistake 2 in Effective dating. Here is a check for mistake 1, then an example of mistake 3.

You can find overlaps with a self-join. A self-join joins a table to itself. Here, someone ended Asha’s IN row on 2026-01-09 by mistake:

SELECT a.customer_key AS first_key,
       b.customer_key AS second_key
FROM dim_customer AS a
JOIN dim_customer AS b
  ON  a.customer_id = b.customer_id
  AND a.customer_key < b.customer_key
  AND a.valid_from < b.valid_to
  AND b.valid_from < a.valid_to;

The third mistake gives a wrong answer with no error. Join Asha’s orders to the current row only:

SELECT c.country, SUM(o.amount) AS revenue
FROM orders AS o
JOIN dim_customer AS c
  ON c.customer_id = o.customer_id AND c.is_current
WHERE o.customer_id = 1
  AND o.status = 'paid'
GROUP BY c.country;

Here is what each question about Asha’s country on 2026-01-10 returns:

Test yourself. Cover the page and answer each question:

• What is the difference between valid time and recorded time?
• Why does valid_to use a half-open range?
• Which four columns make a row bitemporal?
• In a bitemporal table, the move was recorded late. Which column do you change on the old row?
• Why can a snapshot not answer “as true on 2026-01-10”?
• Why does time travel not replace your own date columns?

Each exercise opens a drawing board. You draw the tables, and the review checks them against sample rows.

Start with “Patients, sites, and protocol amendments mid-study”. It uses valid time and recorded time together.