The brief
Model orders, products and customers so analysts can answer sales questions quickly and correctly. Your design should meet these requirements:
- Orders, order items, products and customers are loaded from the store's operational database every day.
- Analysts can report revenue, units and repeat customers by day, product, category and region.
- Refunds, cancellations and reruns of a failed load never double-count or lose sales.
Scale to design for
An online store's operational Postgres database holds orders, order items, products, customers and payments. About 2 million orders a day with around 5 items each (roughly 10 million order lines a day), 50,000 products in a category tree, 20 million customers. Analysts use a BI tool and need yesterday's numbers by 6am, with 3 years of history. That is only a few GB of new fact rows a day and a few TB in total, so one cloud warehouse handles it without a cluster framework.
State these assumptions out loud in the interview, then let them drive your choices: they decide what needs caching, sharding or asynchronous processing.
Before you start
You'll get more out of this round if you're comfortable with:
- Facts, dimensions and grain
- OLTP vs OLAP
- Star schema
- Idempotent batch loads
Questions an interviewer may ask
Expect follow-ups like these once your high-level design is on the board. Have an answer, and a reason, for each:
- What is one row in your fact table, and where does the shipping fee live?
- A product moves from Toys to Games: what does last year's sales-by-category report show?
- An order is refunded two weeks after purchase: how does that appear in the warehouse?
- The nightly load fails halfway and is rerun: how do you avoid double-counting?
- How does an analyst get revenue for one region over the last 30 days without scanning everything?
Common mistakes to avoid
- No declared grain, so the shipping fee is repeated on every order line and revenue is overstated
- Running analytics queries against the production database
- Append-only daily loads that double-count after a rerun
- Overwriting a product's category so last year's report changes
- Incremental extracts that never see deleted or late-updated orders
- Mixing UTC and local dates so daily totals don't match finance
Concepts this round covers
- Fact table grain
- Star schema
- Incremental loads
- Warehousing
What a complete answer covers
A star schema with a declared fact grain and shared dimensions, an incremental and rerunnable daily load, and a plan for refunds, partitioning and data checks.
There is no single correct design. A strong answer meets the requirements at the stated scale and explains the trade-offs behind each choice. New to the format? Read how to prepare for a system design interview.