Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 38 ยท Warehouses and Performance

Micro-Partition Pruning and the Query Profile

Learn how Snowflake micro-partitions and min/max metadata let queries skip data, and how to read the Query Profile to find scans, spilling and bad joins.

Snowflake does not use indexes. Instead it splits every table into micro-partitions, small immutable files of roughly 50 to 500 MB of uncompressed data, stored in a columnar format. For each micro-partition Snowflake records metadata: the minimum and maximum value of every column, the number of distinct values and more. When you filter, Snowflake compares your condition with that metadata and skips every partition that cannot contain a match. This is pruning, and it is the main reason Snowflake can scan huge tables quickly.

How pruning works

Imagine an events table loaded day by day. Each micro-partition holds one or two days, so its event_date range is narrow:

Partition min event_date max event_date
P1 2025-01-01 2025-01-02
P2 2025-01-03 2025-01-04
P3 2025-01-05 2025-01-06
P4 2025-01-07 2025-01-08

The query WHERE event_date = '2025-01-05' only needs P3. Snowflake reads that one partition and ignores the other three. Because the partitions are columnar, it also reads only the columns you ask for.

Pruning depends on how well the data is ordered by the filter column. If each partition holds a random mixture of dates, every partition's range covers the whole year and nothing can be skipped.

Python Pruning simulator (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The same filter reads a small number of partitions when the data is naturally ordered and almost all of them when it is not. The data is identical; only its physical layout changed.

The same idea in SQL

The following example builds a partition-metadata table with plain SQL, and uses it the way Snowflake would to decide which partitions to read. It runs in SQLite and is a model of the technique.

SQL Partition metadata as a table (stand-in) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Compare the number of rows returned by the second query with total_partitions. Orders were inserted roughly in date order, so only a few partitions qualify.

What helps and what hurts pruning

  • Filter on the columns the table is ordered by. Date or timestamp columns on append-only data usually prune very well.
  • Use plain comparisons. WHERE event_date >= '2025-03-01' prunes. Wrapping the column in a function, such as WHERE TO_CHAR(event_date, 'YYYY-MM') = '2025-03', often hides it from the pruner.
  • Select only the columns you need. SELECT * reads every column of every surviving partition.
  • Wide ranges prune poorly. A filter that matches 60% of the partitions skips only 40%.
  • Cluster big tables on the filter column when the natural order is wrong (see clustering keys).

Reading the Query Profile

In Snowsight, open Query History, choose a query, and open the Query Profile. It draws the execution as a graph of operators (nodes), with the data flowing upward. Things to look for:

  1. The most expensive nodes. The profile shows the percentage of time per operator. Start with the biggest.
  2. TableScan statistics. "Partitions scanned" next to "Partitions total" is the pruning ratio. Scanning 90 of 100 partitions for a selective filter means pruning failed.
  3. Bytes spilled to local / remote storage. The operator ran out of memory. Remote spilling is very slow. Fix with a bigger warehouse or by reducing the data going into the join or sort.
  4. Joins that produce more rows than they take in. An "exploding join" usually means a join condition that misses a key, creating a near cross product.
  5. Percentage scanned from cache. Low values on a repeat query suggest the warehouse was suspended in between.
  6. Time breakdown: processing, local and remote disk I/O, network communication, synchronization, initialization.

You can also read the same numbers with SQL:

SQL
SELECT query_id,
       partitions_scanned,
       partitions_total,
       bytes_spilled_to_local_storage,
       bytes_spilled_to_remote_storage,
       total_elapsed_time / 1000 AS seconds
FROM snowflake.account_usage.query_history
WHERE query_text ILIKE '%events%'
ORDER BY start_time DESC
LIMIT 10;

And the plan before running anything:

SQL
EXPLAIN USING TEXT
SELECT region, SUM(amount) FROM sales WHERE sale_date >= '2025-03-01' GROUP BY region;

EXPLAIN shows partitionsAssigned and partitionsTotal without running the query.

A tuning routine

  1. Find the slow query in Query History.
  2. Open the profile and identify the heaviest node.
  3. If it is a scan: improve pruning (filters, clustering, fewer columns).
  4. If it spills: reduce rows earlier (filter before joining, aggregate before joining) or size up.
  5. If a join explodes: fix the join keys and check for duplicates on the "one" side.
  6. Re-run with the result cache disabled and compare.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does Snowflake store for each micro-partition that makes pruning possible?

    Per-column minimum and maximum values let Snowflake skip partitions whose range cannot match the filter.
  2. 2. Which filter is most likely to prevent pruning?

    Wrapping the column in a function hides the plain range from the pruner; compare the column directly.
  3. 3. The Query Profile shows 'Partitions scanned 98 / Partitions total 100' for a very selective filter. What does that mean?

    Almost no partitions were skipped, so the filter column is spread across partitions; consider clustering.
  4. 4. What does 'bytes spilled to remote storage' indicate?

    Spilling means the operator needed more memory than the warehouse had, which is slow, especially to remote storage.

Summary

  • Micro-partitions are small immutable columnar files with min/max metadata.
  • Pruning skips partitions whose range cannot match; it works best when data is ordered by the filter column.
  • Avoid functions on filter columns and SELECT *.
  • The Query Profile shows scan ratios, spilling and exploding joins; EXPLAIN previews the plan.

Next: Clustering keys