Warehouse Considerations

This topic provides general guidelines and best practices for use virtual warehouses in Snowflake to process queries. It does not provide specific or absolute numbers, values, or recommendations because every query scenario is different and is affected by numerous factors, including number of concurrent users/queries, number of tables being queried, and data size and composition, as well as your specific requirements for warehouse availability, latency, and cost.

It also does not cover warehouse considerations for data loading, which are covered in another topic (see the sidebar).

The keys to using warehouses effectively and efficiently are:

  1. Start small.
  2. Experiment with different types of queries and different warehouse sizes to determine the combinations that best meet your specific query needs and workload.

Note

These guidelines and best practices apply to both single-cluster warehouses, which are standard for all accounts, and multi-cluster warehouses, which are a Snowflake Enterprise Edition feature.

In this Topic:

How are Credits Charged for Warehouses?

Credit charges are calculated based on warehouse size, number of clusters (if using multi-cluster warehouses), and the length of time each server in each cluster runs. For example:

X-Small:Utilizes 1 server per cluster and charges 1 credit per hour for each cluster; each successive size doubles the number of servers and credits per cluster.
4X-Large:Utilizes 128 servers per cluster and charges 128 credits per hour for each cluster.
  • Each server in a warehouse cluster has the following characteristics:

    • An internal timer that tracks when the server was started; this internal timer is used to calculate the individual credit charges for the server.
    • A position that is maintained for the warehouse, even when the warehouse is suspended or resized. This position impacts how servers are added and removed, i.e. servers are always removed in reverse order of when they were added (aka LIFO, “Last In, First Out”). This is an important detail because it can impact the credits charged when resuming a suspended warehouse or resizing a running warehouse.
  • Credits are first charged when a server is initially provisioned:

    • The charge for provisioning a server is 1 hour (60 minutes), regardless of whether the server runs for the entire 60 minutes or a fraction of the time.
    • Within a given 60-minute period, you can suspend and resume a warehouse as often as you like without incurring additional credit charges for the servers in the warehouse.
    • There is no benefit to stopping a warehouse before the current 60-minute period is up because the credits have already been charged for that period.
  • Credits are next charged at the beginning of each subsequent 60-minute period, if the server is running when the next period starts. If the server is not running when the next period starts, the timer for the server resets to zero.

  • Resizing a warehouse provisions additional servers for each cluster in the warehouse:

    • This results in a corresponding increase in the number of credits charged for the warehouse (while the additional servers are running).
    • The internal timer for the additional servers starts when they are provisioned, i.e. credits for the additional servers are charged relative to the time when the warehouse was resized.

How Does Query Composition Impact Warehouse Processing?

The number of servers required to process a query depends on the size and complexity of the query. For the most part, queries scale linearly with regards to warehouse size, particularly for larger, more complex queries:

  • When considering data size and its impact on query processing, the overall size of the data in the tables being queried has more impact than the number of rows.
  • Filtering via predicates also has an impact, as does the number of tables in the query.

Tip

To achieve the best results, try to execute relatively homogeneous queries (size, complexity, data sets, etc.) on the same warehouse; executing queries of widely-varying size and/or complexity on the same warehouse makes it more difficult to analyze warehouse load, which can make it more difficult to select the best size to match the size, composition, and number of queries in your workload.

Warehouse Caching

Each warehouse, when running, maintains a cache of table data accessed as queries are processed by the warehouse. This enables improved performance for subsequent queries if they are able to read from the cache instead of from the table(s) in the query. The size of the cache is determined by the number of servers in the warehouse. i.e. the larger the warehouse (and, therefore, the number of servers in the warehouse), the larger the cache.

This cache is dropped when the warehouse is suspended, which may result in slower initial performance for some queries after the warehouse is resumed. As the resumed warehouse runs and processes more queries, the cache is rebuilt, and queries that are able to take advantage of the cache will experience improved performance.

Keep this in mind when deciding whether to suspend a warehouse or leave it running (i.e. consider the trade-off between saving credits by suspending a warehouse versus maintaining the cache of data from previous queries to help with performance).

Creating a Warehouse

When creating a warehouse, the two most critical factors to consider, from a cost and performance perspective, are:

  • Warehouse size (i.e. number of servers per cluster)
  • Manual vs automated warehouse management (i.e. starting/resuming and suspending)

The number of clusters in the warehouse is also important (if multi-cluster warehouses are available for your account). For more details, see Scaling Up vs Scaling Out in this topic.

Selecting an Initial Warehouse Size

To prevent unnecessary and unexpected credit charges, always start with a smaller warehouse size (e.g. Small, Medium, or Large) rather than a larger size (e.g X-Large or larger):

  • You can always increase the size of a warehouse at any time.

  • Bigger is not necessarily better, particularly for smaller, more basic queries.

  • In general, try to match the size of the warehouse to the expected size and complexity of the queries to be processed by the warehouse. For example, small/basic queries, regardless of the number of queries being processed concurrently, typically do not need a Large (or larger) warehouse because they won’t necessarily benefit from the additional resources.

  • Experiment by running the same queries against warehouses of multiple sizes (e.g. Small, Medium, and Large):

    • The queries you use for these experiments should be of a size and complexity that you know will typically complete in 15-30 minutes (or less).
    • You can experiment with larger, more complex queries and larger warehouse sizes, but this will naturally increase the number of credits charged.

