Categories:

Warehouse & Resource Monitor DDL

CREATE WAREHOUSE

Creates a new virtual warehouse in the system.

Initial creation of a virtual warehouse might take some time to provision the compute resources, unless the warehouse is created initially in a SUSPENDED state.

See also:

ALTER WAREHOUSE , DESCRIBE WAREHOUSE , DROP WAREHOUSE , SHOW WAREHOUSES

Syntax

CREATE [ OR REPLACE ] WAREHOUSE [ IF NOT EXISTS ] <name>
        [ [ WITH ] objectProperties ]
        [ objectParams ]

Where:

objectProperties ::=
  WAREHOUSE_SIZE = XSMALL | SMALL | MEDIUM | LARGE | XLARGE | XXLARGE | XXXLARGE | X4LARGE | X5LARGE | X6LARGE
  MAX_CLUSTER_COUNT = <num>
  MIN_CLUSTER_COUNT = <num>
  SCALING_POLICY = STANDARD | ECONOMY
  AUTO_SUSPEND = <num> | NULL
  AUTO_RESUME = TRUE | FALSE
  INITIALLY_SUSPENDED = TRUE | FALSE
  RESOURCE_MONITOR = <monitor_name>
  COMMENT = '<string_literal>'
  ENABLE_QUERY_ACCELERATION = TRUE | FALSE
  QUERY_ACCELERATION_MAX_SCALE_FACTOR = <num>

objectParams ::=
  MAX_CONCURRENCY_LEVEL = <num>
  STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = <num>
  STATEMENT_TIMEOUT_IN_SECONDS = <num>
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]

Required Parameters

name

Identifier for the virtual warehouse; must be unique for your account.

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier Requirements.

Optional Properties (objectProperties)

WAREHOUSE_SIZE = XSMALL | SMALL | MEDIUM | LARGE | XLARGE | XXLARGE | XXXLARGE | X4LARGE | X5LARGE | X6LARGE

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

Valid values

Preview Feature — Open

Larger (5X-Large and 6X-Large) warehouse sizes are available in preview in some regions.

Before provisioning a 5X-Large or 6X-Large warehouse, please contact Snowflake Support.

Supported Values	Synonyms
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

XSMALL

Note

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

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 a 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 (only applies to multi-cluster warehouses).

Valid values

1 to 10

However, note 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

Specifies the policy for automatically starting and shutting down clusters in a multi-cluster warehouse running in Auto-scale mode.

Valid values

• STANDARD: Minimizes queuing by starting clusters.

• ECONOMY: Conserves credits by favoring keeping running clusters fully-loaded.

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

Default

STANDARD

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 many 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.

AUTO_RESUME = TRUE | FALSE

Specifies whether to automatically resume a warehouse when a SQL statement (e.g. query) is submitted to it.

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

Specifies whether the warehouse is created initially in the ‘Suspended’ state.

Valid values

• TRUE: The warehouse is created, but suspended.

• FALSE: The warehouse starts running after it is created.

Default

FALSE

RESOURCE_MONITOR = string

Specifies the name 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'

Specifies a comment for the warehouse.

Query Acceleration Properties

Preview Feature — Open

The Query Acceleration Service feature is available to all accounts that are Enterprise Edition (or higher).

ENABLE_QUERY_ACCELERATION = TRUE | FALSE

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

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. For example, if the QUERY_ACCELERATION_MAX_SCALE_FACTOR = 2 for a warehouse, then that warehouse can lease up to 2 times the amount of compute resources of its size to use in query acceleration. Because the query acceleration service may increase the credit consumption rate of a warehouse, the maximum scale factor can help limit the consumption rate.

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. The amount of resources requested for the service depends on how much of the query is eligible for acceleration and how much data will be processed to answer it.

Valid values

0 to 100

Default

8

Optional Parameters (objectParams)

MAX_CONCURRENCY_LEVEL = num

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

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.

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. The maximum number of unique tags that can be set on an object is 20. For more information, see Tag Quotas for Objects/Columns.

A single CREATE statement can set a maximum of 5 tags on an object.

Access Control Requirements

A role used to execute this SQL command must have the following privileges at a minimum:

Privilege	Object	Notes
CREATE WAREHOUSE	Account	Only the SYSADMIN role, or a higher role, has this privilege by default. The privilege can be granted to additional roles as needed.

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 Access Control in Snowflake.

Usage Notes

• Creating a virtual warehouse automatically sets it as the active/current warehouse for the current session (equivalent to using the USE WAREHOUSE command for the warehouse).

• 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.

• Using OR REPLACE is the equivalent of using DROP WAREHOUSE on the existing warehouse and then creating a new warehouse with the same name.

  CREATE OR REPLACE <object> statements are atomic. That is, when the object is replaced, the old object deletion and the new object creation are processed in a single transaction.

  Any queries running on the dropped warehouse are aborted.

Examples

Create an X-Large warehouse:

CREATE OR REPLACE WAREHOUSE my_wh WITH WAREHOUSE_SIZE='X-LARGE';

Create a Large warehouse in a suspended state:

CREATE OR REPLACE WAREHOUSE my_wh WAREHOUSE_SIZE=LARGE INITIALLY_SUSPENDED=TRUE;