As asked
You are designing how raw orders data should be modelled for reporting. Walk me through how you would structure it for fast, reliable analysis.
Sample answer outline
Describe a star schema: a central fact table at the order or order line grain with foreign keys to conformed dimensions for customer, product, date, and store. Explain why this separates measures from descriptive attributes, keeps the grain explicit, and makes aggregation and slicing simple for both SQL and BI tools. Cover slowly changing dimensions for attributes that change over time and why grain decisions are the most important call.
Expect these follow-ups
- What grain would you choose for the fact table and why?
- How do you handle a customer who changes address over time?
- When would a wide denormalised table beat a star schema?
data-modellingstar-schemawarehouse