Warehouse Sizes and Features

Snowflake virtual warehouse sizes (as mentioned in Chapter 2) are T-shirt sizes such as X-Small, Small, Medium, Large, and so on. The credit charges for a virtual warehouse start at 1 for X-Small and double each size you go up. This makes it an easy pricing model to remember.

Multicluster Virtual Warehouses

Some organizations may replicate data into separate data marts. They may also shift some data workloads outside of normal business hours or queue usage to boost performance. Snowflake offers users the ability to automatically scale out their virtual warehouse by distributing replicated data, in memory, across separate compute clusters.

Resizing your virtual warehouse can provide performance benefits for slow-running queries and data loading. Multicluster warehouses will automatically increase or decrease the number of queries.

Multicluster virtual warehouses are an Enterprise Edition feature. If multicluster virtual warehouses are enabled for your account, you can also set the maximum and minimum number of clusters for the warehouse. Multicluster warehouses will use multiple clusters of servers to handle cases where fluctuating numbers of concurrent queries occur, such as during peak and off-peak hours. As the load increases, the virtual warehouse automatically starts more clusters to prevent queries from queuing. When the additional clusters are no longer needed, it shuts them down.

Multicluster Credits and Usage

To see your consumption in the web interface, click the name of a warehouse to display the average load on the warehouse for all queries processed and queued over the last two weeks. The page displays the query load in intervals of five minutes or one hour depending on the length of the viewing window. Each time a virtual warehouse is resumed or increases in size, your account is billed for one minute of usage; after the first minute, billing is calculated per second. Figure 3-1 shows what the usage screen looks like.

Credits are charged based on the following:

• The number of servers per cluster, which is determined by the virtual warehouse size

• The number of clusters, if using multicluster virtual warehouses

• The length of time each server in each cluster runs

Query Design

Caching Impacts

When a virtual warehouse is in a running state, it maintains a cache of table data that has been accessed from previously completed queries. The size of the cache is determined by the number of servers in the virtual warehouse. The size of the cache increases as the virtual warehouse size increases.

The cache is dropped when the virtual warehouse is suspended, which may result in slower initial performance for some queries as the virtual warehouse is resumed. The cache is rebuilt when a warehouse is resumed, and queries are then able to take advantage of the improved performance that the cache provides.

Consider the trade-off between saving credits by suspending a virtual warehouse versus maintaining the cache of data from previous queries to help with performance.

Scaling

Virtual warehouse resizing is supported by Snowflake at any time. You can even resize a warehouse while the virtual warehouse is running. When you have a slow query and have other similarly sized queries queued up, it might make sense to resize the warehouse while it’s running.

But be aware that larger is not necessarily faster; for smaller, basic queries that are already executing quickly, you may not see any significant improvement after resizing. If a query is already running, then it will not be impacted by the resized virtual warehouse. The additional virtual warehouses will be used only by the queued or new queries that will be processed.

Creating a Virtual Warehouse

Snowflake Web Interface

1. 1.

   Log in to your Snowflake web interface.

    
2. 2.

   Click Warehouses + Create.

    
3. 3.

   Give your virtual warehouse a name, choose a size, and set the maximum idle time before the virtual warehouse automatically suspends. Click Finish. Figure 3-2 shows this information entered into the web interface.

    

Tip

After entering the details, click Show SQL at the bottom of the Create Warehouse dialog to see the SQL that would perform this same task. Figure 3-3 shows what this would look like based on the setup in Figure 3-2.

Starting, Resuming, Suspending, and Resizing

You can also manually start/resume, suspend, and resize a virtual warehouse. These are especially helpful for one-offs or if you don’t want to wait for the automatic start or suspension of your virtual warehouse. A virtual warehouse can be started at any time, even when it’s first created.

Using a Warehouse

The USE WAREHOUSE command is used only as a SQL command. This simply allows the SQL statements to connect to the correct virtual warehouse during execution. When using the Worksheets page in the web interface, the USE WAREHOUSE command is implied by selecting the virtual warehouse at the top right of the screen, as shown in Figure 3-5.

Setting Up Load Monitoring

The load monitoring dashboard is used for the performance monitoring of your virtual warehouses within your account. There is also a feature of the dashboard that lets you monitor your credits over a period of time. Log into your Snowflake web interface to begin setting up load monitoring.

1. 1.

   Click Warehouses and highlight your chosen warehouse. A page will appear on the right allowing you to grant privileges.

    
2. 2.

   Click Grant Privileges.

    
3. 3.

   For “Privileges to grant,” select MONITOR.

    
4. 4.

   For “Grant privileges to,” select ACCOUNTADMIN.

    
5. 5.

   Click Grant, as shown in Figure 3-6.

    

Figure 3-7 shows how the loading monitoring chart looks on a newly created warehouse. There is one bar at the end of the Warehouse Load Over Time chart, because we ran one query. At the bottom there is a date slider that allows you to increase or decrease the date range you want to see in the chart. There is no history in this chart; therefore, no more bars appear.