How would you diagnose a slow dashboard query?

WHAT THIS TESTS: Your ability to methodically troubleshoot a performance bottleneck in an analytics stack. Interviewers want to see a logical progression from the easiest, most localized fixes (the query) to more complex, structural changes (the data model or infrastructure). It shows seniority and cost-awareness by not jumping to the most expensive solution first.

A GOOD ANSWER COVERS: Three main areas, investigated in order. First, Smart Query Optimization and Caching: start by analyzing the query plan to find bottlenecks. Can parts of the query be rewritten? Is a results cache being used effectively or could one be implemented? Second, Strategic Indexing and Partitioning: check if the fact table is indexed on the columns used in JOINs and WHERE clauses. A two-minute load time suggests a full table scan might be happening. Is the table partitioned (e.g., by date) to reduce the amount of data scanned? Third, Data Warehouse Architecture: verify the underlying data model. A star schema, with a central fact table linked to dimension tables, is the go-to for fast reporting. Is the dashboard query joining tables in a way that defeats the schema's design?

COMMON WRONG ANSWERS: A major red flag is suggesting "scale the warehouse" as a first step. This is the most expensive and least targeted solution, signaling a lack of diagnostic skill. Another poor answer is giving a vague list like "check indexes" without specifying which indexes (e.g., on foreign keys in the fact table, or on columns in WHERE clauses) and why. Blaming the BI tool without evidence is also a weak response.

LIKELY FOLLOW-UPS: How would you decide between adding an index versus partitioning a table? If the query is already optimized and indexes are in place, what's next? What if the issue isn't the query but the volume of data being returned to the dashboard client, and how would you handle that?

ONE CONCRETE EXAMPLE: For a 2-minute query on a 5 billion row sales fact table, I'd first check the query plan. If I see a full table scan where the query only needs last month's data, that's the problem. The solution is to ensure the table is partitioned by month and the query's WHERE clause includes the partition key (e.g., WHERE sale_date >= '2023-10-01'). This could reduce the data scanned by over 95% and bring the query time down to seconds.