ドコモのSnowflake環境におけるコストとパフォーマンスの最適化戦略

こんにちは。NTTドコモデータプラットフォーム部の重冨です。本記事ではドコモのデータプラットフォームにおけるコストとパフォーマンスの最適化の取り組みについて紹介します。

NTTドコモのデータプラットフォームについて

NTTドコモではデータ分析基盤としてAIデータクラウド-Snowflakeを採用しています。本記事ではそのSnowflakeについて記載しますが、アーキテクチャや課金体系などの基本概念の説明は割愛しています。

Snowflakeをご存じない方も、以下のポイントだけ押さえておけばふんわりと読み進められます。

SQLクエリを実行するたびに「仮想ウェアハウス（以下WH）」というサーバー群が起動する
クエリを実行する前にWHのサイズ（スペック）を選択する
WHが稼働している時間で課金される従量課金である

ドコモのデータ分析基盤の利用者は社内全体で数千人規模となり、約1億人の会員データを全てのSnowflake利用者が分析やマーケティングに活用することができます。エンジニアだけでなく、営業職・企画職・マーケター・人事総務系まで多種多様な職種の社員が利用しています。

課題設定

多種多様な職種や背景を持つ社員が利用しているということは、それだけSQLやデータに対するスキルセット・理解にも幅が生まれます。

一方で全ての利用者にエンジニアと同等のスキルセットを習得してもらうのは現実的ではありません。営業職はお客さまのことを考えることにリソースを集中すべきですし、企画職はどのようなサービスであればお客さまに喜んでもらえるかを第一に考える必要があります。

そのため、データプラットフォーム部では以下のような取り組みを通して "データ活用の民主化" を進めてきました。

使いやすいデータマートの提供
SQL不要で分析できるアプリケーションの提供
ノウハウやベストプラクティスの発信

データ活用の民主化は順調に進み、数千人規模の利用者がいる巨大なデータプラットフォームが誕生しました。しかしながらその一方で、比例して利用にかかる従量課金コストも指数関数的に増加していました。

利用者や活用機会が増えSnowflakeの利用が増えることは大変喜ばしいことですが、不適切な利用や最適でない使い方も目立つようになってきました。

よくある不適切な利用

利用していないデータパイプラインを動かし続けている
過剰なWHサイズ（過剰なサーバースペック）で簡単なクエリを実行することが常態化している

哲学：コストとの向き合い方を考える

分析用途のデータは使われて初めて価値が生まれます。ただそこにあるだけで全く利用されていないデータは、プラマイゼロどころか維持にコストがかかるので完全に負債です。 ※SoRのようなデータの存在そのものが企業の事業継続に必須であるようなものは除きます。あくまでデータウェアハウスなどの分析基盤の話です

つまり、データ分析基盤においては利用増によるコスト増加そのものは非常にポジティブなことだと考えています。ここに利用最適化の難しさがあります。

「コストが増えている」＝「コスト削減の必要がある」＝「中央集権的に利用を制限する」と短絡的に考えてしまうと、本来はたくさん使ってもらうために導入して作りあげたものなのに、使うことを制限するという大きな矛盾が発生します。

ではどういう考え方で対策をするかですが、私は下記のように進めてきました。

「不適切な利用」「最適でない使い方」のみを対象に最適化する
利用者側にデータ活用における「ROI」の意識を持ってもらえるような仕組みを提供する

後者について少し補足します。

ROIの視点（例え話）

以下の2つの施策があった場合、どちらが優れているでしょうか？

A：マーケティング施策Aを実施したことで 5,000万円 の売上効果が出た
B：マーケティング施策Bを実施したことで 3,000万円 の売上効果が出た

多くの人が「A」の方が優れていると考えると思います。一方で、次の情報を足すとどうでしょう。

A：月に 3,000万円 かけて作ったデータマートを使い、施策Aを実施（売上5,000万円）
B：月に 10万円 かけて作ったデータマートを使い、施策Bを実施（売上3,000万円）

この場合、ほとんどの方が 「Bの方が良い施策だ（ROIが高い）」 と考えるはずです。文字にすると当たり前のことですが、いざ実際の活用となるとなぜかこの「データの原価」が非常に見えにくくなります。

技術的な背景 - なぜデータの原価は見えにくくなるのか

特にSnowflakeを利用している場合、WHの稼働時間による従量課金がベースになるため、組織・プロジェクト単位、ユーザー単位、クエリ単位でのコスト計算をしようとすると工夫が必要です。

