❄️

Snowflake あるパフォーマンス問題における調査手順の言語化

に公開

はじめに

Snowflakeを採用している多くの企業で、データ活用が進むに連れて、コストやパフォーマンスに関する課題が生じていないでしょうか?

ここ最近の弊社で起きたコスト・パフォーマンス問題の振り返りを兼ねて、その中で自分がどんな事を考えながら調査を行っているかを言語化してみようと思いました。

今回の記事は、あくまでも私自身の経験則や暗黙知を言語化してみることに注力しており、何か具体的な技術知見や体系化を目的にしていません。

ある意味、備忘録として残そうとしたものですが、皆様の参考になるものが何かあれば幸いです。

コスト増加問題の発生

Snowflake統合完了後、様々なワークロードが実行されるなかで、コストが全体的に伸びており、予算を超過する状態でした。

これらについて段階的に見直しを行いました。

  • 検証環境で不要な処理が動いていたため、それらを停止
  • 並行した様々な開発環境を整理し、ストレージコストを削減
  • ワークロードを元にオーバースペックのウェアハウスサイズを見直し

上記の対応により、ある程度コストは改善していきましたが、それでももう一段コストを抑制できないか?と考えました。

コスト分析とパフォーマンス分析の手法

select.devのようなコスト最適化サービスもありますが、弊社では採用していないため、私自身の経験則に基づき、以下のような手順で分析を行いました。
この辺り、メンバーへなかなか引き継げないこともあり、言語化してみようと思ったのが今回の記事の意図になります。

では、自分の思考をトレースしてみたいと思います。

一次スクリーニング(ウェアハウス分析)

  • ウェアハウス別の総コスト比較とそれぞれのクエリ実行数、実行時間(最小、最大、平均、中央値)の分析
    • すべてを対象とするのではなく、コストが大きいもの、クエリ数の多いものを選定する
    • クエリ数とクエリ時間を把握することで、全体的なパフォーマンス状況を理解する
  • 上記を元に該当のウェアハウスのクエリを元に感覚的に遅いと思う基準時間以上のクエリを抽出
    • 私自身は、BI系なら長くても1~2分、アドホック分析やETL系なら5~30分以上という感覚です
    • 自社の最適化度合いやウェアハウスサイズ、運用ルールによるため、実値を把握して決めましょう

これらの情報を元に改善の効果の高そうなウェアハウスを絞り込み、その中で平均実行時間より逸脱しているクエリをQUERY_HISTORYよりリストアップします。

--ウェアハウス別クエリ状況分析
select 
 TO_VARCHAR(START_TIME,'YYYYMM') as "実行月"
,WAREHOUSE_NAME
,WAREHOUSE_SIZE
,COUNT(QUERY_ID) as "クエリ数"
,SUM(TOTAL_ELAPSED_TIME/1000/60/60) as "合計時間"
,MIN(TOTAL_ELAPSED_TIME/1000/60/60) as "最小時間"
,MAX(TOTAL_ELAPSED_TIME/1000/60/60) as "最大時間"
,AVG(TOTAL_ELAPSED_TIME/1000/60/60) as "平均時間"
,MEDIAN(TOTAL_ELAPSED_TIME/1000/60/60) as "中央時間"
,PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY TOTAL_ELAPSED_TIME/1000/60/60) as "95パーセンタイル"
,AVG(QUEUED_OVERLOAD_TIME/1000) as "平均キュー時間_秒"  
,SUM(BYTES_SCANNED) as "スキャンバイト数"              
from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
where START_TIME >= '2025-09-01' 
  and START_TIME < '2025-10-01'
  and WAREHOUSE_SIZE is not null
  and EXECUTION_STATUS = 'SUCCESS'
  and TOTAL_ELAPSED_TIME > 0
group by ALL
order by "合計時間" desc

今回の場合、アナリストに割り当てているウェアハウス(Lサイズ)のコスト比率が一番大きく、クエリ数も多いため、これをまずはターゲットとしました。

Vポイントの実績分析やポイント加盟企業向けのレポート、CRM分析受注企業向けのレポート、販促やキャンペーンのセグメンテーションやペルソナ分析など多岐に渡る業務が行われています。

