Overview of Warehouses¶
A warehouse is defined by its size, as well as the other properties that can be set to help control and automate warehouse activity.
In this Topic:
- Warehouse Size
- Warehouse Billing Continuation (WBC)
- Auto-suspension and Auto-resumption
- Query Processing and Concurrency
- Warehouse Usage in Sessions
Size specifies the number of servers that comprise each cluster in a warehouse. Snowflake supports seven warehouse sizes:
|Warehouse Size||Servers/Credits per Cluster||Additional Details|
|X-Small||1||Default size for warehouses created using CREATE WAREHOUSE.|
|Small||2||Default size for warehouses created in the web interface.|
Impact on Credit Usage and Charges¶
There is a one-to-one correspondence between the number of servers in a cluster and the number of credits the cluster uses (and is, therefore, charged) for each hour (or fraction of an hour) that the warehouse runs. For example, a 3X-Large warehouse utilizes a cluster of 64 servers and, therefore, is charged 64 credits for each hour that it runs. However, the actual number of servers and credits associated with each size may change in the future, based on the underlying Snowflake infrastructure.
For a multi-cluster warehouse, the number of credits charged per hour is calculated based on the number of servers per cluster and the number of clusters that run for each hour. For example, if an X-Large multi-cluster warehouse (16 servers per cluster per hour) runs 1 cluster for an hour and then runs 2 clusters for the next hour, the total number of credits charged is 48 (16 + 32).
Impact on Query Processing¶
The size of a warehouse can impact the amount of time required to execute queries submitted to the warehouse, particularly for larger, more complex queries. In general, query performance scales linearly with warehouse size due to the additional compute resources provisioned with each size increase.
If queries processed by a warehouse are running slowly, you can always resize the warehouse to provision more servers. The additional servers do not impact any queries that are already running, but they are available for use by queries that are queued or newly submitted.
Bigger is not necessarily better, especially for small, basic queries. Also, larger warehouses user proportionally more credits per hour that they run, so you should always be mindful of size when creating or resizing a warehouse.
For more warehouse tips and guidelines, see Warehouse Considerations.
Warehouse Billing Continuation (WBC)¶
Each server in a warehouse cluster has an internal clock that tracks when the server was started; this internal clock is used to calculate the individual credit charges for the server. With WBC, Snowflake automatically tracks the last time each individual server in a warehouse was charged and, if the warehouse is suspended and resumed within 60 minutes of the last charge, the server is not charged again. The server charge is continued from the last time as if the warehouse had never been suspended. As a result, a warehouse can be suspended and restarted multiple times within each hour without incurring additional charges. This also applies to resizing a warehouse, which essentially adds or removes servers from each cluster in the warehouse.
Note that WBC does not require any configuration; it is a common feature available for all warehouses.
Auto-suspension and Auto-resumption¶
A warehouse can be set to automatically resume or suspend, based on activity:
- If auto-suspend is enabled, the warehouse is automatically suspended if the warehouse is inactive for the specified period of time.
- If auto-resume is enabled, the warehouse is automatically resumed when any statement that requires a warehouse is submitted and the warehouse is the current warehouse for the session.
These properties can be used to simplify and automate your monitoring and usage of warehouses to match your workload. Auto-suspend ensures that you do not leave a warehouse running (and using credits) when there are no incoming queries. Similarly, auto-resume ensures that the warehouse only starts up again when needed.
Auto-suspend and auto-resume apply to the entire warehouse and not to the individual clusters in the warehouse, i.e. for a multi-cluster warehouse, these properties only apply when the minimum number of clusters is running (the minimum is typically 1 cluster).
Query Processing and Concurrency¶
The number of queries that a warehouse can concurrently process is determined by the size and complexity of each query. As queries are submitted, the warehouse calculates and reserves the compute resources needed to process each query. If the warehouse does not have enough remaining resources to process a query, it is queued, pending resources that are freed as other running queries complete.
Snowflake provides some object-level parameters that can be set to help control query processing and concurrency:
If queries are queuing more than desired, another warehouse can be created and queries can be manually redirected to the new warehouse. In addition, resizing a warehouse can enable limited scaling for query concurrency and queuing; however, warehouse resizing is primarily intended for improving query performance. However, to enable fully automated scaling for concurrency, Snowflake recommends multi-cluster warehouses, which provide essentially the same benefits as creating additional warehouses and redirecting queries, but without any manual intervention.
Warehouse Usage in Sessions¶
When a session is initiated in Snowflake, the session does not, by default, have a warehouse associated with it. Until a session has a warehouse associated with it, queries cannot be submitted within the session.
Default Warehouse for Users¶
To facilitate querying immediately after a session is initiated, Snowflake supports specifying a default warehouse for each individual user. The default warehouse for a user is used as the warehouse for all sessions initiated by the user.
Default Warehouse for Clients/Drivers/Connectors¶
In addition to default warehouses for users, any of the Snowflake client utilities (SnowSQL, JDBC driver, ODBC driver, Python connector, etc.) can have a default warehouse:
- SnowSQL supports both a configuration file and command line option for specifying a default warehouse.
- The drivers and connectors support specifying a default warehouse as a connection parameter when initiating a session.
For more information, see Connecting to Snowflake.
Precedence for Warehouse Defaults¶
When a user connects to Snowflake and start a session, Snowflake determines the default warehouse for the session in the following order:
Default warehouse for the user,
>>> overridden by...
Default warehouse in the configuration file for the client, e.g. SnowSQL, used to connect to Snowflake (if the client supports configuration files),
>>> overridden by...
Default warehouse specified on the command line (for a client) or through connection parameters passed to Snowflake (through a driver/connector).
In addition, the default warehouse for a session can be changed at any time through the USE WAREHOUSE command.