Nightly Sync That Skips, Duplicates, and Gets Throttled
A Python service synchronizes 180,000 endpoints from a controller's REST API every night at 01:00. It fetches pages with ?offset= and ?limit=500, retries any 429 immediately up to five times, and creates a site record before each device. The API returns 429 every few minutes with Retry-After: 30; the service also sees 409 Conflict on about 2% of creates after a retry. During a 6-hour run, the controller's page contents shifted because an operator deleted and re-added 12,000 devices, and the run both skipped and duplicated records. The service also exposes a webhook endpoint for change events; the sender retries for up to 24 hours on non-2xx, and your handler writes directly to the database before returning 200. Two weeks ago a duplicate storm created 38,000 duplicate site rows. There is no queue, and the team wants the smallest change that makes the sync correct and rerunnable.
1. Redesign the 429 handling. Specify concurrency, backoff, retry budget, and why the current immediate retries make the problem worse.
Read the worked answer
Immediate retries amplify the 429: five retries in a tight loop turn one throttled request into six, and with multiple workers you create a synchronized retry storm. Replace with:
- Honor
Retry-Afterwhen present; otherwise exponential backoff with full jitter (base 1 s, cap 60 s, max 5 attempts). - A per-client token bucket below the documented limit, for example 80% of quota, and a bounded worker pool (start with 4-8, not 100).
- A circuit breaker that stops the sync after a sustained 429 rate rather than burning the window.
- A resumable checkpoint (last successful cursor) instead of restarting from offset 0.
- A retry budget: if more than 5% of calls are retried, fail the run and alert rather than continuing for six hours.
Distinguish retryable (429, 500, 502, 503, 504, timeouts) from non-retryable (400, 401, 403, 404). Log request IDs and Retry-After values. Schedule off-peak and add random start jitter so multiple regions do not align. The goal is to stay under the limit, not to win a race after being throttled.
2. The offset pagination both skipped and duplicated records. Explain a correct pagination strategy and the idempotency controls needed for creates that return 409.
Read the worked answer
Offset pagination is not stable when the collection changes: deleting 12,000 records shifts every later page, so you skip records; re-adding them shifts pages back, so you duplicate. Switch to cursor or Link-header pagination if the API supports it; the cursor encodes a stable position. If only offset exists, take a snapshot/version token, or sort by an immutable key such as ID and page with id > last_id, then run a reconciliation pass.
Make writes idempotent: derive a natural key (serial number or MAC) and use an upsert with a unique constraint. A 409 on create means someone won the race; fetch and update, do not create a new row. Put a unique index on the natural key so a duplicate storm cannot create 38,000 rows. If the API accepts an idempotency key, send one per logical operation. Separate discovery from mutation: collect all device IDs first, reconcile locally, then write, so a shifting page cannot change what you are about to update.
3. The webhook sender retries for up to 24 hours. Design the handler so retries and duplicate deliveries cannot create duplicate domain rows.
Read the worked answer
The sender guarantees at-least-once delivery, so your handler must be idempotent. Do not write the domain row and then return 200; if the write succeeds and the response is lost, the retry repeats it.
Instead:
- Verify the signature over the raw body with the shared secret and reject stale timestamps (replay window).
- Insert the event into an inbox table keyed by the sender's event ID under a unique constraint.
- Return 2xx immediately and process asynchronously.
A duplicate event ID hits the constraint and is acknowledged without side effects. Keep a processed-event ledger with a TTL longer than the 24-hour retry window. If the sender has no event ID, hash the canonical payload plus a timestamp. The 38,000 duplicate site rows came from check-then-insert without a unique constraint: two workers both saw 'absent' and both inserted. Add the database constraint, dedupe existing rows, and make the handler's write a single upsert. Monitor inbox lag and duplicate rate, and alert when duplicates exceed baseline.