Data Modeling Interview: Design Schemas for E-Commerce, Analytics, and Time-Series Use Cases

Question

## Round 1 - Data Modeling

## Problem

You will be asked to design data schemas for three scenarios. For each, discuss table/collection design, relationships, indexes, and tradeoffs.

**Scenario 1 — E-Commerce:** Model `Users`, `Products`, `Orders`, and `OrderItems`. Support queries: "all orders for a user" and "total revenue per product in the last 30 days."

**Scenario 2 — Event Analytics:** You ingest 100M user click events per day `(user_id, event_type, page, timestamp, metadata JSON)`. How do you model this for fast time-range queries and funnel analysis?

**Scenario 3 — Time-Series:** Store hourly sensor readings `(sensor_id, metric, value, recorded_at)` for 10,000 sensors over 5 years. Support: last 24h readings per sensor, and min/max/avg over any time window.

## Follow-ups

1. For Scenario 1, what indexes do you add and why? What is the query plan for "total revenue per product in the last 30 days"?
2. For Scenario 2, why is a row-per-event schema problematic at 100M/day? How do columnar formats (Parquet, BigQuery) help?
3. For Scenario 3, compare storing raw rows vs pre-aggregated hourly/daily summaries. What is the staleness tradeoff?
4. A product team wants to join Scenario 2 (clicks) with Scenario 1 (orders) to compute conversion rate. Describe the pipeline.

Data Modeling Interview: Design Schemas for E-Commerce, Analytics, and Time-Series Use Cases

Question Details

Full Details

Topics

More from Netflix