In other words, many/most queries select on, (For example, the query contains an ORDER BY clause on the table.)Ī high percentage of the queries can benefit from the same clustering key(s). Percentage of micro-partitions) in the table. In other words, the queries need to read only a small percentage of rows (and thus usually a small Typically, this means that one or both of the following are true: The queries can take advantage of clustering. The table contains multiple terabytes (TB) of data. The table contains a large number of micro-partitions. Whether you want faster response times or lower overall costs, clustering is best for a table that meets all of For example, the following join would likely cause Snowflake to perform a sort operation:Ĭonsiderations for Choosing Clustering for a Table ¶ Sorting is commonly done for ORDER BY operations,įor GROUP BY operations, and for some joins. Typically, queries benefit from clustering when the queries filter or sort on the clustering key for the table. Should cluster only when queries will benefit substantially from the clustering. (to ensure optimal clustering) is performed automatically by Snowflake.Īlthough clustering can substantially improve the performance and reduce the cost of some queries, the compute resources used to perform clustering consume credits. All future maintenance on the rows in the table This is especially true when other columns are strongly correlated with the columns that comprise the clustering key.Īfter a key has been defined on a table, no additional administration is required, unless you chose to drop or modify the key. Improved scan efficiency in queries by skipping data that does not match filtering predicates.īetter column compression than in tables with no clustering. Using a clustering key to co-locate similar rows in the same micro-partitions enables several benefits for very large tables, including: Views, see Materialized Views and Clustering andīenefits of Defining Clustering Keys (for Very Large Tables) ¶ For a few additional tips specific to materialized The rules forĬlustering tables and materialized views are generally the same. You can cluster materialized views, as well as tables. Instead, Snowflake supports automating these tasks by designating one or more table columns/expressions as a clustering key for the table. These tasks could be cumbersome and expensive. To improve the clustering of the underlying table micro-partitions, you can always manually sort rows on key table columns and re-insert them into the table however, performing Not the number of rows), the data in some table rows might no longer cluster optimally on desired dimensions. In general, Snowflake produces well-clustered data in tables however, over time, particularly as DML occurs on very large tables (as defined by the amount of data in the table,

Keyclusterer