Working with Warehouses

All warehouse tasks can be performed from the Snowflake web interface or using the DDL commands for warehouses.

In this Topic:

Creating a Warehouse

A warehouse can be created through the web interface or using SQL:

Web Interface:Click on WarehousesCreate
SQL:CREATE WAREHOUSE

When you create a warehouse, you can specify whether the warehouse is created initially in the “Started” (i.e. running) or “Suspended” state. If you choose “Started”, the warehouse starts consuming credits once the first server is provisioned for the warehouse.

Note

If you choose to create a warehouse in the “Started” state, the warehouse may take some time to become fully available as Snowflake provisions all the servers for the warehouse.

Starting/Resuming a Warehouse

A warehouse can be started at any time, including on initial creation. Once a warehouse is created, resuming a warehouse is the same as starting a warehouse.

A suspended (i.e. inactive) warehouse can be resumed through the web interface or using SQL:

Web Interface:Click on Warehousessuspended_warehouse_nameResume
SQL:ALTER WAREHOUSE

Starting a warehouse typically takes only a few seconds; however, in some rare instances, it can take longer as Snowflake provisions the servers that provide the compute resources for the warehouse.

Warehouses consume credits while running:

  • A warehouse begins to consume credits when the first server in the warehouse is provisioned:
  • In the event that any individual server takes longer than five minutes to provision, the maximum amount of time charged for all server provisioning is five minutes.
  • Snowflake does not begin executing SQL statements submitted to a warehouse until all of the servers for the warehouse are successfully provisioned, unless any of the servers fail to provision:
    • If any of the servers for the warehouse fail to provision during start-up, Snowflake attempts to repair the failed server(s).
    • During the repair process, the warehouse starts processing SQL statements once 50% or more of the requested servers are successfully provisioned.

Credits are billed on a per-second basis while the warehouse is running, with a 1-minute minimum each time the warehouse is resumed; however, credit consumption is reported in 60-minute (i.e. hourly) increments.

Note

A warehouse must be started/running to process SQL statements submitted in a session; however, the warehouse must also be specified as the current warehouse for the session. For more information, see Using a Warehouse in this topic.

Suspending a Warehouse

A running (i.e. “Started”) warehouse can be suspended at any time, even while executing SQL statements. Suspending a warehouse stops the warehouse from consuming credits once all the servers shut down.

A warehouse can be suspended through the web interface or using SQL:

Web Interface:Click on Warehousesstarted_warehouse_nameSuspend
SQL:ALTER WAREHOUSE

When you suspend a warehouse, Snowflake immediately shuts down all idle servers for the warehouse, but allows any servers that are executing statements to continue until the statements complete, at which time the servers are shut down and the status of the warehouse changes to “Suspended”. Servers waiting to shut down are considered to be in “quiesce” mode.

Resizing a Warehouse

A warehouse can be resized up or down at any time, including while it is running and processing statements.

A warehouse can be resized through the web interface or using SQL:

Web Interface:Click on Warehouseswarehouse_nameConfigure
SQL:ALTER WAREHOUSE

Resizing a warehouse to a larger size is useful when the operations being performed by the warehouse will benefit from more compute resources, including:

  • Improving the performance of large, complex queries against large data sets.
  • Improving performance while loading and unloading significant amounts of data.

Effects of Resizing a Running Warehouse

Resizing a running warehouse adds or removes servers for each cluster in the warehouse. All the usage and credit rules associated with starting or suspending a warehouse apply to resizing a started warehouse, such as:

  • Servers added to a warehouse start using credits when they are provisioned; however, the additional servers don’t start executing statements until they are all provisioned, unless some of the servers fail to provision.
  • Servers are removed from a warehouse only when the servers are no longer being used to execute any current statements.

Resizing a warehouse doesn’t have any impact on statements that are currently being executed by the warehouse. When resizing to a larger size, the new servers are used only to execute statements that are already in the warehouse queue, as well as all future statements submitted to the warehouse.

Effects of Resizing a Suspended Warehouse

Resizing a suspended warehouse does not provision any new servers for the warehouse. It simply instructs Snowflake to provision the additional servers when the warehouse is next resumed, at which time all the usage and credit rules associated with starting a warehouse apply.

Using a Warehouse

To execute a query or DML statement in Snowflake, a warehouse must be running and it must be specified as the current warehouse for the session in which the query/statement is submitted.

A Snowflake session can only have one current warehouse at a time. The current warehouse for a session can be specified or changed at any time through the USE WAREHOUSE command.

Once a running warehouse has been set as the current warehouse for a session, queries and DML statements submitted within the session are processed by the warehouse. In the History and Worksheet pages in the Snowflake web interface, you can view the warehouse used to process each query/statement.