Automating Warehouse Management

Warehouses can be set to automatically suspend when there’s no activity after a specified period of time and automatically resume when new queries are submitted. Note that the defaults for these options are different depending on how the warehouse is created:

Web Interface:
  • Auto-suspend is set to 1 hour by default.
  • Auto-resume is disabled.
SQL:

Both options are disabled by default, i.e. the warehouse does not suspend on inactivity or resume for new queries.

Auto-suspend is enabled by specifying the time period of inactivity for the warehouse. We recommend enabling/disabling auto-suspend according to your workload and availability needs:

  • Consider disabling auto-suspend for a warehouse if:

    • You have a steady workload for the warehouse.
    • You require the warehouse to be available with no delays. Server provisioning is generally very fast (e.g. 1 or 2 seconds); however, depending on the size of the warehouse and the availability of servers to provision, it can take longer.
  • Because Snowflake charges credits per hour that a warehouse runs, from the time when it is resumed, we always recommend setting auto-suspend to a value less than 1 hour:

    • The value you set should match the gaps, if any, in your query workload. For example, if you have regular gaps of 10 minutes (or less) in your workload, it doesn’t make sense to set auto-suspend to a value less than 10 minutes.
    • The most common auto-suspend use case is shutting down a warehouse during periods of inactivity longer than an hour (lunch breaks, end-of-business, overnight, etc.).

For auto-resume, we recommend enabling/disabling this option depending on how much control you wish to exert over usage of your warehouses:

  • If cost and access are not an issue, enable auto-resume to ensure that the warehouse is available whenever needed; however, keep in mind that there could be a short delay in the resumption of the warehouse due to server provisioning.
  • If you wish to control cost and/or user access, leave auto-resume disabled and instead manually resume the warehouse as needed.

Scaling Up vs Scaling Out

Snowflake supports two ways to scale warehouses:

  • Scaling up by resizing the warehouse.
    Resizing a warehouse generally improves query performance, particularly for larger, more complex queries, and can also help reduce the queuing that occurs if a warehouse does not have enough servers to process all the queries that are submitted concurrently. However, warehouse resizing is not intended for handling concurrency issues; instead, use additional warehouses to handle the load or use multi-cluster warehouse (if this feature is available for your account).
  • Scaling out by adding clusters to the warehouse (requires Snowflake Enterprise Edition).
    Multi-cluster warehouses are designed specifically for handling queuing and performance issues related to large numbers of concurrent users and/or queries. In addition, multi-cluster warehouses can help automate this process if your number of users/queries tend to fluctuate.

Resizing a Warehouse to Improve Performance

Snowflake supports resizing a warehouse at any time, even while running. If a query is running slowly and you have additional queries of similar size and complexity that you want to run on the same warehouse, you might choose to resize the warehouse while it is running; however, note the following:

  • As stated earlier about warehouse size, bigger is not necessarily better; for smaller, basic queries that are already executing quickly, you may not see any significant improvement after resizing.
  • Resizing a running warehouse does not impact queries that are already being processed by the warehouse; the additional servers are only used for queued and new queries.
  • When resizing a warehouse, we don’t recommend skipping sizes (e.g. from Small to X-Large), particularly if you are testing to determine optimal warehouse size; this can make it difficult to analyze the impact of resizing.
  • When the additional servers are no longer required, remember to decrease the warehouse size to use less credits.

Tip

Decreasing the size of a running warehouse removes servers from the warehouse. When the servers are removed, the cache associated with the servers is dropped, which can impact performance in the same way that suspending the warehouse can impact performance when it is resumed.

Keep this in mind when choosing whether to decrease the size of a running warehouse or keep it at the current size. In other words, there is a trade-off with regards to saving credits versus maintaining the server cache.

Using a Multi-cluster Warehouse to Improve Concurrency

Multi-cluster warehouses can be used for dynamically adding and removing clusters to meet changing concurrency needs, based on the number of users or queries that use the warehouse. When deciding whether to use multi-cluster warehouses and the number of clusters to use per warehouse, consider the following:

  • If you are using Snowflake Enterprise Edition (or a higher edition), all your warehouses should be configured as multi-cluster warehouses.

  • Unless you have a specific requirement for running in Maximized mode, multi-cluster warehouses should be configured to run in Auto-scale mode, which enables Snowflake to automatically start and stop clusters as needed.

  • When choosing the minimum and maximum number of clusters for a multi-cluster warehouse:

    Minimum:Keep the default value of 1; this ensures that additional clusters are only started as needed.
    Maximum:Set this value as large as possible, while being mindful of the warehouse size and corresponding credit costs. For example, an X-Large warehouse (16 servers) with minimum 1 cluster and maximum 5 clusters uses from 16 to 80 credits for each hour that the warehouse runs, depending on the number of clusters running during the hour.