そのウェアハウスでは、大半のクエリは数秒から数分で完了しているため、先ほどの基準に従い、明らかに遅いという感覚値である、30分以上を目安にクエリをリストアップし、詳細な分析を進めました。

クエリ時間の大小は、企業規模やデータ規模、活用規模で変わるとは考えていません。
あくまでもビジネスや業務で求められるパフォーマンスを満たしているかどうかが判断基準であり、
業務に合わせたスピードで処理できるデータセットを用意することが重要と考えています。

  • ユーザー画面の操作系WH:XS:99.9%以上のクエリが1秒以内に返却 (マスターデータ参照など)
  • BIなどのダッシュボード系WH:M:数秒から数十秒 (集計済データマート:Gold層)
  • アドホック分析系WH:数秒から数分 (前捌き済データマート:Silver層)
  • 大規模ETLウェアハウス:10分~60分を目安。一部2時間越え(ローデータ:Bronze層)

二次スクリーニング(クエリI/O分析)

Snowflakeのクエリパフォーマンスにおいて、マイクロパーティションを活かしたデータ読取量の最小化は重要な戦略です。
個々のSQL最適化も大事ですが、クラスタリングキーやSearch Optimization Service(SOS)などの設定を適切に行うことが全体的なパフォーマンス向上とコスト抑制につながります。

それぞれの技術解説は以下の記事を参照いただければと思います。
マイクロパーティションの解説(公式)
https://docs.snowflake.com/ja/user-guide/tables-clustering-micropartitions

私が以前にまとめたパフォーマンス最適化機能記事
https://zenn.dev/dataheroes/articles/a3ee996f6477d7

そのため、これらのデータ読取量を重点的にチェックすることで課題状況のドリルダウンがしやすくなるため、以下のようなSQLで読取データ量やパーティションプルーニング(絞り込み)、メモリサイズの使用状況を網羅的に分析します。

SQL内のコメントを参考に一度皆さんのデータ基盤でも試していただければと思います。
また最近はこの手の調査は、このSQLと実際のアウトプットのコピペを生成AIに投げ込んで、分析させています。
普段はSnowflakeで触っていないため、どんどんSnowflakeの知識を忘れていること、マルチタスクで仕事を行っているため、一つの作業に集中する時間がないことから、AIと対話しながら仕事をすることが必然的に多く、大体のことは私の代わりにAIが覚えてくれているので、重宝しています。

プロンプト例

Snowflakeにおいて、コストパフォーマンスの課題が生じているため、その改善を図りたい。
以下のSQLはコストボトルネックの調査の一環としてクエリのパフォーマンスを可視化するものである。
- SQLと共にそのクエリ結果を貼り付けるので、その結果を深く読み込み、十分な解析を行うこと
- 検出された課題に優先順位を付け、優先度別に事象の説明と原因として考えられる可能性を分析すること
- その解決策について、Snowflakeの公式ドキュメントやベストプラクティスに基づいた、正確性の高い提案を行うこと
- 分析にはじっくりと時間をかけ、提案内容の妥当性は複数回検証して提出すること
-- クエリ分析(I/O詳細)
select 
 QUERY_ID         
