Categories:

Warehouse & Resource Monitor DDL

ALTER WAREHOUSE

Suspends or resumes a virtual warehouse, or aborts all queries (and other SQL statements) for a warehouse. Can also be used to rename or set/unset the properties for a warehouse.

See also:

CREATE WAREHOUSE , SHOW WAREHOUSES

Syntax

ALTER WAREHOUSE [ IF EXISTS ] [ <name> ] { SUSPEND | RESUME [ IF SUSPENDED ] }

ALTER WAREHOUSE [ IF EXISTS ] [ <name> ] ABORT ALL QUERIES

ALTER WAREHOUSE [ IF EXISTS ] <name> RENAME TO <new_name>

ALTER WAREHOUSE [ IF EXISTS ] <name> SET [ objectProperties ]
                                         [ objectParams ]

ALTER WAREHOUSE [ IF EXISTS ] <name> UNSET { <property_name> | <param_name> } [ , ... ]

Where:

objectProperties ::=
  WAREHOUSE_SIZE = XSMALL | SMALL | MEDIUM | LARGE | XLARGE | XXLARGE | XXXLARGE | X4LARGE | X5LARGE | X6LARGE
  WAIT_FOR_COMPLETION = TRUE | FALSE
  MAX_CLUSTER_COUNT = <num>
  MIN_CLUSTER_COUNT = <num>
  SCALING_POLICY = STANDARD | ECONOMY
  AUTO_SUSPEND = <num>
  AUTO_RESUME = TRUE | FALSE
  RESOURCE_MONITOR = <monitor_name>
  COMMENT = '<string_literal>'
objectParams ::=
  MAX_CONCURRENCY_LEVEL = <num>
  STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = <num>
  STATEMENT_TIMEOUT_IN_SECONDS = <num>
  TAG <TAG_NAME> = '<string_literal>' [ , <TAG_NAME> = '<string_literal>' ... ]

Properties/Parameters

name

Specifies the identifier for the warehouse to alter. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier Requirements.

Note

A warehouse identifier is required or optional depending on the following:

  • When resuming/suspending a warehouse or aborting queries for a warehouse, if a warehouse is currently in use for the session, the identifier can be omitted.

  • When renaming a warehouse or performing any other operations on a warehouse, the identifier must be specified.

SUSPEND | RESUME [ IF SUSPENDED ]

Specifies the action to perform on the warehouse:

  • SUSPEND removes all compute nodes from a warehouse and put the warehouse into a ‘Suspended’ state.

  • RESUME [ IF SUSPENDED ] brings a suspended warehouse to a usable ‘Running’ state by provisioning compute resources.

    The optional IF SUSPENDED clause specifies whether the ALTER WAREHOUSE command completes successfully when resuming a warehouse that is already running:

    • If omitted, the command fails and returns an error if the warehouse is already running.

    • If specified, the command completes successfully regardless of whether the warehouse is running.

ABORT ALL QUERIES

Aborts all the queries currently running or queued on the warehouse.

RENAME TO new_name

Specifies a new identifier for the warehouse; must be unique for your account.

For more details, see Identifier Requirements.

SET ...

Specifies one or more properties/parameters to set for the warehouse (separated by blank spaces, commas, or new lines):

WAREHOUSE_SIZE = string_constant

Specifies the size of the virtual warehouse. The size determines the amount of compute resources in each warehouse and, therefore, the number of credits consumed while the warehouse is running.

For more information see Resizing a Warehouse.

Valid values
  • XSMALL , 'X-SMALL'

  • SMALL

  • MEDIUM

  • LARGE

  • XLARGE , 'X-LARGE'

  • XXLARGE , X2LARGE , '2X-LARGE'

  • XXXLARGE , X3LARGE , '3X-LARGE'

  • X4LARGE , '4X-LARGE'

  • X5LARGE , '5X-LARGE'

  • X6LARGE , '6X-LARGE'

Default

XLARGE

