etl agent hit warehouse rate limit mid-backfill: backoff and resume

TL;DR

When the warehouse starts rate-limiting mid-backfill, checkpoint every completed partition, back off exponentially while halving concurrency, and resume from the last committed checkpoint with idempotent writes so replays are safe.

Symptom

Backfill progress stalls partway through: the warehouse returns rate-limit, too-many-requests, or concurrency-quota errors, and the agent keeps pushing at full concurrency, turning a throttle into a wall of failures.

Steps

1. Checkpoint per partition or batch as it completes. Write the high-water mark (last completed partition id or timestamp) to durable storage, not just agent memory:

save_checkpoint(run_id, last_completed_partition)

Expected output: a restart knows exactly where to resume, down to the partition.

2. On rate-limit errors, back off exponentially and halve concurrency instead of retrying at full speed:

concurrency = max(1, concurrency // 2)
sleep(min(300, 2 ** attempt))

Expected output: the warehouse stops rejecting requests and the run makes progress again, slower but alive.

3. Resume from the last committed checkpoint. Make every write idempotent (merge or upsert on the natural key) so replayed partitions do not duplicate rows:

MERGE INTO target USING staging ON target.id = staging.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...

Expected output: resuming mid-backfill produces exactly-once results even for partitions written twice.

4. Finish the run at reduced concurrency, then restore normal settings for the next run.

Expected output: the backfill completes; the throttle was a temporary degradation, not a failed run.

When to use

• Rate-limit or concurrency errors appear mid-backfill
• The backfill must resume without duplicating rows
• You need the agent to degrade gracefully instead of failing the run

When not to use

• Authentication failures (no backoff fixes a bad credential)
• The limit is on the source API, not the warehouse (throttle the extractor instead)
• A one-off small load (just retry it; this machinery is for long backfills)

Variant phrasings

warehouse concurrency quota hit during a scheduled sync

Same pattern on a smaller scale: checkpoint per batch, halve concurrency, resume.

backfill keeps failing at the same partition

The partition itself may be poisonous (one giant partition trips the limit alone). Split it and retry the halves.

Why it happens

Warehouses throttle concurrent queries and backfills are the burstiest workload an agent runs: dozens of parallel loads that were fine at normal sync volume all fire at once.

Edge cases

• The checkpoint write itself must be atomic; a half-written checkpoint resumes from the wrong place.
• Idempotent writes need a true natural key; without one, replays duplicate.
• Some warehouses rate-limit per user, so running the backfill as a dedicated service user isolates it from interactive workloads.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_E8tsGpFOofZi0qP5JesFwA