Snowflake Partition Pruning: What is it, and why does it matter?

One of the most basic building blocks of Snowflake query performance is partition pruning. Let’s explore partition pruning, identifying when it is occurring, and why it matters for query performance.

Micro-Partitions

Snowflake stores data in micro-partitions. Micro-partitions are proprietarily structured objects in object storage – S3 For AWS. Each micro-partition includes all of the data for a set of rows, but the data is structured within the micro-partition in a columnar format. Object storage allows accessing only parts of an object, so Snowflake knows where each column is stored and how to access only the columns needed to satisfy a query.

One property of objects in cloud object storage is that they are immutable. If we want to change the data in a micro-partition in any way (insert, update, or delete), we need to replace that micro-partition entirely with a new micro-partition.

In the cloud services layer, Snowflake stores guaranteed correct metadata about each micro-partition. This is not the same as Db2’s statistics, which are only correct as of the last time statistics are collected. Because micro-partitions are immutable, the metadata about them is collected at write time and is guaranteed to be correct. The metadata collected for each column includes things like the minimum and maximum values, and the number of distinct values on that micro-partition.

This metadata is also collected for attributes in VARIANT data types, with some important limits. This allows some attributes in VARIANTs to be queried much like they’re columns, with similar performance, though caution should be used when using attributes in VARIANTs for joining and other performance-critical operations.

The usual target size for micro-partitions is 16 MB of compressed data. This can be hundreds of rows, thousands of rows, or millions of rows per micro-partition, depending on the row width.

Partition Pruning

Snowflake can use the metadata to determine if a specific value for a column might exist on a micro-partition or definitely does not exist on a micro-partition. This can be a huge advantage, as only the micro-partitions that might have the specific values need to be read at all. When you’re talking about thousands or millions of micro-partitions, not reading a significant portion of them is a great thing for performance.

There are a number of things that can get in the way of partition pruning, so it is important to understand how it works. The primary method for partition pruning is essentially checking to see whether the value the query is looking for is between the minimum and the maximum values for each micro-partition. This works extremely well if the values for a column do not overlap between micro-partitions. However, it is entirely possible for each micro-partition to have nearly the full range of possible values in the thousands or millions of rows it holds. If this is the case, partition pruning is not able to add value.

How do we keep the min/max range for values on a micro-partition as low and non-overlapping as possible? We do this with clustering. Auto-clustering is an excellent choice to maintain this over time, but for experimentation, we can simulate the effects of auto-clustering by ordering the data as we build the table. Snowflake does not look for an explicitly defined clustering key, but is using the metadata to look at the micro-partitions, so at least for the initial table state, it doesn’t matter how the data gets in that clustered state. Watch out, though – changes to the data in the table over time can have the effect of un-clustering the data without something like auto-clustering.

Partition Pruning Example

I am a huge fan of learning by example, so let’s work through an example to illustrate partition pruning. I’ll be using a free snowflake trial account and the SNOWFLAKE_SAMPLE_DATA, which should be available in any account. Like any of my Snowflake examples, this will use credits(and therefore cost money if you’re not using a free trial account).

First, I’m going to create some objects to use in our examples:

create or replace database datageek_test;
use database datageek_test;
create or replace schema testschema;
use schema testschema;

create or replace warehouse datageek_l_wh with warehouse_size='LARGE' auto_suspend=60;
create or replace warehouse datageek_xs_wh with warehouse_size='XSMALL' auto_suspend=60;

alter session set USE_CACHED_RESULT=FALSE;

I’m just creating and using a database and a schema, and also creating two different sizes of warehouse to use. Remember that Snowflake has no attachment between the warehouse and the data, so I can use any size of warehouse to work with any data. Finally, I’m turning result set caching off, as I often do when testing single query performance. I do like the result set cache on when doing load testing or more realistic testing.

The table I’ll be working with here is the ORDERS table from the TPCH_SF1000 schema in the SNOWFLAKE_SAMPLE_DATA database. I like working with the larger scale factors (the SF in this schema name stands for Scale Factor) when working with Snowflake as it makes it easier to demonstrate some of the concepts.