Note

  • To use a value that contains a hyphen (e.g. '2X-LARGE'), you must enclose the value in single quotes, as shown.

  • To block the immediate return of the ALTER WAREHOUSE command until the resize is complete, add the WAIT_FOR_COMPLETION parameter.

  • Larger warehouse sizes 5X-Large and 6X-Large are currently in preview and only available on Amazon Web Services (AWS). While in preview, provisioning for these larger warehouses may take slightly longer.

WAIT_FOR_COMPLETION = FALSE | TRUE

When resizing a warehouse, you can use this parameter to block the return of the ALTER WAREHOUSE command until the resize has finished provisioning all its compute resources. Blocking the return of the command when resizing to a larger warehouse serves to notify you that your compute resources have been fully provisioned and the warehouse is now ready to execute queries using all the new resources.

Valid values
  • FALSE: The ALTER WAREHOUSE command returns immediately, before the warehouse resize completes.

  • TRUE: The ALTER WAREHOUSE command will block until the warehouse resize completes.

Default

FALSE

Note

  • The value of this parameter is not persisted and must be set to TRUE on every execution if you want the warehouse resizing to complete before this command returns.

  • If set to TRUE and you abort the ALTER WAREHOUSE command, only the waiting is aborted and the warehouse resize will go through. To resize the warehouse back to its original size, you will need to execute another ALTER WAREHOUSE command.

  • This parameter must be used with the WAREHOUSE_SIZE parameter, otherwise an exception will be thrown.

MAX_CLUSTER_COUNT = num

Specifies the maximum number of warehouses for a multi-cluster warehouse. For a single warehouse, this value is always 1.

Valid values

1 to 10

Note that specifying a value greater than 1 indicates the warehouse is a multi-cluster warehouse; however, the value can only be set to a higher value in Snowflake Enterprise Edition (or higher).

For more information, see Multi-cluster Warehouses.

Default

1 (single warehouse)

Tip

For Snowflake Enterprise Edition (or higher), we recommend always setting the value greater than 1 to help maintain high-availability and optimal performance of the (multi-cluster) warehouse. This also helps ensure continuity in the unlikely event that any single warehouse fails.

MIN_CLUSTER_COUNT = num

Specifies the minimum number of warehouses for a multi-cluster warehouse.

Valid values

1 to 10

Note, however, that MIN_CLUSTER_COUNT must be equal to or less than MAX_CLUSTER_COUNT:

  • If both parameters are equal, the warehouse runs in Maximized mode.

  • If MIN_CLUSTER_COUNT is less than MAX_CLUSTER_COUNT, the warehouse runs in Auto-scale mode.

For more information, see Multi-cluster Warehouses.

Default

1

SCALING_POLICY = STANDARD | ECONOMY

Object parameter that specifies the policy for automatically starting and shutting down warehouses in a multi-cluster warehouse running in Auto-scale mode.

For a detailed description of this parameter, see Setting the Scaling Policy for a Multi-cluster Warehouse.

AUTO_SUSPEND = num | NULL

Specifies the number of seconds of inactivity after which a warehouse is automatically suspended.

Valid values

Any integer 0 or greater, or NULL:

  • Setting a value less than 60 is allowed, but may not result in the desired/expected behavior because the background process that suspends a warehouse runs approximately every 60 seconds and, therefore, is not intended for enabling exact control over warehouse suspension.

  • Setting a 0 or NULL value means the warehouse never suspends.

Default

600 (the warehouse suspends automatically after 10 minutes of inactivity)

Important

Setting AUTO_SUSPEND to 0 or NULL is not recommended, unless your query workloads require a continually running warehouse. Note that this can result in significant consumption of credits (and corresponding charges), particularly for larger warehouses.

For more details, see Warehouse Considerations.

AUTO_RESUME = TRUE | FALSE

Specifies whether to automatically resume a warehouse when a SQL statement (e.g. query) is submitted to it. If FALSE, the warehouse only starts again when explicitly resumed using ALTER WAREHOUSE or through the Snowflake web interface.

