By Prakhar Shrivastava · October 19, 2026

Yes. direction=”nearest” can select a record dated after the event you are analyzing. For an as-of lookup that must not use later effective dates, choose a backward match, retain the matched timestamp, and define a maximum acceptable age. Time proximity alone is not a business rule.

Test a future price that is closer

Suppose a fictional retailer wants the reference price effective when each order occurred. All three orders occur at 10:00 UTC. The US reference table contains $100 at 09:50 and $120 at 10:01. The later price is only one minute away, while the earlier price is ten minutes away. A nearest match selects $120 even though its effective time is after the order. All amounts in this exercise are USD; the market labels do not imply currency conversion.

The UK price is effective exactly at the order time. Germany’s latest price is two hours old. For this exercise only, a reference price older than 30 minutes is unacceptable. That threshold is a synthetic policy assumption, not a general recommendation for retail data.

Use a backward lookup with an explicit age limit

import pandas as pd
orders = pd.DataFrame({
    'order_id': [1, 2, 3], 'market': ['US', 'UK', 'DE'],
    'ordered_at': pd.to_datetime(['2026-10-19 10:00Z'] * 3)
})
prices = pd.DataFrame({
    'market': ['US', 'US', 'UK', 'DE'],
    'effective_at': pd.to_datetime([
        '2026-10-19 09:50Z', '2026-10-19 10:01Z',
        '2026-10-19 10:00Z', '2026-10-19 08:00Z']),
    'price_usd': [100, 120, 80, 60]
})
orders = orders.sort_values('ordered_at')
prices = prices.sort_values('effective_at')
result = pd.merge_asof(
    orders, prices, left_on='ordered_at', right_on='effective_at',
    by='market', direction='backward',
    tolerance=pd.Timedelta('30min'), allow_exact_matches=True
)
print(result[['order_id', 'market', 'price_usd']])

Executed with pandas 2.2.3, the result is:

order_id  market  price_usd
1         US      100.0
2         UK       80.0
3         DE        NaN

The US result uses the earlier $100 price. The UK result accepts the exact-time $80 record. Germany remains unmatched because its $60 record is outside the permitted age. Do not replace that missing value with zero: an unavailable reference price is different from a confirmed free item. Retain a status such as “no eligible reference price” for review.

Keep the effective timestamp in the output

The output retains effective_at, so the reviewer can calculate ordered_at minus effective_at for matched records. In this fixture the US age is ten minutes and the UK age is zero. Check that all matched ages are nonnegative and within the chosen tolerance. Also check that each input order appears once. An unchanged row count alone cannot prove that the matched record was appropriate.

Effective time is not necessarily availability time

A backward match protects the effective-date condition used here. It does not prove the information was actually available at the order time. A price entered tomorrow could be backdated to yesterday. For an analysis of what a system knew when it made a decision, preserve ingestion or publication timestamps and validate availability separately. The data contract must specify which clock answers the question. This is especially relevant to model evaluation and historical decision audits.

Normalize timestamps to a shared timeline only after interpreting their source timezone correctly. A local timestamp without an offset is not automatically UTC. The local-time parsing example shows that distinction with US and UK data.

Validate grouping and ambiguous reference rows

The by=”market” condition prevents one market’s reference price from being assigned to another market. A real catalog might also require product, channel, or contract identifiers. Build that key from the source definition. Missing entity keys and timestamps need an explicit exception workflow before matching.

If two reference records share a market and effective timestamp but disagree on price, stop and resolve the conflict using a documented revision or source-priority rule. Do not let accidental input order decide which value the report trusts. Use the duplicate-key audit approach to distinguish exact replays from conflicting records. This fixture deliberately contains no such ties.

Reproducibility and further reading

Five checks passed in pandas 2.2.3: the US backward price is $100, the UK exact match is $80, the stale German record is unmatched, nearest instead selects the future US $120 record, and the output preserves three unique order IDs. Record your pandas version and rerun the fixture when upgrading.

The official merge_asof documentation defines direction, tolerance, grouping, and exact-match options. Both inputs must be sorted by their time merge key; sorting by market first can leave time globally unsorted. For ordinary equality joins, see merge validation and revenue reconciliation.