Data Modeling Questions
Part of the Data Analyst Roadmap
In short
"Design tables for a food delivery app" is a common interview question. They want to see you think about grain, keys and how analysts will query it, not just list columns.
'How would you design tables for X system?' — testing your ability to think through entities, relationships and keys, connecting back to Phase 3's ER diagram skills.
Think of it like this
Drawing a house blueprint
Before building, an architect decides where the rooms go, how they connect and which walls hold the weight. Changing it later is expensive. A data model is the blueprint for your tables: decide grain and links first, then add the details.
Watch it happen
A star schema for food orders
1/5Start with the grain: one row per item in an order. This is the fact table, full of numbers like quantity and amount.
The big ideas
What you need to know
State the grain first
Say exactly what one row means. Most interview mistakes come from mixing order-level and item-level data.
Facts and dimensions
Facts are events with numbers. Dimensions describe who, what, where and when. This is the star schema.
Keys and relationships
Explain primary keys, foreign keys and whether each link is one-to-many.
Handle changes over time
If a customer moves city, do old orders keep the old city? Mentioning slowly changing dimensions shows depth.
See it in code
Walk through the code
1-- Grain: one row per product in an order2CREATE TABLE fact_order_items (3 order_id BIGINT,4 line_no INT,5 date_key INT REFERENCES dim_date (date_key),6 customer_key INT REFERENCES dim_customer (customer_key),7 restaurant_key INT REFERENCES dim_restaurant (restaurant_key),8 product_key INT REFERENCES dim_product (product_key),9 quantity INT,10 amount NUMERIC(10, 2),11 PRIMARY KEY (order_id, line_no)12);1/4Say the grain out loud in the interview. It anchors every other decision.
A fact table that writes its grain at the top. Every dimension is a foreign key, and the measures are plain numbers.
Save yourself the pain
Common mistakes
Oops
Listing columns without saying what one row represents.
Do this instead
Open with the grain, then the facts, then the dimensions and keys.
Oops
Putting customer name and city on every order row as plain text.
Do this instead
Store them once in a customer dimension and link by key, so updates happen in one place.
Your mission
Model an app you use
Quick check
In a star schema for sales, where does the product's category belong?
Go deeper
- 1Practice sketching a schema for a simple, familiar system (e.g. a food delivery app).
- 2Talk through your choice of primary and foreign keys as you design.
- 3Learn to ask clarifying questions about scale and use case before designing.
More sql & technical at this stop
Live SQL Coding Questions
Query Optimization Questions
Stuck on this topic? Ask an Insider
Get 1:1 guidance from people who've walked this exact path — on the InsideEdge app.
