Snowflake Cheatsheet: Virtual Warehouses

A virtual warehouse (VW) in Snowflake is a logical container for compute resources. A VW is used to run queries and other workloads.

Virtual warehouses are a key concept in Snowflake’s architecture. They allow you to scale your compute resources up or down as needed, without having to manage any physical hardware.

How Virtual Warehouses Work

When you create a VW in Snowflake, you specify the number of compute units (CUs) that you want to allocate to the VW. CUs are a measure of compute power. The more CUs you allocate to a VW, the more powerful the VW will be.

Once you have created a VW, you can use it to run queries and other workloads. When you run a query, Snowflake will allocate the necessary CUs from the VW to the query. The query will run until it is finished, and then the CUs will be released back to the VW.

Scaling Virtual Warehouses

You can scale VWs up or down as needed. To scale a VW up, you simply add more CUs to the VW. To scale a VW down, you simply remove CUs from the VW.

Scaling VWs is a simple process that can be done from the Snowflake web interface or using the Snowflake API.

Benefits of Using Virtual Warehouses

There are several benefits to using virtual warehouses in Snowflake:

• Scalability: Virtual warehouses are highly scalable. You can scale them up or down as needed, without having to manage any physical hardware.
• Cost-effectiveness: Virtual warehouses are cost-effective. You only pay for the CUs that you use.
• Performance: Virtual warehouses offer high performance. Snowflake’s architecture is designed to optimize the performance of queries that run on VWs.

Hands on

The CREATE WAREHOUSE statement is used to create a new virtual warehouse in Snowflake. The syntax for the CREATE WAREHOUSE statement is as follows:

CREATE WAREHOUSE <warehouse_name>
  WAREHOUSE_SIZE = <warehouse_size>
  INITIALLY_SUSPENDED = <boolean>

The <warehouse_name> is the name of the new virtual warehouse. The <warehouse_size> is the number of compute units (CUs) that you want to allocate to the virtual warehouse. The options are:

The <boolean> value for INITIALLY_SUSPENDED specifies whether the virtual warehouse should be created in a suspended state. If the value is TRUE, the virtual warehouse will be created in a suspended state. If the value is FALSE, the virtual warehouse will be created in an active state.

For example, the following command creates a new virtual warehouse called mywarehouse:

CREATE WAREHOUSE mywarehouse
  WAREHOUSE_SIZE = 'SMALL'
  INITIALLY_SUSPENDED = FALSE

We can make the create statement slightly more complex, too.

AUTO_RESUME is a feature that automatically resumes a suspended warehouse when it is idle for a specified period of time. This can be useful if you have a warehouse that you use occasionally, but you don’t want to have to manually resume it each time you need to use it.

AUTO_SUSPEND is a feature that automatically suspends a warehouse when it is idle for a specified period of time, the value is specified in seconds.

The STANDARD scaling policy is the default scaling policy in Snowflake. It is designed to provide a good balance between performance and cost. The STANDARD scaling policy will automatically scale the size of your warehouse up or down based on the number of active sessions. The minimum size of a warehouse that is scaled up by the STANDARD scaling policy is 2 CUs, and the maximum size is 144 CUs.

The ECONOMY scaling policy is designed to provide the best possible cost savings. The ECONOMY scaling policy will automatically scale the size of your warehouse up or down based on the number of active sessions. The minimum size of a warehouse that is scaled up by the ECONOMY scaling policy is 1 CU, and the maximum size is 64 CUs.

CREATE WAREHOUSE mywarehouse 
WAREHOUSE_SIZE = 'SMALL' 
AUTO_SUSPEND = 600
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED=TRUE
SCALING_POLICY = 'STANDARD';

Alter Warehouse

You can alter warehouses in Snowflake using the ALTER WAREHOUSE statement. The ALTER WAREHOUSE statement allows you to change the following properties of a warehouse:

• Warehouse name: The name of the warehouse.
• Warehouse size: The number of compute units (CUs) that are allocated to the warehouse.
• Scaling policy: The scaling policy that is used to control the size of the warehouse.
• Auto resume: Whether the warehouse is automatically resumed when it is idle.
• Auto suspend: How long of idling until a warehouse will be suspended

The ALTER WAREHOUSE statement is a powerful tool that can be used to manage your warehouses. By using the ALTER WAREHOUSE statement, you can ensure that your warehouses are sized appropriately and that they are configured to meet your specific needs.

Here is the syntax for the ALTER WAREHOUSE statement:

ALTER WAREHOUSE <warehouse_name>
  [WAREHOUSE_SIZE = <warehouse_size>]
  [SCALING_POLICY = <scaling_policy>]
  [AUTO_RESUME = <boolean>]
  [AUTO_SUSPEND = <boolean>]
  [AUTO_SUSPEND_INTERVAL = <interval>]

Virtual warehouses are a key concept in Snowflake’s architecture. They allow you to scale your compute resources up or down as needed, without having to manage any physical hardware. Virtual warehouses are also cost-effective and offer high performance. I hope this blog summary around virtual warehouses is helpful. Let me know if you have any other questions.

Much of the text in this blog has been generated by Google Bard to help accelerate the time to write this article.