,QUERY_TEXT as "クエリテキスト"
,QUERY_TYPE
,USER_NAME
,ROLE_NAME
,WAREHOUSE_NAME
,WAREHOUSE_SIZE
-- 実行時間系
,ROUND(TOTAL_ELAPSED_TIME/1000/60, 2) as "実行時間_分"
,ROUND(COMPILATION_TIME/1000, 2) as "コンパイル時間_秒"
,ROUND(EXECUTION_TIME/1000, 2) as "実行時間_秒"
,START_TIME
,END_TIME
-- データスキャン効率
,ROUND(BYTES_SCANNED/1024/1024/1024, 2) as "スキャンデータ_GB" -- 大量データ読み取りの検出
,ROUND(BYTES_WRITTEN/1024/1024, 2) as "書き込みデータ_MB" -- 書き込み量の確認
,ROUND(BYTES_READ_FROM_RESULT/1024/1024, 2) as "結果読み取り_MB" -- 結果セットサイズ
,ROWS_PRODUCED -- 生成行数(物理的書き込み行数)
,ROWS_INSERTED -- 論理挿入行数
,ROWS_UPDATED -- 論理更新行数  
,ROWS_DELETED -- 論理削除行数
-- I/O効率とパフォーマンス指標
,ROUND(PERCENTAGE_SCANNED_FROM_CACHE * 100, 1) as "キャッシュ利用率_%" -- 参考程度、高いほど良い
,ROUND(BYTES_SPILLED_TO_LOCAL_STORAGE/1024/1024, 2) as "ローカルスピル_MB" -- メモリ不足懸念、0が理想
,ROUND(BYTES_SPILLED_TO_REMOTE_STORAGE/1024/1024, 2) as "リモートスピル_MB"-- WHサイズ不足懸念、0が必須
,ROUND(BYTES_SENT_OVER_THE_NETWORK/1024/1024, 2) as "ネットワーク送信_MB" -- ネットワーク負荷
-- パーティション効率
,PARTITIONS_SCANNED -- スキャンしたパーティション数
,PARTITIONS_TOTAL -- 対象テーブル総パーティション数
,ROUND((PARTITIONS_SCANNED/NULLIF(PARTITIONS_TOTAL,0)) * 100, 1) as "パーティションスキャン率_%" --高いとパーティションプルーニングが上手く働いていない
-- I/O効率指標
,ROUND((BYTES_SCANNED/NULLIF(BYTES_READ_FROM_RESULT,0)), 2) as "スキャン効率比" -- 低いほど効率的、理想は1に近い
,ROUND((BYTES_SCANNED/1024/1024/1024)/(NULLIF(TOTAL_ELAPSED_TIME/1000/60,0)), 2) as "GB毎分" -- データスキャン速度
-- QAS(Query Acceleration Service)関連(あんまり使ってないけど)
,QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR -- QAS使用時のスケールファクター
,ROUND(QUERY_ACCELERATION_BYTES_SCANNED/1024/1024/1024, 2) as "QASスキャン_GB" -- QASによるデータスキャン量
,QUERY_ACCELERATION_PARTITIONS_SCANNED as "QASパーティション数" -- QASによるパーティションスキャン数
from 
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
where TOTAL_ELAPSED_TIME >= 1000*60*30 -- 30分以上のクエリ
and   WAREHOUSE_NAME = 'ANALYST'
and   START_TIME >= '2025-09-01' 
and   START_TIME < '2025-10-01'
and   EXECUTION_STATUS = 'SUCCESS'
and   BYTES_SCANNED > 1024*1024*1024 -- 1GB以上スキャンしたクエリのみ
order by BYTES_SCANNED desc -- スキャンデータ量順
limit 100

三次スクリーニング(プロファイルドリルダウン)

  • 基準時間以上のクエリの中で、より時間が掛かっているクエリから一つずつクエリプロファイルを確認
    • 時間が大きいクエリより、チェックすることでボトルネックを把握しやすくする
  • クエリプロファイルで「負荷の高いノード」より順番に詳細を確認する
    • 特に下流工程のデータ転送量が全体のパフォーマンス影響を与えている可能性がある
    • 上流で負荷が高い場合は、SQLの結合などの最適化で解消するケースが多い(と思われる)

生成AIを含めたクエリ分析の示唆も考慮しながら、プロファイルを以下のようにチェックするのが良いと考えています。

チェックポイント

  • クエリインサイト

    • パフォーマンス改善のアドバイスを確認
      • 結合条件やクラスタリングキーの適用のアドバイスをしてくれます
      • 英語表記なので、DeepLや先ほどの生成AIにアドバイスを投入し、意訳してもらいましょう。
  • プロファイル概要

    • ローカルディスクI/O、リモートディスクI/O
      • メモリ上で処理できず、スピル(一時ファイルへの書き出し≒メモリあふれ)が起きている
      • スピルにより、ローカルディスクへのデータ書き出しが行われ、パフォーマンスが悪化
    • コンパイル時間
    • クエリ実行までの時間が掛かっている場合、そもそもクエリのシンプル化をした方が良い
  • 統計

    • スキャンされたバイト数
      • データ量が多いと上述のスピルの主原因となりうる
      • データ転送量を下げるためにパーティションや結合条件の見直しを行い、最小化する
    • スキャン済パーティション数とパーティションの合計の差
      • 差が小さいとパーティションが有効に利用できていないか未設定(フルスキャン)

