Automatic Clustering | Snowflake Documentation (2024)

Ease-of-maintenance¶

Automatic Clustering eliminates the need for performing any of the following tasks:

• Monitoring the state of clustered tables.

  Instead, as DML is performed on these tables, Snowflake monitors and evaluates the tables to determine whether they would benefit from reclustering, and automaticallyreclusters them, as needed.

• Designating warehouses in your account to use for reclustering.

  Snowflake performs automatic reclustering in the background, and you do not need to specify a warehouse to use.

All you need to do is define a clustering key for each table (if appropriate) and Snowflake manages all future maintenance.

Full control¶

You can suspend and resume Automatic Clustering for a clustered table at any time using ALTER TABLE … SUSPEND / RESUME RECLUSTER. While Automatic Clustering is suspendedfor a table, the table is never automatically reclustered, regardless of its clustering state and, therefore, does not incur any related credit charges.

You can also drop the clustering key on a clustered table at any time, which prevents all future reclustering on the table.

Non-blocking DML¶

Automatic Clustering is transparent and does not block DML statements issued against tables while they are being reclustered.

Optimal efficiency¶

With Automatic Clustering, Snowflake internally manages the state of clustered tables, as well as the resources (servers, memory, etc.) used for all automated clusteringoperations. This allows Snowflake to dynamically allocate resources as needed, resulting in the most efficient and effective reclustering.

Also, Automatic Clustering does not perform any unnecessary reclustering. Reclustering is triggered only if/when the table would benefit from the operation.

Credit usage and warehouses for Automatic Clustering¶

Automatic Clustering consumes Snowflake credits, but does not require you to provide a virtual warehouse. Instead, Snowflake internallymanages and achieves efficient resource utilization for reclustering the tables.

Your account is billed only for the actual credits consumed by automatic clustering operations on your clustered tables.

Estimating Automatic Clustering cost¶

You can call the SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS function to help estimate the compute cost ofenabling Automatic Clustering for a table and maintaining the table in a well-clustered state. You can also call the function to help predictthe compute cost of changing the cluster key of a table.

Viewing Automatic Clustering cost¶

Automatic clustering consumes credits as it uses serverless compute resources for theautomated background maintenance of each clustered table, including initial clustering and reclustering as needed. To learn how manycredits per compute-hour are consumed by automatic clustering, refer to the “Serverless Feature Credit Table” in theSnowflake service consumption table.

Users with the proper privileges can view the cost of automatic clustering using Snowsight, theClassic Console, or SQL:

Snowsight:

Select Admin » Cost Management.

Classic Console:

Select Account » Billing & Usage

Automatic Clustering costs show up as a separate Snowflake-provided warehouse named AUTOMATIC_CLUSTERING.

SQL:

Query either of the following:

• AUTOMATIC_CLUSTERING_HISTORY table function (in the Snowflake Information Schema).

• AUTOMATIC_CLUSTERING_HISTORY view (in Account Usage).

  The following queries can be executed against the AUTOMATIC_CLUSTERING_HISTORY view:

  Query: Automatic Clustering cost history (by day, by object)

  This query provides a list of tables with Automatic Clustering and the volume of credits consumed via the service over the last 30 days,broken out by day. Any irregularities in the credit consumption or consistently high consumption are flags for additional investigation.

  SELECT TO_DATE(start_time) AS date, database_name, schema_name, table_name, SUM(credits_used) AS credits_usedFROM snowflake.account_usage.automatic_clustering_historyWHERE start_time >= DATEADD(month,-1,CURRENT_TIMESTAMP())GROUP BY 1,2,3,4ORDER BY 5 DESC;

  Copy

  Query: Automatic Clustering History & m-day average

  This query shows the average daily credits consumed by Automatic Clustering grouped by week over the last year. It can help identifyanomalies in daily averages over the year so you can investigate spikes or unexpected changes in consumption.

  WITH credits_by_day AS ( SELECT TO_DATE(start_time) AS date, SUM(credits_used) AS credits_used FROM snowflake.account_usage.automatic_clustering_history WHERE start_time >= DATEADD(year,-1,CURRENT_TIMESTAMP()) GROUP BY 1 ORDER BY 2 DESC)SELECT DATE_TRUNC('week',date), AVG(credits_used) AS avg_daily_creditsFROM credits_by_dayGROUP BY 1ORDER BY 1;

  Copy