Your Historical Data Knows the Future

A value dated January 1 does not necessarily belong in a decision made on January 2. It might have arrived on January 3. It might have been corrected on January 20.

That distinction survives every improvement to your storage stack. A faster query can still answer the wrong historical question.

Two clocks, one decision

Consider this synthetic record history:

We want to replay a decision from January 10. The latest revised account of January 1 is 70. The value available on January 10 was 100. Both answers can be correct, but they answer different questions.

Effective time describes the period a fact concerns. Availability time records when the decision-making system could use it. Neither is automatically the timestamp when an analyst later copied the data into a warehouse.

An ingestion timestamp can approximate availability if ingestion is the actual boundary used by the serving system. If data takes another hour to become queryable, that approximation leaks an hour of future knowledge. Define the timestamp at the boundary you intend to reproduce.

A query that leaks the correction

This SQLite query chooses the newest effective fact and breaks ties with the newest revision:

SELECT value
FROM facts
WHERE effective <= :decision_at
ORDER BY effective DESC, available DESC, revision DESC
LIMIT 1;

For January 10, it returns 70. The effective date passes the predicate even though the correction arrived ten days after the decision.

For this fixture, adding the availability constraint gives the intended reconstruction:

SELECT value
FROM facts
WHERE effective <= :decision_at
  AND available <= :decision_at
ORDER BY effective DESC, available DESC, revision DESC
LIMIT 1;

Now January 10 returns 100. January 2 returns no row. The missing result matters: silently substituting today’s value would reintroduce leakage.

This ordering implements a specific rule: choose the most recent effective fact known at the decision time, then its latest known revision. Other domains need validity intervals, retractions, publication delays, or different revision precedence. Write that rule down before selecting a join operator.

What ASOF actually buys you

DuckDB’s ASOF join is useful when each left row needs its nearest temporal match on the right. An ASOF LEFT JOIN also preserves left rows without a match.

It cannot infer which timestamp means “available to this system.” An ASOF join against a table that already overwrote its old values has no old revision to recover. A nearest preceding event-time join against all revisions can still expose later backfills. Establish an availability-correct relation for each decision before choosing the temporal match.

This is also a feature-store concern. Feast’s point-in-time documentation, checked in its v0.66-branch documentation, distinguishes event-time matching from filter_by_created_timestamp=True. The latter adds a created-time bound. It excludes null created timestamps and errors for unsupported stores. That flag is useful only if the created timestamp represents the availability you need; it cannot repair a misleading source field.

A small replay contract

For each decision, retain its entity key, timestamp and time basis, data-source version, feature definition, and enough revision history to reconstruct the inputs. Keep an explicit missing-value policy. “No known value” and “known value equals zero” should not collapse into the same feature.

Test these cases before trusting the backtest:

• An initial fact arrives after the decision: no match.
• A correction arrives after the decision: select the earlier revision.
• Two revisions share a timestamp: use a documented deterministic tie-breaker, or reject the ambiguity.
• The newest known fact is too old: apply the domain’s staleness rule.
• A fact is retracted: reproduce whether the retraction was known then.

The downloadable Python fixture executes the first two cases in an in-memory SQLite database. It prints 70 for the leaking query, 100 for the availability-aware query, and null before arrival. The dates share one time basis. This is an executable mechanism demonstration, not a DuckDB benchmark or a Feast integration test.

Add this before another optimization

The Vortex, DuckDB and Arrow article focuses on moving and querying market data efficiently. Availability-aware history is a correctness layer underneath that work. Columnar compression, predicate pushdown and zero-copy interchange help after we know which rows belong in the answer.

For your next historical evaluation, pick one revised fact and manually replay it across arrival and correction dates. If the system cannot reproduce those three states, its accuracy score is measuring access to today’s history, not the knowledge available at the time.