How to diagnose a slow dashboard query?

WHAT THIS TESTS: Your ability to methodically diagnose a performance problem that spans multiple layers of the data stack. Interviewers want to see a structured approach, not a random guess. They are evaluating your understanding of analytical query engines, data modeling trade-offs, and full-stack problem-solving.

A GOOD ANSWER COVERS: A three-tiered investigation, starting with the cheapest and most likely culprits. First, the query itself: obtain the exact SQL from the dashboard, run an EXPLAIN plan, and look for obvious anti-patterns like full table scans on a 10-billion-row table, inefficient joins, or functions in the WHERE clause that prevent predicate pushdown. Second, the data's physical structure: inspect the table's DDL. Is it partitioned by a common filter column like event_date? Is it clustered or sorted by high-cardinality columns used in joins or filters? An unpartitioned multi-terabyte table is a huge red flag. Third, the surrounding infrastructure: check for resource contention on the data warehouse (are other heavy queries running?), check the BI tool's configuration (is caching disabled?), and analyze the volume of data being sent to the client (is it trying to pull 5 million rows to render a single number?).

COMMON WRONG ANSWERS: The most common mistake is jumping to a solution without a diagnosis. For example, saying "I'd build a daily aggregate table" before you've even looked at the query. This suggests a lack of discipline. Another red flag is suggesting the most expensive solution first, like "We need to scale up the warehouse from an X-Large to a 2X-Large." This should be a last resort after query and data model optimizations have been exhausted. Finally, giving vague answers like "I'd optimize the query" without specifying how (e.g., by analyzing the query profile, rewriting a subquery as a CTE, etc.) shows a lack of depth.

LIKELY FOLLOW-UPS: "Let's say the query plan shows a full table scan, but the WHERE clause is on a partitioned column. What could be happening?" (This tests knowledge of partition key gotchas, e.g., wrapping the column in a function like DATE(ts)). "You decide to create an aggregate table. What are the trade-offs?" (This tests understanding of cost, data freshness, and maintenance overhead). "How would you balance the need for near real-time data with dashboard performance?"

ONE CONCRETE EXAMPLE: A dashboard filtering on the last 7 days is slow. You find the fact table has 50 billion rows and is partitioned by event_month, not event_date. A query for 7 days at the end of a month forces the engine to scan the entire month's partition (e.g., ~1.6 billion rows) instead of just 7 days' worth of data. The fix is to re-partition the table by event_date. This simple DDL change could reduce the data scanned by over 75% for this query, likely bringing load time from over two minutes to under ten seconds.