ml-002 · As-of price joins — Concepts

The big idea

A retail order should be billed at the price that was in effect at the moment the order was placed — not the current price, not the average price, and not every price the product has ever had. When prices live in a history table (one row per price change, with an effective_from timestamp), matching each order to its correct price is a temporal join, usually called an as-of join: for each order, pick the price row with the largest effective_from that is at or before order_datetime. A plain equality join on product_id cannot express “at or before”, so it silently attaches every price version to every order — duplicating rows and mixing in prices that did not exist yet when the order was placed.

Definitions

  • Price history (slowly changing values): a table with one row per (product_id, effective_from) pair. A price row is in force from its effective_from until the product’s next effective_from (or forever, if it is the latest row).
  • As-of join (backward direction): for each left-table row with timestamp t, join the single right-table row (within the same group, here product_id) whose timestamp is the maximum one satisfying effective_from <= t.
  • Effective price: the price returned by the as-of join for a given order — the most recent price change at or before order_datetime.
  • Future price / lookahead leakage: a price whose effective_from is after order_datetime. It exists in the table but was not in force when the order happened; attaching it misstates revenue.
  • No-price-yet order: an order placed before the product’s first effective_from. A correct as-of join returns a missing price for it, which must be handled explicitly (flag, impute, or exclude — a business decision).

Why it matters

Revenue reconstruction, invoicing audits, and any “value at time of event” computation (exchange rates at transaction time, tax rates at sale time, subscription tiers at signup time) all reduce to the same pattern. Getting it wrong is quiet and expensive: a naive join inflates the row count (each order appears once per price version, so summing “revenue” multiple-counts orders) and, if someone deduplicates by keeping an arbitrary row, they can bill an order at a price introduced months later. Both errors pass a schema check and produce plausible-looking totals.

Pitfalls

  • Joining on the key only. orders x price_history on product_id yields one row per (order, price version). With 1–5 versions per product, 400 orders can become well over a thousand rows.
  • Deduplicating with “latest price per product”. Taking each product’s most recent price and joining that fixes the row count but bills every order at the final price — wrong for any order placed before the last change.
  • Using < instead of <=. A price that becomes effective at the exact timestamp of the order should apply to it. Off-by-one boundary handling is a classic source of small, hard-to-audit discrepancies.
  • Forgetting the sort requirement. Dedicated as-of implementations (e.g. pandas.merge_asof) require both tables sorted by the time key and will raise (or worse, mis-match) otherwise.
  • Dropping no-price-yet orders silently. An inner filter-join makes orders without an eligible price disappear; always re-join back to the full orders table so the gaps stay visible.

Check your understanding

The Mar 1 00:00 order pays $12: the as-of rule is effective_from <= order_datetime, and the Mar 1 row satisfies it with the latest timestamp. The Feb 15 order pays $10, because at that moment the Mar 1 change had not yet taken effect — even though it already exists as a row in the table. The Sep 1 price is a future price for both orders and must never be attached to them.

The equality join matches an order to all price rows for its product, so an order for a product with 4 price versions appears 4 times (row duplication), and 3 of those rows may carry prices from before or after the order (temporal mismatch, including future prices). Keeping only each product’s latest price restores one row per order but applies the end-of-period price to the whole year, so every order placed before the final change is billed incorrectly. The correct rule is per-order, not per-product: latest effective_from at or before that specific order_datetime.