The Dashboard That Melts at 9 A.M.
A finance team publishes an AI/BI dashboard with nine tiles. Every tile reads gold.fct_gl_entry (~2.4 billion rows) filtered to the trailing 13 months; in isolation each tile scans roughly 180 GB and takes 40-70 seconds. The dashboard's scheduled refresh fires at 08:55 and the first viewers arrive at 09:00. The backend is a single shared Pro SQL warehouse, 2X-Small, max 1 cluster, auto-stop after 10 minutes of idle. Peak concurrency at 09:00 is about 12 users. Users report a spinning "starting warehouse" message, then tiles that are correct but slow. Query history shows the refresh job taking 11 minutes with several queries sitting in a queued state. Finance has approved at most a 2x increase in compute spend and wants evidence before approving more.
1. Identify the distinct causes of the poor 09:00 experience and rank them by how much each contributes to the 11-minute refresh and the slow tiles.
Read the worked answer
Cause 1 - fixed, small compute with no concurrency headroom (largest contributor). A 2X-Small, single-cluster warehouse has one fixed set of task slots. Twelve roughly-simultaneous queries (nine tiles plus arrivals) cannot execute in parallel; they queue. Nine tiles at 40-70 s each, serialized plus queueing overhead, is a good fit for the observed 11 minutes. The queueing, not the per-query speed, is the dominant cost.
Cause 2 - cold start inside the refresh window. Auto-stop at 10 minutes means the warehouse is stopped overnight. The 08:55 refresh pays cluster startup at the worst possible moment, then the 09:00 users inherit whatever is still running. Startup time is pure loss here.
Cause 3 - cache is structurally unusable. Two effects: (a) if tiles use current_date() or now() in the date filter, the query text/predicate changes every run, so result-cache entries never match a later run; (b) different tiles have different predicates, so they cannot share cached results with each other. Caching cannot rescue a workload whose predicates are unique per tile and per day.
Diagnosis to verify, not assume: pull queue time separately from execution time from system.query.history. If total queue time is a large fraction of wall-clock, Cause 1 is confirmed. If the first query alone shows a multi-minute gap before execution begins, Cause 2 is confirmed. Do not accept "the warehouse is too small" as a conclusion until the split is visible.
2. Propose a concrete compute and query strategy that fits the 2x spend ceiling. State explicitly what you would refuse to do and why.
Read the worked answer
Rank the fixes by cost-per-second-saved, and start with the one that is not a sizing change.
- Precompute the tiles (cheapest, biggest win). All nine tiles are trailing-13-month slices of the same fact. Materialize a 13-month aggregate at a grain the tiles can consume (for example daily by account by cost center) refreshed once before 08:55, and point the tiles at it. Each tile then scans megabytes instead of 180 GB. This removes most of the workload instead of buying compute to serve it.
- Fix the cache-killing predicates. Change
current_date()to a parameterized/truncated date so repeated refreshes inside a day produce identical query text and can hit the result cache. - Move to a serverless SQL warehouse sized Small or Medium. Serverless scales concurrency automatically instead of forcing you to guess min/max clusters, scales to zero so idle cost stays low, and gives you predictive I/O on the large scans that remain. Set auto-stop short; on serverless it costs you little and nothing is lost by stopping.
- If Pro must be retained: set min 1 / max 3 clusters on a Small, and schedule a trivial
SELECT 1at 08:50 to warm the warehouse outside the refresh window.
What I would refuse to do: jump to a 4X-Large. It multiplies cost per query and still leaves cold-start and serialization behaviour untouched, so users would still see the starting-warehouse message, just on a more expensive machine. Scaling is the right answer only after queue time and scan volume are under control.
Budget check: the aggregate table plus a Small serverless warehouse is comfortably inside 2x, and the leftover headroom should be held in reserve rather than spent up front.
3. How would you prove after two weeks that the problem is actually solved, and what would make you reverse a decision?
Read the worked answer
Measure the split of the user-visible latency, not just the average.
- From
system.query.history: p50/p95 queue time vs execution time per tile, and count of queries queued. Success looks like queue time approaching zero, not merely execution time dropping. - Warehouse start counts and durations from warehouse event data, to confirm the refresh no longer triggers a cold start.
- Bytes read per tile before vs after the aggregate table. This is the strongest single number: if bytes read did not fall by an order of magnitude, the tiles are still hitting the fact table and something is misconfigured.
- Cached-read share, to confirm the predicate change actually produces repeatable query text.
Reconcile, don't trust. Run the old detail query and the new aggregate-backed tile for the same three months and diff the totals before declaring victory. A fast wrong number is worse than a slow right one, and a silent fan-out or a dropped late-arriving journal entry is the classic failure mode of pre-aggregation.
Triggers to reverse course: (a) finance adds a dimension that the aggregate grain cannot support, so tiles fall back to the fact table and bytes read climb again; (b) the aggregate refresh job starts taking longer than the user-facing dashboard it replaced, meaning you moved the queue rather than removed it; (c) queue time is still material at 09:00, which means concurrency, not scan volume, was the real constraint and the warehouse sizing should be revisited.