Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 38 ยท Warehouses and Performance

Virtual Warehouse Sizing and Credits

Understand Snowflake virtual warehouses: T-shirt sizes, credits per hour, per-second billing, auto-suspend and auto-resume, and how to pick a size.

In Snowflake, storage and compute are separate. Your data sits in cloud storage, and virtual warehouses are the clusters of compute that run your queries. A warehouse does no work for you until a query arrives, and you pay for it only while it is running. Choosing its size and behavior is the first and biggest performance and cost decision you make.

This course cannot run Snowflake itself. Snowflake-specific statements appear in read-only code blocks. The runnable examples are small stand-ins written in portable SQL (SQLite in your browser) or Python, and each one says what it imitates.

What a warehouse is

A warehouse is a named compute resource. It has a size, a state (started, suspended, resizing) and settings such as auto-suspend. Every query you run needs a current warehouse, selected with USE WAREHOUSE.

SQL
CREATE WAREHOUSE reporting_wh
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60          -- seconds of idle time before it stops
  AUTO_RESUME = TRUE         -- starts again when a query arrives
  INITIALLY_SUSPENDED = TRUE;

USE WAREHOUSE reporting_wh;

Two properties make warehouses cheap to experiment with. They start in seconds, and they can be resized with one statement, even while queries are running (running queries finish on the old size, new queries use the new one).

Sizes and credits

Warehouse sizes follow a T-shirt scale. Each step up doubles the number of servers, and also doubles the credits billed per hour:

Size Credits per hour
X-Small 1
Small 2
Medium 4
Large 8
X-Large 16
2X-Large 32
3X-Large 64
4X-Large 128

(5X-Large and 6X-Large continue the pattern at 256 and 512.) A credit is Snowflake's unit of compute spending; its price in money depends on your edition, cloud and region.

Billing is per second, with a 60-second minimum each time a warehouse starts or resumes. So a warehouse that runs for 10 seconds is billed for 60, and one that runs for 5 minutes is billed for 300 seconds. Suspended warehouses cost nothing for compute.

Python Credit calculator (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

This is only arithmetic that imitates the billing rule, not a call to Snowflake. It already shows the key insight: doubling the size doubles the hourly rate.

Bigger is not always more expensive

A query that scans a lot of data can often run almost twice as fast on a warehouse twice as large, because the work is spread over twice as many servers. If it does, the credits used are the same: half the time at double the rate. So a large warehouse can finish a heavy query sooner for roughly the same cost, and the user waits less.

That scaling stops helping when:

  • the query is small (it does not have enough work to use more servers),
  • it is limited by one slow step that cannot be parallelized, or
  • it is bounded by something outside the warehouse, such as waiting for a remote function.
Python When does a bigger size pay off?

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The big scan gets much faster as it grows with a similar bill (the 60-second minimum matters only at the extremes), while the tiny query stops improving almost immediately and just costs more. The numbers are invented; real timings come from the Query Profile (see pruning and the Query Profile).

Auto-suspend and auto-resume

AUTO_SUSPEND is the idle time, in seconds, after which the warehouse stops. AUTO_RESUME = TRUE starts it again when a query needs it. Together they mean you rarely pay for idle compute.

Choose the idle time with the cache in mind. A running warehouse keeps recently read data on local SSD (see the caching layers); suspending throws it away. Typical choices:

  • Interactive BI warehouses: a few minutes, so dashboards stay warm between clicks.
  • Batch and ETL warehouses: 60 seconds or less. The job ends, the warehouse stops.
  • Because of the 60-second minimum, an auto-suspend below 60 seconds saves very little.

Manual control is also available:

SQL
ALTER WAREHOUSE reporting_wh SUSPEND;
ALTER WAREHOUSE reporting_wh RESUME;
ALTER WAREHOUSE reporting_wh SET WAREHOUSE_SIZE = 'MEDIUM';

Picking a size

A practical method:

  1. Start with X-Small for a new workload.
  2. Run a representative query and open its Query Profile. If it shows bytes spilled to local or remote storage, the warehouse is running out of memory: try the next size up.
  3. If a large query takes minutes, try two or three sizes up and compare the time and credits. Stop when time stops dropping near-proportionally.
  4. Keep separate warehouses for separate workloads (loading, BI, data science) so one cannot slow down another, and so you can see the cost of each.

Warehouse types matter too. A standard warehouse suits most work. A Snowpark-optimized warehouse offers much more memory per node for memory-hungry machine learning code (see Snowpark DataFrames).

Never size up to fix a query that is slow because it scans far too much data. Fix the data access first (see clustering keys): a smaller warehouse reading less data beats a bigger one reading everything.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A Medium warehouse costs how many credits per hour?

    Credits per hour double with each size: X-Small 1, Small 2, Medium 4, Large 8.
  2. 2. A warehouse resumes, runs one query for 10 seconds and suspends. How many seconds are billed?

    Billing is per second but with a 60-second minimum each time a warehouse starts or resumes.
  3. 3. A query scales almost perfectly with size. What does moving it from Small to Large usually do to its credits?

    Four times the rate for about a quarter of the time gives about the same cost, so the user simply waits less.
  4. 4. What does AUTO_RESUME = TRUE do?

    With auto-resume, a query sent to a suspended warehouse starts it automatically.

Summary

  • A virtual warehouse is separate, resizable compute; you pay only while it runs.
  • Each size step doubles servers and credits per hour (XS = 1).
  • Billing is per second with a 60-second minimum per resume.
  • Large sizes help big scans; they do not help tiny queries or badly pruned scans.
  • Use auto-suspend and auto-resume, and one warehouse per workload.

Next: Multi-cluster warehouses