処理全体のそれぞれを確認した上で、各ノードのプロファイル概要と統計を同じように確認することでより詳細な原因特定ができます。

今回生じた問題

弊社にて今回の問題で、これらの詳細を調査していくと、以下の傾向があることが分かりました。

クエリプロファイル分析

  • ローカルディスクI/Oが非常に大きい(スピルの頻発)
  • パーティションを絞れておらず、ほぼフルスキャン(マイクロパーティションを活かせていない)

またそれらの多くは、データサイエンティストが作成したテーブル群であることが分かり、それらのテーブル構造を調べることにしました。

四次スクリーニング(テーブル分析)

テーブルについては目視で以下の観点でチェックを行います。

テーブルチェック観点

  • トランザクションテーブルかマスタテーブルかを見極める
  • テーブルの件数や項目をサンプリングし、内容を把握する
  • 結合キーや検索、指定されるキーを想像する
  • それらの前提理解を元にクラスタリングキーの設定要否を確認する
  • 各項目の属性や桁数を確認する

これらを進めていった結果、以下のことが分かりました。

テーブル分析結果

  • テーブルの目的は顧客のライフスタイルを推計した項目ごとのスコア値が格納されている
  • 顧客IDと数百以上のスコア値を格納した横長のテーブル
  • スコア値にはFLOAT型の設定になっている(項目ごとの予測率のような値)
  • 顧客IDは文字型だが、デフォルト桁数(16777216)になっている
  • クラスタリングキーやSearch Optimization Service(SOS)の設定はなし

項目が横長でそれらを大量に取得しており、クラスタリングキーやSOSの設定もしていないため、クエリパフォーマンスが悪化しているのは当然かとも思いましたが、それらの設定には作成・維持コストもかかります。
また項目数は多いですが、大半が数値項目であるにもかかわらず、全体の転送サイズ自体が大きすぎる違和感を感じ、いくつかの仮説を立てて、もう一段深堀することにしました。

仮説①

  • 定義上の桁数でデータ格納が行われていることで転送サイズが肥大していないか?
    • 実際の格納サイズでバイト数は消費されるため、定義上の桁数はストレージサイズに影響しない
  • 定義上の桁数でクエリ時のメモリサイズが過剰に消費されていないか?
    • 実際の格納サイズは小さいため、定義上の桁数はメモリサイズに影響しない
  • 定義上の桁数が異なることによる結合時に暗黙的な型変換も起きていないか?
    • VARCHAR同士であれば、実際の値での結合となり、型変換は起きない
      ただBIなどを利用する場合、項目定義に従ったメモリサイズを確保してしまう可能性もあるので、適切な桁数に直すのが良いと判断しました。

このような場合、Deep Researchにかけて、公式ドキュメントを元に仕様を徹底的に調べられるようになったことで、仮説に対する仕様確認や調査は、本当に楽になったと思います。

仮説②

  • FLOAT型であることがパフォーマンスに影響しないか?
    そもそも自身でFLOAT型をほとんど使うことがなかったこともあり、こちらもDeep Reseachや推論モデルを使いながら、詳細な仕様の理解に努めつつ、テーブル定義に関する分析もAIと対話しながら、客観的に評価してもらいながら、テーブル設計の妥当性を評価しました。
    ちなみにプロファイル画面のスクリーンショットもAIに読み込ませるとかなり的確なアドバイスをしてくれますので、お薦めします。

その結果、FLOAT型について以下の特徴があることが分かりました。

FLOAT型の特徴

  • 非常に多くの桁数や浮動小数点を取り扱えること
  • 機械学習等で高速な演算のために用いられること
  • データを格納するサイズは8byte固定であること

ここで新たな疑問と仮説が生じました。

仮説③

  • 少数点の取り扱いがあるとしても8Byteというサイズは大きすぎないだろうか?
    • NUMBER型に比べるとデータ格納サイズが大きい=パフォーマンス影響の可能性がある
  • 膨大な少数点桁数が実際のデータ転送時間に影響しないか?
    • データ格納サイズによるため、実際の桁数がデータ転送に影響することは少なそう
  • そもそも最終的なスコア値において、小数点にそこまでの精度が必要だろうか?
    • 必要性以上の少数点により、マイクロパーティションの圧縮がが効きにくくなっている可能性がある

