Vintage Databases and As-Of Joins

Most databases keep only the current value of a field: today's version of a company's Q1 revenue, say. But that Q1 revenue figure has probably been revised at least once since it was first reported — restated, corrected, reclassified — and a backtest that queries "what was Q1 revenue?" and gets today's fully-revised number is testing a strategy on information that didn't exist at the time the strategy would have had to act on it. A vintage database solves this by storing every version of every value along with the date each version became known, so a query can ask for the value as it stood on a specific past date rather than the value as it stands today.

The idea

Instead of one column per data field, a vintage database keeps a row for every revision: the value, the date it applied to (say, Q1 2019 earnings), and the date it was published or became known. An "as-of join" is the operation that answers a backtest's real question — for a given historical date, join each security to the most recent value that had already been published by that date, ignoring any later revisions. This is different from an ordinary join, which just matches on a key and returns whatever the current table holds.

Building this correctly requires two separate timestamps for every fact: when the fact is about (the period it describes) and when the fact was known (when it was published or became available). A backtest's as-of join must always use the second one.

Worked example

A company reports Q1 earnings per share of $1.10 on April 20th. Six weeks later, an accounting restatement revises it down to $0.95. A vintage database stores both rows: ($1.10, published April 20) and ($0.95, published June 5). A backtest simulating a trade on May 1st does an as-of join for "the earnings figure known as of May 1st" and correctly retrieves $1.10, because the restatement hadn't happened yet — even though a naive query against today's database would return $0.95 and give the backtest information nobody actually had on May 1st.

What this means in practice

Vintage databases cost more to build and store than simple "latest value" databases, which is why a lot of vendor and in-house data infrastructure skips them — until a backtest quietly benefits from hindsight it shouldn't have. Any research process using fundamental data, analyst estimates, or index membership for a systematic strategy needs this kind of as-of capability somewhere in the pipeline, whether built in-house or bought from a vendor that specializes in point-in-time data.