The performance on the data as it is may not be optimal. Let’s say my primary query pattern for this data is querying data based on o_custkey. If I have a simple query of a couple of o_custkeys, it might look like this:

select * 
from snowflake_sample_data.tpch_sf1000.orders 
where o_custkey in (96387769,112388848) 
order by o_orderdate desc;

For me, this query executes in 4.2 seconds to return 58 rows on an already resumed warehouse with a warm data cache. The query profile for this looks like this:

Note in the output here, that the table scan took nearly all of this time, despite much of the data already being cached on the warehouse. At the lower right, you can see that this table consists of 3,242 micro-partitions, and we had to scan all 3,242 of them to generate the query result set. We did not see any partition pruning for this query.

Also notice the cardinality coming out of the table scan. It is only 58 rows. To me, this screams of opportunity for performance improvement.

Now, I’d like to create a copy of the table, but with the data in a specific order. Ordering the data as we place it in the table has the effect of narrowing the distance between the minimum and the maximum on each micro-partition. It may be more work that we would have to do, as narrowing that range does not require perfect order. Here’s the syntax I used to do that:

use warehouse datageek_l_wh;
create table orders_clustered_custkey as(
    select * from snowflake_sample_data.tpch_sf1000.orders order by o_custkey

);
alter warehouse suspend;

Note that I’m using my larger warehouse here. Creating a copy (not a clone!) of a table is one of the places it makes sense to use more compute power. Assuming a non-trivial data size, I use the largest warehouse I’m comfortable with when creating an ordered copy of a table. This allows for more memory to perform the sort, along with more CPU resources to just write out a bunch of micro-partitions at once. This step took 2 minutes and 36 seconds for me to run. I also suspend my warehouse immediately when done, because I know I only want to use it for this one task, and I don’t need to wait for auto-suspend to kick in.

Now, if I run the exact same query against the same data in a different order, using the xs warehouse, it looks like this:

use warehouse datageek_xs_wh;
select * 
from orders_clustered_custkey 
where o_custkey in (96387769,112388848) 
order by o_orderdate desc;


The result set is exactly the same here as it was before, but the query took less than a second to execute. It gets even lower with repeated runs to populate the warehouse data cache.

Notice that the table scan still takes the majority of our shorter duration. In the lower right corner, we now see that we are only reading 2 out of 2,322 micro-partitions. This is where our time savings comes in.

In our first execution the table had 3,242 micro-partitions. It now has 2,322 micro-partitions. How is this possible? The top reason is that compression does much better when similar data is on the same micro-partition. A table that is well-clustered is not only faster to query, but also takes up less space! There can be other causes for this. Depending on table activity and loading methodology, micro-partitions may also be undersized. Undersized (and therefore MORE) micro-partitions can lead to poorer performance.

Adding Complexity

Now I’m going to throw a monkey wrench in the works. Let’s replace those nice, clean o_custkeys with things that we as humans know will equate to the same exact numbers, but that Snowflake wouldn’t treat quite the same way. I’m going to add a random number between 1/5 and 1/10th to each, and then use the floor function to each. Is this something you’d normally do with an order number? No. This is, indeed, a contrived example, but every day, I see people using conditions that are not as easily pushed to the table scan, and I want to show you what that looks like.

Here’s the modified syntax and result

select * 
from orders_clustered_custkey 
where o_custkey in (floor(96387769+1/uniform(5,10,random())),floor(112388848+1/uniform(5,10,random()))) 
order by o_orderdate desc;

This gives us the same 58 rows as the last query, but takes 18 seconds on the table that has the narrow min/max ranges. The query profile looks like this:

Do you see the difference here? We are now scanning 2,322 of 2,322 micro-partitions. It is important when digging into query performance to observe if there are specific conditions or combinations of conditions that lead to this lack of pruning. Sometimes by changing how we are specifying conditions, we can make a huge difference in the number of micro-partitions that must be scanned.

Summary

Micro-partition pruning is important for query performance in Snowflake. This was a very simplified look at the basics. There are a lot of complexities here. I deal daily with queries that are hundreds or thousands of lines of SQL, so these seem like simple examples, but the concepts apply even with more complex SQL. Looking at the query profile for a query can help identify where we are seeing partition pruning and where we are not.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 554

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.