Performance Optimization · Topic 2 of 7

Warehouse Sizing, Multi-cluster and Snowpark-optimized Warehouses

Choose between scaling up and scaling out in Snowflake, set virtual warehouse properties, and know when a Snowpark-optimized warehouse is the right fit.

  • 5 min
  • Includes a diagram
In a nutshell
  • Scale up for query complexity and size, scale out (multi-cluster, Enterprise) for concurrency.
  • Maximized mode means min equals max; Auto-scale means max is above min. Policies are STANDARD (default) and ECONOMY.
  • Know AUTO_SUSPEND, AUTO_RESUME, MAX_CONCURRENCY_LEVEL (default 8) and the two statement timeout parameters.

What it is

A virtual warehouse is the compute that runs your queries and DML. You tune it in two directions. Scale up means a bigger size for faster individual queries. Scale out means more clusters for more concurrent queries.

A Snowpark-optimized warehouse is a special warehouse type with much more memory per node, built for memory hungry Snowpark code such as machine learning training.

One slow queryneeds more powerScale upbigger sizeMany queued queriesneed more capacityScale outmore clustersMemory heavy Snowpark codeneeds RAMSnowpark optimizedwarehouse type

How it works

Scale up compared to scale out

Scaling up changes WAREHOUSE_SIZE, doubling credits per hour per step, for big single queries; scaling out adds clusters of a multi-cluster warehouse (Enterprise) for queuing from many concurrent queries.

Scale up (resize)Scale out (multi-cluster)
ChangeWAREHOUSE_SIZEMIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT
SolvesSlow single queries, large scans, joins, sorts, spilling, big loadsQueuing from many concurrent users or jobs
Does not solveConcurrency limitsOne query that is too big for one cluster
EditionAll editionsEnterprise or higher
Applies toNew queries after the change; running queries finish on the old sizeClusters start and stop as load changes

Sizes run from X-Small to 6X-Large, and each step up doubles the compute and the credits per hour (X-Small is 1 credit per hour, Small is 2, Medium is 4). A query that scans little data gains little from a bigger size, so test rather than assume.

Exam tip

"Dashboard users complain about waiting" means concurrency, so add clusters. "One nightly query takes too long" means a bigger warehouse.

Multi-cluster modes and scaling policy

  • Maximized mode: minimum and maximum cluster counts are equal (and above 1). All clusters start with the warehouse.
  • Auto-scale mode: maximum is higher than minimum. Snowflake adds clusters when queries queue and removes them when load falls.
Scaling policyBehavior
STANDARD (default)Favors avoiding queues; starts additional clusters soon after queuing appears
ECONOMYFavors keeping clusters fully used; starts a new cluster only when enough work exists to keep it busy for about 6 minutes, so queries may wait longer

Maximum hourly credits equal the credits for the size times the maximum cluster count. Resizing applies the new size to all clusters.

Virtual warehouse properties to know

PropertyMeaning
WAREHOUSE_SIZECompute per cluster
WAREHOUSE_TYPESTANDARD or SNOWPARK-OPTIMIZED
MIN_CLUSTER_COUNT, MAX_CLUSTER_COUNT, SCALING_POLICYMulti-cluster settings
AUTO_SUSPENDSeconds idle before suspending; saves credits but drops the warehouse cache
AUTO_RESUMEWakes automatically when a query arrives
INITIALLY_SUSPENDEDCreate the warehouse without starting it
MAX_CONCURRENCY_LEVELConcurrent statements per cluster before queuing (default 8)
STATEMENT_QUEUED_TIMEOUT_IN_SECONDSHow long a statement may wait in the queue before it is cancelled
STATEMENT_TIMEOUT_IN_SECONDSHow long a statement may run before it is cancelled
RESOURCE_MONITORResource monitor assigned to the warehouse
ENABLE_QUERY_ACCELERATION, QUERY_ACCELERATION_MAX_SCALE_FACTORQuery acceleration service settings
CREATE WAREHOUSE bi_wh
  WAREHOUSE_SIZE = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 4
  SCALING_POLICY = 'STANDARD'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

Billing is per second, with a 60 second minimum each time a warehouse starts or resumes. Lowering MAX_CONCURRENCY_LEVEL gives each query more of the cluster's resources, which can help heavy queries at the cost of throughput.

Watch out

Very short AUTO_SUSPEND values save idle credits but throw away the warehouse's local disk cache. For repeated, similar queries a slightly longer idle time can be cheaper overall.

Configure for a scenario

BI dashboardsmany usersMulti clusterauto scale, standard policyNightly ETLbig queriesLarger single warehousesuspend after the jobML in SnowparkmemorySnowpark optimizedMEMORY_16X or higher

Separate workloads onto separate warehouses so loading, reporting and data science do not compete, and so each can be sized and monitored on its own.

Snowpark-optimized warehouses

Set WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'. The default gives 16 times the memory per node of a standard warehouse, and RESOURCE_CONSTRAINT chooses the memory level and CPU architecture.

ConstraintMemory (up to)Minimum size
MEMORY_1X, MEMORY_1X_x8616 GBX-Small
MEMORY_16X (default), MEMORY_16X_x86256 GBMedium
MEMORY_64X, MEMORY_64X_x861 TB (preview, AWS only)Large
CREATE WAREHOUSE ml_wh WITH
  WAREHOUSE_SIZE = 'LARGE'
  WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'
  RESOURCE_CONSTRAINT = 'MEMORY_16X';
  • Best for machine learning training in a stored procedure on a single node, and memory heavy UDFs and UDTFs in Snowpark.
  • Plain SQL workloads usually gain little.
  • Creating or resuming one can take longer than a standard warehouse.

Common mistakes

Adding clusters to speed up one query. Extra clusters serve more queries at once; they do not make a single query faster.

Resizing to fix queuing. A bigger warehouse raises throughput somewhat, but the targeted fix for concurrency is multi-cluster.

Expecting a running query to speed up after a resize. The new size applies to queries that start afterward.

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

What to remember for the exam

  • Scale up for query complexity and size, scale out (multi-cluster, Enterprise) for concurrency.
  • Maximized mode means min equals max; Auto-scale means max is above min. Policies are STANDARD (default) and ECONOMY.
  • Know AUTO_SUSPEND, AUTO_RESUME, MAX_CONCURRENCY_LEVEL (default 8) and the two statement timeout parameters.
  • Snowpark-optimized: WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED', 16x memory by default, Medium or larger for MEMORY_16X, for ML training and memory heavy UDFs.

Check yourself

  • Many analysts run queries at once and queries queue. What do you change?

    Enable multi-cluster (increase MAX_CLUSTER_COUNT) so extra clusters start to absorb the concurrent load.

  • Which scaling policy waits to start clusters until they would stay busy for about 6 minutes?

    ECONOMY, which favors credit savings over minimal queuing.

  • What warehouse type suits training a machine learning model in a Snowpark stored procedure?

    A Snowpark-optimized warehouse, which provides far more memory per node.

Topic 2 of 7 in Performance Optimization

Test yourself on Performance Optimization

The full Snowflake DEA-C02 question bank has 709 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.