Bitemporal Data Modeling

A company reports Q1 earnings of $1.00 per share. Six months later, after an accounting review, they restate that figure to $0.85. If your database simply overwrites the old value with the new one, you've made a mistake that's easy to miss: any backtest re-run today will use $0.85 for a decision that, at the time, a real trader could only have seen as $1.00. The restated number didn't exist yet when the original trading decision was made available to the market — using it anyway leaks future information into the past. This is exactly the failure a bitemporal database is built to prevent.

A bitemporal record keeps two separate timelines for every fact. Valid time answers "when was this true in the world?" — the earnings figure was Q1's actual result the whole time, whether reported as $1.00 or $0.85. Transaction time answers "when did our database learn this?" — we knew $1.00 starting the day it was first reported, and $0.85 starting the day of the restatement. Valid time is what a research question about the world usually wants; transaction time is what a faithful backtest needs, since it can only use what was actually knowable on a given date.

Querying "as of" a transaction-time cutoff — sometimes called an "as-known" or point-in-time query — is what makes an honest backtest possible. Asking "what did the database believe about Q1 EPS as of last September" returns $1.00, because the restatement hadn't happened yet, even though today's database correctly shows the final $0.85 as the true valid-time value. Without transaction time explicitly stored, there's no way to answer that question at all — the old value is simply gone, overwritten, and every backtest run from today onward silently assumes perfect foresight about every future restatement.

Worked example: two queries, two different answers

A bitemporal table stores rows with four columns: the fact, its valid-time start/end, and its transaction-time start/end. The Q1 EPS fact has two transaction-time versions: $1.00 (known from March 15 through September 20) and $0.85 (known from September 21 onward), both describing the same valid-time period, Q1. A query "what is Q1 EPS, as known on July 1" scans for the row where July 1 falls inside the transaction-time range — it returns $1.00, correctly reproducing what a trader on July 1 actually saw. A query "what is Q1 EPS, as known today" returns $0.85, correctly reflecting the current best-known truth. Same fact, same table, two different valid answers depending on which point-in-time question is asked.

What this means in practice

Any dataset subject to restatement — earnings, index membership, credit ratings, even corrected trade prints — needs transaction-time tracking if it's going to be used in backtests. Storing only the current value is fine for a live dashboard, which only cares about today's best-known truth, but it silently corrupts historical simulation. Full bitemporal modeling adds real storage and query complexity, so most systems apply it selectively to the handful of tables where restatement risk is highest.