Automatic Clustering¶

Benefits of Automatic Clustering¶

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.

Enabling Automatic Clustering for a Table¶

In most cases, no tasks are required to enable Automatic Clustering for a table. You simply define a clustering key for the table.

However, the rule does not apply to tables created by cloning (CREATE TABLE … CLONE …) from a source table that has clustering keys. The new table starts with Automatic Clustering suspended – even if Automatic Clustering for the source table is not suspended. (This is true whether the CLONE command cloned the table, the schema containing the table, or the database containing the table.)

For information about choosing optimal clustering keys, see Strategies for Selecting Clustering Keys.

To add clustering to a table, you must also have USAGE or OWNERSHIP privileges on the schema and database that contain the table.

Viewing the Automatic Clustering Status for a Table¶

You can use SQL to view whether Automatic Clustering is enabled for a table:

• SHOW TABLES command.

• TABLES view (in the Snowflake Information Schema).

• TABLES view (in the Account Usage shared database).

The AUTO_CLUSTERING_ON column in the output displays the Automatic Clustering status for each table, which can be used to determine whether to suspend or resume Automatic Clustering for a given table.

In addition, the CLUSTER_BY column (SHOW TABLES) or CLUSTERING_KEY column (TABLES view) displays the column(s) defined as the clustering key(s) for each table.

Suspending Automatic Clustering for a Table¶

To suspend Automatic Clustering for a table, use the ALTER TABLE command with a SUSPEND RECLUSTER clause. For example:

ALTER TABLE t1 SUSPEND RECLUSTER;

SHOW TABLES LIKE 't1';

+---------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+----------+----------------+----------------------+
|           created_on            | name | database_name | schema_name | kind  | comment | cluster_by | rows | bytes |  owner   | retention_time | automatic_clustering |
+---------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+----------+----------------+----------------------+
| Thu, 12 Apr 2018 13:29:01 -0700 | T1   | TESTDB        | MY_SCHEMA   | TABLE |         | LINEAR(C1) | 0    | 0     | SYSADMIN | 1              | OFF                  |
+---------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+----------+----------------+----------------------+

Copy

Resuming Automatic Clustering for a Table¶

To resume Automatic Clustering for a clustered table, use the ALTER TABLE command with a RESUME RECLUSTER clause. For example:

ALTER TABLE t1 RESUME RECLUSTER;

SHOW TABLES LIKE 't1';

+---------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+----------+----------------+----------------------+
|           created_on            | name | database_name | schema_name | kind  | comment | cluster_by | rows | bytes |  owner   | retention_time | automatic_clustering |
+---------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+----------+----------------+----------------------+
| Thu, 12 Apr 2018 13:29:01 -0700 | T1   | TESTDB        | MY_SCHEMA   | TABLE |         | LINEAR(C1) | 0    | 0     | SYSADMIN | 1              | ON                   |
+---------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+----------+----------------+----------------------+

Copy

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