ASOF join
An ASOF join matches each row to the most recent matching row as of its timestamp, per key.
Also called: as-of join, temporal join
ASOF join - an ASOF join matches each row to the most recent row on the other side at or before the row's own timestamp, per key. The canonical case is FX: a 10:01 transaction should convert at the rate that took effect at 10:00, not the one published at 10:02, and an ASOF join picks exactly that version. Mechanically, the right side is a chain of versions per key, each stamped with when it became valid, and the join takes the greatest valid-from at or before the event's time.
It is the quiet workhorse of time-series enrichment: prices to trades, calibrations to sensor readings, feature values to the moment a prediction was made. Done wrong, as a plain join on the latest value, it leaks the future into the past, which in finance and ML has a name: lookahead bias.
Do you need it? If your dimensions change over time, yes. Ubik ships DuckDB's ASOF JOIN syntax, follows a Kafka changelog as the live dimension, and gates each pick on the dimension's watermark, so a row never joins a version that might still be superseded. The picks survive kill -9, checkpointed. For joining two streams in a band instead, see interval join.