Slow dashboard querying a large fact table: first three checks?

WHAT THIS TESTS: Whether you can systematically triage a slow dashboard query against a large fact table without guessing. Interviewers want to see if you understand the full stack from the BI layer down to the data warehouse storage, and whether you prioritize cheap fixes like indexing and query rewriting before expensive infrastructure changes.

A GOOD ANSWER COVERS: First, investigate the query execution plan and indexing strategy. Look for full table scans, missing indexes on join keys or filter columns, and inefficient joins between the fact table and dimension tables. Second, examine the data model and partitioning. Confirm whether the schema follows a star schema design, whether the fact table is partitioned on commonly filtered columns like date, and whether partition pruning is actually happening. Third, inspect the BI layer and caching architecture. Check if the dashboard is running raw queries on every load, if materialized views or pre-aggregated rollups exist, and whether result caching or semantic-layer caching is configured. Optimization strategies should include adding targeted indexes, aligning partitions with query filters, rewriting queries to avoid selecting unnecessary columns, implementing materialized views or aggregation tables, and enabling caching at the database or BI tool level.

COMMON WRONG ANSWERS: Jumping straight to scaling the warehouse by adding nodes or CPUs without analyzing the execution plan. Suggesting to denormalize everything into a single wide table without understanding the query pattern. Proposing to rebuild the entire pipeline from scratch instead of profiling the specific bottleneck. Blaming the BI tool without checking the underlying SQL it generates.

LIKELY FOLLOW-UPS: How would you verify if the BI tool is generating inefficient SQL? What specific metrics would you look at in the query profiler? How do you decide between adding an index versus building a materialized view? When would you recommend changing the data model from snowflake to star schema? How do you handle performance when users demand real-time data on a multi-terabyte fact table?

ONE CONCRETE EXAMPLE: Imagine a sales dashboard that loads a two-year order history fact table on every refresh. The execution plan shows a sequential scan because the date filter is applied as a function on the timestamp column, preventing partition pruning. The fix involves rewriting the filter to use the bare partition key column, adding a composite index on date and region, and creating a nightly materialized view that pre-aggregates monthly sales by region. Dashboard load time drops from two minutes to under three seconds without increasing cluster size.

Source: multishoring.com