CertKeen

Multi-cluster warehouses vs scaling up: fixing slow Snowflake workloads

· 2 min read

"The dashboard is slow" has at least three different root causes in Snowflake, and each has a different fix. Architect-level questions test whether you can tell them apart.

Scale up vs scale out

  • Scaling up means a bigger warehouse size. Each size step (XS → S → M → L → XL …) doubles the compute and the credits per hour (1, 2, 4, 8, 16 …). It helps individual heavy queries.
  • Scaling out means a multi-cluster warehouse: extra clusters of the same size that start as concurrent demand grows. It helps many simultaneous queries. Multi-cluster warehouses require Enterprise edition or higher.

Scaling up won't fix a queue, and scaling out won't make one big query faster.

Diagnose first

Symptom Where to look Likely fix
Queries wait before they start QUEUED_OVERLOAD_TIME in QUERY_HISTORY Scale out (multi-cluster)
Queries spill to disk BYTES_SPILLED_TO_LOCAL_STORAGE / BYTES_SPILLED_TO_REMOTE_STORAGE Scale up, or process less data
Queries scan most partitions PARTITIONS_SCANNED vs PARTITIONS_TOTAL Better filters, a clustering key, or search optimization — not a bigger warehouse
Occasional outliers with huge scans Query Profile Query Acceleration Service 
SELECT query_id,
       total_elapsed_time / 1000   AS seconds,
       queued_overload_time / 1000 AS queued_seconds,
       bytes_spilled_to_local_storage,
       bytes_spilled_to_remote_storage,
       partitions_scanned,
       partitions_total
FROM snowflake.account_usage.query_history
WHERE start_time > DATEADD('day', -1, CURRENT_TIMESTAMP())
  AND warehouse_name = 'BI_WH'
ORDER BY total_elapsed_time DESC
LIMIT 50;

Configuring a multi-cluster warehouse

ALTER WAREHOUSE bi_wh SET
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 4
  SCALING_POLICY = 'STANDARD';
  • Auto-scale mode (MIN_CLUSTER_COUNT < MAX_CLUSTER_COUNT): clusters start and stop with demand. This is what you want for business-hours spikes.
  • Maximized mode (min = max): every cluster runs whenever the warehouse runs. Use it only when load is consistently high.
  • Scaling policy: STANDARD starts clusters quickly to minimize queuing; ECONOMY waits until there's enough load to keep a new cluster busy, trading some queuing for fewer credits.

Cost mechanics that show up in questions

  • Warehouses bill per second, with a 60-second minimum each time they start or resize.
  • AUTO_SUSPEND stops billing when idle, but suspending also drops the warehouse's local disk cache — very short suspend times can make repeated queries slower.
  • A multi-cluster warehouse bills for each running cluster: four Medium clusters cost the same per hour as one X-Large.
  • Resource monitors cap credit usage and can notify or suspend warehouses at thresholds.
  • MAX_CONCURRENCY_LEVEL (default 8) controls how many queries one cluster runs at once. Raising it rarely fixes queuing; it usually just makes each query slower.

A worked scenario

Hundreds of short BI queries run between 9:00 and 11:00. Each finishes in under two seconds once it starts, but users wait 20+ seconds. QUERY_HISTORY shows large queued times and no spilling.

  • A bigger warehouse? No — the queries are already fast once they run.
  • A clustering key? No — pruning isn't the problem.
  • A multi-cluster warehouse in auto-scale mode with a sensible MAX_CLUSTER_COUNT and the standard scaling policy. The queue drains, and the extra clusters shut down after the morning peak.

Checklist

  1. Measure queuing, spilling and pruning before touching warehouse settings.
  2. Queue → scale out. Spill → scale up. Poor pruning → fix the data layout or the query.
  3. Put a resource monitor on every warehouse that can scale.

Test yourself with 10 free SnowPro Advanced: Architect practice questions.

More study guides