Parcel scans after the watermark
Parcel scans after the watermark
You are a data engineer at a logistics platform. Every night an incremental pipeline picks up fresh parcel scans from the parcel_scans table and appends them to the warehouse. The position of the last successful load (the watermark) is stored in the etl_state table — in the row with source_name = 'parcel_scans' (the table may hold rows for other sources too). Your job is to build the next increment: the scans the pipeline has not seen yet, i.e. those whose time is strictly greater than the watermark. Scans with an unknown time (scan_time IS NULL) are considered not ready and must not be included.
Return columns:
- scan_id — scan identifier;
- parcel_code — parcel code;
- hub_code — sorting hub code;
- scan_time — scan time (timestamp, as stored);
- status — parcel status (may be NULL — output as is).
Sorting: by scan_time ascending, ties broken by scan_id ascending. A scan whose time equals the watermark exactly is NOT part of the result.
Row order matters
Sign in to see submission history
Sign inSign in to use AI Mentor
Sign in