Skip to main content
A data app on a big fact table will time out if every filter re-queries the warehouse. The fix is an architecture — static-first — and the diagnostic prompts below are a reusable prop library for investigating any slow or wrong app. (Drawn from a real fix: an authorizations app that hung on every filter and, once static-first, filtered instantly.)

​
The failure mode — the “scan storm”

One “Apply filters” click fanning out into 15+ full scans of a multi-million-row fact table, each joined to several dimensions, nothing cached because filter combinations never repeat. The browser hangs. The bottleneck is the scans, not the payload — raising a payload limit doesn’t help, and parallelizing the scans just multiplies warehouse cost.

​
The fix — static-first snapshot

  • Pre-aggregate once, at refresh. Push the GROUP BY into the warehouse a single time into a small snapshot (a base metrics cube + breakdown tables + supporting series).
  • Filter in the browser. Every filter, sort, tab, and chart interaction runs against that snapshot — zero warehouse queries on routine use. Filters apply instantly, no Apply button, no spinner.
  • Show the snapshot timestamp, and serve the last good snapshot if a refresh fails.
  • Reserve live queries for genuinely un-cacheable detail (e.g. distinct counts under a custom filter) — one bounded, debounced query, never a fan-out.
  • Tune the client side too. Update charts in place instead of rebuilding them on every interaction, and make one optimized pass over the snapshot in the filter engine. Target: cached interactions land well within ~250 ms — instant to a human.

​
Which serving strategy — the decision rule

SituationStrategy
The aggregate fits in a shippable snapshotShip the snapshot, filter in-browser — the default above
The aggregate is too big to ship, but still ≪ the raw factBuild a pre-aggregated serving table in the warehouse; the app queries that, never the raw fact
Genuinely on-demand, un-cacheable detailOne bounded, debounced live query
Never: re-scan the raw fact per interaction · parallelize many full scans · treat a payload/row limit as the fix
Correctness trap: duplicate dimension rows double-count — A dimension with more than one row per key silently doubles every dollar and count the moment a filtered query joins it — and the default (unfiltered) view often doesn’t hit that join, so it looks fine until someone filters. De-duplicate dimensions before joining, and always verify a filtered slice, not just the landing screen.

​
Prop library — diagnostic prompts

Copy these when investigating an app. They go from symptom → cause → fix → proof.
Prompt
Prompt
Prompt
Prompt

​
Troubleshooting quick-reference

SymptomLikely causeFix
Filters hang / endless spinnerScan storm — every filter re-queries the factStatic-first snapshot; filter in-browser
Numbers double the moment you filterDimension with >1 row per key fans out on joinDe-duplicate the dimension at the source
Fine by default, wrong once filteredDefault view skips the offending joinAlways verify a filtered slice, not just the landing view
Distinct counts wrong under a filterSummed from a pre-aggregated cubeOne bounded, debounced live query for that count only
Slow only on first load / after refreshSnapshot rebuild is the costSchedule a daily refresh; serve the last good snapshot meanwhile
”Just raise the payload / row limit”Misdiagnosis — payload isn’t the bottleneckCut the scans, don’t ship more rows
State the tradeoffs — no silent caps — By design, breakdown tiles slice by a chosen subset of dimensions (to keep the snapshot small) and distinct counts under a custom filter use the one bounded query. Say so in the app’s notes — a silent cap reads as “we covered everything” when you didn’t.

​
✅ Checkpoint

  • No warehouse query fires on an ordinary filter (you counted)
  • The default view and one filtered slice both reconcile to the dollar
  • You can name which dimensions the breakdowns do and don’t slice by