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 Warehouses > Create 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 using credits as soon as you submit the creation request.
If you chose to start a warehouse on creation, the warehouse may take 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 Warehouses > suspended_warehouse_name > Resume SQL: ALTER WAREHOUSE
Starting a warehouse can take some time, up to five minutes and sometimes longer, as Snowflake provisions the servers that provide the compute resources for the warehouse:
- A warehouse begins to use credits as soon as the resume (or creation) request is submitted.
- 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.
Running warehouses use (and are charged) credits for each hour (or fraction of an hour) that they run.
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. A warehouse can be suspended through the web interface or using SQL:
Web Interface: Click on Warehouses > started_warehouse_name > Suspend SQL: ALTER WAREHOUSE
When you suspend a warehouse, Snowflake immediately shuts down all idle servers for the warehouse, but allows 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.
Suspending a warehouse stops the warehouse from using credits once all the servers shut down. However, there is no benefit, i.e. credit savings, associated with suspending a warehouse because all credits are charged at the beginning of each hour that the warehouse runs.
Also, you can resume a warehouse again within the hour in which it was first resumed and no additional credits are charged until the next hour starts.
Resizing a Warehouse¶
A warehouse can be resized 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 Warehouses > warehouse_name > Configure 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 the cluster for 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 the resize request is submitted; 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. The new number of servers (larger or smaller) are used only to execute statements that are already in the warehouse queue, as well as all future statements submitted to the warehouse.
A resized warehouse is charged for the number of credits associated with its largest size within the hour in which it was resized, i.e. sizing a warehouse to a smaller size uses the credits associated with the original, larger size until the hour passes.
- You start a large warehouse (8 credits).
- After 30 minutes, you resize it to medium (4 credits).
- After 25 minutes, you suspend the warehouse.
The total credits charged for the warehouse is 8 (i.e. for the large size). After resizing, if the warehouse runs longer than an hour, it is charged 4 credits (i.e. for the medium size) for each additional hour (or fraction) that it runs, until it is suspended or resized again.
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.