Data and Object Management · Topic 1 of 11

Virtual Warehouses - Design, Sizing, Properties and Session Use

Design and maintain virtual warehouses: size and type impact on loading and queries, auto-suspend, auto-resume, cache effects and session level controls.

  • 5 min
In a nutshell
  • Each size step doubles compute and credits per hour; billing is per second with a 60 second minimum per start.
  • Size helps complex queries; concurrency needs clusters or more warehouses.
  • Defaults: AUTO_SUSPEND 600, AUTO_RESUME true, default size X-Small.

What it is

A virtual warehouse is the compute that runs queries, DML and data loading. Storage and compute are separate, so an administrator can create as many warehouses as the workloads need, each sized and tuned on its own.

For the ADA-C02 exam you are rarely asked what a warehouse is. You are asked which size, type and settings fit a workload, and what a change will do to cost and speed.

Business requirementBI, ETL, data scienceWarehouse typeStandard or Snowpark optimizedSizeX Small to 6X LargePropertiessuspend, resume, clustersControlstimeouts, resource monitors

How it works

Size and type

Sizes run from X-Small to 6X-Large. Each step up doubles the compute and also doubles the credits used per running hour. Billing is per second, with a 60 second minimum every time the warehouse starts.

TypeBuilt forNotes
StandardGeneral queries, loading, transformationsDefault type, default size X-Small
Snowpark-optimizedMemory heavy work such as ML training and large Snowpark jobsMuch more memory per node; the default memory setting MEMORY_16X needs Medium or larger; minimum size depends on the memory setting

The Snowpark-optimized memory choice is set with RESOURCE_CONSTRAINT. MEMORY_1X starts at X-Small, MEMORY_16X at Medium and MEMORY_64X at Large.

Exam tip

If a scenario says a Snowpark job fails or spills heavily because of memory, the answer is a Snowpark-optimized warehouse, not just a bigger standard one.

Impact on loading and queries

  • Complex queries on large data gain from a bigger warehouse. Simple queries often gain little.
  • Concurrency is not fixed by size. Many users waiting in a queue need more clusters (the next page) or more warehouses.
  • Data loading depends on the number of files and the data per file. A bigger warehouse helps only when there are enough files to spread across it.
  • Resizing a running warehouse does not affect queries already running. New and queued queries use the added resources once they are provisioned.
  • Scaling down or suspending drops the warehouse's local cache, so the first queries after resume can be slower.

The way to size is to run representative queries on several sizes and compare, not to guess.

Properties you configure

PropertyMeaningDefault
AUTO_SUSPENDSeconds of inactivity before suspending. 0 or NULL means never600
AUTO_RESUMEResume automatically when a statement arrivesTRUE
INITIALLY_SUSPENDEDCreate the warehouse in the suspended stateFALSE
MIN_CLUSTER_COUNT / MAX_CLUSTER_COUNTMulti-cluster limits1 and 1
WAREHOUSE_SIZESize of each clusterXSMALL
CREATE WAREHOUSE bi_wh
  WAREHOUSE_SIZE = 'MEDIUM'
  AUTO_SUSPEND = 120
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

With per-second billing a short auto-suspend (a few minutes) is generally right. If gaps between queries are shorter than the timeout, the warehouse never suspends. If they are only a little longer, every resume bills the 60 second minimum and the cache is lost. Steady heavy workloads, or cases where no resume delay is acceptable, may justify turning auto-suspend off.

Maintaining warehouses

By default SYSADMIN or a higher role holds CREATE WAREHOUSE. On an existing warehouse the privileges split the work:

  • USAGE lets a role run queries on it.
  • OPERATE lets a role suspend, resume and cancel queries.
  • MODIFY lets a role change size and properties.
  • MONITOR lets a role see its usage.

A resource monitor (created by ACCOUNTADMIN) caps credits and can notify or suspend warehouses at thresholds.

Warehouses in sessions

A session runs statements on its current warehouse. The user's default warehouse is used at login, and USE WAREHOUSE switches it. With no current warehouse, queries that need compute fail.

Session level controls protect the account from runaway statements:

ParameterEffectDefault
STATEMENT_TIMEOUT_IN_SECONDSCancels a statement that runs too long172800 (2 days)
STATEMENT_QUEUED_TIMEOUT_IN_SECONDSCancels a statement that waited too long in the queue0 (no limit)
MAX_CONCURRENCY_LEVELStatements one cluster runs at once (warehouse parameter)8

The two timeouts can be set on the account, warehouse, user or session; when the warehouse and the session both set one, the lower non-zero value applies. MAX_CONCURRENCY_LEVEL is set on the account or warehouse only. A common pattern is to give a heavy ETL job its own large warehouse, or to resize before the job and back afterwards.

SessionUSE WAREHOUSE etl_whALTER WAREHOUSEset size LARGERun jobALTER WAREHOUSEset size XSMALL

Common mistakes

Sizing up to fix queuing. More concurrency needs more clusters or warehouses, not a bigger single cluster.

Setting auto-suspend to a few seconds on a busy warehouse. Each resume costs the 60 second minimum and a cold cache.

One warehouse for every workload. Loading, BI and data science compete for the same compute. Separate them.

Assuming a resize stops running queries. It does not; only new and queued work benefits.

What to remember for the exam

  • Each size step doubles compute and credits per hour; billing is per second with a 60 second minimum per start.
  • Size helps complex queries; concurrency needs clusters or more warehouses.
  • Defaults: AUTO_SUSPEND 600, AUTO_RESUME true, default size X-Small.
  • Suspending or scaling down drops the local cache.
  • Snowpark-optimized warehouses are for memory heavy work.
  • OPERATE suspends and resumes, MODIFY resizes.

Check yourself

  • Queries queue up at 9 a.m. when many analysts log in. Does a larger size fix it?

    Not reliably; add clusters with a multi-cluster warehouse or split the workload across warehouses.

  • What happens to a query that is running when you resize its warehouse?

    Nothing; it continues on the old resources, and only new and queued queries use the new size.

  • Which role privilege lets a team lead suspend and resume a warehouse but not resize it?

    OPERATE.

Topic 1 of 11 in Data and Object Management

Test yourself on Data and Object Management

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