Data Modeling

Bitemporal data modeling answers a specific problem: how to correct historical facts without destroying the record of what your system previously believed. Instead of storing one timestamp, you store two time ranges: the period when the fact was valid in the business and the period when that version of the fact was recorded by the data system.

When bitemporal modeling is needed

Use bitemporal data modeling when historical correction matters. The model is most useful when a late update can change the business meaning of a past period, and when you still need to reproduce the report, decision, invoice, eligibility result, or metric that was produced before the correction arrived.

Common examples include customer status, account ownership, contract terms, pricing plans, insurance coverage, compliance classifications, employee assignments, and financial mappings. In each case, the business fact may have an effective date that differs from the date when the warehouse learned about it.

You usually do not need a full bitemporal model for append-only telemetry, simple operational logs, or facts where late correction has no reporting consequence. Bitemporal history adds complexity. Use it where the ability to ask “what did we believe then?” is worth the extra care.

The two clocks: valid time and transaction time

A bitemporal table separates two questions that are often accidentally mixed together.

  • Valid time asks: during which business period was this fact true?
  • Transaction time asks: during which recorded period did our data system believe this version of the fact?

For analytics, valid time is usually driven by source-system effective dates, contract dates, event dates, or domain-specific business dates. Transaction time should be controlled by the warehouse or modeling process that records the version. It should not be confused with a source system’s updated timestamp unless that timestamp truly represents when your analytical record became known.

A practical convention is to model both clocks as half-open intervals: the start is included and the end is excluded. For example, a row with valid time from 2026-01-15 to 9999-12-31 is active on 2026-01-15, but a row ending at 2026-01-15 is not. This avoids double-counting at boundaries.

Operator rule

If a row has only one time axis, it can usually answer either what was true or what was known, but not both after a correction.

A timestamped correction fixture

Use a small fixture before designing the table. The fixture should force the model to cross the boundaries that usually cause errors: month close, late arrival, and correction of a previously recorded fact.

Here is a hypothetical customer-region example. Customer C001 was first recorded as belonging to EMEA. Later, the source reported a move to APAC effective February 1. After February reporting had already been produced, an audit correction said the move was actually effective January 15.

The important point is not the domain. The important point is that the correction changes historical knowledge. A normal slowly changing dimension can store the latest corrected history, but unless it also preserves transaction time, it cannot show what the warehouse believed before the correction.

Recorded event Recorded at Business assertion Why it is troublesome
Initial load 2026-01-02 09:00:00 UTC C001 region is EMEA from 2026-01-01 onward. Creates the first known version.
Late change 2026-02-03 10:00:00 UTC C001 region is APAC from 2026-02-01 onward. Arrives after the February 1 effective boundary.
Correction 2026-03-05 14:30:00 UTC C001 region is APAC from 2026-01-15 onward. Changes what the system previously believed about a closed January period.

How the bitemporal history looks after correction

After the March correction, the table must preserve both the superseded belief and the corrected belief. That means old recorded versions are closed on the transaction-time axis, not overwritten in place.

The resulting bitemporal history has multiple versions for the same customer and business period. That is expected. The transaction-time interval tells you which version was believed at which recorded point in time.

Customer Region Valid from Valid to Transaction from Transaction to Meaning
C001 EMEA 2026-01-01 9999-12-31 2026-01-02 09:00:00 UTC 2026-02-03 10:00:00 UTC The warehouse initially believed EMEA was valid indefinitely.
C001 EMEA 2026-01-01 2026-02-01 2026-02-03 10:00:00 UTC 2026-03-05 14:30:00 UTC After the late change, the warehouse believed EMEA ended on February 1.
C001 APAC 2026-02-01 9999-12-31 2026-02-03 10:00:00 UTC 2026-03-05 14:30:00 UTC After the late change, the warehouse believed APAC began on February 1.
C001 EMEA 2026-01-01 2026-01-15 2026-03-05 14:30:00 UTC 9999-12-31 After correction, the warehouse believes EMEA ended on January 15.
C001 APAC 2026-01-15 9999-12-31 2026-03-05 14:30:00 UTC 9999-12-31 After correction, the warehouse believes APAC began on January 15.

Query with two as-of parameters

A bitemporal query needs two independent inputs.

  • valid_as_of: the business date or timestamp you want to describe.
  • known_as_of: the transaction timestamp representing what the data system knew at that point.

The core filter is: choose rows where the valid interval contains valid_as_of and the transaction interval contains known_as_of. In plain SQL terms, that means start is less than or equal to the as-of value, and end is greater than the as-of value.

