Bounded lookback and late-arriving updates

A saved watermark can miss a record that arrives later with an older source timestamp. One recovery policy re-reads a bounded overlap and relies on version-aware writes to make replay safe. This fixture declares an inclusive lower bound of watermark minus lookback and a frozen inclusive upper bound.

The input is a finite owned delivery using integer logical ticks. These ticks are a simplified source-order domain, not parsed wall-clock strings. The lookback policy deliberately cannot recover records older than its lower boundary; reconciliation or a reviewed backfill is still required for arrivals beyond that declared tolerance.

The baseline validates the window, selects records between lower and upper inclusive, and applies them twice through highest-version selection. The output identifies retained entities and reports the exact window. The second pass should leave current state unchanged because replay is part of the intended policy.

Trace a record just before the saved watermark but inside the overlap: it belongs in the selected delivery. A record beyond the frozen upper bound belongs to a later run. Keeping that upper bound stable prevents one extraction attempt from chasing a moving end while also making its coverage explainable in an audit.

The mutation selects only ticks greater than the watermark. It removes the overlap that the late-arrival policy promised, so an eligible late update disappears. A high final watermark may still look healthy even while the sink has missed a record behind it. Progress metrics alone do not prove completeness.

Making the lookback arbitrarily large is not a complete correctness argument either. The source may have unbounded delay, changed history or retention limits. Choose a bound from an explicit service agreement and operational evidence, then monitor older corrections and reconcile snapshots. Event time windows and delivery cursors should remain separately named in those reports.

Extend the report with versions, the retained source versions in sorted identity order. Preserve the inclusive window and rejection of negative lookback or a backward upper bound. The added field should show which update survived replay rather than merely restating the first selected row.

Read the deduplicated latest map after both passes. Sort identities and emit each retained record’s version. Use the overlapping-version case, empty selection and boundary case to verify that the report describes canonical state. Do not advance a real source checkpoint merely because the local selection function has returned a window.

Step 1: Expose retained source versions while reprocessing a bounded lookback window idempotently. Step 2: Edit the owned exercise file and run verify.py --practice etl-late. Preserve caller inputs and the independently declared failure cases.

The solution adds visible source-version evidence while retaining deterministic replay. The prepared result shows both coverage and chosen versions, which helps distinguish a missing selection from an incorrect update rule. Neither observation claims that data outside the lower bound has been recovered or that a live source supplied a consistent snapshot.

In a production extraction, save the run's upper bound and source contract alongside its checkpoint. Large backfills may need a separate namespace or staged replacement so they do not accidentally overwrite newer state. Version guards and a reconciliation plan are complementary: one protects ordering, the other checks whether coverage was complete.

Explain the difference between a late arrival, an old business event and a stale source version. A record can be late in one clock and new in another. Identify which field defines selection and which defines authoritative state in this example, then describe the policy for events outside the configured overlap.

For a stretch, add lower-bound, upper-bound and just-outside fixtures with explicit expected inclusion. Design a separate full-snapshot reconciliation that reports missing identities before changing the sink. Do not silently widen the production window or label a bounded lookback as a guarantee against every possible historical correction.

Optional stretch — after the core task works: Test both inclusive boundaries and design a separate missing-identity reconciliation report.

Computer practice: https://applydjinn.com/typorithm/etl-data-engineering-lab-20261107.zip. Use an isolated Python 3.13 environment, install requirements.txt and run verify.py. Edit exercises/etl-late.py and run --practice etl-late. Each case owns its files and database; Parquet uses the pinned DuckDB engine. The recovery probe terminates a separate interpreter and reopens the SQLite file. The app retains static examples, prepared results and questions without an editor or code execution. Use the course Download button once to save every lesson and illustration; opening an unsaved course does not automatically download it.