Catalog Sync with a Flaky Partner API
A retail analytics team must refresh a product catalog every morning by combining a partner REST API with an internal PostgreSQL table. The API returns 100 records per page with cursor pagination, enforces 60 requests per minute, and serves about 48,000 SKUs. The internal table has 47,200 rows keyed by product_id and includes updated_at. During yesterday's load, the job took 22 minutes, received repeated HTTP 429 responses, and ended with 1,300 API SKUs absent from the final table. The partner sometimes reorders records while a pagination run is in progress, so some SKUs appear on two pages and others can be skipped. The team needs an idempotent refresh that finishes within two hours and produces a reconciled table. A manager proposes scraping the partner's public website instead because all products appear on one search page. You are asked to review the plan.
1. Evaluate the scraping proposal and recommend an acquisition design. Address cursor stability, rate limits, retries, checkpointing, and reconciliation.
Read the worked answer
Scraping is the wrong primary fix. It may violate the partner's terms, breaks when markup changes, adds load to a public site, and still needs deduplication. The API is designed for bulk sync. At 100 records/page, 48,000 SKUs is about 480 pages; at 60 requests/minute the theoretical floor is 8 minutes, so two hours is feasible if retries are controlled.
Use cursor pagination, persist the cursor after each committed page, and resume from it. Handle 429 with Retry-After and exponential backoff plus jitter; cap retries. Reordering means the cursor is not a stable snapshot, so deduplicate by SKU keeping the record with the latest updated_at; do not assume page coverage proves completeness. Load into a staging table, upsert by SKU in one transaction, and mark rows absent from a complete successful run only after reconciliation.
Reconcile with a source count if available, plus page counts, min/max SKU checks, and a full ID set comparison against the prior snapshot. Missing 1,300 records suggests failed pages were treated as empty; a page-level success ledger would catch that.
2. Write the core idempotent load logic in SQL or pseudocode and list validation checks that would have caught the 1,300 missing SKUs.
Read the worked answer
A safe pattern is staging plus upsert with a success ledger:
for page in cursor_pages(start_cursor):
fetch with retry and Retry-After handling
validate schema and count <= 100
normalize records; dedupe by sku keeping max(updated_at)
begin transaction
upsert into staging(sku, name, price_cents, currency, active, updated_at)
record committed page cursor
commit
if every page succeeded:
compare source ID set, staging ID set, and previous snapshot
begin transaction
merge staging into product on sku
mark absent SKUs inactive only after reconciliation
commit
else:
do not publish; resume from last committed cursor
Validation checks: page success count equals expected pages; no page returned an empty body before the last page; distinct SKU count is within an expected range; duplicate SKU count is reported and explained; every fetched SKU exists in staging after commit; source IDs minus final IDs is empty or explicitly approved; final IDs minus source IDs is empty; updated_at is not older than the prior snapshot for unchanged rows. Log page numbers and counts without logging secrets.