5.2 Core Workflows and Decision Points

Scale Up vs Scale Out

A virtual warehouse is the compute that runs queries. It has two independent dials.

Scaling up means choosing a larger size: X-Small, Small, Medium, Large, X-Large, up through 6X-Large. Each step roughly doubles the compute and the credit consumption per hour — an X-Small burns 1 credit/hour, a Small 2, a Medium 4, a Large 8, and so on. A bigger warehouse has more memory and local SSD, so it processes a single large or complex query faster and spills less to disk. Scale up for heavy individual queries, large sorts/joins, and bulk loads.

Scaling out means running a multi-cluster warehouse with more clusters of the same size. You set MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT (the highest allowed MAX_CLUSTER_COUNT depends on warehouse size: for example, 300 for X-Small through Medium and 160 for Large). This solves concurrency: when many users submit queries at once and requests start queuing, additional clusters spin up so each query gets compute. Scaling out does not make any single query faster — it only adds throughput for parallel requests.

The two dials are independent and combinable: a multi-cluster Large warehouse scales both heavy individual queries (size) and concurrency (clusters). Sizing also halves both ways — dropping from Large to Medium halves the credit rate but doubles the time a compute-bound query takes, so the credits for that one query are roughly unchanged; the savings come from idle time, not the query itself.

Multi-Cluster Modes and Scaling Policy

A multi-cluster warehouse runs in one of two modes:

• Maximized mode: MIN_CLUSTER_COUNT = MAX_CLUSTER_COUNT (e.g., 3 = 3). All clusters start immediately and stay running — predictable capacity, no auto-scaling.
• Auto-scale mode: MIN < MAX. Snowflake starts and stops clusters automatically based on load, governed by the scaling policy.

The scaling policy only matters in auto-scale mode:

STANDARD prioritizes performance and starts clusters aggressively; ECONOMY conserves credits by keeping running clusters busy before adding more. A common trap: assuming the policy affects single-cluster size — it does not. It only controls when extra clusters start and stop.

The Three Cache Layers

Know exactly where each cache lives and what invalidates it.

1. Result cache (cloud services layer). Stores the complete result set of every query for 24 hours. Reusing the result resets the 24-hour clock, up to a maximum retention of 31 days. It requires no running warehouse — the cloud services layer serves it. Conditions to hit it: the new query is syntactically identical (same text, even whitespace/case can matter), the underlying data has not changed, the role has the needed privileges, and the query is deterministic (no CURRENT_TIMESTAMP, no non-deterministic UDFs). It can be disabled with USE_CACHED_RESULT = FALSE.

2. Warehouse local-disk cache (compute layer). Each warehouse caches raw micro-partition data it fetched from remote storage on its local SSD. Subsequent queries on the same data read from SSD instead of cloud storage — much faster. This cache is lost when the warehouse is suspended and rebuilds when it resumes; larger warehouses hold more.

3. Metadata cache (cloud services layer). Always on, cannot be disabled. Stores object statistics — row counts, min/max per column, distinct-value estimates, and micro-partition boundaries. It lets SELECT COUNT(*), MIN, MAX, and similar metadata-only queries return instantly without starting a warehouse, and it powers pruning.

Warehouse Economics: Auto-Suspend, Auto-Resume, and Billing

Compute cost is controlled by three settings the exam tests directly:

• AUTO_SUSPEND — seconds of inactivity before the warehouse suspends (stops billing). A low value (e.g., 60s) prevents idle credit burn; the trade-off is that suspension clears the local-disk cache, so the next query warms the cache again. For workloads that benefit heavily from the cache, a slightly higher auto-suspend can be worth the idle credits.
• AUTO_RESUME — when TRUE, a suspended warehouse automatically restarts when a query is submitted, so users never see "warehouse not running."
• Billing granularity — credits are charged per second with a 60-second minimum each time a warehouse resumes. Frequent suspend/resume cycles can therefore cost more than staying warm if queries arrive in tight bursts.

Resizing behavior is also testable. Scaling up or down (ALTER WAREHOUSE ... SET WAREHOUSE_SIZE) affects queries submitted after the resize — running queries finish on the old size. By contrast, multi-cluster scaling out adds clusters that immediately start serving queued queries.

A recurring distractor pairs the wrong dial with the symptom — e.g., enlarging a warehouse to fix queuing, or adding clusters to fix a single slow query. Match the dial to the symptom and the credit goal, and these questions become routine.