Data analyst system design interview questions

As asked

You are designing how raw orders data should be modeled 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?

As asked

Design a pipeline that turns raw source tables into a trusted weekly executive report, including how you would handle freshness and broken data.

Sample answer outline

Lay out the stages: ingestion into a warehouse, transformation into clean modeled tables with a tool such as dbt, tested aggregate tables, and a BI layer on top. Add data quality tests for nulls, row counts, and freshness that block the report if they fail, plus alerting so a broken upstream feed is caught before the executive sees a wrong number. Document lineage so any figure can be traced to source.

Expect these follow-ups

• How do you stop a broken upstream feed from silently shipping?
• What tests would you run before the report is published?
• How do you communicate when the report is delayed by a data issue?