Warehouse sizes and scaling in Snowflake - Commands & Configuration

Introduction

When you run data queries, you need the right amount of computing power. Warehouse sizes let you choose how much power to use. Scaling helps you add or remove power automatically to handle more or less work.

When you want faster query results by using more computing power.

When your data workload changes during the day and you want to save money by using less power when idle.

When multiple users run queries at the same time and you want to avoid waiting.

When you want to control costs by choosing a warehouse size that fits your budget.

When you want your warehouse to automatically add or remove clusters based on demand.

Commands

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

Terminal

CREATE WAREHOUSE my_warehouse WITH WAREHOUSE_SIZE = 'MEDIUM' AUTO_SUSPEND = 300 AUTO_RESUME = TRUE;

Expected Output

Statement executed successfully.

→

WAREHOUSE_SIZE - Sets the size of the warehouse which controls compute power.

→

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 warehouse size to large to increase computing power for faster queries.

Terminal

ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE = 'LARGE';

Expected Output

Statement executed successfully.

→

WAREHOUSE_SIZE - Adjusts the compute power by changing the warehouse size.

This command enables multi-cluster scaling. The warehouse will have at least 1 cluster and can scale up to 3 clusters automatically based on workload.

Terminal

ALTER WAREHOUSE my_warehouse SET MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 3 SCALING_POLICY = 'STANDARD';

Expected Output

Statement executed successfully.

→

MIN_CLUSTER_COUNT - Minimum number of clusters to keep running.

→

MAX_CLUSTER_COUNT - Maximum number of clusters to scale up to.

→

SCALING_POLICY - Controls how clusters are added or removed.

This command shows the current configuration and status of the warehouse named 'my_warehouse'.

Terminal

SHOW WAREHOUSES LIKE 'my_warehouse';

Expected Output

name          | state   | size   | min_cluster_count | max_cluster_count | scaling_policy | auto_suspend | auto_resume

my_warehouse | RUNNING | LARGE  | 1                 | 3                 | STANDARD       | 300          | TRUE

Key Concept

If you remember nothing else from this pattern, remember: warehouse size controls power and scaling adjusts clusters automatically to match workload.

Common Mistakes

Setting warehouse size too small for workload

Queries run slowly because there is not enough compute power.

Choose a warehouse size that matches your query speed needs.

Not enabling auto_suspend

Warehouse keeps running and costs money even when idle.

Set AUTO_SUSPEND to a reasonable time to save costs.

Setting MAX_CLUSTER_COUNT lower than needed

Warehouse cannot scale up enough during peak demand, causing query delays.

Set MAX_CLUSTER_COUNT high enough to handle peak workloads.

Summary

Create a warehouse with a chosen size and auto suspend/resume settings.

Change warehouse size to increase or decrease compute power.

Enable multi-cluster scaling to automatically add or remove clusters based on workload.

Check warehouse status and configuration with SHOW WAREHOUSES command.