Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Process files that arrive out of order

Pipelines & scenarios · Production Scenarios

Process files that arrive out of order

Mediumpipelines-54
scenarioout-of-orderlate-filesevent-timeidempotency

Question

Files for different hours arrive out of order, and sometimes an older file arrives after a newer one. How do you keep the target correct?

Solution

Make the result depend on the data's event time and keys, not on the order that files arrive. If the load for a given hour or key produces the same output whenever it runs, arrival order stops mattering.

Partition by event time and overwrite idempotently

Each file belongs to a time window (the hour in the file name, or the timestamps in the rows). Load it into the partition for that window, and overwrite that partition with the file's contents. If the hour-10 file arrives after hour 11, it fills the hour-10 partition and does not disturb hour 11. If a corrected hour-10 file arrives later, it replaces the earlier hour-10 data. The final table is the same as if files had arrived in order.

When rows (not files) can be updated: MERGE with a version

If files contain updates to the same records (an order's new status), use MERGE by key, and only accept a change if its version is newer than what the target holds:

MERGE INTO orders t
USING staged s ON t.order_id = s.order_id
WHEN MATCHED AND s.source_updated_at > t.source_updated_at THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

The version must come from the source (an updated_at timestamp or a sequence number), not from when you loaded the file. Then an older file arriving late cannot overwrite newer data.

Track what you have processed

Keep a control table of files (name, hour covered, checksum, processed time, status). Process by listing which expected files are missing or new, not by "everything since the last file I saw". That is the trap with a simple high watermark: if you record "last processed hour = 11", a file for hour 10 that arrives afterwards is skipped forever. Use the watermark idea only with a lookback window, or track each file individually.

Downstream

Anything built from these tables must be rebuilt for the partitions that changed. Orchestrators can trigger downstream runs by partition, or an incremental model can reprocess a trailing window (for example the last three days) every run, to absorb late arrivals.

Define "too late"

Decide how long you accept late files (say 3 days), and what happens after that (manual backfill, with a report of what changed). Alert when a file arrives later than that.

Check

A reconciliation of counts per hour against the source, and a test for duplicate keys, will tell you if ordering problems have crept in.

PreviousNext