Snowflake AI Data Cloud Features & Architecture · Topic 7 of 17

Virtual Warehouse Types, Gen 2 and Multi-Cluster Scaling Policies

Configure Snowflake virtual warehouses: sizes, Standard Gen 1 and Gen 2, Snowpark-optimized memory, multi-cluster modes and Standard versus Economy policy.

  • 5 min
  • Includes a diagram
In a nutshell
  • Types: Standard (Gen 1 or Gen 2) and Snowpark-optimized.
  • SQL defaults: XSMALL, AUTO_SUSPEND 600, AUTO_RESUME TRUE, 1 cluster, STANDARD policy, MAX_CONCURRENCY_LEVEL 8.
  • Each size step doubles compute and credits per hour.

What it is

A virtual warehouse is the compute that runs queries, DML, data loading and Snowpark code. You configure four things: its type, its size, its cluster count (single or multi-cluster) and its scaling policy.

Warehouses can be created and changed at any time with SQL or in Snowsight, and they consume credits only while running.

WarehousetypeStandardGen 1 or Gen 2sizeXSMALL to X6LARGEscale upclustersMin and max countscale outpolicyStandard or Economyauto scale only

How it works

Core properties and defaults

CREATE WAREHOUSE bi_wh
  WAREHOUSE_SIZE = 'MEDIUM'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 4
  SCALING_POLICY = 'STANDARD';
PropertyDefault when created with SQL
WAREHOUSE_TYPESTANDARD
WAREHOUSE_SIZEXSMALL (Snowpark-optimized defaults to MEDIUM)
AUTO_SUSPEND600 seconds (0 or NULL means never suspend)
AUTO_RESUMETRUE
INITIALLY_SUSPENDEDFALSE
MIN_CLUSTER_COUNT / MAX_CLUSTER_COUNT1 / 1
SCALING_POLICYSTANDARD
MAX_CONCURRENCY_LEVEL8

Sizes run XSMALL, SMALL, MEDIUM, LARGE, XLARGE, XXLARGE, XXXLARGE, X4LARGE, X5LARGE and X6LARGE. Each step up doubles the compute resources and the credits consumed per hour.

Standard warehouses: Gen 1 and Gen 2

Standard warehouses are the general-purpose type for SQL queries, loading and BI. They come in two generations:

Gen 1Gen 2
HardwareOriginal standard warehouseFaster hardware plus software optimizations
Best forGeneral workloadsAnalytics and data engineering; faster DELETE, UPDATE, MERGE and table scans
How to choose itGENERATION = '1' or RESOURCE_CONSTRAINT = STANDARD_GEN_1GENERATION = '2' (recommended) or RESOURCE_CONSTRAINT = STANDARD_GEN_2
SizesAll sizesNot available for X5LARGE or X6LARGE

New standard warehouses default to Gen 2 when you omit the clause, if Gen 2 is available in the region. The generation clause is set with SQL, and you can switch an existing warehouse between generations whether it is running or suspended. Gen 2 consumes credits at its own rate, so test the workload before switching everything.

Snowpark-optimized warehouses

A Snowpark-optimized warehouse (WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED') gives much more memory per node. The default configuration provides 16x the memory per node of a standard warehouse.

Use it for memory-intensive Snowpark work, such as ML training in a stored procedure, or large Python UDFs and UDTFs. Workloads that do not use Snowpark usually gain nothing from it.

RESOURCE_CONSTRAINT sets the memory and CPU architecture: MEMORY_1X, MEMORY_16X (the default) and their _x86 variants, with larger options in preview on some clouds. Snowpark-optimized warehouses can take longer to start and resume than standard ones.

Exam tip

"Out of memory" errors in a Snowpark Python model training procedure point to a Snowpark-optimized warehouse, not just a larger standard warehouse.

Multi-cluster warehouses

A multi-cluster warehouse (Enterprise Edition or higher) runs several clusters of the same size to serve more concurrent queries. It does not make a single query faster.

ModeSettingsBehavior
MaximizedMIN = MAX, greater than 1All clusters start with the warehouse; fixed capacity
Auto-scaleMAX greater than MINClusters start and stop with demand

Credits scale with running clusters: a MEDIUM warehouse running 3 clusters uses three times the credits of one MEDIUM cluster.

Scaling policies

!Multi-cluster warehouse modes: Maximized starts all clusters for fixed capacity; Auto-scale uses a scaling policy, where Standard starts a cluster as soon as a query queues and optimizes for performance, and Economy starts one only if it would stay busy for at least 6 minutes and optimizes for credits. The scaling policy applies only in auto-scale mode and controls how eagerly clusters start and stop.

PolicyStarts a new clusterShuts down a clusterOptimizes for
Standard (default)As soon as a query queues, or Snowflake estimates the current clusters lack capacityAfter a sustained period of low loadPerformance, minimal queuing
EconomyOnly if Snowflake estimates enough load to keep the new cluster busy for at least 6 minutesWhen less than 6 minutes of work remains for the least loaded clusterCredits; accepts some queuing
Queriesqueue formsStandard policyNew clusterstarts at onceQueriesqueue formsEconomy policyEstimate6 minutes of workonly thenNew clusterstarts

How the exam asks about this

  • "Several hundred analysts; short waits are acceptable; keep the number of active clusters low" -> auto-scale with the Economy policy.
  • "Queries must never queue during the trading day" -> Standard policy, or Maximized mode for fixed capacity.
  • "A Python stored procedure trains a model and runs out of memory" -> Snowpark-optimized warehouse.

Common mistakes

Using multi-cluster to speed up one slow query. Extra clusters add concurrency, not power. A complex query needs a larger size.

Setting a scaling policy in Maximized mode. When MIN equals MAX, all clusters run all the time; the policy has no effect.

Choosing Snowpark-optimized for ordinary SQL. It costs more per hour and helps only memory-heavy Snowpark code.

Assuming Gen 2 is a separate warehouse type. Gen 2 is a generation of the Standard type, selected with GENERATION or RESOURCE_CONSTRAINT.

What to remember for the exam

  • Types: Standard (Gen 1 or Gen 2) and Snowpark-optimized.
  • SQL defaults: XSMALL, AUTO_SUSPEND 600, AUTO_RESUME TRUE, 1 cluster, STANDARD policy, MAX_CONCURRENCY_LEVEL 8.
  • Each size step doubles compute and credits per hour.
  • Gen 2: faster DML and scans; GENERATION = '2'; not for X5LARGE or X6LARGE.
  • Snowpark-optimized: 16x memory per node by default; for ML training and memory-heavy UDFs.
  • Multi-cluster needs Enterprise; Maximized vs Auto-scale.
  • Standard policy favors performance; Economy favors credits with a 6-minute load estimate.
Watch out

Resizing a running warehouse does not speed up queries already running. Only queued and new queries use the new size.

Check yourself

  • Which scaling policy keeps the number of running clusters as low as possible, accepting some queuing?

    Economy, which starts a cluster only when Snowflake estimates at least 6 minutes of work for it.

  • When does a scaling policy have no effect?

    In Maximized mode, where MIN_CLUSTER_COUNT equals MAX_CLUSTER_COUNT and all clusters always run.

  • What does a Snowpark-optimized warehouse provide by default compared to a standard one?

    16 times the memory per node, for memory-intensive Snowpark workloads such as ML training.

Topic 7 of 17 in Snowflake AI Data Cloud Features & Architecture

Test yourself on Snowflake AI Data Cloud Features & Architecture

The full Snowflake SnowPro Core COF-C03 question bank has 845 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.