System Design · medium
A dashboard is slow to load. Where do you look?
Asked in BI Developer interviews, in the System Design round.
Short answer
Usually the query, not the tool: too fine a grain pulled to the client, a live query where an extract would do, or unfiltered scans. Pre-aggregate in the warehouse to the grain the dashboard actually shows, push filters down, and cache or extract where freshness allows. Row-level detail behind a summary should load on demand, not upfront.
How to answer it
Isolate the layer first
A dashboard is three things stacked, and the fix is completely different in each. Establish which one before touching anything.
1. The query run it directly against the warehouse. If it takes
40 seconds there, the dashboard is not the problem
2. The transport a fast query and a slow dashboard means volume: the tool
is pulling a million rows to render a chart of twelve
3. The rendering many charts, each firing its own query, or a browser
struggling with an enormous table widgetTiming the query alone takes a minute and tells you which of the three you are in. Skipping that step is how people spend a day optimizing the wrong layer.
Common causes by layer
- Query: no partition filter, so a full scan; joins fanning out; a view stacked on a view; an aggregate computed at row level and summarized in the tool.
- Transport: returning raw rows instead of aggregates. A chart with twelve bars needs twelve rows, not a million.
- Rendering: twenty widgets each issuing a query on load; no caching; a table with fifty thousand rows the browser must lay out.
The usual fixes
- Pre-aggregate in the warehouse. The dashboard should read a small, purpose-built table.
- Return aggregates, not detail. Push the GROUP BY down.
- Partition and cluster on the filter columns people actually use, usually date.
- Cache, with a refresh cadence matching how fast the data really changes.
- Reduce widgets, or lazy-load below the fold.
- Set sensible default filters. A dashboard defaulting to all time scans everything on every load.
Key points
- Time the query alone first. It splits the problem in one step.
- A chart needs aggregates, not raw rows.
- A default of all time is the single most common self-inflicted cause.
- Pre-aggregated marts fix this more reliably than query tuning.
- Caching is legitimate: most dashboards do not need second-level freshness.
FAQ
- Should I cache? Usually yes, matched to the real update frequency. Hourly data does not need a live query per view.
- Why is the first load slow and later ones fast? Warehouse result caching. Test with the cache cold, or you optimize an illusion.
- BI extract or live connection? An extract is faster and staler. Choose deliberately by how fresh the data must be.
The 30-second answer
I would separate the three layers before changing anything: run the query directly against the warehouse and time it. If it is slow there, the dashboard is not the problem. If it is fast there but the page is slow, I am looking at how much data is being shipped, or at rendering.
On the query side the usual causes are a missing partition filter causing a full scan, views stacked on views, or a join fanning out. On the transport side it is almost always returning raw rows when the chart needs aggregates. On rendering it is twenty widgets each firing their own query.
The fixes I would reach for are pre-aggregating into a purpose-built table, pushing the group-by down so the tool receives twelve rows rather than a million, and setting a sensible default date filter, because defaulting to all time is the most common self-inflicted version of this.
In the interview
- What they are checking: grain and pre-aggregation, filter pushdown, and cache matched to refresh.
- Common mistake: blaming the BI tool and buying a faster plan for it.