E-commerce Product Catalog and Price Updates

0) Problem Restatement

Design the product catalog for a large marketplace such as Walmart, Amazon or Pinterest shopping. Merchants add and edit products: titles, images, variants (size and color), stock and prices. Sometimes they upload bulk edits of millions of products at once. Shoppers read product pages and search results at very high traffic. Changes must show up quickly and consistently on the product page, in search and in caches.

A common variant: a price changes at most once a day, but product pages read prices constantly. How do we serve prices with very low latency?

1) Requirements

1.1 Functional

• Create and update products and variants (single and bulk).
• Serve product details and prices for product pages and lists.
• Keep search, recommendations and caches in sync with changes.
• Show price and availability accurately, and validate them again at checkout.

1.2 Non-Functional

• Reads: very high QPS with low latency (under 50 ms).
• Writes: heavy bursts from bulk uploads without hurting read latency.
• Freshness: changes visible within seconds to a few minutes.
• Correctness at checkout: the charged price must be the current real price.

1.3 Scale Estimates

• 500M products (SKUs), ~5 KB each → 2.5 TB of catalog data.
• Reads: 200K product views/sec at peak.
• Writes: 5K updates/sec normally. A bulk edit can bring 10M updates in an hour (~3K/sec extra).

1.4 API Design

• GET /v1/products/{id} and GET /v1/products?ids=1,2,3 (batch for lists)
• PATCH /v1/products/{id} { price, stock, title, ... } (merchant)
• POST /v1/merchants/{id}/bulk-updates (upload CSV/JSON) → { job_id }, then GET /v1/bulk-updates/{job_id}

2) High-Level Architecture

2.1 Overview

• Catalog Write Service: validates changes and writes them to the source-of-truth DB (sharded by product ID).
• Bulk Import Service: splits huge files into chunks, validates them, and feeds them to the write path at a controlled rate.
• CDC stream: change data capture, which means reading every committed DB change as an event (e.g., Debezium → Kafka).
• Read model: a denormalized "product page view" document in a fast key-value store (e.g., DynamoDB/Redis), built from the CDC stream.
• Search indexer: updates the search index from the same stream.
• Caches + CDN: product pages and images cached at the edge.

2.2 Architecture Diagram

flowchart LR
    M["Merchants"] --> WS["Catalog Write Service"]
    M -->|"bulk file"| BI["Bulk Import - chunk, validate, throttle"]
    BI --> WS
    WS --> DB[("Catalog DB - source of truth")]
    DB -->|"CDC"| K[("Kafka - product changes")]
    K --> RM["Read-model builder"]
    RM --> KV[("Product view store")]
    K --> SI["Search indexer"]
    K --> INV["Cache invalidator"]
    INV --> CDN["CDN / page cache"]
    S["Shoppers"] --> CDN
    CDN --> RS["Product Read Service"]
    RS --> KV

3) Data Model

products:  product_id, merchant_id, title, description, category_id, brand, attributes (JSON), status
variants:  variant_id, product_id, size, color, sku
prices:    variant_id, price_cents, currency, valid_from, version
inventory: variant_id, warehouse_id, available_qty

The read model joins all of these into one document per product. A product page then needs one fast lookup instead of five joins.

4) Key Flows

4.1 A merchant changes a price

1. The write service updates prices (bumping version) in the source DB.
2. CDC emits the change. The read-model builder updates the product document, and the invalidator purges the CDN or page cache for that product.
3. The search indexer updates the price field so price filters stay correct.
4. The new price is visible within seconds.

4.2 Bulk edit of 10M products

1. The merchant uploads a file and gets a job ID.
2. The import service splits it into chunks of 1,000 rows, validates each row, and records bad rows in an error report.
3. It writes chunks at a throttled rate (e.g., max 2K rows/sec per merchant), so normal traffic isn't affected.
4. Each chunk is idempotent (keyed by job_id + chunk_no), so retries don't double-apply.
5. The merchant sees progress and a downloadable error report.

5) Deep Dive A — Separate the read path from the write path

Reads and writes have very different needs, so we split them (the CQRS idea: separate models for commands and queries):

• Writes go to a normalized, strongly consistent DB.
• Reads go to a denormalized view that is fast to fetch and easy to cache.
• The view is eventually consistent: it may lag a second or two behind. That's fine for browsing.
• At checkout, the order service re-reads price and stock from the source of truth. If the price changed, the buyer is told before paying.

6) Deep Dive B — Prices read constantly, changed daily

• Prices change at most once a day, so cache aggressively: an in-memory cache on each read server plus the CDN, with a long TTL (e.g., 1 hour), and explicit invalidation on change so we're never stale for long.
• For product lists, use batch reads (ids=1..50) to avoid 50 separate calls.
• If price changes are scheduled ("new price at midnight"), precompute and push them to caches just before they take effect.
• Hot products (a flash sale): the CDN absorbs traffic, and request coalescing on cache misses stops thousands of requests from hitting the DB at once.

7) Trade-offs & Alternatives

8) Common Follow-up Questions

• "Two updates to the same product arrive out of order?" Use a version number and ignore older versions in the read-model builder.
• "How do you keep search in sync?" The same CDC stream feeds the indexer, and a nightly job compares counts or checksums to catch drift.
• "Many merchants selling the same product?" Keep a canonical product and a separate offers table for each merchant's price and stock, and pick the "buy box" winner in the read model.

9) Wrap-Up

Write product changes to a sharded source-of-truth DB, stream every change with CDC into Kafka, and build a denormalized product view plus search index from that stream. Serve reads from the view, caches and the CDN with invalidation on change. Handle bulk edits as chunked, throttled, idempotent jobs, and always re-check price and stock at checkout.