小規模な利用であればWHを組織やプロジェクト・チーム単位に分割すれば問題ないのですが、数千人規模のユーザーがいる大規模な利用の場合、その方式をとるとWH数があまりにも増えすぎます。さらにWHは起動時間中は同時に複数クエリを処理できるため、コスト効率的にはWHはできるだけまとめた方がコストメリットが生まれます。

このような背景からデータを作るところのコストは「共通的なところ」に費用を計上するしかなくなり、実際には費用が発生しているのにも関わらず各利用者のコスト意識が薄れていき、自身やチームがどれだけのコストを使っているかを考えなくなってしまいます。

戦略と実行：最適化への2つのアプローチ

Snowflakeの課金はWHの稼働時間がベースになるため、ユーザー単位やプロジェクト単位でのコスト計算には工夫が必要です。

WHごとの課金額を見ることで大まかな傾向は分かりますが、さらに踏み込むためには「ユーザー単位」「プロジェクト（データパイプライン）単位」にコストを分解する必要があります。

この時にまず取るアプローチは「QUERY_HISTORY（クエリの実行履歴）を実行ユーザー別に集計する」ことでしょう。では、実際に集計してみましょう。ドコモのある組織では下記のような傾向になりました。(ユーザー名はマスクしています)

USER_NAME = "SYSTEM"がクレジットのほとんどを占めている・・・・

実はこれは、Snowflakeの中でデータ加工を自動化している場合に良く発生する事象です。データパイプラインの構築にSnowflake Taskを利用されているチームは多いかと思いますが、 Snowflake Taskの実行履歴のユーザーはすべて「SYSTEM」ユーザーによる実行でまとめられます。

つまり、利用最適化のためには「個人ユーザー(人)」と「SYSTEMユーザー(データパイプライン)」の最適化をそれぞれ行う必要があります。そこで、Snowflakeのコストを利用方法別に以下の2つに分類し、それぞれに適した戦略をとりました。

アドホック分析（人）
- その場限りの分析。WHサイズの最適化や各利用者のコスト意識が鍵。
データパイプライン（SYSTEM）
- スケジュール定期実行。不要な処理の削除と効率化が鍵。

実践①：アドホック分析（人）への「(ほぼ)リアルタイムな気づき」の実装

課題

ユーザーは悪気なく巨大なWHを使ってショートクエリを連発したりしてしまう。（サーバー32台相当を起動して10行のデータを処理することを繰り返すなど）本記事ではこのような非効率なクエリを「XL以上のWHサイズで10秒未満の実行」と定義し、分かりやすく"毒クエリ"と呼びます。

解決策

SnowflakeアラートとSlack連携によるリアルタイム通知

説明

Snowflakeの仮想ウェアハウスの概念や課金体系を正しく理解していない場合、過剰なWHサイズで短いクエリを連発してしまうケースがあります。多くの場合、このパターンのユーザーは「このオペレーションにどれだけのコストがかかっているか」を意識しておらず、またその請求もWH全体でまとめられてしまうため、自身の無駄なコストの垂れ流しに気づき、振り返り、改善できる機会が永遠に訪れません。

（※執筆時点でウェアハウスサイズを動的に制御するアダプティブウェアハウスはまだリリースされていません。）

処理概要

ユーザー単位にクエリ履歴を集計し、当月内で特定の閾値を超えたタイミングで2種類のSlack通知を行います。

通知1. クエリ実行時間の積み上げによる推定利用額が各閾値を超過（10万,30万,50万,100万,200万,300万）
通知2. 毒クエリの実行回数が各閾値を超過（100回,300回,500回,1,000回）

利用した機能とデータ

機能: Snowflakeアラート, Snowflakeタスク
データ: SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY

設計

ユーザー別集計のところはマテビューやダイナミックテーブルにしてリアルタイム通知にしても良かったのですが、コスト監視でコストを垂れ流しても本末転倒なので一旦日次でSnowflake Taskで集計する形にしました。

Snowflakeアラートの実装

※SQLの大部分を抽象化しています。Snowflakeアラートってこんな処理まで書けるんだ！くらいの参考情報にしてください。

