Warehouse selection strategies in Snowflake - Commands & Configuration

Introduction

When you run queries in Snowflake, you need to choose a warehouse. Warehouses are like engines that process your data. Choosing the right warehouse helps your queries run faster and saves money.

When you want to run a quick report without waiting too long.

When you have many users running queries at the same time.

When you want to save money by using smaller warehouses for light tasks.

When you need to run heavy data processing jobs quickly.

When you want to balance speed and cost by switching warehouses based on workload.

Commands

This command sets the current warehouse to COMPUTE_WH_SMALL. It tells Snowflake to use this warehouse for running your queries.

Terminal

USE WAREHOUSE COMPUTE_WH_SMALL;

Expected Output

No output (command runs silently)

This command shows which warehouse is currently active. It helps you confirm that your selection worked.

Terminal

SELECT CURRENT_WAREHOUSE();

Expected Output

CURRENT_WAREHOUSE()

COMPUTE_WH_SMALL

This command changes the warehouse for the current session to COMPUTE_WH_LARGE. Use this when you need more power for big queries.

Terminal

ALTER SESSION SET WAREHOUSE = COMPUTE_WH_LARGE;

Expected Output

No output (command runs silently)

This command lists all warehouses in your Snowflake account. It helps you see available options and their sizes.

Terminal

SHOW WAREHOUSES;

Expected Output

name              | state   | size    | type     | auto_suspend | auto_resume

COMPUTE_WH_SMALL  | started | X-Small | standard | 300          | true

COMPUTE_WH_LARGE  | started | Large   | standard | 300          | true

Key Concept

If you remember nothing else from this pattern, remember: choosing the right warehouse size balances query speed and cost.

Common Mistakes

Not setting a warehouse before running queries.

Queries will fail because Snowflake does not know which compute resource to use.

Always run USE WAREHOUSE command or set warehouse in your session before querying.

Using a large warehouse for small queries.

This wastes money because large warehouses cost more to run.

Use smaller warehouses for light workloads to save cost.

Forgetting to check which warehouse is active.

You might run queries on the wrong warehouse, causing unexpected cost or slow performance.

Run SELECT CURRENT_WAREHOUSE() to confirm your warehouse before running queries.

Summary

Use USE WAREHOUSE command to select the warehouse for your queries.

Check the active warehouse with SELECT CURRENT_WAREHOUSE().

Switch warehouses with ALTER SESSION SET WAREHOUSE for different workloads.

List all warehouses with SHOW WAREHOUSES to choose the right one.