Problem Statement: Bulk Catalog Ingestion
Bulk product import — Problem Statement: Bulk Catalog Ingestion
Problem Statement: Bulk Catalog Ingestion
Design a bulk catalog import system: merchants upload CSV/JSON feeds with hundreds of thousands of SKUs, and the platform must validate, normalize, stage, and publish those changes to the live catalog without corrupting search, inventory, or storefront caches. The mental model is an ETL pipeline with a publish gate, not a CRUD endpoint — the file is the input, the live catalog is the carefully-guarded output.
What makes bulk import hard
Three pressures shape every decision. Idempotent retries: merchants re-upload after a network blip, so a replayed chunk must be a no-op — dedup on natural keys (merchant_id + external_sku + feed_version) so a re-upload never double-publishes a product. Partial-failure tolerance: a single bad column must not block an entire 400k-row feed unless the merchant explicitly chose strict mode — bad rows are quarantined, the good rows proceed. Downstream lag: search indexes and CDN image pipelines trail the publish, so the job status surfaces an index_freshness_lag_seconds rather than pretending the change is instantly live everywhere.
Scale anchors
State these explicitly: 50k active merchants, 2M SKUs for the largest tenant, feeds up to 500k rows / 200MB CSV, and holiday peaks of 10k concurrent import jobs. The SLOs: p99 job-status API < 200ms and publish completeness within 30 minutes for a 300k-row apparel file on the standard tier. The defining failure to avoid is the duplicate re-upload: without chunk-level idempotency, a merchant re-sending the same file inflates inventory with double-counted variants. The design dedups on chunk_id, returns the prior job result on an Idempotency-Key match, and never double-publishes the same product version.
Key Highlights
- •Bulk catalog import is an ETL pipeline with a publish gate: validate -> normalize -> stage -> publish to the live catalog without corrupting search/inventory/storefront caches
- •Three pressures: idempotent retries (dedup on natural keys merchant_id+external_sku+feed_version), partial-failure tolerance (one bad column doesn't fail a 400k-row feed), downstream lag (surface index_freshness_lag_seconds)
- •Capacity: 50k merchants, 2M SKUs largest tenant, 500k rows/200MB CSV, 10k concurrent jobs peak; SLOs p99 job-status <200ms, publish completeness <30 min for a 300k-row file
- •Defining failure: a duplicate re-upload inflating inventory with double-counted variants — dedup on chunk_id, return the prior result on Idempotency-Key match, never double-publish a product version
Section Rescue Kit
Buzzwords to use:
Safe statements:
- "I will separate validation throughput from publish correctness—never skip version checks under load."
- "If time is short, I defer image CDN polish before nailing job state machine and idempotency."