Static and Dynamic Partition Pruning in Snowflake

One key to performance in Snowflake is using partition pruning to read as little data as possible. I’ve previously written about what partition pruning is in Snowflake, but there are really two different types of partition pruning.

Static Partition Pruning

Static partition pruning occurs when Snowflake can use the metadata it collects about every eligible column on every micro-partition to eliminate micro-partitions based on filtering conditions specified in the query (or in a an object definition like a view or dynamic table). This type of partition pruning happens at compilation/optimization time.

Dynamic Partition Pruning

Dynamic partition pruning occurs when Snowflake can use the metadata it collects about every eligible column on every micro-partition to eliminate micro-partitions based on join conditions specified in the query (or in a an object definition like a view or dynamic table). Dynamic pruning happens at runtime, and therefore after compilation/optimization.

Investigating Partition Pruning

Let’s work with a specific query on the Snowflake sample data to experiment here. I need to cluster a couple of tables to show results here, so here’s how I accomplished that:

create or replace warehouse ecrooks_l_wh warehouse_size='LARGE' auto_suspend=60;
use warehouse ecrooks_l_wh;

create or replace table orders_clustered_custkey as
    select * 
    from snowflake_sample_data.TPCH_SF1000.orders
    order by o_custkey;

create or replace table customer_clustered_custkey as
    select * 
    from snowflake_sample_data.TPCH_SF1000.customer
    order by c_custkey;

alter warehouse suspend;

I’m using a large warehouse here so I don’t have to wait too long for the table creation with the sort. Here’s a query to explore static and dynamic pruning:

select * 
from orders_clustered_custkey as o
    join customer_clustered_custkey as c
    on o.o_custkey=c.c_custkey
where c_name = 'Customer#103312738';

In the query profile, we can easily see what partition pruning occurred. It looks something like this:

In this example, we can see that this query only had to read 2 micro-partitions out of 2,770. It doesn’t get much better than that. But how do we understand what pruning came from static pruning and what came from dynamic pruning?
If we run explain on the same query, we can see the plan the the optimizer generated for this query. Scrolling to the right lets us see the columns for partitionsTotal and partitionsAssigned:

partitionsTotal tells us the number of micro-partitions that exist for that table. partitionsAssigned tells us how many micro-partitions the optimizer expects to scan for the table.

We can see from this information that the optimizer planned to only have to scan 1 out of 448 micro-partitions for the table CUSTOMER_CLUSTERED_CUSTKEY, but it planned to have to scan all 2322 micro-partitions for the table ORDERS_CLUSTERED_CUSTKEY. This doesn’t match the query profile, which showed us only scanning 2 micro-partitions.

To understand the difference, let’s use get_query_operator_stats. This is using SQL to access the information from the query profile. The query profile in Snowflake is not just how the optimizer planned for the query execution, but what actually happened. The query we’ll use here is:

select operator_attributes:table_name
    , operator_statistics:pruning 
from table(GET_QUERY_OPERATOR_STATS('01b56e86-0002-96f3-0000-007116877035'));

The query id I specified is from my original execution of the query that I used to get the query profile. Here is what the output looks like:

In this output, partitions_total represents the number of micro-partitions for the table, while partitions_scanned represents the number of micro-partitions that were actually scanned. We can see here that the table CUSTOMER_CLUSTERED_CUSTKEY had 448 micro-partitions, but we only scanned one of them. This lines up with the results from the explain. For the table ORDERS_CLUSTERED_CUSTKEY, there were 2322 total micro-partitions, but we only had to scan one of them. This is different from what showed up in the explain.

Note that get_query_operator_stats only works for queries executed within the last 14 days.

Think about this in terms of the query. Our only filtering condition is on CUSTOMER_CLUSTERED_CUSTKEY, so we cannot plan to prune any micro-partitions when scanning ORDERS_CLUSTERED_CUSTKEY. However, when the query actually runs, Snowflake recognizes that the values of the join key (CUSTKEY) needed only exist on one micro-partition, so Snowflake using dynamic partition pruning to avoid scanning most of that table as well.

This is a simple example. Data and queries in the real world are rarely this simple, so knowing whether partition pruning is coming from a filtering condition or a join can be very useful to understand what is going on for a query.

Taking Action Based on This Information

What actions are there to take based on this information? It can be useful when considering a clustering key to understand when partition pruning is coming from the filtering conditions or from the join conditions. This can allow us to more closely target clustering key choices to a specific query or workload.

Knowing this information may not directly allow us to make a change to make a query faster or cheaper, and that’s probably why Snowflake doesn’t make the information clearer or easier to find. I am a fan of understanding as much as I can about what is happening.