そこでFLOAT型に対し、他の数値型とそれらの小数点の取り扱い仕様を元に比較を行いました。

データ型適合性マトリクス表

データ型 ストレージ仕様(非圧縮時の目安) 1桁整数
(0–9)
5桁整数
(12345)
小数
(123.45)
SMALLINT NUMBER(38,0)同義
値域依存(1~8バイト)
✅最適
(1バイト)
✅最適
(2バイト)
❌整数のみ
NUMBER(5,2) 値域依存
(-999.99~999.99→2バイト)
スケールは処理・メモリに影響
✅適合
(2バイト)
⚠️整数部制約
(4バイトまで)
✅最適
(2バイト)
FLOAT 8バイト固定
誤差リスク
⚠️過剰+誤差
(8バイト)
⚠️過剰
(8バイト)
⚠️誤差リスク
(8バイト)
VARCHAR(n) 実データ長+2バイト
オーバーヘッド
⚠️文字列化
(~1+2B)
⚠️文字列化
(~5+2B)
⚠️文字列化
(データ長+2B)

https://docs.snowflake.com/en/sql-reference/data-types-numeric

上記の結果より、FLOAT型は演算効率は良いものの格納バイト数としては大きいこと。
またNEMBER型も小数点を含む桁数の有無次第で格納サイズは大きく変わることも分かりました。

そしてスコアリング時の値を丸めず、そのままFLOAT型で格納していることにより、小数点の差により、マイクロパーティションの格納効率が悪いのではないかと推測しました。

これは上記の公式ドキュメントに記載のある以下の一文より推測しています。

原文
However, scale (the number of digits following the decimal point) affects storage. For example, the same value stored in a column of type NUMBER(10,5) consumes more space than NUMBER(5,0). Also, processing values with a larger scale might be slightly slower and consume more memory.

日本語訳
ただし、スケール(小数点以下の桁数)はストレージに影響します。例えば、同じ値をNUMBER(10,5)型の列に格納すると、NUMBER(5,0)型よりも多くのメモリを消費します。また、スケールが大きい値の処理は、処理速度が若干低下し、メモリ消費量も増加する可能性があります。

具体的な性能検証

さて、本題に戻り、ここからはこれらの仮説を証明するための検証手順と結果を話して行きたいと思います。
まず該当のテーブルのFLOAT項目をいくつかサンプリングし、最大値、最小値を確認しました。それに基づき、分析への致命的な影響は生じないだろうとひとまず仮判断し、NUMBER(5,2)に変更したテーブルを作成し、元テーブルより、最大桁数と少数点2桁までに切り捨て、全件Insertを行いました。

これにより、スコアを-999.99~999.99となり、小数点3桁以下の精度は落ちるものの、
FLOATの8バイト固定から、NUMBER(5,2)の値域に応じた動的最適化サイズに変更されます。

テーブルサイズの検証
そして格納後にこの2つのテーブルのサイズと中身を確認したところ、件数は合致、桁数は少数点2桁で切り捨てされている状態でデータ品質が保たれていることを確認しました。
また肝心のテーブルサイズは約1/4に削減されていることが分かりました。

マイクロパーティション最適化の仕組み

自動分割: データを50〜500MB(非圧縮)単位で分割
メタデータ管理: Min/Max値、重複度、NULL数を記録し高速プルーニング
動的圧縮: カラム特性に応じて辞書圧縮やRLE等を自動選択 (データ特性に依存し、一般に大幅に縮小される)

これらのFLOAT固定8バイトからNUMBER動的最適化への変更に加え、小数点2桁制限により、同一値が増加し、マイクロパーティション内での値域収束と圧縮効率が改善され、大幅なデータサイズの圧縮が実現されたと考えられます。

これを整数化するとどうでしょうか?さらにSAMLLINT型(NUMBER(38,0))とNUMBER型を整数化してみた結果も含めた、それぞれのサイズ比較です。

