Incident Lab · SEV2 · Snowflake
Delivered shipments showing twice in the tracking table
Support: the customer portal shows shipment 1 as both 'out_for_delivery' and 'delivered'. The ops dashboard counts it twice.
dbt test unique_current_shipment_status_shipment_id: FAIL 2
current shipment status keeps the latest event per shipment by joining each shipment to its max(updated at) . The carrier's feed truncates timestamps to the minute and sends events in batches, so two events can share the exact same updated at . On a tie the join keeps both rows. (Switching to row number() ... ORDER BY updated at DESC alone doesn't fix it: it would pick one of the tied rows arbitrarily.) The feed also carries status seq , the carrier's per-shipment sequence number, which always increases. That's the tiebreaker. Investigate: Fix current shipment status to return exactly one row per shipment: the event with the latest updated at , ties broken by the highest status seq . Columns: shipment id , status , updated at . Order by shipment id .