What you will be able to do
- Find spilling, exploding joins and poor pruning in a Query Profile and pick the fix that matches each
- Use EXPLAIN to see a query's plan and partition estimate without running the query
- Write filters that let Snowflake skip micro-partitions using their min/max metadata
- Use clustering depth and query behaviour to decide whether a clustering key is worth what it costs
Key concept
Micro-partition metadata — For every micro-partition, Snowflake records the range of values in each column. Pruning, clustering depth and the pruning statistics in a profile all come from comparing a query's filters with those ranges.
1.Reading the Query Profile for spilling and exploding joins
Tuning a slow query starts with its Query Profile, which you open from the query in Query History. The profile shows the plan as a tree of operators such as TableScan, Filter, Join and Aggregate, and each operator has its own statistics. Look for the operator that consumed the most time, then check what its numbers say. Three patterns explain most slow queries: spilling, exploding joins and poor pruning. Pruning has its own section further down.
Spilling happens when an operator's intermediate results do not fit in the warehouse's memory. Duplicate elimination over a very large data set is a typical cause. The engine first writes the overflow to local disk. If local disk also runs out, it writes to remote storage, and remote spilling is much slower. The profile shows both amounts as Bytes spilled to local storage and Bytes spilled to remote storage.
The fix is more memory for that one query. Use a larger warehouse, which gives the operation more memory and local disk, or process the data in smaller batches. Settings that help other problems do nothing here. A longer auto-suspend keeps cached table data, but the cache adds no memory. Search optimization and better pruning cut how much data is read, but an aggregation that has to group a huge input still needs space to hold it.
SELECT query_id, SUBSTR(query_text, 1, 50) partial_query_text, user_name, warehouse_name,
bytes_spilled_to_local_storage, bytes_spilled_to_remote_storage
FROM snowflake.account_usage.query_history
WHERE (bytes_spilled_to_local_storage > 0
OR bytes_spilled_to_remote_storage > 0 )
AND start_time::date > dateadd('days', -45, current_date)
ORDER BY bytes_spilled_to_remote_storage, bytes_spilled_to_local_storage DESC
LIMIT 10;An exploding join comes from a missing join condition, which produces a Cartesian product, or from a condition where one row matches many rows on the other side. In the profile, the Join operator outputs far more records than it reads in, often by orders of magnitude, and it usually takes a large share of the time. Check the join keys before you resize anything. A bigger warehouse just runs the wrong join faster.
Two cheaper problems are worth checking in the same pass. A UnionAll operator with an extra Aggregate on top means the query used UNION where UNION ALL would do, and is paying for duplicate removal it doesn't need. In a star-schema BI query, look for a TableScan of a dimension that contributes no columns to the result. RELY and join elimination lets the optimizer drop that kind of join without rewriting the query.
Checkpoint 1 of 7· Check yourself
A nightly query has slowed down. In its Query Profile, the Aggregate operator takes most of the time and shows a large value for Bytes spilled to remote storage. Which change goes straight at the cause?
Spilling means the operator's intermediate results did not fit in memory. Remote spilling means local disk ran out too. A larger warehouse adds memory and local disk, and smaller batches shrink what has to be held. The warehouse cache only holds table data, so it adds no memory. Search optimization and pruning cut how much data is read, but the aggregation still needs room to group its input.
“A running warehouse maintains a cache of table data that can be accessed by queries running on the same warehouse.”Source: docs.snowflake.com
Checkpoint 2 of 7· Exam question
An analyst runs a nightly aggregation on a 4 TB sales_fact table using a MEDIUM warehouse. The Query Profile shows the Aggregate and Join nodes with large values for 'Bytes spilled to remote storage', and the query runs for 50 minutes. Which action is the most direct fix for the bottleneck?
Correct answer: B — Resize the warehouse to a larger size so each query gets more memory and local SSD, reducing spilling to remote storage
- A. The local disk cache holds table data that was read, not operator intermediate results. Keeping it longer does not stop an aggregation from spilling.
- B. A larger warehouse provides more memory and local disk per query, so intermediate results stay off remote storage. Remote spilling is the slowest tier and is the classic signal to scale up.
- C. Search optimization speeds up selective lookups and does not reduce memory pressure in aggregate or join operators. The spilling would remain.
- D. Multi-cluster warehouses add clusters for concurrent queries; a single query still runs on one cluster. Adding clusters does not give this query more memory.
2.Previewing a plan with EXPLAIN
EXPLAIN [ USING { TABULAR | JSON | TEXT } ] <statement> returns the logical plan for a statement without running it. Each row is an operation, such as TableScan, Filter or Join, with its parent operators and expressions. TABULAR is the default format. It uses no compute credits, but compiling the statement consumes Cloud Services credits, as other metadata operations do. If no warehouse is in use, Snowflake builds the plan as if for an XSMALL warehouse, and the plan can differ by warehouse size.
For performance work, the partition columns matter most. partitionsTotal is the number of micro-partitions in the object, and partitionsAssigned is how many remain after compile-time pruning. A filter that leaves partitionsAssigned close to partitionsTotal will scan the whole table, and you can see that before running anything. Treat the assigned counts as an upper bound: runtime optimizations such as join pruning can cut the scan further. The plan is logical, so operations may not run in the order shown.
Checkpoint 3 of 7· Check yourself
You run EXPLAIN on a report query that filters for a single day in a table holding a year of data. The plan shows partitionsTotal = 12,000 and partitionsAssigned = 11,900. What should you conclude before running the query?
partitionsAssigned is the number of micro-partitions left after compile-time pruning. A value close to partitionsTotal for a query that selects about 1/365 of the data means pruning is poor. The count is only an upper bound, because runtime optimizations such as join pruning can cut the scan further. EXPLAIN does not show spilling.
“the closer the ratio of scanned micro-partitions and columnar data is to the ratio of actual data selected, the more efficient is the pruning performed”Source: docs.snowflake.com
Sources3
3.How micro-partition pruning works and how filters defeat it
Snowflake splits every table into micro-partitions automatically, based on the order the rows were loaded. Each one holds 50 to 500 MB of uncompressed data, stored by column. For each micro-partition, Snowflake records the range of values in every column and the number of distinct values. When a filter can be compared with those ranges, Snowflake skips every micro-partition whose range cannot contain a match, then reads only the columns the query references in the ones that are left.
Ideally, a query that selects 10% of a column's value range scans about 10% of the micro-partitions. Take a year of data with date and hour columns: a query for one hour would ideally scan 1/8760th of the table. In the Query Profile, compare Partitions scanned with Partitions total on the TableScan. If scanned is a small fraction of total, pruning worked. If the two are close, it didn't.
Pruning needs two things. First, the storage order has to match the filter. If a date filter hits partitions that each cover the whole year, every range matches and nothing is skipped. Second, the predicate has to be a form that can be checked against stored ranges. Not every form qualifies. For example, Snowflake does not prune on a predicate that contains a subquery, even when the subquery returns a constant.
A worked case: an events table is well clustered on event_date, yet a dashboard filter TO_VARCHAR(event_date, 'YYYY-MM') = '2026-09' scans every partition. The stored min/max values describe event_date itself. A range on the bare column, event_date >= '2026-09-01' AND event_date < '2026-10-01', can be checked directly against them. Clustering guidance makes the same point: an expression only keeps pruning working if it preserves the column's original order. Adding ORDER BY or a LIMIT doesn't help, because neither changes how the filter compares with the stored ranges.
Checkpoint 4 of 7· Check yourself
An events table is already well clustered on event_date. A dashboard filters with TO_VARCHAR(event_date, 'YYYY-MM') = '2026-09', and its TableScan shows Partitions scanned almost equal to Partitions total. Which change lets Snowflake skip the other months' micro-partitions?
The stored min/max ranges describe event_date itself. A range on the bare column can be compared with them directly, but the TO_VARCHAR expression does not keep the column's order, so nothing is skipped. ORDER BY and LIMIT do not change how the filter compares with the ranges. Snowflake does not prune on predicates that contain subqueries, even when they return constants. The table is already well clustered, so a clustering key would not fix the predicate.
“The expression should preserve the original ordering of the column so that the minimum and maximum values in each partition still enable pruning.”Source: docs.snowflake.com
Checkpoint 5 of 7· Exam question
A dashboard query filters a 6 billion row events table that is well clustered on event_date:
SELECT COUNT(*) FROM events
WHERE TO_VARCHAR(event_date, 'YYYY-MM') = '2026-09';
Query Profile shows 'Partitions scanned' equal to 'Partitions total'. What change restores partition pruning?
Correct answer: D — Rewrite the predicate as a range such as event_date >= '2026-09-01' AND event_date < '2026-10-01' on the bare column
- A. Time Travel retention does not change pruning of active partitions. The slow scan is caused by the function wrapped around the column.
- B. ORDER BY adds a sort step and does not influence which partitions are read. Pruning depends only on filter predicates against column metadata.
- C. A LIMIT does not provide the metadata comparison pruning needs, and a COUNT still has to read all qualifying rows. The scan stays broad.
- D. Pruning compares predicate ranges with micro-partition min/max metadata for the column. A direct range on the unwrapped column lets Snowflake skip partitions outside September.
4.Clustering depth and when a clustering key pays
Clustering depth measures how much micro-partitions overlap on a set of columns. Snowflake tracks the total number of micro-partitions, how many of them overlap, and how deep the overlap goes. The average depth is 1 or greater for a populated table, and 0 for an empty one. The smaller the average depth, the better clustered the table. When no ranges overlap at all, the micro-partitions are in a constant state and clustering cannot improve them. A large table rarely reaches that state, and it doesn't need to for queries to be fast.
Depth goes up over time. Snowflake usually produces well-clustered data, because micro-partitions follow load order, so a table loaded strictly in ascending date order starts out clustered on that date. Extensive DML on a very large table breaks that order, and the overlap grows. Two signals together suggest a clustering key would help: a large average depth on the filter column, and queries that scan most partitions even though they filter on a narrow range. Depth is only a rough guide. If queries perform as expected, the table is probably clustered well enough.
A clustering key names up to a few columns or expressions that Snowflake keeps co-located in the same micro-partitions. You set it with CREATE TABLE or ALTER TABLE, and you can change or drop it at any time. Automatic Clustering then maintains the order in the background, but only when the table would benefit. That maintenance consumes credits, and the more often the table changes, the more it costs. The best candidates are multi-terabyte tables that are queried often and changed rarely, where most queries filter or sort on the same few columns. On tables with heavy DML, group the DML into large, infrequent batches. Before you add a key, measure a representative set of queries so you have a baseline to compare against.
| Candidate | Example | Verdict |
|---|---|---|
| Column used in selective range filters | invoice_date on a fact table | First choice |
| Columns used in join predicates | table2.column_A = table1.column_B | Add if there is room in the key |
| Very low cardinality | IS_NEW_CUSTOMER (Boolean) | Minimal pruning |
| Very high cardinality | Nanosecond timestamps | Use an order-preserving expression on the column instead |
Keep keys short. For most tables, Snowflake recommends no more than 3 or 4 columns or expressions, because beyond that cost tends to rise faster than benefit. Cardinality has two limits. The column needs enough distinct values for pruning to be useful, but few enough that Snowflake can group rows into the same micro-partitions.
Checkpoint 6 of 7· Match them up
Match each clustering-depth reading to what it tells you about the table
Tap a term, then the definition that fits it.
Lower depth means less overlap, so the table is better clustered. A depth near 1 or a constant state is not a reason to add a key. Only a large depth, together with narrow filters that still scan most partitions, suggests a key would help. An empty table has a depth of 0.
“The smaller the average depth, the better clustered the table is with regards to the specified columns.”Source: docs.snowflake.com
Checkpoint 7 of 7· Exam question
A data engineer evaluates whether orders (about 8 TB, filtered mostly by order_date) needs a clustering key. They run SELECT SYSTEM$CLUSTERING_INFORMATION('orders', '(order_date)'). Which TWO findings suggest that clustering on order_date would be beneficial? Select TWO.(Select 2)
Correct answers: A, D — The average_depth value is large, which means many micro-partitions overlap on the same range of order_date values; Most queries scan a large share of partitions in Query Profile although they filter on a narrow order_date range
- A. A high average depth means partitions overlap, so a date filter must read many of them. Clustering would reduce overlap and improve pruning.
- B. A histogram concentrated at depth one means ranges rarely overlap. The table is already well clustered on this column.
- C. Constant partitions need no further reclustering because each holds a single key range. A high count points to a table that is already well clustered.
- D. A high ratio of partitions scanned to total despite a narrow filter shows poor pruning. A clustering key aligned to the filter column is the standard remedy.
- E. Strictly ordered loading already gives natural clustering. Such a table prunes well and a clustering key would only add maintenance cost.
Sources
(6)
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“For such queries, the Join operator produces significantly (often by orders of magnitude) more tuples than it consumes.”
↩︎ Reading the Query Profile for spilling and exploding joins“If the local disk space is not sufficient, the spilled data is then saved to remote disks.”
↩︎ Reading the Query Profile for spilling and exploding joins“A common mistake is to use UNION when the UNION ALL semantics are sufficient.”
↩︎ Reading the Query Profile for spilling and exploding joins“If the former is a small fraction of the latter, pruning is efficient. If not, the pruning did not have an effect.”
↩︎ How micro-partition pruning works and how filters defeat it“However, for this to have an effect, the data storage order needs to be correlated with the query filter attributes.”
↩︎ How micro-partition pruning works and how filters defeat it“Using a larger warehouse (effectively increasing the available memory/local disk space for the operation), and/or”
↩︎ Exam trap 1 - 2.
“If the query requires even more memory, it spills onto remote cloud-provider storage, which results in even worse performance.”
↩︎ Reading the Query Profile for spilling and exploding joins - 3.
“The assignedPartitions and assignedBytes values are upper bound estimates for query execution.”
↩︎ Previewing a plan with EXPLAIN“the number of partitions that might be scanned by the query.”
↩︎ Previewing a plan with EXPLAIN“EXPLAIN compiles the SQL statement, but does not execute it, so EXPLAIN does not require a running warehouse.”
↩︎ Prediction - 4.
“Snowflake does not prune micro-partitions based on a predicate with a subquery, even if the subquery results in a constant.”
↩︎ How micro-partition pruning works and how filters defeat it“When there is no overlap in the range of values across all micro-partitions, the micro-partitions are considered to be in a constant state”
↩︎ Clustering depth and when a clustering key pays“The range of values for each of the columns in the micro-partition.”
↩︎ Key concept“The smaller the average depth, the better clustered the table is with regards to the specified columns.”
↩︎ Exam trap 2“the closer the ratio of scanned micro-partitions and columnar data is to the ratio of actual data selected, the more efficient is the pruning performed”
↩︎ Checkpoint“The smaller the average depth, the better clustered the table is with regards to the specified columns.”
↩︎ Checkpoint - 5.
“The expression should preserve the original ordering of the column so that the minimum and maximum values in each partition still enable pruning.”
↩︎ How micro-partition pruning works and how filters defeat it“The clustering depth for the table is large.”
↩︎ Clustering depth and when a clustering key pays“clustering is generally most cost-effective for tables that are queried frequently and do not change frequently.”
↩︎ Clustering depth and when a clustering key pays“For most tables, Snowflake recommends a maximum of 3 or 4 columns (or expressions) per key.”
↩︎ Clustering depth and when a clustering key pays
Also cited
“A running warehouse maintains a cache of table data that can be accessed by queries running on the same warehouse.”
↩︎ Checkpoint