Bitemporal data, backtests, and the importance of remembering what we knew.
IMAGINE A STORY:
Arjun is a quantitative researcher. One Monday morning, he builds a model using a company's financial data. The backtest looks promising, and he's happy with the results.
A few months later, his team revisits the model. Someone asks a simple question:
“Would the model have made the same decisions if we'd run it back then?”
Arjun opens his database. The numbers look right. But something bothers him.
Let's rewind.
On April 15, his data vendor reports that a company earned ₹1,000 crore in annual revenue. Arjun's system picks up the number, and his model uses it on April 20.
On May 10, the vendor corrects the figure. The actual revenue was ₹950 crore.
Arjun updates the database. After all, why keep an incorrect number when you know the right one?
But now, when he tries to reproduce his April 20 backtest, the database gives him ₹950 crore.
That's not what his model saw back then.
His database knows the latest version of the truth, but it has forgotten its own past.
And Arjun has a problem. Did his model perform well because of a genuinely useful signal, or because the historical data now contains information that wasn't available at the time?
This is where a seemingly mundane database design decision becomes important.
In quantitative finance, we don't just need to know what a number is. Sometimes, we need to know when that number was true and when we knew about it.
The first is called valid time: the period to which a fact applies. The second is transaction time: when that fact entered our system and, eventually, when it was superseded.
Together, these form the foundation of bitemporal data storage.
For Arjun, preserving both timelines means he can retrieve the latest revenue figure while still reconstructing what his system knew on April 20. He can investigate old decisions, reproduce historical research, and reduce the risk of look-ahead bias.
Of course, bitemporal storage isn't free. It adds complexity, requires careful handling of historical versions, and consumes more storage. Not every application needs it.
But when your business depends on understanding the past accurately, deleting history can be a costly shortcut.
Arjun's mistake wasn't correcting a number. It was assuming that correcting the present meant rewriting the past.
Sometimes, the most important thing a database can remember isn't just what happened. It's what it knew when it happened.
A little about implementation
Technically, bitemporal storage can be implemented by maintaining four timestamps: valid_from, valid_to, system_from, and system_to. Each correction creates a new version of the record instead of overwriting the old one. The previous version is marked as superseded in system time, while its valid-time period can remain unchanged. This allows us to query both the latest accepted value and the version our system knew at a particular point in time.
