A job holds a local copy of a reference table - which three approaches keep that copy current, and what does each cost?

## Why the copy exists and why freshness is a question at all A reference lives in some store outside the job. Consulting it for every record would put the job's input rate onto that store, so the usual move is to hold a copy alongside the work. The moment you hold a copy, you own a consistency problem the reference store does not have: the copy is a picture of the reference at some past instant, and every record enriched from it inherits however far behind that picture is. Freshness is therefore not an optimisation detail - it is the thing that decides how wrong the output is allowed to be. Where that copy physically sits, and how it is keyed, has an owner elsewhere in this tree; so does the decision to place a whole input on every worker rather than redistribute both sides. This leaf owns only the question of keeping it current. ## The three approaches 1. **Periodic full reload.** On a schedule, read the reference again and replace the copy. - *Staleness:* up to the whole interval, and on average half of it. Every record in the gap is enriched from the previous picture. - *Load on the reference store:* a burst proportional to the reference's size, repeated every interval, whether anything changed or not. - *Per-record latency:* none added. Records never wait on the reference. - *Failure behaviour:* a failed reload usually leaves the previous copy in place, so the job keeps running on stale data rather than stopping - quiet, and worth alerting on. - *Deletes:* handled for free, because the copy is replaced wholesale. 2. **A feed of committed changes.** The reference store emits each insert, update and delete as it commits, and the job applies them to its copy in order. - *Staleness:* the feed's own lag, typically far smaller than a reload interval, and it can be measured. - *Load on the reference store:* proportional to the change rate rather than the reference's size, and spread out rather than bursty. - *Per-record latency:* none added in the simple form. It also opens a stronger option: hold a record until the feed has advanced past its moment, which converts "nearly current" into "correct as of the record" - paid for in latency and in records held meanwhile. - *What it demands:* the feed must exist, must be ordered per key, and must carry deletes explicitly, because nothing else will ever remove a row from the copy. It also needs a starting point - almost always a full load first, then the feed from that position. - *Producing the feed* out of an operational store is a subject of its own with its own owner, and is assumed here rather than explained. 3. **A lookup per record.** No copy. For each record, ask the reference store. - *Staleness:* only the store's own replication lag - but note this returns the value current at lookup time, which is not the same as the value in force at the record's moment unless the store is asked an as-of question. - *Load on the reference store:* scales with the *input* rate, which is the dangerous property: a backlog being worked off at ten times normal rate puts ten times the load on an operational store at the worst possible moment. - *Per-record latency:* a network round trip, which usually forces batching of lookups and caching to be viable at all - and a cache is a copy again, with its own staleness. ## Comparing them on the three axes | | periodic full reload | feed of committed changes | lookup per record | |---|---|---|---| | staleness | up to the interval | the feed's lag | the store's own lag | | load on the reference store | bursty, sized by the reference | steady, sized by the change rate | sized by the input rate | | added per-record latency | none | none, unless deliberately waiting for the feed | a round trip, every record | | handles deletes | yes, wholesale | only if the feed carries them | not applicable | | what it needs to exist | read access | an ordered change feed and a starting load | capacity on the store | | behaviour during a backlog | unchanged | unchanged | worst case: load scales with catch-up rate | ## Choosing, and combining The practical decision runs on three questions: how fast does the reference change, how wrong may the output be, and how much load will the reference store tolerate. A reference that changes a few times a day and tolerates a one-hour error does not deserve a change feed. A reference behind money, entitlements or pricing usually does. And the three are not exclusive: the standard build is one full load at start plus a change feed afterwards, sometimes with a periodic full reload retained as a repair mechanism for the day the feed drifts out of agreement with the source. Two further points are easy to miss: - **Freshness is not as-of correctness.** Making the copy perfectly current still answers "what is true now". Enriching a record with the value in force at *its* moment needs versions in the copy, not just recency. - **Engines differ in when the refresh can take effect.** Where the runtime advances one record at a time through long-lived operators, an applied change can affect the very next record. Where arrivals are collected for a short span and a finite job runs over the collected set, the copy is in practice fixed for the width of that span. A finite job that runs once simply reads the reference when it starts and has no freshness question at all.