As-of joins: the value that was true at the time

The report that rewrites history

A product cost ₹400 in January, ₹450 from March, and ₹500 from July. A report of margin by month joins each order to the products table, reads cost, and every January order is now shown with a cost of ₹500. Run the same report in December and it disagrees with the version run in June. Nothing was wrong with the join. The join was to the current value of a fact that changes, and the question was about the value at the time of the order.

The lesson on keeping each fact in one place showed one answer: copy the value onto the order row at the moment it happens, the way an invoice carries its own tax rate. That is right when the row is a legal record. It is not available when the history lives in a separate table of changes, which is the usual case for prices, exchange rates, tax rules, staff assignments and customer segments. Then the join itself has to be time-aware.

An effective-dated table

The clean shape stores each version of the fact with the period it was true for:

CREATE TABLE product_costs (
  product_id  bigint  NOT NULL,
  valid_from  date    NOT NULL,
  valid_to    date,             -- NULL means "still current"
  cost        numeric NOT NULL
);

Half-open periods, as with every date range in this course: a row is in force from valid_from up to but not including valid_to. The join then asks for the version whose period contains the order’s date:

SELECT o.order_id, o.placed_at, pc.cost
FROM orders o
JOIN product_costs pc
  ON  pc.product_id = o.product_id
  AND o.placed_at >= pc.valid_from
  AND (o.placed_at < pc.valid_to OR pc.valid_to IS NULL);

Each order matches exactly one cost row, provided the periods for a product neither overlap nor leave gaps. That proviso is the whole risk, and it gets its own check below.

When there is only a start date

Often the history table records only when each value began: a price_changes table with changed_at and the new price. There is no valid_to to test against. The version in force at a moment is then “the latest change at or before that moment”, which is a top-1-per-group question with a date condition:

SELECT o.order_id, o.placed_at, pc.price
FROM orders o
JOIN LATERAL (
  SELECT price FROM price_changes p
  WHERE p.product_id = o.product_id AND p.changed_at <= o.placed_at
  ORDER BY p.changed_at DESC
  LIMIT 1
) pc ON true;

LATERAL lets the subquery refer to the order it is being run for. It is correct and it runs once per order, which is fine for a hundred thousand and worth measuring for a hundred million. The window alternative is to compute valid_to once with lead(changed_at) OVER (PARTITION BY product_id ORDER BY changed_at), turning the start-only table into an effective-dated one, and then use the range join above.

DuckDB has this join as a first-class construct: FROM orders o ASOF JOIN price_changes p ON o.product_id = p.product_id AND o.placed_at >= p.changed_at picks the latest matching row per order without the subquery, and it is one of the strongest reasons to reach for DuckDB on a laptop. Pandas offers merge_asof for the same operation in Python.

Validating the history table

An effective-dated table that overlaps gives an order two cost rows, and the join multiplies; one with gaps gives an order none, and an inner join drops it. Both failures produce a plausible total. Check the table before trusting any join to it:

-- overlaps: a version starts before the previous one ends
SELECT product_id, valid_from
FROM (SELECT *, lag(valid_to) OVER (PARTITION BY product_id ORDER BY valid_from) AS prev_to
      FROM product_costs) t
WHERE valid_from < prev_to;

-- gaps: a version starts after the previous one ended
... WHERE valid_from > prev_to;

Both should return no rows. Run them whenever the history table changes, because the person adding a new price does not think of themselves as editing a join.

Which is right: copy the value, or join as of?

Copying at write time makes the row self-contained, survives the history table being wrong later, and is what auditors expect on a document. Joining as of read time keeps one source of truth, lets a mistaken price be corrected everywhere at once, and is what analysis usually wants. Many systems do both: the invoice carries the rate it was issued with, and the analytics query joins as of the date to ask what the rate should have been. The disagreement between the two is itself a report worth running.