This gives you four useful modes of questioning. You can ask what you believed in February about January, what you now believe about January, what a dashboard would have shown before correction, or how much a correction restated prior periods.

Question valid_as_of known_as_of Answer in the fixture
What did we believe on February 28 about January 31? 2026-01-31 2026-02-28 12:00:00 UTC EMEA
What do we now believe about January 31 after the correction? 2026-01-31 2026-03-10 12:00:00 UTC APAC
What did the February dashboard likely show for February 15? 2026-02-15 2026-02-28 12:00:00 UTC APAC
Did the March correction restate January history? 2026-01-31 Compare 2026-02-28 with 2026-03-10 Yes: EMEA became APAC for that valid date.

Rules for loading corrections safely

The loader should treat corrections as new knowledge, not as permission to erase old knowledge. A reliable load procedure follows a few durable rules.

  1. Never update away the prior belief. Close its transaction interval and insert the new version.
  2. Use a stable transaction timestamp. All rows created by the same correction batch should share the same recorded timestamp so the bitemporal state is internally consistent.
  3. Represent current rows explicitly. Use either a high end timestamp or a nullable end with consistent query logic. Do not mix conventions casually.
  4. Prevent overlapping current valid intervals for the same natural key and attribute set. At a given transaction as-of time, the business history should usually resolve to one answer unless the domain allows multiple simultaneous facts.
  5. Keep source event identity where possible. Store the source record ID, correction ID, batch ID, or assertion ID that explains why the version exists.

The implementation can use SQL window functions, merge logic, or batch transformations. The durable part is the semantics: corrections close transaction-time intervals and create a new recorded version of the valid-time history.

Practical checkpoint

A correction should close transaction time on superseded rows. It should not delete the evidence that those rows were once believed.

Common failure modes

Bitemporal models fail most often because teams store the two clocks but do not protect their meanings.

  • Overwriting history. The corrected row replaces the old row, so prior reports can no longer be reproduced.
  • Using source updated_at as transaction time. This can be wrong if the warehouse received the update later, replayed it, or loaded it in a different order.
  • Mixing inclusive end dates. Inclusive ends create edge cases at midnight, month close, and adjacent changes. Half-open intervals are easier to reason about.
  • Only testing the happy path. A model that works for in-order changes may fail when a correction splits an existing interval.
  • Ignoring deletion and retraction semantics. If a source says a prior assertion was invalid, model whether that means “unknown,” “not applicable,” or a corrected alternate value.
  • Letting BI tools choose the clock implicitly. Dashboards should be clear about whether they show current corrected history or what was known at report time.

Boundary tests for a bitemporal model

Before relying on a bitemporal table, test it with a deliberately uncomfortable fixture. The fixture should include adjacent intervals, late-arriving changes, and a correction that changes a closed reporting period.

For the customer-region example, useful tests include these questions.

  • On 2026-02-28, what did the warehouse believe about customer C001 on 2026-01-31?
  • On 2026-03-10, what does the warehouse now believe about customer C001 on 2026-01-31?
  • Can the model reproduce the February dashboard state after the March correction?
  • Does the correction split the prior valid interval cleanly at 2026-01-15?
  • At any known-as-of time, does a single valid date resolve to one region for this customer?

If these tests are hard to express, the model may not have clear semantics yet. If they are easy to express but return surprising answers, the load logic is probably closing the wrong interval.

Testing rule

Test the model with a correction that changes a period you have already reported. That is where bitemporal value becomes visible.

A practical starting point

Start with one entity and one corrected attribute rather than making the entire warehouse bitemporal. Define the natural key, the business attribute, the valid interval, the transaction interval, and the source assertion fields. Then run a fixture like the one above through your load process.

Only expand the pattern after you can answer both kinds of historical question: “what was true for the business date?” and “what did we know when the report ran?” Bitemporal modeling is not mainly about adding columns. It is about making time semantics explicit enough that corrections do not silently rewrite institutional memory.

Key takeaways

  • Bitemporal data modeling stores both valid time and transaction time so corrections can change history without erasing prior beliefs.
  • The model is most useful when you must reproduce what a report or system knew before a later correction arrived.
  • Use half-open intervals and stable transaction timestamps to avoid ambiguous boundary behavior.
  • Corrections should close old transaction-time intervals and insert new recorded versions rather than overwrite rows.
  • A small timestamped fixture is the fastest way to verify whether your model handles late changes, month-close boundaries, and restatements.

Next step

Build a three-event fixture for one corrected business attribute in your own domain. Write the expected answers for two valid_as_of dates and two known_as_of timestamps before writing load logic.

Recommended next reads