A vendor publishes quarterly earnings and later restates them. Your research table holds one row per company and quarter. Design the table so a backtest run today reproduces exactly what was knowable on 2019-03-15, and write the predicate that selects that view.

A vendor publishes quarterly earnings and later restates them. Your research table holds one row per company and quarter. Design the table so a backtest run today reproduces exactly what was knowable on 2019-03-15, and write the predicate that selects that view.

Approach: Give the row two independent time axes and treat a restatement as an insert rather than an update, then reason about which axis the backtest filters on.

Store two time axes and never update a row in place: valid_from and valid_to describe the period the fact is about, knowledge_from and knowledge_to describe when your system believed it, and a restatement closes the old row's knowledge_to and inserts a new row. The backtest view is then one as of predicate, WHERE knowledge_from <= '2019-03-15' AND knowledge_to > '2019-03-15' AND valid_from <= '2019-03-15', which filters the transaction time axis and then the valid time axis. The unique key is (company, quarter, knowledge_from), so the same quarter legitimately appears many times. One trap is that the vendor's own publication timestamp is not your knowledge time: use the moment the row landed in your system, because a vendor backfilling a file dated three weeks ago would otherwise hand the backtest information it did not have. A second trap is the initial load, where every row imported from a vendor history file carries a knowledge_from equal to the load date, so nothing before that date has point in time correctness and the backtest must start after it. An updated in place row with a separate audit log fails, because the log is not joinable at the same cost and every query has to reconstruct state.

Follow-up: How do you serve the same as of view when the vendor sends a delete rather than a restatement?

Key concepts: valid time, transaction time, point in time correctness, as of predicate.