ALTER WAREHOUSE¶

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> 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 }
  RESOURCE_CONSTRAINT = { MEMORY_1X | MEMORY_1X_x86 | MEMORY_16X | MEMORY_16X_x86 | MEMORY_64X | MEMORY_64X_x86 }
  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 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 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 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_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'

• 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

• The default size for Snowpark-optimized warehouses 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 the WAIT_FOR_COMPLETION parameter.

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

RESOURCE_CONSTRAINT = { MEMORY_1X | MEMORY_1X_x86 | MEMORY_16X | MEMORY_16X_x86 | MEMORY_64X | MEMORY_64X_x86 }

Preview Feature — Open

Resource constraints are available to all accounts.

Specifies the memory and CPU architecture for Snowpark-optimized warehouses. The following table includes the valid values for the property, available memory, CPU architecture, and the minimum warehouse size required for the resource_constraint setting:

Valid values:

Value	Memory (up to)	CPU architecture	Min warehouse size required	Max warehouse size
MEMORY_1X	16 GB	Standard	XSMALL	X4LARGE
MEMORY_1X_x86	16 GB	x86	XSMALL	X4LARGE
MEMORY_16X	256 GB	Standard	MEDIUM	X6LARGE
MEMORY_16X_x86	256 GB	x86	MEDIUM	X4LARGE
MEMORY_64X	1 TB [1]	Standard	LARGE	X4LARGE
MEMORY_64X_x86	1 TB [1]	x86	LARGE	X4LARGE

Default value:

MEMORY_16X

This property can only be set if the WAREHOUSE_TYPE is 'SNOWPARK-OPTIMIZED'.

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 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:

• 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 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 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 can only 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 maintain high-availability and optimal performance of the (multi-cluster) warehouse. This also helps ensure continuity in the unlikely event that 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 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:

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

• Setting a value less than 30, or a value that is not a multiple of 30, is allowed but might not result in the expected behavior 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 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 (for example, 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 = monitor_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 cluster. When the level is reached:

• For a single-cluster 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 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 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 = '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 on this 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 based on warehouse size.

Setting the QUERY_ACCELERATION_MAX_SCALE_FACTOR to 0 eliminates the limit and allows queries to lease as many resources as necessary and as 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 by the available resources in the service and the number of other concurrent requests. For more details, refer to Adjusting 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 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.

Access control requirements¶

A role used to execute this SQL command must have at least one of the following privileges 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 on securable 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 suspend a 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 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.

• 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 appropriate warehouse privileges on the warehouse.

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;

Modify the memory resources to 1 TB and set the CPU architecture to x86 for Snowpark-optimized warehouse so_warehouse:

ALTER WAREHOUSE so_warehouse SET
  RESOURCE_CONSTRAINT = 'MEMORY_64X_x86';