11 days of missing data: how we backfilled with duplicate-safe writes

After the GCP to AWS migration resolved, we had 11 days of missing data (June 14-24). The pipeline had been running but writing to a database it couldn't reach. Backfilling 11 days of per-coin, per-exchange OHLCV data is not a replay — it's a data archaeology project.

The challenge: many of the external APIs (exchange websockets, on-chain RPC nodes) don't allow historical queries beyond a rolling window. Binance's REST API goes back 500 candles, but only for recent pairs. BigQuery public datasets have lag — some chains are 6-12 hours behind real-time. A simple "re-run the last 11 days" would produce partial results at best.

The strategy was three-tier: 1. Primary: re-query exchange REST APIs for the window, using pagination where available. 2. Fallback: pull from BigQuery public crypto datasets, which have complete historical coverage but at delayed freshness. 3. Last resort: interpolate from daily snapshots stored in GCS (we had daily Parquet exports going back months — a design choice that saved us).

The write side used INSERT ... ON CONFLICT DO UPDATE everywhere, so re-running the same day twice produced the same result. The backfill took 36 hours across staggered jobs, each writing to a staging schema first, then swapping partitions atomically.

The lesson that saved us: daily Parquet exports to object storage. We set them up as a debugging aid and never used them until the migration broke. Now they're a non-negotiable part of every pipeline's data contract.