Categories:
Warehouse & Resource Monitor DDL

CREATE WAREHOUSE

Creates a new virtual warehouse in the system. Creating a warehouse includes specifying its size. Once a warehouse is created, it can be specified as the current warehouse for a user session through the USE WAREHOUSE command.

Initial creation of a warehouse may take some time to provision the servers, unless the warehouse is set to be created initially in a SUSPENDED state.

See also:
ALTER WAREHOUSE , SHOW WAREHOUSES

Syntax

CREATE [ OR REPLACE ] WAREHOUSE [ IF NOT EXISTS ] <name>
        [ [ WITH ] objProperties ]
        [ objParams ]

Where:

objProperties ::=
  WAREHOUSE_SIZE = XSMALL | SMALL | MEDIUM | LARGE | XLARGE | XXLARGE | XXXLARGE | X4LARGE
  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>'
objParams ::=
  MAX_CONCURRENCY_LEVEL = <num>
  STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = <num>
  STATEMENT_TIMEOUT_IN_SECONDS = <num>

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

Optional Properties (objProperties)

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

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

Valid values:
Supported Values Synonyms
XSMALL 'X-SMALL'
SMALL  
MEDIUM  
LARGE  
XLARGE 'X-LARGE'
XXLARGE X2LARGE , '2X-LARGE'
XXXLARGE X3LARGE , '3X-LARGE'
X4LARGE '4X-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.

MAX_CLUSTER_COUNT = num

Specifies the maximum number of server clusters for the warehouse.

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 be only 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 warehouse. This also helps ensure continuity in the unlikely event that a cluster fails.

MIN_CLUSTER_COUNT = num

Specifies the minimum number of server clusters for the 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 number greater than 59 or NULL:

  • Setting a value less than 59 has no effect because the minimum amount of time a warehouse can run before it suspends is 1 minute.
  • Setting a NULL value means the warehouse never suspends.
Default:

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

Important

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

For more details, see:doc:user-guide/warehouses-considerations.

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)

WAIT_FOR_PROVISIONING = TRUE | FALSE

Specifies whether the warehouse, after being resized, waits for all the servers to provision before executing any queued or new queries.

Valid values:
  • TRUE: The warehouse waits for all servers to be provisioned.
  • FALSE: The warehouse starts executing queries as soon as enough servers are available.

For more details, see Working with Resource Monitors.

Default:

FALSE

COMMENT = 'string_literal'
Specifies a comment for the warehouse.

Optional Parameters (objParams)

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.

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;