Performance Tuning and Optimization
What candidates must know
Optimization questions resolve to a few levers. Warehouse sizing scales up (bigger warehouse, more credits per second, more memory for sorts and joins); multi-cluster warehouses scale out for concurrency. Caching has three layers: the result cache (account-wide, ~24 hours, invalidated by DML on the underlying tables), the local disk (SSD) cache held by a running warehouse, and metadata statistics used for pruning. Learn to read the Query Profile: operator-level time, partitions scanned versus total (pruning quality), bytes spilled to local and remote storage, and queueing versus execution time.
Traps
- Wrapping a filter column in a function or implicit cast defeats micro-partition pruning.
- Resizing a warehouse discards its local disk cache; suspending it does not, but a fresh warehouse starts cold.
- The result cache is bypassed when the query text differs, even trivially, and after any DML on the referenced tables.
- Remote spillage signals an undersized warehouse or an exploding join, not a bad query plan.
- Automatic clustering and materialized views consume credits; they are not free optimizations.
Rule of thumb
Look at the Query Profile in this order: pruning (partitions scanned), then spillage, then queueing. Fix pruning first with predicates and clustering; scale up only when spillage dominates; add clusters only when queueing dominates.
Worked example
A query scans all 40,000 partitions but returns few rows: the predicate uses TO_CHAR(event_ts) = '2024-01-01'. Rewrite as a range predicate on the raw column (event_ts >= ... AND event_ts < ...) and prune the table or add a clustering key on the filter column.