Warehouse Performance
Fresh, fast and affordable — pick the lever set
Warehouse performance is a three-way balance of freshness, cost and speed. Partitioning, clustering, pre-aggregation, result caching and concurrency sizing are the tools that keep BI both fast and affordable.
What you will learn
- Sequence performance effort: prune, cluster, pre-aggregate, cache
- Read a warehouse profile to find the expensive pattern
- Size compute and concurrency around real dashboard demand
What is this?
Warehouse performance covers how quickly and cheaply queries return: pruning partitions, clustering sort-order, pre-aggregating repeated grains, caching repeated results, and sizing compute/concurrency. The discipline is proving which lever changes measured cost, not guessing.
Why do you need it?
- Dashboard latency is product quality; warehouse spend is a line item leaders audit.
- The same report can cost 10x depending on how it's shaped.
- Cache alone hides problems; structural fixes hide nothing and scale.
- Concurrency planning prevents month-end dashboard collapses.
Mental model
A library's SORTED SHELVES and a returns desk
Warehouse performance
Sorted shelves (clustering) plus aisle signs (partitioning) plus a returns desk (cache) mean regulars get served immediately. Re-shelving efficiently (pre-aggregation) keeps the common requests off the full stacks.
How it works
- Partitioning by date limits scans via WHERE to only those partitions.
- Clustering orders storage so scans read contiguous, relevant blocks.
- Pre-aggregated tables serve hot grains, trading freshness for cost.
- Result caches return identical recent queries without re-scanning.
- Warehouse size and concurrency trade latency for cost per query.
- Profiles/EXPLAIN reveal bytes scanned, shuffle, and which join fed the bill.
The lever sequence for a slow dashboard.
In production
- A partition-by-day, cluster-by-store sales table makes the regional daily dashboard scan one small slice.
- A pre-aggregated daily revenue table serves 90% of report queries while raw truth stays for reconciliation.
- Result caching handles the 20 identical morning queries the exec team runs; warehouse auto-scaling absorbs the rest.
Best practices
- Profile before optimizing — optimize the measured spend, not the theory.
- Partition on the filter everyone uses; cluster on the second-fattest join key.
- Pre-aggregate grains with a stable definition and reconciliations back to raw.
- Set freshness SLAs per dataset; not everything needs minute-level updates.
Common mistakes
- Clustering on a column nobody filters — sorting that costs on every write.
- Over-partitioning into millions of tiny parts that slow scans.
- Paying warehouse compute at 'always-on' for occasional dashboards.
- Caching a query that changes every minute (cache hit rate will be zero).
Common issues
Monthly report scans years of historyNo partition predicate or partition not on the filter
Partition by date; enforce WHERE on the partition key in the report.
Warehouse bill grew 5x but queries unchangedConcurrency/size scaled up while report queries went uncached or unpartitioned
Re-shape the costly queries first, then right-size the warehouse.
Debugging tips
- High bill week? Profile the top queries by cost and scan bytes.
- Slow at 8am? Check concurrency and whether refresh jobs are racing readers.
- Cache useless? Look at whether the query text is stable and repeatable.
Interview checkpoint
Quick quiz
The most impactful first step for a slow, expensive dashboard is:
Result caching makes a poorly-shaped query cost nothing forever.