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.
Exemplo de resposta esperada
É assim que se parece uma resposta correta: ela tem seu próprio número de linhas e não precisa coincidir com as tabelas do esquema. Sua resposta precisa retornar os mesmos nomes de colunas do exemplo: use AS para renomeá-las se for preciso.
| scan_id | parcel_code | hub_code | scan_time | status |
|---|---|---|---|---|
| 8 | PKG-1006 | HUB-MUC | 2025-06-10T12:00:01 | arrived |
| 9 | PKG-1003 | HUB-BER | 2025-06-10T15:20:00 | delivered |
| 10 | PKG-1007 | HUB-HAM | 2025-06-10T18:45:00 | arrived |
Entre para ver seu histórico de envios
EntrarEntre para usar o AI Mentor
Entrar