> ## Documentation Index
> Fetch the complete documentation index at: https://docs.textql.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Module 6 · Make It Fast & Troubleshoot

> 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 reusabl… (~25 min)

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

<table><tr><th>Situation</th><th>Strategy</th></tr>
<tr><td>The aggregate fits in a shippable snapshot</td><td>**Ship the snapshot, filter in-browser** — the default above</td></tr>
<tr><td>The aggregate is too big to ship, but still ≪ the raw fact</td><td>Build a **pre-aggregated serving table** in the warehouse; the app queries *that*, never the raw fact</td></tr>
<tr><td>Genuinely on-demand, un-cacheable detail</td><td>One bounded, debounced live query</td></tr>
<tr><td colspan="2">**Never:** re-scan the raw fact per interaction · parallelize many full scans · treat a payload/row limit as the fix</td></tr></table>

<Warning>
  **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.
</Warning>

## Prop library — diagnostic prompts

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

```text Prompt theme={null}
This data app is slow / times out when I apply a filter. Trace exactly what happens on one filter click: how many warehouse queries fire, against which tables, how many rows each scans, and what (if anything) is cached. Tell me whether the bottleneck is the scans or the payload — don't guess, show the query count.
```

```text Prompt theme={null}
Re-architect this app static-first: pre-aggregate the metrics once at refresh into an in-memory snapshot (base cube + breakdown tables + supporting series) and run all filter/sort/tab/chart interaction in the browser against it — no warehouse query on routine filtering. Apply filters instantly (drop the Apply button), show the snapshot timestamp, and fall back to the last good snapshot on refresh failure. Use at most one bounded, debounced live query for un-cacheable distinct counts under a custom filter.
```

```text Prompt theme={null}
Check every dimension this app joins for more than one row per key. For any that fan out, show me how much it inflates a filtered total, and de-duplicate at the source. Confirm a filtered total matches before and after the fix.
```

```text Prompt theme={null}
Reconcile this app: reproduce the default (unfiltered) headline numbers exactly, then pick one filtered slice (e.g. one region + one category + a rolling window) and match it to the dollar against a fresh query on the warehouse. Then tell me which dimensions the breakdown tiles do and don't slice by, so I know the tradeoffs.
```

## Troubleshooting quick-reference

<table>
  <tr><th>Symptom</th><th>Likely cause</th><th>Fix</th></tr>
  <tr><td>Filters hang / endless spinner</td><td>Scan storm — every filter re-queries the fact</td><td>Static-first snapshot; filter in-browser</td></tr>
  <tr><td>Numbers double the moment you filter</td><td>Dimension with >1 row per key fans out on join</td><td>De-duplicate the dimension at the source</td></tr>
  <tr><td>Fine by default, wrong once filtered</td><td>Default view skips the offending join</td><td>Always verify a filtered slice, not just the landing view</td></tr>
  <tr><td>Distinct counts wrong under a filter</td><td>Summed from a pre-aggregated cube</td><td>One bounded, debounced live query for that count only</td></tr>
  <tr><td>Slow only on first load / after refresh</td><td>Snapshot rebuild is the cost</td><td>Schedule a daily refresh; serve the last good snapshot meanwhile</td></tr>
  <tr><td>"Just raise the payload / row limit"</td><td>Misdiagnosis — payload isn't the bottleneck</td><td>Cut the scans, don't ship more rows</td></tr>
</table>

<Note>
  **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.
</Note>

### ✅ 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