ALTER WAREHOUSE | Snowflake Documentation (2024)

Syntax¶

ALTER WAREHOUSE [ IF EXISTS ] [ <name> ] { SUSPEND | RESUME [ IF SUSPENDED ] }ALTER WAREHOUSE [ IF EXISTS ] [ <name> ] ABORT ALL QUERIESALTER WAREHOUSE [ IF EXISTS ] <name> RENAME TO <new_name>ALTER WAREHOUSE [ IF EXISTS ] <name> SET [ objectProperties ] [ objectParams ]ALTER WAREHOUSE [ IF EXISTS ] <name> SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]ALTER WAREHOUSE [ IF EXISTS ] <name> UNSET TAG <tag_name> [ , <tag_name> ... ]ALTER WAREHOUSE [ IF EXISTS ] <name> UNSET { <property_name> | <param_name> } [ , ... ]

Where:

objectProperties ::= WAREHOUSE_TYPE = { STANDARD | 'SNOWPARK-OPTIMIZED' } 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> | NULL } AUTO_RESUME = { TRUE | FALSE } RESOURCE_MONITOR = <monitor_name> COMMENT = '<string_literal>' ENABLE_QUERY_ACCELERATION = { TRUE | FALSE } QUERY_ACCELERATION_MAX_SCALE_FACTOR = <num>

Copy

objectParams ::= MAX_CONCURRENCY_LEVEL = <num> STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = <num> STATEMENT_TIMEOUT_IN_SECONDS = <num>

Copy

Properties/parameters¶

name

Specifies the identifier for the warehouse to alter. If the identifier contains spaces or special characters, the entire string must be enclosedin 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 identifiercan 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 puts 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 thatis 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_TYPE = { STANDARD | 'SNOWPARK-OPTIMIZED' }

Specifies the warehouse type.

Valid values:

• STANDARD, 'STANDARD'

• 'SNOWPARK-OPTIMIZED'

Default:

STANDARD

Note

To use a value that contains a hyphen ('SNOWPARK-OPTIMIZED'), you must enclose the value in single quotes, as shown.

WAREHOUSE_SIZE = string_constant

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

For more information see Resizing a warehouse.

Valid values:

• XSMALL , 'X-SMALL'

  See Also

  Maximizing Warehouse Efficiency: Key Inventory Control StrategiesTailoring Snowflake Virtual Warehouse Size for Efficient Data LoadingManagement 101: How to Maximize Your Warehouse Space UtilizationTop 10 Warehouse Management Challenges & Their Solutions

• 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

• XSMALL, SMALL, X5LARGE, and X6LARGE sizes do not support Snowpark-optimized warehouses. The default size for Snowpark-optimizedwarehouses is MEDIUM.

• To use a value that contains a hyphen (for example, '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 theWAIT_FOR_COMPLETION parameter.

• Larger warehouse sizes 5X-Large and 6X-Large are generally available in all Amazon Web Services (AWS) and Microsoft Azure regions.

  Larger warehouse sizes are in preview in US Government regions (requires FIPS support on ARM).

WAIT_FOR_COMPLETION = { TRUE | FALSE }

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

Valid values:

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

• FALSE: The ALTER WAREHOUSE command returns immediately, before 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 tocomplete 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 gothrough. 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 clusters for a multi-cluster warehouse. For a single-cluster 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 canonly be set to a higher value in Snowflake Enterprise Edition (or higher).

For more information, see Multi-cluster warehouses.

Default:

1 (single-cluster warehouse)

Tip

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

MIN_CLUSTER_COUNT = num

Specifies the minimum number of clusters 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 clusters in a multi-cluster warehouserunning 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:

• The background process that suspends a warehouse runs approximately every 30 seconds and therefore, the setting forthis property is not intended for enabling precise control over warehouse suspension.

  See Also

  What is snowflaking (snowflake schema)? | Definition from TechTarget

• Setting a value less than 30, or a value that is not a multiple of 30, is allowed but might not result in the expectedbehavior due to the 30 second poll interval for 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 runningwarehouse. 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 (for example, query) is submitted to it. If FALSE, the warehouseonly 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 = monitor_name

Specifies the identifier of a resource monitor that is explicitly assigned to the warehouse. When a resource monitor is explicitly assignedto 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 cluster. Whenthe level is reached:

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

• For a multi-cluster warehouse (in Auto-scale mode), additional clusters 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 iscanceled 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 = 'tag_value' [ , tag_name = 'tag_value' , ... ]

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256.

For information about specifying tags in a statement, see Tag quotas for objects and columns.

ENABLE_QUERY_ACCELERATION = { TRUE | FALSE }

Specifies whether to enable the query acceleration service for queries that rely onthis warehouse for compute resources.

Enterprise Edition Feature

Query acceleration service requires Enterprise Edition (or higher).To inquire about upgrading, please contact Snowflake Support.

Valid values:

• TRUE Enables Query Acceleration

• FALSE Disables Query Acceleration

Default:

FALSE: Query Acceleration is disabled

QUERY_ACCELERATION_MAX_SCALE_FACTOR = num

Specifies the maximum scale factor for leasing compute resources for query acceleration. The scale factor is used as a multiplier basedon warehouse size.

Setting the QUERY_ACCELERATION_MAX_SCALE_FACTOR to 0 eliminates the limit and allows queries to lease as many resources as necessary andas available to service the query.

Regardless of the QUERY_ACCELERATION_MAX_SCALE_FACTOR value, the amount of available compute resources for query acceleration is bound bythe available resources in the service and the number of other concurrent requests. For more details, refer toAdjusting the scale factor.

Valid values:

0 to 100

Default:

8

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 bya 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 onlybe changed using SET.

Access control requirements¶

A role used to execute this SQL command must have at least one of the followingprivileges at a minimum:

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions onsecurable objects, see Overview of Access Control.

Usage notes¶

• A warehouse does not need to be suspended to set or change any of its properties, except for type.

• To change the warehouse type, the warehouse must be in the suspended state. Execute the following statement to suspenda warehouse:

  ALTER WAREHOUSE mywh SUSPEND;

  Copy

• When the warehouse size is changed, the change does not impact any statements, including queries, that are currently executing. Once thestatements 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, thewarehouse completes the queries, then shuts down the compute resources used to process the queries. During this time period, the warehouseis in quiescing mode. When all the compute resources are shut down, the warehouse’s status changes to Suspended.

• Regarding metadata:

  Attention

  Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.

• Resuming a Snowpark-optimized virtual warehouse may take longer than standard warehouses.

• Snowpark-optimized warehouses do not support Query Acceleration.

• Snowpark-optimized warehouses are not supported on XSMall, SMALL, X5LARGE, or X6LARGE warehouse sizes.

• Specifying the IF EXISTS clause requires the role in use or a role in the active role hierarchy to have the appropriatewarehouse privileges on the warehouse.

Examples¶

Rename warehouse wh1 to wh2:

ALTER WAREHOUSE IF EXISTS wh1 RENAME TO wh2;

Copy

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;

Copy