System Design · medium · Reported at Shopify
Design a data warehouse schema for an e-commerce company.
Asked in Analytics Engineer interviews, in the System Design round.
Short answer
Star schema: fact_orders plus dimensions (user, product, date, geo), with slowly changing dimensions where attributes drift.
How to answer it
Requirements first
Ask what questions it has to answer before choosing a shape. Revenue by category and month, repeat purchase rate, cohort retention, inventory turns: those determine the grain and the dimensions. Then ask about history, because "what segment was this customer in when they ordered" decides whether you need Type 2 dimensions.
Architecture
A star schema, as in the diagram above. One fact table at the centre holding the measures and the foreign keys, with dimension tables around it.
fact_order_items grain: ONE ROW PER ORDER LINE
order_id, line_id
customer_key, product_key, date_key, store_key
quantity, unit_price, discount, net_amount
dim_customer (Type 2: valid_from, valid_to, is_current)
dim_product (Type 2 if price or category changes matter)
dim_date (calendar attributes, fiscal periods, holiday flags)
dim_storeGrain is the first decision
Declare it in one sentence and write it down: one row per order line. Everything else follows. Mixing grains in a fact table, an order-level shipping cost stored on every line, is the single most common way these designs break, because the shipping cost then gets counted once per line.
If you genuinely need both, use two fact tables at different grains rather than one confused one.
Fact table types
Transaction one row per event. Orders, clicks. The default.
Periodic one row per entity per period. Daily inventory balance.
Accumulating one row per process, updated as it progresses. Order to
delivery, with milestone timestamps.Tradeoffs
- Star against snowflake: a snowflake normalizes dimensions into sub-tables, which saves storage and costs joins. On a modern warehouse storage is cheap and analyst clarity is not, so star usually wins.
- Surrogate keys over natural keys in the fact table, so a Type 2 dimension can version without the fact needing to change.
- Denormalise deliberately, not accidentally, and record why.
Key points
- Declare the grain in one sentence before anything else.
- One fact table, dimensions joined on a single key each. Facts hold numbers, dimensions hold words.
- Type 2 dimensions where history matters, joined with a range condition on the order timestamp.
- Do not mix grains. Use a second fact table instead.
- A date dimension is worth building even though a date column exists; fiscal periods and holiday flags live there.
FAQ
- Star or One Big Table? OBT is simpler for a single use case and duplicates enormously across many. Star scales to more consumers.
- Where does dbt fit? Staging models clean each source, marts build these facts and dimensions.
- Do I need surrogate keys? For Type 2 dimensions, yes, because the natural key is no longer unique once versions exist.
The 30-second answer
I would start by asking what questions it has to answer and whether history matters, because those decide the grain and whether the dimensions need to be Type 2.
Then a star schema: one fact table for order lines holding the foreign keys and the measures, quantity, price and net amount, with dimensions for customer, product, date and store around it.
The first thing I would write down is the grain, one row per order line, because everything follows from it. The classic failure is mixing grains, putting an order-level shipping cost on every line, which then gets counted once per line. If both grains are genuinely needed that is two fact tables, not one. And where customer attributes change over time I would make that dimension Type 2 and join on the order timestamp.
In the interview
- What they are checking: grain, SCD2 on the customer, and additive measures.
- Common mistake: one wide "orders" table with customer attributes copied onto it, which cannot answer what the customer's segment was last year.