Snowflake AI Data Cloud Features & Architecture · Topic 9 of 17
Warehouse Best Practices - Sizing Up and Down, Scaling In and Out, Auto-Suspend
Know when to size a warehouse up or down, scale clusters out or in, and how to set Auto-suspend against per-second billing and cache loss for COF-C03.
- 5 min
- Size up for complex or spilling queries; size down when capacity sits unused.
- Resizing does not affect running queries; it applies to queued and new ones.
- Scale out (more clusters) for concurrency; scale in happens automatically in auto-scale mode.
What it is
Snowflake gives you two independent ways to change compute. Sizing (up or down) changes the power of each cluster. Scaling (out or in) changes the number of clusters in a multi-cluster warehouse. Auto-suspend then decides how long a warehouse sits idle before it stops consuming credits.
Good practice is about matching each lever to the right problem and not paying for idle compute.
How it works
Sizing up and down
Sizing up moves the warehouse to a larger size, such as SMALL to LARGE. Each step doubles the compute and the credits per hour.
ALTER WAREHOUSE etl_wh SET WAREHOUSE_SIZE = 'LARGE';
What happens when you resize a running warehouse:
- Running queries are not affected; they finish on the old resources.
- Queued and new queries use the new size once the resources are provisioned.
- Sizing down removes resources and drops the cache held on them.
Size up when a query is complex or scans a lot of data: large joins, big aggregations, or a Query Profile that shows bytes spilled to local or remote storage (memory ran out). For many simple queries, a larger size often makes little difference.
Size down when queries finish well within their target time, because a larger warehouse that is lightly used wastes credits.
For a big, well-parallelized query, doubling the size can roughly halve the run time, so the total credits stay similar while results arrive sooner. That is why sizing up is the standard fix for one slow complex query.
Scaling out and in
Scaling out adds clusters of the same size to a multi-cluster warehouse (Enterprise Edition or higher). It solves concurrency: queries queuing because too many arrive at once.
- Scale out by raising
MAX_CLUSTER_COUNT. In auto-scale mode, Snowflake starts clusters as queues form. - Scale in happens automatically in auto-scale mode as load drops; the scaling policy (Standard or Economy) controls how quickly.
- Keep
MIN_CLUSTER_COUNTat 1 unless you need guaranteed capacity or high availability at all times.
| Symptom | Lever | Why |
|---|---|---|
| One query is slow even when the warehouse is idle | Size up | It needs more power per cluster |
| Queries wait in a queue at peak times | Scale out | More clusters run more queries at once |
| Query spills to remote storage | Size up | More memory and local disk per cluster |
| Warehouse is mostly idle | Size down or shorten Auto-suspend | You pay for unused capacity |
Auto-suspend and auto-resume
AUTO_SUSPEND is the number of idle seconds before a warehouse suspends. The default for warehouses created with SQL is 600 seconds (10 minutes). Setting 0 or NULL means the warehouse never suspends.
AUTO_RESUME = TRUE (the default) starts the warehouse automatically when a query arrives. You can also suspend and resume manually:
ALTER WAREHOUSE adhoc_wh SET AUTO_SUSPEND = 60;
ALTER WAREHOUSE adhoc_wh SUSPEND;
ALTER WAREHOUSE adhoc_wh RESUME;
Two billing rules shape the right value:
- Billing is per second, so a suspended warehouse costs nothing; short Auto-suspend saves credits.
- Each start or resume is billed for at least 60 seconds. A value much shorter than the normal gap between queries causes constant suspend and resume cycles, each paying that minimum.
Suspending also drops the warehouse cache. The first queries after a resume read from remote storage and may run slower until the cache rebuilds.
| Workload | Auto-suspend guidance |
|---|---|
| Ad-hoc and loading | Short, so idle time is not billed |
| BI dashboards with steady use | Longer, to keep the cache warm |
| Continuous heavy workload | Consider disabling, if there is never real idle time |
How the exam asks about this
- "A complex query spills to remote storage" -> size up the warehouse.
- "At 9 AM hundreds of queries queue, but each runs quickly" -> scale out with a multi-cluster warehouse.
- "Ad-hoc warehouse sits idle most of the day but is billed for hours" -> lower AUTO_SUSPEND and keep AUTO_RESUME on.
Common mistakes
Sizing up to fix queuing. A larger warehouse runs each query faster, which can help a little, but the real fix for concurrency is more clusters.
Scaling out to fix one slow query. A single query runs on one cluster. Extra clusters do nothing for it.
Setting Auto-suspend lower than the workload's normal gap. The warehouse keeps stopping and starting, paying the 60-second minimum each time and losing its cache.
Expecting a resize to speed up a query already running. Only queued and new queries use the new size.
What to remember for the exam
- Size up for complex or spilling queries; size down when capacity sits unused.
- Resizing does not affect running queries; it applies to queued and new ones.
- Scale out (more clusters) for concurrency; scale in happens automatically in auto-scale mode.
- Multi-cluster requires Enterprise Edition; clusters are all the same size.
- AUTO_SUSPEND is in seconds; default 600 with SQL; 0 or NULL never suspends.
- Billing is per second with a 60-second minimum per start or resume.
- Suspending drops the local cache; the result cache is not affected.
A warehouse with Auto-suspend disabled bills for every second it runs, even with no queries. Disable it only for workloads that truly never go idle.
Check yourself
A dashboard warehouse queues queries at peak, but each query takes one second. Size up or scale out?
Scale out with a multi-cluster warehouse, because the problem is concurrency rather than query complexity.
What happens to a query already running when you resize its warehouse?
Nothing; it finishes on the existing resources, and only queued and new queries use the new size.
Why can a very short Auto-suspend increase cost?
Each resume is billed for at least 60 seconds, so frequent suspend and resume cycles add minimum charges and lose the cache.
Topic 9 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.