Valid values
  • TRUE: The warehouse resumes when a new query is submitted.

  • FALSE: The warehouse only resumes when explicitly resumed using ALTER WAREHOUSE or through the Snowflake web interface.

Default

TRUE (the warehouse resumes automatically when a SQL statement is submitted to it)

INITIALLY_SUSPENDED = TRUE | FALSE

Not applicable when altering a warehouse

RESOURCE_MONITOR = rm_name

Specifies the identifier of a resource monitor that is explicitly assigned to the warehouse. When a resource monitor is explicitly assigned to a warehouse, the monitor controls the monthly credits used by the warehouse (and all other warehouses to which the monitor is assigned).

Valid values

Any existing resource monitor.

For more details, see Working with Resource Monitors.

Default

No value (no resource monitor assigned to the warehouse)

Tip

To view all resource monitors and their identifiers, use the SHOW RESOURCE MONITORS command.

COMMENT = 'string_literal'

Adds a comment or overwrites an existing comment for the warehouse.

MAX_CONCURRENCY_LEVEL = num

Object parameter that specifies the concurrency level for SQL statements (i.e. queries and DML) executed by a warehouse. When the level is reached:

  • For a single warehouse or a multi-cluster warehouse (in Maximized mode), additional statements are queued until resources are available.

  • For a multi-cluster warehouse (in Auto-scale mode), additional warehouses are started.

This parameter can be used in conjunction with STATEMENT_QUEUED_TIMEOUT_IN_SECONDS to ensure a warehouse is never backlogged.

For a detailed description of this parameter, see MAX_CONCURRENCY_LEVEL.

STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = num

Object parameter that specifies the time, in seconds, a SQL statement (query, DDL, DML, etc.) can be queued on a warehouse before it is canceled by the system.

This parameter can be used in conjunction with MAX_CONCURRENCY_LEVEL to ensure a warehouse is never backlogged.

For a detailed description of this parameter, see STATEMENT_QUEUED_TIMEOUT_IN_SECONDS.

STATEMENT_TIMEOUT_IN_SECONDS = num

Object parameter that specifies the time, in seconds, after which a running SQL statement (query, DDL, DML, etc.) is canceled by the system.

For a detailed description of this parameter, see STATEMENT_TIMEOUT_IN_SECONDS.

TAG TAG_NAME = 'string_literal' [ , TAG_NAME = '<string_literal>' ... ]

Object parameter that specifies the tag name (i.e. the key) and the tag value.

The tag value is always a string.

UNSET ...

Specifies one (or more) properties and/or parameters to unset for the database, which resets them to the defaults:

  • property_name

  • param_name

    • TAG TAG_NAME [ , TAG_NAME ... ]

You can reset multiple properties/parameters with a single ALTER statement; however, each property/parameter must be separated by a comma. Also, when resetting a property/parameter, you only specify the name; no value is required.

Note

UNSET can be used to unset all the properties and parameters for a warehouse, except WAREHOUSE_SIZE, which can only be changed using SET.

Usage Notes

  • A warehouse does not need to be suspended to set or change any of its properties, including size.

  • When the warehouse size is changed, the change does not impact any statements, including queries, that are currently executing. Once the statements complete, and the compute resources are fully provisioned, the new size is used for all subsequent statements.

  • Suspending a warehouse does not abort any queries being processed by the warehouse at the time it is suspended. Instead, the warehouse completes the queries, then shuts down the compute resources used to process the queries. During this time period, the warehouse is in quiescing mode. When all the compute resources are shut down, the warehouse’s status changes to Suspended.

Examples

Rename warehouse wh1 to wh2:

ALTER WAREHOUSE IF EXISTS wh1 RENAME TO wh2;

Resume a warehouse named my_wh and then change the size of the warehouse while it is running:

ALTER WAREHOUSE my_wh RESUME;

ALTER WAREHOUSE my_wh SET warehouse_size=MEDIUM;