Katkat
Back

Design a Snapshot-Driven Product Catalog

AttentiveSoftware Engineer · Onsite
MediumSoftware EngineerOnsiteSystem Design

Last updated

Design a multi-tenant product catalog ingestion and serving system that processes periodic, complete merchant snapshots while deterministically layering administrator overrides, supporting atomic cutovers, and maintaining read consistency.

Scenario

Many e-commerce and marketing automation platforms ingest product catalogs from external merchant platforms (such as Shopify, Magento, or custom ERP systems) via periodic full-catalog snapshots (e.g., daily CSV or JSON dumps of millions of SKUs). Concurrently, marketing administrators need the ability to manually override product metadata—such as titles, custom tags, promotional pricing, and visibility—directly within the platform. These overrides must survive subsequent snapshot ingests until explicitly removed or superseded.

Design the ingestion, storage, and serving architecture for this snapshot-driven product catalog.

Requirements

Functional

  • Snapshot Ingestion & Validation: Ingest complete catalog snapshots asynchronously. Run data validation gates (e.g., schema checks, anomaly detection like >30% SKU drop) before publishing.
  • Administrator Overrides & Provenance: Support field-level overrides that always take precedence over snapshot attributes. Provide an audit trail showing the provenance (snapshot vs. admin) of each attribute.
  • Atomic Publication & Rollback: Publishing a new snapshot must be atomic to consumers; partial states must never be exposed. Teams must be able to roll back to a prior published version instantly.
  • Deletion Semantics: Distinguish between items explicitly deleted by an admin, items omitted from the latest snapshot, and items marked inactive by the merchant.
  • Catalog Serving & Stable Pagination: Serve product search, filtering, and listing with stable pagination (no duplicate or skipped items if an ingest cuts over mid-session).

Non-Functional

  • Multi-Tenant Isolation: Hard or logical isolation across thousands of merchant tenants.
  • Scale: Up to 10,000 tenants; large tenants have up to 10 million SKUs; aggregate read traffic peaks at 50,000 QPS.
  • Latency & Freshness: A 1-million SKU snapshot must be processed and ready for cutover within 15 minutes. Read API p99 latency < 50ms.

What Your Design Should Cover

  1. Data Model & Provenance: How raw snapshots and manual overrides are modeled, stored, and merged.
  2. Ingestion & Cutover Pipeline: End-to-end flow from file drop to atomic switchover, including validation gates.
  3. Serving, Caching, & Pagination: Data store choice for serving, cache invalidation strategy, and how to guarantee stable pagination.
  4. Failure Modes & Rollback: Recovery from corrupted feeds, network partitions, and fast version rollbacks.

Hints

Hint 1

Separate the immutable snapshot data from the mutable overrides table, merging them into an immutable versioned catalog index rather than updating existing records in place.

Hint 2

Use a version pointer per tenant (e.g., active_version_id) to make cutovers atomic and allow instantaneous rollbacks by pointing to the previous version.

Hint 3

For stable pagination during a background cutover, encode the active version identifier inside the pagination cursor or session token so client pagination requests remain pinned to a specific snapshot.

Follow-up questions

  • How would you optimize ingestion compute and storage costs if 95% of items in a 10M SKU snapshot are unchanged from the previous version?
  • If an admin overrides a price to $40 and a subsequent snapshot updates the merchant price to $40, how do you handle override staleness or conflict detection?
  • How does your caching layer handle high-throughput personalization queries (e.g., filtering out-of-stock items for millions of SMS marketing recipients) during a live catalog switch?