はじめに
Snowflakeを使い始めてしばらくすると、クレジットの消費が気になってくる。
「このクエリ、なんで遅いんだろう」「先月のクレジット消費が思ったより多い」という問題が出てきたので、クエリ最適化とコスト管理を真剣に調べた。RDBMSのチューニングと似ている部分もあるが、Snowflake固有の考え方が必要な部分もあった。
まずクエリのパフォーマンスを計測する
最適化の前に現状把握から。
-- 直近のクエリ実行時間を確認
SELECT
query_id,
query_text,
total_elapsed_time / 1000 AS seconds,
execution_time / 1000 AS execution_seconds,
compilation_time / 1000 AS compilation_seconds,
bytes_scanned / 1024 / 1024 / 1024 AS gb_scanned,
percentage_scanned_from_cache AS cache_hit_pct,
rows_produced,
warehouse_name,
start_time
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
AND total_elapsed_time > 5000 -- 5秒以上かかったクエリ
ORDER BY total_elapsed_time DESC
LIMIT 20;
-- クレジットを最も消費しているウェアハウス
SELECT
warehouse_name,
SUM(credits_used) AS total_credits,
SUM(credits_used_cloud_services) AS cloud_credits,
COUNT(*) AS query_count
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY total_credits DESC;
Pythonからも取得できるので定期的にレポートを出す仕組みを作った。
from snowflake_client import SnowflakeClient
from config import SnowflakeConfig
sf = SnowflakeClient(SnowflakeConfig())
def get_expensive_queries(days: int = 7, threshold_seconds: int = 10):
return sf.query(f"""
SELECT
query_id,
LEFT(query_text, 100) AS query_preview,
total_elapsed_time / 1000 AS seconds,
bytes_scanned / 1024 / 1024 / 1024 AS gb_scanned,
percentage_scanned_from_cache AS cache_hit_pct,
warehouse_name
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD(day, -{days}, CURRENT_TIMESTAMP())
AND total_elapsed_time > {threshold_seconds * 1000}
ORDER BY total_elapsed_time DESC
LIMIT 50
""")
expensive = get_expensive_queries(days=7, threshold_seconds=10)
print(expensive.to_string())
クエリプロファイルを読む
Snowflake UIの「Query Profile」機能が強力。
-- クエリを実行してIDを取得
SELECT * FROM orders WHERE amount > 10000;
-- 直前のクエリIDを確認
SELECT LAST_QUERY_ID();
Query ProfileをUIで見ると：
OperationNode
├── TableScan（テーブルスキャン）
│ ├── Partitions scanned: 100/500 ← 全パーティションの20%だけスキャン
│ └── Bytes scanned: 2.5 GB
├── Filter（フィルタ）
└── Aggregate（集計）
注目すべき指標：
Partitions scanned / total → 低いほど良い（pruningが効いている）
Bytes scanned → 少ないほど良い
percentage_scanned_from_cache → 高いほど良い（キャッシュヒット）
Spillage to disk → あるとメモリ不足のサイン
マイクロパーティションとプルーニング
Snowflakeの内部でデータはマイクロパーティションという単位で格納されている。
テーブル
└── マイクロパーティション（50MB〜500MB程度）
├── min/max値のメタデータ
├── NULL値の数
└── 実データ（列指向で圧縮）
WHERE句の条件がマイクロパーティションのmin/max範囲外なら、そのパーティションをスキャンしない。これをプルーニングという。
-- プルーニングが効く例（日付範囲での絞り込み）
SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';
-- created_atでデータが並んでいれば1月のパーティションだけスキャン
-- プルーニングが効きにくい例（ランダムなIDの絞り込み）
SELECT * FROM orders
WHERE user_id = 12345;
-- user_idがランダムに分散していると全パーティションをスキャン
プルーニングを効かせるためのデータ投入順
-- 悪い例 — ランダムな順序でINSERT
INSERT INTO orders SELECT * FROM source ORDER BY RANDOM();
-- 良い例 — 日時順でINSERT（日付クエリのプルーニングが効く）
INSERT INTO orders SELECT * FROM source ORDER BY created_at;
クラスタリングキー
大きなテーブルでプルーニングが効かない場合、クラスタリングキーを設定する。
-- クラスタリングキーを設定
ALTER TABLE orders CLUSTER BY (created_at::DATE);
-- 複合クラスタリングキー
ALTER TABLE orders CLUSTER BY (region, created_at::DATE);
-- クラスタリングの状態を確認
SELECT SYSTEM$CLUSTERING_INFORMATION('orders', '(created_at::DATE)');
// CLUSTERING_INFORMATIONの出力例
{
"total_partition_count": 500,
"total_constant_partition_count": 0,
"average_overlaps": 2.4, // 低いほど良い（1.0が理想）
"average_depth": 3.1, // 低いほど良い
"partition_depth_histogram": {...}
}
average_overlapsが高いとクラスタリングが崩れているサイン。Snowflakeは自動的にReclustering（再クラスタリング）するが、追加コストが発生するので注意。
クラスタリングキーを使うべきケース
✓ 数TB以上の大きなテーブル
✓ 特定のカラムで絞り込むクエリが頻繁に実行される
✓ プルーニング率が低い（Query Profileで確認）
✗ 小さなテーブル（効果がなく維持コストだけかかる）
✗ 様々な列でランダムにクエリされるテーブル
マテリアライズドビュー
集計クエリを事前に計算して結果を保持する仕組み。
-- 通常のビュー（クエリのたびに計算する）
CREATE VIEW daily_sales_view AS
SELECT
created_at::DATE AS sale_date,
region,
SUM(amount) AS total_amount,
COUNT(*) AS order_count
FROM orders
GROUP BY 1, 2;
-- マテリアライズドビュー（結果を保持し、自動更新する）
CREATE MATERIALIZED VIEW daily_sales_mv AS
SELECT
created_at::DATE AS sale_date,
region,
SUM(amount) AS total_amount,
COUNT(*) AS order_count
FROM orders
GROUP BY 1, 2;
-- マテリアライズドビューへのクエリ（高速）
SELECT * FROM daily_sales_mv
WHERE sale_date >= '2024-01-01'
ORDER BY total_amount DESC;
マテリアライズドビューはベーステーブルに変更があると自動でリフレッシュされる。リフレッシュにもクレジットが消費されるので、更新頻度の高いテーブルには向かない。
通常ビューとマテリアライズドビューの使い分け
通常ビュー:
✓ ベーステーブルが頻繁に更新される
✓ クエリの頻度が低い
✓ 複雑な結合やサブクエリが含まれる
マテリアライズドビュー:
✓ 集計クエリが頻繁に実行される
✓ ベーステーブルの更新頻度が低い〜中程度
✓ クエリのレスポンスタイムを優先したい
結果キャッシュの活用
Snowflakeは同じクエリを24時間以内に再実行すると、ウェアハウスを使わずに結果キャッシュから返す。
-- 1回目（ウェアハウスを使う）
SELECT region, SUM(amount) FROM orders GROUP BY region;
-- 実行時間: 3.2秒
-- 2回目（結果キャッシュから返る）
SELECT region, SUM(amount) FROM orders GROUP BY region;
-- 実行時間: 0.02秒
-- キャッシュを使わないようにする（テスト時）
ALTER SESSION SET USE_CACHED_RESULT = FALSE;
-- キャッシュヒット率を確認
SELECT
AVG(percentage_scanned_from_cache) AS avg_cache_hit_pct
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP());
キャッシュを活用するために：
-- ✓ クエリの書き方を統一する（大文字小文字・スペースが違うとキャッシュミス）
-- ✓ ダッシュボードのクエリは定期実行してキャッシュを温める
-- ✗ CURRENT_TIMESTAMP()を使うとクエリが毎回変わりキャッシュが効かない
-- 悪い例（毎回クエリが変わる）
SELECT * FROM orders
WHERE created_at >= DATEADD(hour, -24, CURRENT_TIMESTAMP());
-- 良い例（日付を固定するとキャッシュが効く）
SELECT * FROM orders
WHERE created_at::DATE = CURRENT_DATE() - 1;
ウェアハウスサイズの選定
クエリによって適切なウェアハウスサイズが違う。
-- 小さいクエリ用のウェアハウス
CREATE WAREHOUSE small_wh
WAREHOUSE_SIZE = 'X-SMALL'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE;
-- 重い集計用のウェアハウス
CREATE WAREHOUSE large_wh
WAREHOUSE_SIZE = 'LARGE'
AUTO_SUSPEND = 120
AUTO_RESUME = TRUE;
# クエリの種類によってウェアハウスを切り替える
def run_heavy_aggregation(sql: str):
with get_snowflake_connection() as conn:
cursor = conn.cursor()
# 重い処理の前に大きいウェアハウスに切り替え
cursor.execute("USE WAREHOUSE large_wh")
cursor.execute(sql)
result = cursor.fetch_pandas_all()
# 処理後は小さいウェアハウスに戻す
cursor.execute("USE WAREHOUSE small_wh")
return result
ウェアハウスのスケールアップとスケールアウト
-- スケールアップ（サイズを大きくする）
-- メモリ不足(Spillage)が発生しているとき
ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE = 'LARGE';
-- スケールアウト（同じサイズで並列度を上げる）
-- 同時クエリが多いとき
ALTER WAREHOUSE my_warehouse SET MAX_CLUSTER_COUNT = 3;
スケールアップ が有効:
・単一クエリが遅い
・Spillage to disk が発生している
・JOINやGROUP BYが重い
スケールアウト が有効:
・クエリが並行して大量に実行される
・待ち行列（Queuing）が発生している
クエリを書くときの最適化ポイント
SELECT * を避ける
-- 悪い例
SELECT * FROM orders;
-- 良い例（必要な列だけ取得）
SELECT order_id, user_id, amount, created_at FROM orders;
Snowflakeは列指向ストレージなので、SELECT *は全列を読み込んでスキャン量が増える。
フィルタは早めに適用する
-- 悪い例（JOINしてからフィルタ）
SELECT o.*, u.region
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.created_at >= '2024-01-01';
-- 良い例（サブクエリで先にフィルタ）
SELECT o.*, u.region
FROM (
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
) o
JOIN users u ON o.user_id = u.user_id;
LIMIT を使う（開発時）
-- 開発中はLIMITで確認する
SELECT * FROM very_large_table LIMIT 100;
-- COUNT(*)で件数だけ確認
SELECT COUNT(*) FROM very_large_table
WHERE created_at >= '2024-01-01';
DISTINCT よりGROUP BYが速いことがある
-- DISTINCTより
SELECT DISTINCT user_id FROM orders;
-- GROUP BYのほうが速いことがある
SELECT user_id FROM orders GROUP BY user_id;
リソースモニターでコスト上限を設定
-- 月次のクレジット上限を設定
CREATE RESOURCE MONITOR monthly_monitor
WITH CREDIT_QUOTA = 500 -- 月500クレジットまで
FREQUENCY = MONTHLY
START_TIMESTAMP = IMMEDIATELY
TRIGGERS
ON 50 PERCENT DO NOTIFY -- 50%消費で通知
ON 80 PERCENT DO NOTIFY -- 80%消費で通知
ON 100 PERCENT DO SUSPEND -- 100%でウェアハウス停止
ON 110 PERCENT DO SUSPEND_IMMEDIATE; -- 即時停止
-- ウェアハウスにモニターを適用
ALTER WAREHOUSE my_warehouse SET RESOURCE_MONITOR = monthly_monitor;
最適化チェックリスト
実務で確認するポイントをまとめた。
クエリレベル:
□ SELECT * を使っていないか
□ WHERE句でパーティションプルーニングが効いているか
□ JOINの前にフィルタを適用しているか
□ 結果キャッシュが活用されているか（CURRENT_TIMESTAMP()に注意）
□ Query Profileでボトルネックを確認したか
テーブルレベル:
□ 大きなテーブルにクラスタリングキーが設定されているか
□ 頻繁に参照する集計にマテリアライズドビューを使っているか
ウェアハウスレベル:
□ AUTO_SUSPENDが設定されているか
□ クエリの種類に合ったウェアハウスサイズか
□ Spillage to diskが発生していないか（サイズアップが必要）
コスト管理:
□ リソースモニターが設定されているか
□ 定期的にクレジット消費量を確認しているか
□ 不要なウェアハウスが起動したままになっていないか
まとめ
- Query Profileでボトルネックを特定してから最適化する
- マイクロパーティションのプルーニングを意識したクエリを書く
- 大きなテーブルにはクラスタリングキーを設定する
- 頻繁に実行する集計クエリはマテリアライズドビューを検討する
- 結果キャッシュを活用するためクエリの書き方を統一する
- ウェアハウスはクエリの種類によってサイズを使い分ける
- リソースモニターで上限を設定してコスト暴走を防ぐ
「なんとなく動いている」状態から「なぜ速い/遅いのかわかった上で動かしている」状態になるには、Query Profileを読む習慣をつけるのが一番の近道だった。