Point-in-Time Databases

A normal database overwrites. A company reports Q1 earnings of $1.20 per share; six months later it restates that number to $1.15 after an accounting review. An ordinary table just updates the row — query it today and you get $1.15, with no trace that $1.20 was ever there. That is fine for an accounting system, but it is a disaster for a backtest: a strategy that "traded on Q1 earnings" in April only ever saw $1.20, and if your data layer quietly hands the backtest $1.15 instead, you're testing on information that didn't exist yet. A point-in-time database keeps every version of every fact, tagged with when it was true and when it was known, so a query can ask "what did the world look like as of April 15th" and get the honest answer.

The idea: two timestamps, not one

The key design idea is separating when a fact was true (valid time — the fiscal quarter the earnings cover) from when we learned it (knowledge time — the date it was reported or restated). A single "as of" timestamp isn't enough on its own; what you actually want to reconstruct is what a query run on that date would have returned, which means filtering on knowledge time and keeping the version of the fact that was current at that moment.

SELECT value
FROM earnings_history
WHERE ticker = 'ABC'
  AND fiscal_quarter = '2024Q1'
  AND knowledge_date <= '2024-04-15'   -- only facts known by this date
ORDER BY knowledge_date DESC
LIMIT 1;                                -- the latest version known at that time

In words: pull every version of this fact that existed on or before the query date, and take the most recent one — that's what a researcher (or a live strategy) would have seen if they'd run this query on April 15th, restatements from later dates excluded entirely.

Worked example: a restated earnings figure

Row history for one company's Q1 2024 EPS:

knowledge_date	value
2024-04-10	1.20
2024-06-02	1.15 (restated)

A backtest simulating a decision on April 15 should see 1.20 — the restatement hadn't happened yet. A backtest simulating a decision on June 10 should see 1.15. Querying the current table (a normal, non-point-in-time system) would return 1.15 for both dates, silently leaking June's information into the April simulation — a form of lookahead bias that inflates backtested performance and doesn't show up until the strategy underperforms live.

def value_as_of(history, query_date):
    valid = [r for r in history if r["knowledge_date"] <= query_date]
    if not valid:
        return None
    return max(valid, key=lambda r: r["knowledge_date"])["value"]

value_as_of(history, "2024-04-15")   # -> 1.20
value_as_of(history, "2024-06-10")   # -> 1.15

Where it shows up

Interview questions in this space are usually schema-design questions: "design a table that supports both 'give me the current value' and 'give me the value as of any past date' efficiently" — the expected answer is a bitemporal table (see Bitemporal Data Modeling) with an index on knowledge time, and a discussion of the storage cost of never deleting old versions.

In production, this is the backbone of any credible backtesting or research platform: fundamentals, analyst estimates, index membership, and credit ratings all get restated or revised, and every one of them needs point-in-time storage or the backtest is quietly cheating (see Point-in-Time Data and Vendor Restatements and Data Revisions). Feature pipelines feeding live models have the same requirement in reverse — the model at inference time must only see what was knowable at that instant, which is the same discipline applied going forward instead of backward (see Point-in-Time Correctness in Feature Pipelines).