skip to content

In an enrichment join, what separates matching the reference row that is current now from matching the version valid at the record's own moment?

level: middleimportance: must knowfreq 57%

answer

  1. one asks now, one asks then
  2. key only against key plus moment
  3. they agree only while caught up
  4. history costs rows and copy size
  5. half-open span, or duplicates

basics

~20 s

Current-row matching asks what is true now; as-of matching asks what was true when the record happened, by picking the version whose validity span contains the record's moment. The two agree only while the stream is caught up and the value has not changed since.

solid answer

~50 s

Two different questions are being asked of the same reference. **Current-row matching** joins on the key alone and takes whatever row the reference holds at the instant the join runs. **As-of matching** joins on the key *and* a predicate over time: the record's own moment must lie inside the version's span of validity, so the record sees the value that was in force when it happened. They give the same answer only when the stream is caught up and nothing has changed since the record was produced - which is exactly the condition that fails on any re-run, any catch-up after a backlog, and any record that took a detour on the way in. As-of matching also costs something the other does not: the reference must keep one row per key per validity span, and the local copy must keep them too, so the copy grows with history rather than with key count.

code

sql · 13 lines
sql
-- current-row match: key only
select o.order_id, o.amount, p.price
from orders o
join prices p
  on p.product_id = o.product_id;

-- as-of match: key plus the record's own moment inside a half-open span
select o.order_id, o.amount, v.price
from orders o
join price_versions v
  on v.product_id = o.product_id
 and o.event_moment >= v.valid_from
 and o.event_moment <  v.valid_to;

go deeper

for a junior

Recall the two rules and what each needs from the reference: a key lookup needs one row per key, an as-of match needs one row per key per span of validity with its from and to moments.

for a middle

Write the predicate out loud, explain why the span is half-open, and name the four situations - backlog, re-run, late record, fast-changing reference - in which the two rules stop agreeing.

for a senior

Judge which rule the downstream consumer actually needs, and price the as-of one honestly: history retention on the reference, a local copy that grows with versions, and a retention cut-off that silently corrupts re-runs older than it.

for a principal

The call to own is the contract and its retention horizon - how far back output is guaranteed reproducible, what that costs to keep, and what the organisation is told about figures recomputed beyond it.

## The two rules, stated precisely Both rules enrich a record by looking up a key in a reference. They differ in one clause. - **Match the current row.** Predicate: the reference key equals the record's key. Whatever row the reference holds at the instant the join executes is the answer. The reference needs one row per key and is rewritten in place when a value changes. - **Match as of the record's moment.** Predicate: the reference key equals the record's key **and** the record's own moment lies inside the version's span of validity. The reference needs one row per key *per span*, each carrying the moment the version took effect and the moment it ceased to apply - a versioned reference row. A validity span is normally half-open: the version applies from its start moment up to but not including the start of the next version. Half-open matters, because closed-closed spans make the changeover instant match two versions and produce duplicated output records. ## When the two agree, and when they diverge Current-row matching is not a mistake in itself; it is an approximation whose error is zero under one condition: the record is being processed while its value is still in force. That holds for a healthy, caught-up job over a reference that changes rarely, which is why the bug hides so well in a test environment and appears in production months later. It stops holding whenever: 1. **The job is behind.** After a backlog, records an hour old are being processed against a reference that has moved on. 2. **The input is re-read.** A re-run over history matches every old record against today's values. 3. **The record itself arrived late** - a device that buffered offline, a partner file that landed a day later. 4. **The reference changes often**, which shortens the window in which the two rules coincide to almost nothing. The damage is quiet in every one of those cases. No error is raised, no record is dropped, and the output has the right shape - it simply contains numbers that were never true. ## What each rule demands | | current-row match | as-of match on a validity span | |---|---|---| | join predicate | key only | key plus a moment inside the span | | reference shape | one row per key | one row per key per span of validity | | local copy size | grows with key count | grows with key count times versions kept | | result on a re-run | today's values attached to old records | the same values the first run produced | | result when the job is behind | values from the future relative to the record | values in force at the record's moment | | what makes it wrong | the value changed since the record's moment | a version missing from the copy, or the wrong clock | ## Which clock, and which moment on the reference An as-of match needs two time decisions, not one, and candidates usually name only the first. - **The record's clock.** Matching on the moment a worker reached the record reintroduces exactly the error as-of matching was meant to remove, because that moment moves when the job is slow. Matching on the moment stamped by the source is what makes the output a function of the record. - **The reference's own notion of time.** A version has the moment it became true in the world, and separately the moment it was written down. A backdated correction - a price fixed a week after the fact - changes the answer to "what was true then" for records already processed. Deciding which of those two the span is measured on, and modelling it, is a data-modelling subject with its own owner; what belongs here is knowing the question exists and stating which one the join assumes. ## Cost, and why nobody does this by default As-of matching is strictly more expensive: - The reference must retain history, so its row count grows with change volume rather than with entity count. - A local copy must hold those versions too, or at least the ones still reachable by records in flight, and it never shrinks on its own. - The lookup is no longer a single-key probe; it needs the versions for a key ordered by time, and the right one selected. - Dropping old versions is a retention decision with teeth: the day the oldest retained version is younger than the oldest record you might re-read, re-runs start silently falling back to the earliest version you kept. That is why the honest engineering answer is not "always match as-of". It is: decide whether the consumer of this output is asking a question about *now* or about *then*. A dashboard of currently-open sessions wants now. An invoice line, a settlement, an audited figure or anything that will be recomputed wants then - and for those, current-row matching is a defect that will not be noticed until someone re-runs the job. ## What engines have to do with it Little, which is the point. Every engine in this class can express both rules; several provide a shorthand for the as-of form and several do not, in which case it is written by hand as a predicate over the version's span. What differs is how the reference copy is held and refreshed, not what the two rules mean.

  • Why is a half-open validity span preferred over one with both ends inclusive?
    With both ends inclusive, the changeover moment lies in two versions at once, so a record stamped exactly on it matches twice and the join emits duplicate output. Half-open spans - from the start moment up to but not including the next - partition time exactly, so every moment belongs to one version.
  • A correction is backdated: a version's value is fixed a week after it was in force. What does that do to as-of matching?
    It changes the answer to "what was true then" for records already enriched, so two runs over the same records disagree even though both matched as-of correctly. Distinguishing when a version was true from when it was recorded is what makes that tractable; modelling it is a data-modelling subject, and the join only has to say which of the two its span uses.
  • When is matching the current row the right choice rather than a shortcut?
    When the consumer's question is about now: current status, a live count, an alert on present state. There the record's own moment is not the reference point, and retaining version history would add cost for an answer nobody asks.

Asking what a house was worth in 2019 by reading today's listing is current-row matching: fast, available, and answering a different question than the one you asked. As-of matching is looking up the valuation that was on file in 2019 - which only works if somebody kept the old valuations.

saying these in an interview costs you the question

  • Says matching the current row is always fine because references rarely change
  • Cannot state the extra predicate that turns a key lookup into an as-of match
  • Assumes the reference automatically keeps every past version
  • Uses closed-closed validity spans and never notices the duplicate at changeover
  • Matches on the moment a worker reached the record and calls it as-of
  • Thinks as-of matching is free once the predicate is written