Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 38 ยท Architecture

Virtual Warehouses

Create and manage Snowflake virtual warehouses. Learn t-shirt sizes, credits per hour, auto-suspend and auto-resume, and how to pick a size for a workload.

A virtual warehouse is the compute engine of Snowflake. Every query that reads a table, every load and every INSERT ... SELECT needs one. Learning to create and size warehouses is the single most useful skill for controlling both speed and cost.

Creating a warehouse

SQL
-- Snowflake SQL (read-only)
CREATE WAREHOUSE IF NOT EXISTS reporting_wh
  WAREHOUSE_SIZE      = 'XSMALL'
  AUTO_SUSPEND        = 60       -- seconds idle before it pauses
  AUTO_RESUME         = TRUE     -- starts on the next query
  INITIALLY_SUSPENDED = TRUE
  COMMENT = 'Dashboards and ad-hoc analysis';

USE WAREHOUSE reporting_wh;

Key settings:

  • WAREHOUSE_SIZE: how many servers it has.
  • AUTO_SUSPEND: seconds of inactivity before it shuts down. 60 is a common starting point; the minimum billed time each time it starts is 60 seconds.
  • AUTO_RESUME: if true, a query automatically wakes the warehouse. If false, you must run ALTER WAREHOUSE ... RESUME.
  • INITIALLY_SUSPENDED: create it without starting and billing it.

Sizes and credits

Each size doubles the compute of the one before it, and doubles the credits burned per hour:

Size Servers Credits per hour
X-Small 1 1
Small 2 2
Medium 4 4
Large 8 8
X-Large 16 16
2X-Large 32 32
3X-Large to 6X-Large 64 to 512 64 to 512

Billing is per second (after the 60-second minimum on each start). The price of a credit depends on your edition and region.

The counter-intuitive and very useful consequence: for a query that scales well, a bigger warehouse often costs about the same total credits because it finishes proportionally faster. A query taking 60 minutes on an X-Small (1 credit) may take about 15 minutes on a Medium (4 credits per hour times 0.25 hour = 1 credit). You pay the same and wait a quarter as long. Scaling stops paying off when the query cannot use more parallelism, which is typical for small data.

Do the math yourself with this stand-in, which tabulates credits for a run that halves in time at each size step and eventually stops scaling:

SQL Stand-in: size versus cost

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

The numbers are illustrative, not measured. They show the shape: cost stays flat while the query scales, then climbs once it stops.

Start, stop, resize

SQL
-- Snowflake SQL (read-only)
ALTER WAREHOUSE reporting_wh SUSPEND;
ALTER WAREHOUSE reporting_wh RESUME;
ALTER WAREHOUSE reporting_wh SET WAREHOUSE_SIZE = 'MEDIUM';   -- affects new queries
ALTER WAREHOUSE reporting_wh SET AUTO_SUSPEND = 300;
SHOW WAREHOUSES;

Resizing does not interrupt running queries; they finish on the old size, and new queries use the new one. Suspending the warehouse throws away its local data cache (Caching Layers), so a warehouse that suspends after 60 seconds starts cold each time. For BI tools that fire a burst of queries every few minutes, a longer auto-suspend such as 5 minutes can be faster and not much costlier.

Scale up or scale out?

There are two ways to get more throughput:

  • Scale up (larger size): speeds up a single large query. Good for complex joins, big scans and heavy loads.
  • Scale out (more clusters): handles many concurrent queries. This is a multi-cluster warehouse (Enterprise edition and above) that adds clusters when queries queue and removes them when quiet.
SQL
-- Snowflake SQL (read-only)
CREATE WAREHOUSE bi_wh
  WAREHOUSE_SIZE    = 'SMALL'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 4
  SCALING_POLICY    = 'STANDARD'   -- or 'ECONOMY' to wait longer before adding clusters
  AUTO_SUSPEND      = 120;

Rule of thumb: if one query is slow, make the warehouse bigger. If many users' queries wait in a queue, add clusters. The two problems have different fixes. Later lessons on warehouse performance revisit the signals (queuing, spilling to disk) that tell you which one you have.

flowchart LR
  A["One slow query"] --> B["Scale UP<br/>bigger size"]
  C["Many queries waiting"] --> D["Scale OUT<br/>more clusters"]

Match the fix to the symptom.

Dedicated warehouses per workload

Because warehouses are cheap to create, give each workload its own: loading, transformation, BI dashboards, data science. Benefits:

  • one team's heavy query cannot slow another's,
  • you can size each for its job,
  • you can attribute credits to each team, which makes cost reports meaningful,
  • you can set a resource monitor on each to cap spending.
SQL
-- Snowflake SQL (read-only)
CREATE RESOURCE MONITOR bi_monitor
  WITH CREDIT_QUOTA = 100 FREQUENCY = MONTHLY START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 80 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND;
ALTER WAREHOUSE bi_wh SET RESOURCE_MONITOR = bi_monitor;

Choosing a starting size

A reliable approach: start with X-Small and test. Run your typical query, look at the duration and the Query Profile, double the size and run again. When the time stops dropping nearly in half, you have found the sweet spot. Never leave a large warehouse running idle: always set an auto-suspend.

Here is a stand-in that finds that sweet spot from the measurements above, by comparing each size's time to the previous one:

SQL Stand-in: finding where scaling stops

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

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

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

    Each size doubles: X-Small 1, Small 2, Medium 4, Large 8.
  2. 2. What is the effect of AUTO_SUSPEND = 60?

    Auto-suspend shuts the warehouse down after the idle time so you stop paying for compute.
  3. 3. Dozens of dashboard users run queries at once and many wait in a queue. Best fix?

    Queuing from concurrency is solved by more clusters. A bigger size speeds individual queries rather than adding parallel capacity.
  4. 4. Why might a larger warehouse not cost more for the same query?

    Doubling the size doubles the credit rate, but a well-scaling query takes half the time.

Summary

  • A virtual warehouse is the compute that runs queries and loads; you pay per second while it runs.
  • Sizes double compute and credits per hour at each step.
  • Set AUTO_SUSPEND and AUTO_RESUME; suspension stops compute billing but loses the local cache.
  • Scale up for one slow query, scale out (multi-cluster) for concurrency.
  • Use separate warehouses per workload and cap them with resource monitors.

Next: Cloud Services and Metadata