TABLE_NAME ROW_COUNT SIZE_GB BYTES_PER_ROW
FLOAT型テーブル(オリジナル) 149,802,274 866 6,205
NUMBER(9,6)化 999.999999 149,802,274 368 2,639
NUMBER(7,4)化 999.9999 149,802,274 277 1,986
NUMBER(5,2)化 999.99 149,802,274 187 1,340
NUMBER(38,0)化 999.0 149,802,274 96 687
NUMBER(3,0)化 999.0 149,802,274 94 673

整数化することでデータ格納サイズは90%近く削減されています。
値の精度とデータサイズの大小のトレードオフとして検討可能な範囲ではないでしょうか?

実際のクエリ検証
上記を踏まえ、このテーブルと顧客マスタテーブルを当てて、簡単な集計比較をそれぞれ実施したところ、ローカルディスクI/Oは半分に下がっており、処理時間も同等に半分程度に改善されていました。

TABLE_NAME スキャンバイト数 パーティション総数 実行時間
FLOAT型テーブル(オリジナル) 21.7GB 48,779 8.2秒
NUMBER(5,2)化テーブル 6.13GB 12,206 2.1秒
NUMBER(3,0)化テーブル 3.48GB 6,121 1.6秒

FLOAT性能比較

これにより、データサイズ削減により、スピルが抑制され、処理速度はデータサイズに従い、効率化されることが分かりました。
ただ、それでもまだデータ転送サイズとしては比較的大きい状態です。

またそのほかに以下のような選択肢もありましたが、今回のケースでは効果的ではないと判断しました。
クラスタリングキーの適用:顧客IDと多数のスコア値項目のため、適切な採用が難しい
SOSの適用:大量検索が多く、ルックアップ検索(1件取得)用途が少なく、効果が薄い
縦持ちテーブルへの変更:項目ごとの値を列として保持し、レコード化する(レコード数800倍・・)
QASの適用:重たいクエリに対し、パフォーマンス向上の可能性はあるが全体的なコスト増の懸念

また本音を言えば、-999.99~999.99を-99999~99999にする、すなわち完全整数化し、SMALLINT型(NUMBER(38,0)同義)にすると値域最適化を図りたいとも考えましたがさすがに影響範囲も大きく、データモデルを大きく見直すという選択肢もゼロではないですが、そのテーブル構造で組まれたパイプラインも数多くある状況で、すぐの着手は難しく、まずはデータサイズの最適化のみ行うことを判断しました。

最終的な精度は、データエンジニアメンバー(基盤提供者)とデータサイエンティスト(データオーナー)、データアナリスト(データ利用者)間で合意した桁数にしてよいとし、いずれにせよ、NUMBER型にする方がストレージコスト、I/O負荷を下げる効果ありと申し送り、私自身の対応としては完了としました。

学び

上記の検証結果を元に該当のテーブル群のメンテナンスを行うとともにデータ型に関するガードレールの必要性を感じました。

  • データサイエンスメンバーには、スコアリング値についてNUMBER型に格納すること
    • 浮動小数点何桁まで必要かを明確に取り決める

また、FLOAT型自体が悪いという訳ではなく、機械学習の途中計算ではFLOAT、最終出力・保存にはNUMBERというような使い分けを的確にする事がパフォーマンスを最適化することにつながると学びました。

この辺り、データの民主化や生成AIによるバイブコーディングが進んでいく中で、事故を抑制するガードレール整備の重要性を改めて感じました。
データの民主化やAI活用が進んでいる企業では、このような問題を未然に防ぐためのガードレールは充分に整備されていると思いますので、我々も一層強化していかなければいけないと身を引き締めた次第です。

最後に

今回の記事は、ほぼ備忘録的な内容となり、何かを体系立てた内容というよりも、実際に起きた事象をパフォーマンス改善における手順や思考プロセスの言語化にチャレンジしてみました。

このような実行手順は経験則で行われ、それぞれの暗黙知であることが多いため、私自身もこの方法が最適なのか?よりベストな手順や観点があるのか自信がありません。

皆様の知見やノウハウの中で、より良いベストプラクティスがありましたら、ぜひアドバイスいただければ大変ありがたく存じます。

この記事が少しでも皆様のお役に立てれば幸いです。ここまで読んでいただいてありがとうございました。

Snowflake Data Heroes

Discussion