Multi-cluster warehouses¶

What is a multi-cluster warehouse?¶

By default, a virtual warehouse consists of a single cluster of compute resources available to the warehouse for executing queries. As queries are submitted to a warehouse, the warehouse allocates resources to each query and begins executing the queries. If sufficient resources are not available to execute all the queries submitted to the warehouse, Snowflake queues the additional queries until the necessary resources become available.

With multi-cluster warehouses, Snowflake supports allocating, either statically or dynamically, additional clusters to make a larger pool of compute resources available. A multi-cluster warehouse is defined by specifying the following properties:

• Maximum number of clusters, greater than 1. The highest value you can specify depends on the warehouse size. For the upper limit on the number of clusters for each warehouse size, see Upper limit on number of clusters for a multi-cluster warehouse (in this topic).

• Minimum number of clusters, equal to or less than the maximum.

Additionally, multi-cluster warehouses support all the same properties and actions as single-cluster warehouses, including:

• Specifying a warehouse size.

• Resizing a warehouse at any time.

• Auto-suspending a running warehouse due to inactivity; note that this does not apply to individual clusters, but rather the entire multi-cluster warehouse.

• Auto-resuming a suspended warehouse when new queries are submitted.

Examples of multi-cluster credit usage¶

The following four examples illustrate credit usage for a multi-cluster warehouse. Refer to Virtual warehouse credit usage for the number of credits billed per full hour by warehouse size.

Creating a multi-cluster warehouse¶

You can create a multi-cluster warehouse in Snowsight or by using SQL:

Snowsight:

Click on Admin » Warehouses » + Warehouse

1. Expand Advanced Options.

2. Select the Multi-cluster Warehouse checkbox.

3. In the Max Clusters field, select a value greater than 1.

   Note

   Currently, the highest value you can choose in Snowsight is 10. The maximum sizes shown in Upper limit on number of clusters for a multi-cluster warehouse apply to the CREATE WAREHOUSE and ALTER WAREHOUSE commands in SQL only.

4. In the Min Clusters field, optionally select a value greater than 1.

5. Enter other information for the warehouse, as needed, and click Create Warehouse.

SQL:

Execute a CREATE WAREHOUSE command with:

• MAX_CLUSTER_COUNT set to a value greater than 1. For the highest value you can specify depending on the warehouse size, see Upper limit on number of clusters for a multi-cluster warehouse (in this topic).

• MIN_CLUSTER_COUNT (optionally) set to a value greater than 1.

To view information about the multi-cluster warehouses you create:

Snowsight:

Click on Admin » Warehouses.

The Clusters column displays the minimum and maximum clusters for each warehouse, as well as the number of clusters that are currently running if the warehouse is started. You can sort by the Clusters column in descending order to list the multi-cluster warehouses at the top.

Classic Console:

Click on Warehouses

SQL:

Execute a SHOW WAREHOUSES command.

The output includes three columns (min_cluster_count, max_cluster_count, started_clusters) that display the same information provided in the Clusters column in the web interface.

Tip

If the SHOW WAREHOUSES output is difficult to read because it includes so many columns, you can follow it with another query that includes just the columns you want, along with any other clauses for filtering and sorting. Use a query like the following, and adjust it to suit your needs. The column names are quoted because they’re case-sensitive in the SHOW WAREHOUSES output.

-- Must do the SHOW first to produce the full result set.
SHOW WAREHOUSES;
-- result_scan() helps to customize a report using the result set from the previous query.
SELECT "name", "state", "size", "max_cluster_count", "started_clusters", "type"
  FROM TABLE(result_scan(-1))
  WHERE "state" IN ('STARTED','SUSPENDED')
  ORDER BY "type" DESC, "name";

Copy

All other tasks for multi-cluster warehouses (except for the remaining tasks described in this topic) are identical to single-cluster warehouse tasks.

Setting the scaling policy for a multi-cluster warehouse¶

