Design a scalable data warehouse for a global e-commerce platform

Instruction: Design a petabyte-scale e-commerce analytics warehouse. Define measurable freshness and recovery objectives, fact grains, regional data boundaries, currency/time semantics and validation. Explain tradeoffs; do not assume one product guarantees real-time global consistency or regulatory compliance.

Context: This question evaluates the candidate's ability to design complex, scalable data warehousing solutions for high-velocity, high-volume data. Candidates should demonstrate knowledge of distributed computing, data partitioning strategies, real-time data processing, and compliance with global data protection laws.

Updated

Official answer

First turn “real-time” and “global compliance” into specific requirements: which reports need what freshness, acceptable lag/loss during outages, allowed storage/processing locations, access restrictions and deletion/retention obligations. Have the responsible privacy/security specialists establish those obligations; a warehouse product alone cannot certify them.

Keep regional operational sources and permitted raw landing zones with explicit ownership. Capture changes with stable source identifiers, commit/order metadata and replay checkpoints. Route only approved fields or aggregates across regional boundaries, and trace where derived copies and backups exist.

Build separate order-line, payment-event, refund and inventory-snapshot facts with consistent product/date dimensions. Record original currency and amounts plus the approved conversion rate/version when producing common-currency reports. Do not join all event tables at detail grain and accidentally multiply revenue.

Use incremental transformations and an idempotent publication protocol. Expose freshness and completeness markers so provisional fast results can be reconciled with corrected historical results. Handle late events, deletion propagation and dimension history explicitly.

Choose physical partitioning, clustering, storage and workload isolation from measured petabyte-scale access patterns and cost constraints. Test backfill, regional outage, schema changes, duplicate delivery and restore. Separate availability/freshness goals from correctness promises, and publish a measured service objective rather than claiming instant globally consistent analytics under every failure.

Related Questions