Snowflake Multi-cluster Warehouse: Practical Guide

A Multi-cluster Warehouse is a Snowflake warehouse that dynamically starts and stops multiple compute clusters to scale out concurrency. The mental model is simple: control individual query speed with warehouse size (scale up), and control the number of queries that can run concurrently with Multi-cluster (scale out). The feature requires Enterprise Edition or higher.

How It Works

A normal warehouse consists of a single cluster. With a Multi-cluster Warehouse, you set MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT, and Snowflake automatically launches additional clusters when concurrent queries exceed the existing capacity. When load decreases, unused clusters are shut down automatically.

同時実行クエリが増加した場合の動作：

[クラスタ1] ■■■■ 4クエリ実行中
                    ↓ キューイング検知
[クラスタ1] ■■■■   [クラスタ2] ■■ 追加起動
                    ↓ さらに増加
[クラスタ1] ■■■■   [クラスタ2] ■■■■   [クラスタ3] ■■ 追加起動

負荷が減少した場合：
[クラスタ1] ■■     [クラスタ2] 停止     [クラスタ3] 停止

CREATE WAREHOUSE Syntax

-- BIダッシュボード向け：レスポンス重視
CREATE WAREHOUSE bi_dashboard_wh
  WAREHOUSE_SIZE = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 6
  SCALING_POLICY = 'STANDARD'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

-- バッチETL向け：コスト重視
CREATE WAREHOUSE batch_etl_wh
  WAREHOUSE_SIZE = 'LARGE'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 3
  SCALING_POLICY = 'ECONOMY'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

-- 常時稼働（MIN = MAX で固定クラスタ数）
CREATE WAREHOUSE always_on_wh
  WAREHOUSE_SIZE = 'SMALL'
  MIN_CLUSTER_COUNT = 3
  MAX_CLUSTER_COUNT = 3
  AUTO_SUSPEND = 0
  AUTO_RESUME = TRUE;

Standard vs Economy Comparison

Diagnosing and Resolving Queuing

Queuing happens when every cluster in the warehouse is busy and new queries are forced to wait in line. It translates directly into degraded user response times, so use the following steps to identify and fix the root cause.

-- キューイングが発生しているクエリの確認
SELECT
  query_id,
  warehouse_name,
  user_name,
  queued_overload_time,
  total_elapsed_time,
  execution_time,
  query_text
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE queued_overload_time > 0
  AND start_time >= DATEADD(HOUR, -24, CURRENT_TIMESTAMP())
ORDER BY queued_overload_time DESC
LIMIT 20;

-- ウェアハウスの負荷パターンを時間帯別に分析
SELECT
  DATE_TRUNC('HOUR', start_time) AS hour_slot,
  warehouse_name,
  COUNT(*) AS query_count,
  AVG(queued_overload_time) / 1000 AS avg_queue_sec,
  MAX(queued_overload_time) / 1000 AS max_queue_sec
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(DAY, -7, CURRENT_TIMESTAMP())
  AND warehouse_name = 'BI_DASHBOARD_WH'
GROUP BY 1, 2
ORDER BY hour_slot;

Queuing Remediation Checklist

Tuning Parameters in Production

-- クラスタ数の変更（即時反映）
ALTER WAREHOUSE bi_dashboard_wh SET
  MIN_CLUSTER_COUNT = 2
  MAX_CLUSTER_COUNT = 10;

-- スケーリングポリシーの変更
ALTER WAREHOUSE bi_dashboard_wh SET
  SCALING_POLICY = 'ECONOMY';

-- ウェアハウスサイズの変更（次のクエリ実行時に反映）
ALTER WAREHOUSE bi_dashboard_wh SET
  WAREHOUSE_SIZE = 'LARGE';

Monitoring

-- ウェアハウスの負荷履歴（クラスタ稼働状況）
SELECT
  start_time,
  end_time,
  warehouse_name,
  avg_running,
  avg_queued_load,
  avg_queued_provisioning,
  avg_blocked
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY
WHERE start_time >= DATEADD(DAY, -7, CURRENT_TIMESTAMP())
  AND warehouse_name = 'BI_DASHBOARD_WH'
ORDER BY start_time DESC;

-- クレジット消費の推移
SELECT
  start_time::DATE AS usage_date,
  warehouse_name,
  SUM(credits_used) AS total_credits,
  SUM(credits_used_compute) AS compute_credits,
  SUM(credits_used_cloud_services) AS cloud_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(MONTH, -1, CURRENT_TIMESTAMP())
  AND warehouse_name = 'BI_DASHBOARD_WH'
GROUP BY 1, 2
ORDER BY usage_date DESC;

Design Patterns

• Separate warehouses for BI and ETL: Put interactive queries (Standard / high MAX_CLUSTER_COUNT) and batch ETL (Economy / large size) on different warehouses to eliminate cross-workload interference.
• MIN_CLUSTER_COUNT = 1 for cost optimization: Run just one cluster at idle and let AUTO_SUSPEND shut the warehouse down completely.
• Fixed cluster count (MIN = MAX) for predictable cost: Useful when you need to forecast peak credit consumption, at the cost of flexibility under varying load.
• Combine with Resource Monitors: Configure a generous MAX_CLUSTER_COUNT but cap credit consumption via Resource Monitors to prevent budget overruns.

Check Your Understanding

Frequently Asked Questions