Context: production ETL pipeline syncing ShipHero warehouse data (orders, shipments, line items) into SQL Server for an e-commerce client's analytics and QC reporting.
Symptom
QC reporting flagged orders that existed in ShipHero's dashboard but were absent from our warehouse tables. There was no error in the pipeline logs. Runs completed successfully, row counts looked plausible, and the gaps only surfaced when an analyst cross-checked specific order numbers. The worst kind of data bug: silent, intermittent, and invisible to the pipeline's own health checks.
Investigation
First hypotheses were the usual suspects: API rate limiting dropping pages, timezone boundary conditions on the incremental window, or dedup logic incorrectly discarding rows. All three were ruled out by replaying historical windows: the missing orders were never present in any response payload. The API simply never returned them to us.