Snowflake is a widely customizable platform – which also means adjusting it to best perform and not break your bank can be more than a little difficult.
Even after you’ve started with warehouse right-sizing and query optimization, there’s still more work that can be done with Snowflake clustering.
After reading this article, you’ll walk away knowing:
- What Snowflake clustering is
- Why use clustering
- What clustering keys are
- How exactly clustering works
- How Snowflake clustering works
And you’ll know fully if you should introduce Snowflake clustering to your tech stack. Let’s dive in.
What Is Snowflake Clustering?
Snowflake clustering is a technique that groups related rows in a table into the same micro-partition.
Why should you care?
This grouping can give you a big boost when it comes to query execution, and can be enormously helpful when working on query pruning.
But before we go any further here, let’s define a couple of these terms.
Snowflake Micro-Partitions
Snowflake micro-partitions are where you divide and store data in small, self-contained units inside of a Snowflake warehouse. These are created and optimized automatically for maximum storage efficiency, typically ranging from 50MB to 500MB of uncompressed data in size.
Query Pruning
Query pruning is a Snowflake query optimization technique where you reduce the amount of data that has to be scanned when you execute a query. You can do this by using Snowflake micro-partitions to divide table data into sections that can be skipped and sections that must be scanned.
For example, say you’re looking at a data column that’s storing the min and max values of a set of dates. If you have a micro-partition with the max date of 2023-12-31, and your query only needs dates up until 2022-12–31, you can skip that entire micro-partition.
Why Use Snowflake Clustering?
When you cluster a table in Snowflake, each micro-partition has an assigned metadata. This metadata helps reduce the number of files that have to be scanned, which means that less data has to be accessed and processed. Basically, the query can be a lot faster – and because Snowflake operates on a consumption-based pricing model, you get to pay less.
What Are Snowflake Clustering Keys?
To effectively set up Snowflake clustering, you’ll need to make sure your clustering keys are aligned correctly.
Clustering keys are designated expressions or columns that determine how data should be sorted within a table. When you set up or adjust one of your Snowflake tables, you can pick one or more columns to act as your clustering keys. You define clustering keys at the table level and can specify and modify them using SQL.
How can you create clustering keys?
There are a few different ways.
You can define clustering keys when you create a table with the CREATE TABLE command. Or you can use the ALTER command to specify clustering keys at a later time.
If you want to cluster a table based on a particular column, you can use the ALTER command like this:
ALTER TABLE some_table_name CLUSTER BY (clustering_column_name);You can also cluster a table based on an expression that uses a column. Here’s an example using a LEFT function to cluster with the first two characters in a column:
ALTER TABLE some_table_name CLUSTER BY (LEFT(clustering_column_name, 2));When you’re setting up your clustering columns, keep these things in mind:
- Cluster your data based on relevant columns
- Confirm Snowflake is using the values in clustering key columns to assign rows to micro-partitions during data loading
This keeps related rows stored together and gets you the best of both worlds with reduced data scanning and improved query performance.
Snowflake offers automatic clustering services which means you don’t have to constantly manually monitor things yourself and make adjustments as your data changes. If you want maximum performance and cost optimization clustering though, you’re better off looking into third-party brands for help.
How Exactly Does Snowflake Clustering Work?
Let’s start from the beginning:
First, your data is loaded into Snowflake and stored in a “load table”. This table is a temporary table used for staging data before it’s loaded into an actual table. Snowflake will automatically reorganize this table into micro-partitions based on any clustering keys defined for that table.
Apply your clustering key based on how you want to group your data. If you decide to use automatic clustering to cluster or recluster your tables, you won’t have to bother with manual adjustments, but you will have to cough up some extra Snowflake credits.
Automatic clustering uses Snowflake credits without requiring you to provide a dedicated virtual warehouse. Snowflake will internally manage resource allocation for reclustering your tables, and you’ll be billed for the credits consumed by the automatic clustering.
The amount of credit consumed varies based on:
- Table size
- Number of clustered or reclustered rows
If you enable Snowflake automatic clustering and haven’t clustered in some time, you’ll probably experience some higher charges as Snowflake brings your tables to the optimally-clustered state.
Should I Try Snowflake Clustering?
Clustering can significantly improve query performance and reduce costs, but it requires careful planning. While automatic clustering simplifies management, the additional credits can add up quickly for large, frequently-changing tables.
But automatic clustering can be an additional charge atop of an already opaque bill, and if you’re already struggling to get your Snowflake platform performance into shape, you may be in need of a different kind of optimization.
If managing clustering manually feels overwhelming, or if you’re looking for comprehensive Snowflake optimization beyond just clustering, specialized tools can help automate the entire process.
Third-party tools like Yuki are designed to cluster tables, right-size warehouses, and optimize your queries automatically, no additional dev lift needed. Yuki’s plug-and-play tool means you can start saving up to 40% on your Snowflake spend at the click of a button.
Curious how much Yuki can help you start to save? Reach out now for your free demo.
