Why virtual warehouses control compute independently in Snowflake - Why It Works

Introduction

Virtual warehouses in Snowflake manage compute resources separately from storage. This separation lets you scale compute power up or down without affecting your stored data, making your data processing flexible and efficient.

When you want to run multiple queries at the same time without slowing each other down

When you need to pause compute resources to save costs but keep your data safe

When you want to increase compute power temporarily for heavy data processing

When you want to isolate workloads so one does not affect the performance of another

When you want to manage compute costs by controlling warehouse size and usage independently

Commands

This command creates a virtual warehouse named 'my_warehouse' with a small size. It will automatically suspend after 5 minutes of inactivity to save costs and resume automatically when a query runs.

Terminal

CREATE WAREHOUSE my_warehouse WITH WAREHOUSE_SIZE = 'SMALL' WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 300 AUTO_RESUME = TRUE;

Expected Output

Statement executed successfully.

→

WAREHOUSE_SIZE - Sets the compute power size for the warehouse.

→

AUTO_SUSPEND - Automatically suspends the warehouse after inactivity to save costs.

→

AUTO_RESUME - Automatically resumes the warehouse when a query is submitted.

This command changes the compute size of 'my_warehouse' to medium, increasing its compute power without affecting stored data.

Terminal

ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE = 'MEDIUM';

Expected Output

Statement executed successfully.

This command lists all virtual warehouses and their current states, showing compute sizes and whether they are running or suspended.

Terminal

SHOW WAREHOUSES;

Expected Output

name          | state     | size   | type     | auto_suspend | auto_resume

my_warehouse | SUSPENDED | MEDIUM | STANDARD | 300          | TRUE

This command sets 'my_warehouse' as the active warehouse for running queries, using its compute resources independently.

Terminal

USE WAREHOUSE my_warehouse;

Expected Output

Statement executed successfully.

Key Concept

Virtual warehouses let you control compute power separately from data storage, so you can scale and manage resources flexibly without affecting your data.

Common Mistakes

Trying to change compute size while the warehouse is running heavy queries

It can cause delays or errors because the warehouse is busy and cannot resize immediately.

Pause or wait for the warehouse to be idle before resizing compute power.

Not setting AUTO_SUSPEND, leading to unnecessary costs

The warehouse keeps running and consuming compute resources even when not in use.

Always set AUTO_SUSPEND to a reasonable time to save costs when idle.

Confusing storage scaling with compute scaling

Storage is managed separately and does not change when you resize a warehouse, so expecting data size to affect compute size causes confusion.

Understand that compute scaling only affects processing power, not data storage.

Summary

Create virtual warehouses to manage compute resources independently from data storage.

Use commands to resize warehouses and control auto suspend/resume for cost efficiency.

Check warehouse status to monitor compute resource usage and state.