How do you optimize query performance in Snowflake (warehouse sizing, clustering, pruning, result cache)?

Snowflake query optimization covers four levers, each addressing a different root cause. Using the wrong lever wastes money without helping performance.

Warehouse sizing (scale up) helps when a single query is slow due to complexity — large sorts, multi-way joins, or spilling to disk. Check Query Profile for Bytes Spilled to Local/Remote Storage. Doubling the warehouse size doubles available memory and parallelism.

Multi-cluster scale-out helps when many users are queuing, not when individual queries are slow. Check Queue Time in Query History. Scaling out adds parallel clusters, each serving a different concurrent query.

Clustering keys help when a large table (1 TB+) is scanned with selective WHERE predicates and partition scanned / partition total is high in Query Profile. Defining a clustering key on the filter column co-locates relevant micro-partitions, reducing scan volume dramatically.

Result cache: make your dashboard SQL deterministic and idempotent. Standardize query text across users (no variable timestamps, no per-user LIMIT injections). Each reuse costs zero credits.

Additional optimizations:

• Select only needed columns — never SELECT * on wide tables.
• Push selective filters early in CTEs; avoid late filtering after expensive joins.
• Use UNION ALL instead of UNION when duplicates are already excluded.
• Cast VARIANT paths to typed columns to enable statistics-based pruning.
• Use SHOW TABLES / INFORMATION_SCHEMA metadata queries instead of scanning data for schema information.