Automatic Clustering¶

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 automatically reclusters 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 suspended for 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 clustering operations. 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 internally manages 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 of enabling Automatic Clustering for a table and maintaining the table in a well-clustered state. You can also call the function to help predict the 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 the automated background maintenance of each clustered table, including initial clustering and reclustering as needed. To learn how many credits per compute-hour are consumed by automatic clustering, refer to the “Serverless Feature Credit Table” in the Snowflake Service Consumption Table.

Users with the proper privileges can view the cost of automatic clustering using Snowsight, the Classic 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_used
  FROM snowflake.account_usage.automatic_clustering_history
  WHERE start_time >= DATEADD(month,-1,CURRENT_TIMESTAMP())
  GROUP BY 1,2,3,4
  ORDER 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 identify anomalies 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_credits
  FROM credits_by_day
  GROUP BY 1
  ORDER BY 1;
  

  Copy