Speeding up a slow query once you know what's holding it back

Query performance in Snowflake is optimized primarily through virtual warehouse sizing/concurrency, clustering, caching, and understanding execution via Query Profile. Since Snowflake separates storage and compute, most tuning focuses on right-sizing compute and minimizing unnecessary data scanning rather than traditional indexing.

1 · Learn the must-know

• 1Query Profile visualizes the execution plan and highlights bottlenecks like exploding joins, data spillage to local/remote disk, or partition scanning inefficiencies.
• 2Micro-partition pruning is key to performance: clustering keys help Snowflake skip irrelevant micro-partitions, but over-clustering or poor key choice can increase reclustering costs without benefit.
• 3The Result Cache (24-hour, cross-session) returns instant results for identical repeated queries with no compute cost, while the Local Disk (warehouse) cache speeds up repeated scans on the same warehouse.
• 4Increasing warehouse size (scaling up) improves performance for large, complex, or data-intensive single queries, while adding clusters via multi-cluster warehouses (scaling out) addresses high concurrency/queuing, not raw query speed.
• 5Spilling to local or remote storage (visible in Query Profile as 'Bytes spilled') indicates a warehouse is undersized for the working set of a query and often signals a need to scale up rather than optimize SQL alone.
• 6Search Optimization Service improves performance for selective point-lookup queries on large tables with high-cardinality columns, which clustering alone may not efficiently support.

2 · Check your understanding