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.

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.