Business analyst system design interview questions

As asked

The business wants flexible self-service reporting on sales. At a conceptual level, how would you model the data so non-technical users can slice it themselves?

Sample answer outline

Propose a star schema: a central fact table of sales transactions with measures such as quantity and value, surrounded by dimension tables for date, product, customer, and region. Define the grain of the fact table explicitly, since that decides what questions are answerable. Explain that conformed dimensions let users slice consistently and that this structure is what makes self-service tools intuitive. A strong answer talks grain, dimensions, and measures in plain business terms.

Expect these follow-ups

• Why does the grain of the fact table matter so much?
• How would you handle a customer whose attributes change over time?
• What is a conformed dimension and why does it help?

As asked

The leadership team wants a single dashboard to run the business from. How would you design what goes on it and how it is structured?

Sample answer outline

Start from the decisions leaders make, not the data available, and choose a small set of KPIs that map to strategic objectives. Structure it top down: headline metrics with trend and target first, then the ability to drill into drivers, with clear definitions and a stated refresh cadence. A strong answer resists cramming in every chart, designs for the few questions executives actually ask, and considers who maintains the underlying data feeds.

Expect these follow-ups

• How do you stop a dashboard becoming a wall of charts nobody reads?
• How do you show a metric against both target and trend in limited space?
• How do you handle metrics that refresh at different frequencies?

As asked

Sales data lives in a CRM, an ERP, and a spreadsheet. At a high level, how would you design a pipeline so a single trustworthy dashboard pulls from all three?

Sample answer outline

Lay out the stages: extract from each source on a schedule, land it in a staging area, transform and conform it into a common model with shared keys and definitions, then load it into a reporting layer the dashboard reads. Address data quality checks, handling of late or failed loads, matching customers across systems, and a clear single source of truth. A strong answer names where reconciliation happens and how the business is told when data is stale.

Expect these follow-ups

• How do you match the same customer across three systems with no shared key?
• What happens to the dashboard when one source fails to load overnight?
• Where in the pipeline do you enforce the agreed business definitions?