{"id":864,"date":"2023-05-28T19:13:05","date_gmt":"2023-05-28T19:13:05","guid":{"rendered":"https:\/\/bandittracker.com\/?p=864"},"modified":"2023-05-28T19:13:09","modified_gmt":"2023-05-28T19:13:09","slug":"partitions-and-clusters-in-snowflake","status":"publish","type":"post","link":"https:\/\/bandittracker.com\/partitions-and-clusters-in-snowflake\/","title":{"rendered":"Partitions and Clusters In Snowflake (For Beginners)"},"content":{"rendered":"\n<p>In some database systems, you explicitly choose when to partition tables. In Snowflake, table partitioning happens automatically behind the scenes using a concept called micro-partitions.<\/p>\n\n\n\n<p>Although you don\u2019t need an in-depth understanding of micro-partitions, you do need to understand how they allow you to cluster your tables for better performance.<\/p>\n\n\n\n<p>This article quickly explains the underlying concepts and then walks you through how to implement clustering and reclustering of your Snowflake tables.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_63 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<p class=\"ez-toc-title\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6601c2319f35a\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6601c2319f35a\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/bandittracker.com\/partitions-and-clusters-in-snowflake\/#What_Are_Micro-Partitions\" title=\"What Are Micro-Partitions?\">What Are Micro-Partitions?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/bandittracker.com\/partitions-and-clusters-in-snowflake\/#What_Is_Clustering_In_Snowflake\" title=\"What Is Clustering In Snowflake?\">What Is Clustering In Snowflake?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/bandittracker.com\/partitions-and-clusters-in-snowflake\/#What_Is_Pruning\" title=\"What Is Pruning?\">What Is Pruning?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/bandittracker.com\/partitions-and-clusters-in-snowflake\/#How_To_Define_A_Cluster_Key_In_Snowflake\" title=\"How To Define A Cluster Key In Snowflake\">How To Define A Cluster Key In Snowflake<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/bandittracker.com\/partitions-and-clusters-in-snowflake\/#How_Cluster_Keys_Work\" title=\"How Cluster Keys Work\">How Cluster Keys Work<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/bandittracker.com\/partitions-and-clusters-in-snowflake\/#How_To_Recluster_Tables\" title=\"How To Recluster Tables\">How To Recluster Tables<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/bandittracker.com\/partitions-and-clusters-in-snowflake\/#How_To_Show_Cluster_Keys\" title=\"How To Show Cluster Keys\">How To Show Cluster Keys<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/bandittracker.com\/partitions-and-clusters-in-snowflake\/#How_To_Get_Information_About_Clusters\" title=\"How To Get Information About Clusters\">How To Get Information About Clusters<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/bandittracker.com\/partitions-and-clusters-in-snowflake\/#Automatic_Clustering\" title=\"Automatic Clustering\">Automatic Clustering<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/bandittracker.com\/partitions-and-clusters-in-snowflake\/#Typical_Use_Case_Partitioning_By_Date_Column\" title=\"Typical Use Case: Partitioning By Date Column\">Typical Use Case: Partitioning By Date Column<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"What_Are_Micro-Partitions\"><\/span>What Are Micro-Partitions?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Micro-partitions are the fundamental units of storage in Snowflake. When you load data into a table, Snowflake automatically divides the data into micro-partitions.<\/p>\n\n\n\n<p>A Snowflake micro-partition is basically a file with a proprietary format. The data is compressed and grouped by column.<\/p>\n\n\n\n<p>Each file typically contains between 50 MB and 500 MB of uncompressed data.<\/p>\n\n\n\n<p>As well as being columnar, each micro-partition only contains complete rows of data. That may be confusing, so let\u2019s look at this with a visual.<\/p>\n\n\n\n<p>The left of the picture below has the logical structure of a product table with six rows.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"600\" height=\"300\" src=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/05\/snowflake-micropartition.jpg\" alt=\"\" class=\"wp-image-865\" srcset=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/05\/snowflake-micropartition.jpg 600w, https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/05\/snowflake-micropartition-300x150.jpg 300w\" sizes=\"(max-width: 600px) 100vw, 600px\" \/><\/figure>\n\n\n\n<p>The first micro-partition is on the right. The data is grouped by column, but the partition holds an entire row (six in this example).<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"What_Is_Clustering_In_Snowflake\"><\/span>What Is Clustering In Snowflake?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Clustering in Snowflake refers to the organization of data in a table. Specifically, it refers to how the data is ordered across the micro-partitions that Snowflake automatically creates.<\/p>\n\n\n\n<p>The simple explanation is that clustering is about sorting data in your table in a way that can speed up queries and improve overall performance.<\/p>\n\n\n\n<p>Although micro-partitions are managed automatically, you can influence how they&#8217;re organized by defining a clustering key when creating a table.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"What_Is_Pruning\"><\/span>What Is Pruning?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Pruning refers to the process of reducing the amount of data that needs to be scanned when executing a query. This is done by skipping over partitions of the data that are not relevant to the query.<\/p>\n\n\n\n<p>When a query is executed, Snowflake uses the metadata of a partition to check if it may contain relevant data.<\/p>\n\n\n\n<p>If the query includes a where clause and the metadata shows that none of the data in that micro-partition can possibly meet the filter condition, then Snowflake can skip over that micro-partition entirely. This process is known as &#8220;pruning.&#8221;<\/p>\n\n\n\n<p>Pruning is particularly effective when a table has a defined clustering key. This makes it more likely that Snowflake can prune a large number of micro-partitions when the query filter conditions include the clustering key column(s).<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_To_Define_A_Cluster_Key_In_Snowflake\"><\/span>How To Define A Cluster Key In Snowflake<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>To define a clustering key when creating a table, use the CLUSTER BY clause.<\/p>\n\n\n\n<p>Let\u2019s say that you know that you usually query the product table with a specific category in the WHERE clause. So, you want to cluster by that column.<\/p>\n\n\n\n<p>This statement creates the cluster key:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE Product (\n    ProductID NUMBER NOT NULL PRIMARY KEY,\n    ProductName VARCHAR NOT NULL,\n    Category STRING NOT NULL,\n    Quantity NUMBER\n)\nCLUSTER BY (Category);<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">How to remove a cluster key from a table<\/h3>\n\n\n\n<p>You use the <a href=\"https:\/\/docs.snowflake.com\/en\/sql-reference\/sql\/alter-table\" target=\"_blank\" rel=\"noreferrer noopener\">ALTER TABLE<\/a> to drop the cluster key from a table.<\/p>\n\n\n\n<p>This example drops the key that we created with the Product table in the previous example.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE Product DROP CLUSTERING KEY<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">How to add a cluster key to an existing table<\/h3>\n\n\n\n<p>You use the ALTER TABLE command to add a cluster key to a table that already exists.<\/p>\n\n\n\n<p>For example, suppose you want to add a new cluster key to the Product table (because we dropped it). This syntax puts the one we dropped back in place:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE Product CLUSTER BY (Category);<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_Cluster_Keys_Work\"><\/span>How Cluster Keys Work<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"600\" height=\"500\" src=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/05\/snowflake-cluster-key.jpg\" alt=\"\" class=\"wp-image-866\" srcset=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/05\/snowflake-cluster-key.jpg 600w, https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/05\/snowflake-cluster-key-300x250.jpg 300w\" sizes=\"(max-width: 600px) 100vw, 600px\" \/><\/figure>\n\n\n\n<p>Once a clustering key is defined, Snowflake tries to store rows in the same micro-partition or in adjacent micro-partitions where the clustering key values are similar.<\/p>\n\n\n\n<p>When a query is executed, Snowflake is able to reduce the number of micro-partitions that need to be scanned by leveraging the clustering keys. This results in faster query performance.<\/p>\n\n\n\n<p>Note that the initial loading of data into a table with a clustering key does not automatically sort the data fully according to the clustering key.<\/p>\n\n\n\n<p>The benefits of the clustering key become apparent when the data is reclustered. Let\u2019s look at that next.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_To_Recluster_Tables\"><\/span>How To Recluster Tables<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Over time, as you load, delete, or update data, the micro-partitions will become less well-ordered with respect to the clustering key. This is known as &#8220;clustering skew.&#8221;<\/p>\n\n\n\n<p>When the skew becomes too high, the performance starts to degrade.<\/p>\n\n\n\n<p>You can recluster the table to reorder the micro-partitions by using syntax like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE Product RECLUSTER;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_To_Show_Cluster_Keys\"><\/span>How To Show Cluster Keys<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>If you want to get a list of tables in your database with cluster keys, you can query the <a href=\"https:\/\/docs.snowflake.com\/en\/sql-reference\/info-schema\" target=\"_blank\" rel=\"noreferrer noopener\">schema metadata<\/a> through the INFORMATION_SCHEMA.TABLES object.<\/p>\n\n\n\n<p>Here is a sample query:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CLUSTERING_KEY \n  FROM INFORMATION_SCHEMA.TABLES\n WHERE CLUSTERING_KEY IS NOT NULL;<\/code><\/pre>\n\n\n\n<p>This picture shows the query running on the database where I created the Product table earlier.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"600\" height=\"300\" src=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/05\/snowflake-view-cluster-keys.jpg\" alt=\"\" class=\"wp-image-867\" srcset=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/05\/snowflake-view-cluster-keys.jpg 600w, https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/05\/snowflake-view-cluster-keys-300x150.jpg 300w\" sizes=\"(max-width: 600px) 100vw, 600px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_To_Get_Information_About_Clusters\"><\/span>How To Get Information About Clusters<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Snowflake provides a system function to help you understand and manage the clustering of your tables.<\/p>\n\n\n\n<p>It pulls back a number greater than 1 for a populated table. The smaller the average depth, the better it is clustered.<\/p>\n\n\n\n<p>Here is how to call it for our example table:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT SYSTEM$CLUSTERING_DEPTH('Product');<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Automatic_Clustering\"><\/span>Automatic Clustering<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Snowflake offers automatic clustering for tables as a premium (and costly) feature.<\/p>\n\n\n\n<p>If you enable automatic clustering, Snowflake periodically and automatically manages the reclustering process for the table in the background. <\/p>\n\n\n\n<p>This keeps the data in the table well-clustered without manual intervention.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Typical_Use_Case_Partitioning_By_Date_Column\"><\/span>Typical Use Case: Partitioning By Date Column<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>A common scenario is retrieving the most recent data from a growing table with records continuously being added.<\/p>\n\n\n\n<p>A great solution is to create a cluster key on a year or other type of date column. Snowflake will ensure that the data is sequenced by that date column. Your queries by date will be highly optimized.<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In some database systems, you explicitly choose when to partition tables. In Snowflake, table partitioning happens automatically behind the scenes using a concept called micro-partitions. Although you don\u2019t need an in-depth understanding of micro-partitions, you do need to understand how they allow you to cluster your tables for better performance. This article quickly explains the &#8230; <a title=\"Partitions and Clusters In Snowflake (For Beginners)\" class=\"read-more\" href=\"https:\/\/bandittracker.com\/partitions-and-clusters-in-snowflake\/\" aria-label=\"More on Partitions and Clusters In Snowflake (For Beginners)\">Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[25],"tags":[],"_links":{"self":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/864"}],"collection":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/comments?post=864"}],"version-history":[{"count":1,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/864\/revisions"}],"predecessor-version":[{"id":868,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/864\/revisions\/868"}],"wp:attachment":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/media?parent=864"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/categories?post=864"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/tags?post=864"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}