Snowflake Performance Deep Dive: Storage Internals, Pruning and Query Tuning

Kumaran C.S

13 min read

·

Oct 20, 2026

--

Listen

Share

Why some queries scan 2% of a table and others scan 100%, and how to find and fix slow queries

Press enter or click to view image in full size

👉 Not a Medium member? Here’s a link for you!🌟

Two queries on the same 5 TB table:

• One returns in 2 seconds and reads 40 MB.
• The other takes 12 minutes and reads all 5 TB.

Same table, same warehouse. The difference is almost always about how much data Snowflake has to read, and then how efficiently it processes what it reads.

This post explains both halves, without hands-on exercises:

• Part 1: Storage internals and pruning: micro-partitions, metadata, clustering depth and overlap, clustering keys, Automatic Clustering cost, the Search Optimization Service and materialized views
• Part 2: Query tuning: the Query Profile, spilling, exploding joins, the three caches, EXPLAIN, the Query Acceleration Service, QUERY_HISTORY and Query Insights

Part 1: Storage Internals and Pruning

Micro-partitions

Every Snowflake table is stored as micro-partitions: immutable files, each holding roughly 50–500 MB of uncompressed data (much less once…