Slow dashboard querying a large fact table: first three checks?
This tests systematic diagnosis of fact-table query latency. A strong answer checks the execution plan and indexing, evaluates partitioning and data model fit, and inspects caching or pre-aggregation.
What's really being asked
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.
The full answer
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.
The mistakes people make
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.
What usually comes next
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?
A 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.
Interview question
Which action should be your first priority when triaging a slow dashboard query against a large fact table?
- a.Denormalize the star schema into a single wide table to eliminate join overhead
- b.Inspect the BI tool's caching layer and semantic-layer configuration
- c.Add compute nodes to the warehouse to reduce query latency
- d.Review the query execution plan and indexing strategy for full table scans or missing indexesCorrect
Why? this is the answer
Systematic triage starts with profiling the execution plan and indexes to find cheap fixes like missing indexes or partition pruning failures. Scaling compute or denormalizing without this diagnosis is premature and costly, while caching should be evaluated only after the underlying query cost is understood.
Just read this? Test yourself on what you have been reading.
Read the original → multishoring.com
- #data warehouse
- #query optimization
- #performance tuning
- #analytics engineering
- #bi dashboards
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on data warehouse — each one lists the topics its interview covers.
See open roles