CREATE OR REPLACE ALERT ALERT_CREDIT_OVERAGE_USER
    WAREHOUSE = MY_COMPUTE_WH
    SCHEDULE = 'USING CRON 0 9 * * * Asia/Tokyo' -- 毎朝9時にチェック
    IF( EXISTS( SELECT 1 FROM DAILY_COST_USAGE ... ) ) -- データが存在する場合のみ実行
    THEN
        -- ▼▼▼ ここから通知ロジック ▼▼▼
        DECLARE
            notification_msg TEXT;
        BEGIN
            WITH 
            -- 1. ユーザーごとの当月利用料を計算（ドル→円換算）
            MONTHLY_COST AS (
                SELECT USER_NAME, SUM(COST) * RATE AS JPY_COST
                FROM DAILY_COST_USAGE
                GROUP BY USER_NAME ...
            ),

            -- 2. 設定マスタと突き合わせ、閾値を超えているユーザーを抽出
            ALERT_CANDIDATES AS (
                SELECT c.USER_NAME, m.ALERT_LEVEL, m.MESSAGE
                FROM MONTHLY_COST c
                JOIN CONFIG_MASTER m ON c.JPY_COST > m.THRESHOLD ...
            ),

            -- 3. 既に通知済みの同レベルのアラートを除外
            NEW_ALERTS AS (
                SELECT a.* FROM ALERT_CANDIDATES a
                LEFT JOIN ALERT_HISTORY h 
                    ON a.USER_NAME = h.USER_NAME 
                    AND a.ALERT_LEVEL = h.ALERT_LEVEL
                WHERE h.USER_NAME IS NULL  ...
            ),

            -- 4.  Slack IDを紐付け、通知用メッセージを生成
            NOTIFY_PAYLOAD AS (
                SELECT 
                    LISTAGG('<@' || s.SLACK_ID || '>', ' ') AS MENTIONS,
                    MESSAGE
                FROM NEW_ALERTS n
                JOIN SLACK_USER_MASTER s ON n.USER_NAME = s.USER_NAME  ...
            )
            ...
            -- 5. メッセージを変数に格納
            SELECT LISTAGG(MESSAGE || MENTIONS, '\n') INTO :notification_msg FROM NOTIFY_PAYLOAD;

            -- 6. 通知実行 & 履歴書き込み
            IF (:notification_msg IS NOT NULL) THEN
                CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(:notification_msg, ...);
                INSERT INTO ALERT_HISTORY ...; 
            END IF;
        END;

結果

無事に通知できました。

実践②：データパイプラインの「原価」を可視化する

課題

使われていないのに動き続ける「ゾンビマート」や、過剰な頻度で更新されるパイプラインの放置。

解決策

Snowflake Task へのタグ（Tag）付けルールの導入
組織や業務、プロジェクト、パイプライン単位でのコスト可視化

説明

前述の通り、データパイプラインの自動化に使われることが多いSnowflake Taskでは、実行ユーザーがSYSTEMにまとめられるため、クエリ履歴から「誰が何のために実行しているのか」が非常に分かりにくくなります。

ここで使うのが 「クエリタグ(Query Tag)」 です。

クエリタグについて

Snowflake Taskにクエリタグをセットすることにより、そのタスクから実行されるすべてのクエリに「タグ」が付与され、この「タグ」はQUERY_HISTORYに反映されます。そしてここで最も強調して紹介したいポイントはクエリタグをJSON形式でセットすることです。

クエリタグは当然ながら履歴の1項目に格納されます。つまりシンプルなテキスト（例.チーム名、パイプライン名、プロジェクト名）を入れると情報が1つしか入れられません。

クエリ履歴の実行ユーザーはSYSTEMとなっていますが、クエリタグの値から目的や管理元を判別できます

Snowflakeでは、値がJSONで格納されている際に簡単に展開できるPARSE_JSONなどの関数が用意されています。例えばクエリタグに下記のようにセットすれば、QUERY_HISTORYのSYSTEMユーザーのクエリを任意の集計軸で集計することができます。

{"org":"営業第一部","team":"法人営業","pipeline":"売り上げ分析","admin_email":"aaa@bbb"}

結果

クエリタグの仕組みを利用して、パイプライン単位でのダッシュボードを構築できました。 Snowflake Taskのデータパイプラインを組織・部署・チーム・プロジェクト・パイプラインの単位で深掘りすることができます。

成果:驚異のコスト50%減。あまりに出来すぎた成果とその考察

数字だけを見るとあまりにも出来過ぎで、私自身も目を疑いましたが、紛れもない事実としてお伝えします。 本記事の仕組みを先行導入した組織において、約50%のコスト削減を達成しました。 具体的な金額は載せておりませんが、導入組織の利用者は300名ほどで全員がエンジニアであるため、非常に大きい規模の削減額となっています。