To help control the credits consumed by a multi-cluster warehouse running in Auto-scale mode, Snowflake provides scaling policies. Snowflake uses the scaling policies to determine how to adjust the capacity of your multi-cluster warehouse by starting or shutting down individual clusters while the warehouse is running. You can specify a scaling policy to make Snowflake prioritize responsiveness and throughput for the queries in that warehouse, or to minimize costs for that warehouse.

The scaling policy for a multi-cluster warehouse only applies if it is running in Auto-scale mode. In Maximized mode, all clusters run concurrently, so there is no need to start or shut down individual clusters.

Snowflake supports the following scaling policies:

You can set the scaling policy for a multi-cluster warehouse when it is created or at any time afterwards, either in Snowsight or using SQL:

Snowsight:

When you select Multi-cluster Warehouse under Advanced Options in the New Warehouse dialog, you can select the scaling policy from the Scaling Policy drop-down list.

For an existing multi-cluster warehouse, click Admin » Warehouses. Then select Edit under the More menu (…).

In the Scaling Policy field, select the desired value from the drop-down list.

Tip

You only see the Scaling Policy drop-down list when the warehouse you selected is a multi-cluster warehouse, and the maximum clusters value is higher than the minimum clusters value.

Classic Console:

Click on:

• Warehouses » Create or

• Warehouses » <warehouse_name> » Configure

In the Scaling Policy field, select the desired value from the drop-down list.

SQL:

Execute a CREATE WAREHOUSE or ALTER WAREHOUSE command with SCALING_POLICY set to the desired value.

For example, in SQL:

CREATE WAREHOUSE mywh WITH MAX_CLUSTER_COUNT = 2, SCALING_POLICY = 'STANDARD';
ALTER WAREHOUSE mywh SET SCALING_POLICY = 'ECONOMY';

Copy

Increasing or decreasing clusters for a multi-cluster warehouse¶

You can increase or decrease the maximum and minimum number of clusters for a warehouse at any time, even while it is running and executing statements. You can adjust the maximum and minimum clusters for a warehouse in Snowsight or using SQL:

Snowsight:

Click on Admin » Warehouses.

Click on the warehouse name to view its properties and historical activity. Select Edit from More menu (…). You can also deselect the Multi-cluster Warehouse checkbox to reset the maximum and minimum cluster settings to 1, changing the warehouse to a single-cluster one.

Classic Console:

Click on Warehouses » <warehouse_name> » Configure

SQL:

Execute an ALTER WAREHOUSE command.

The effect of changing the maximum and minimum clusters for a running warehouse depends on whether it is running in Maximized or Auto-scale mode:

• Maximized:

  ↑ max & min:

  Specified number of clusters start immediately.

  ↓ max & min:

  Specified number of clusters shut down when they finish executing statements and the auto-suspend period elapses.

• Auto-scale:

  ↑ max:

  If new_max_clusters > running_clusters, no changes until additional clusters are needed.

  ↓ max:

  If new_max_clusters < running_clusters, excess clusters shut down when they finish executing statements and the scaling policy conditions are met.

  ↑ min:

  If new_min_clusters > running_clusters, additional clusters immediately started to meet the minimum.

  ↓ min:

  If new_min_clusters < running_clusters, excess clusters shut down when they finish executing statements and the scaling policy conditions are met.

Monitoring multi-cluster warehouses¶

You can monitor usage of multi-cluster warehouses through the web interface:

Snowsight:

Click on Admin » Warehouses and then select a warehouse name.

That way, you can monitor one warehouse in precise detail, such as viewing queries that are currently running or queued.

Alternatively, you can click on Monitoring » Query History. This page lets you view activity across multiple warehouses in your account. To see the activity only for one warehouse, select Warehouse under the Filters drop-down menu. Then choose a warehouse name from the list.

Classic Console:

Click on History

When you monitor a multi-cluster warehouse, you can see all the queries the warehouse processed. For each query, you can see details such as how long it took, how many bytes it scanned, and how many rows it returned. You can also see the cluster used to execute each statement that the warehouse processed. To choose which details to view, select the items such as Cluster Number, Duration, Rows, and so on under the Columns drop-down menu.