Design an e-commerce analytics warehouse
Company: Axon
Role: Software Engineer
Category: Analytics & Experimentation
Difficulty: hard
Interview Round: Technical Screen
Design an analytics warehouse for an e-commerce platform that has services: item search, item detail page view, user login, add to cart, view cart, purchase/checkout, and view order. The warehouse must support pre-authored queries including:
(
1) In the current calendar month, how many unique users in Asia viewed a specified item A?
(
2) Right now, what are the three orders with the highest total amount? Specify: the event schema (fields, data types, primary/foreign keys), session model and how a tracking/session ID is generated, user identity stitching from anonymous to logged-in sessions, how to record event objects and quantities (e.g., cart contents and order totals), whether to store events as semi-structured JSON vs normalized columns and the trade-offs, partitioning and indexing strategy, deduplication and idempotency, handling late/out-of-order events, dimensional modeling (users, items, geography) and slowly changing dimensions, and the ingestion/processing pipeline (batch vs streaming) with scaling and cost considerations. Optionally, provide example queries your design would enable for the two questions above.
Quick Answer: The question evaluates data engineering and analytics competencies including event schema design, session and identity stitching, dimensional modeling, ingestion pipelines, and operational concerns such as deduplication, partitioning, and handling late or out‑of‑order events.