Star schema for ~2M orders/day with facts (orders, clicks, inventory) and SCD Type 2 dimensions.
system-designdimensional-modelingstar-schemascd
Start here
The problem in plain words
An online shop wants a data warehouse so analysts can answer questions like "revenue by product category, by day". The first attempt often puts everything into one wide table: the order, its items, the customer, the product. It looks convenient until someone adds up revenue and the total is twice what it should be.
That happens when two different things share a table. If an order has two items and the order total is stored on each item row, summing the totals counts the order twice. The fix is to decide, before building anything, what exactly one row means. That decision is called the grain.
Star schema is a way of organising a warehouse around that decision. In the middle sits a fact table of things that happened, and around it sit small dimension tables describing who, what, where and when. Drawn on paper, it looks like a star.
Words you will meet
Fact table
A table of events or measurements, such as one row per item sold, holding numbers like quantity and amount.
Dimension table
A table describing things, such as customers, products, stores and dates, holding labels analysts filter and group by.
Grain
What exactly one row of a fact table represents, written in one sentence, such as "one row per order line".
Measure
A number you add up or average, such as quantity or revenue.
Natural key
The identifier from the source system, such as a SKU or customer id.
Surrogate key
A new, meaningless number the warehouse assigns to each dimension row. Facts point to it.
Star schema
A fact table joined to several dimension tables in a star shape.
SCD Type 2
A way of keeping history in a dimension by adding a new row each time a tracked attribute changes.
Degenerate dimension
An identifier, such as an order id, kept in the fact table because it has no attributes of its own.
Rollup
A summarised version of very large data, such as clicks per product per day.
Why mixing order and item rows double-counts
Press Play to watch it move
Each arrow is one thing happening. Play it step by step, or read the list below.
1.Order 100 has two item lines. The mixed table stores the order total, 100, on each of the two rows.
2.The dashboard sums the column and gets 200 for an order worth 100. Every report is quietly wrong.
The numbers, as things you can picture
About 2 million orders a dayWith several items each, that is many millions of item rows a day. Plenty, but a warehouse handles it comfortably.
Clicks are far more numerous than ordersClicks arrive at a much higher volume, so they are usually kept in a separate place or summarised heavily.
What the interviewer wants
Model the shop's data as facts and dimensions with a clear grain for each, keep customer and product history correct over time, and explain how clicks relate to orders without mixing their grains.
The interview setup
Design a data warehouse for mid-size e-commerce (2M orders/day). Use a star schema with facts (orders, clicks, inventory) and dimensions (customers, products, dates). Handle SCD Type 2 for customer and product.
This interview tests dimensional modeling judgment: grain first, then keys, then history. Tool choice is secondary.
Star schema
A large fact table of additive measurements sits in the center, surrounded by dimension tables that describe who, what, where, when. Queries join facts to dims for slices such as revenue by category by day.
Grain
Grain is the meaning of one fact row in one sentence. "1 row per order line per order" is a grain. If you mix order headers and lines in one table, dashboards double-count.
Surrogate keys
Warehouse keys (surrogate integers/hashes) stay stable even when source natural keys change or recycle. Dimensions own them. Facts store them.
SCD Type 2
When a tracked attribute changes, insert a new dimension row with validity interval. Facts keep the surrogate key that was active at event time so history remains correct.
Clicks versus orders
Clicks arrive at much higher volume and different grain. Dumping raw clicks beside order facts without rollup is a common modeling mistake.
The expanded problem
Define grain for each fact
Design core dimensions with surrogate keys
Implement SCD2 for customer and product
Support BI queries such as revenue by category by day
Relate clicks to orders carefully
dim_date
dim_customer (SCD2) \
dim_product (SCD2) +--> fact_order_item (grain: one line per order line)
dim_store /
What good looks like
Say grain before columns
Separate facts for different grains
SCD2 example with valid_from/valid_to
Caution on raw click grain
Partition facts by date
Clarifying questions
Order fact grain: header versus line item?
Do clicks need user-level detail forever?
Which attributes need SCD2 versus Type 1?
Timezone for business dates?
Out of scope
Picking Redshift vs BigQuery as religion
Building the entire semantic layer UI
Real-time inventory microservices deep dive
Clarifying questions strategy
Ask questions that change grain, retention, SLA, or cost. If told to decide, state assumptions explicitly and continue.
What good looks like verbally
Narrate why stages exist. Name atomic publish. Name how reruns stay safe. Mention what is out of scope.
Scope control
Batch interviews reward candidates who protect the critical morning path and park nice-to-haves. Say what can wait until after the SLA table lands.