アソビューのDWH一本化プロジェクト：BigQuery・Snowflake 検討のプロセス

はじめに

こんにちは。 アソビューでデータエンジニアをしている山野です。

アソビューのデータ基盤ではこれまでBigQueryとSnowflakeの２つのDWHを利用してきました。
しかしながらこのシステム構成を見直し、DWHを一本化することを決め、今年の初めから半年ほどかけ選定検証を行ってきました。

今回はアソビューで実際にどのような検証を行ったか共有したいと思います。
この内容がDWHの変更や移行を検討しているデータエンジニアの方々にとって少しでも参考になれば幸いです。

前提 - アソビューのデータ基盤のこれまで

アソビューのデータ基盤については過去テックブログでもご紹介してきましたが、 当初はGCP（現: Google Cloud）利用実績やGAなどとの親和性からBigQuery（BQ）をDWHとして採用しました。 tech.asoview.co.jp whatweuse.dev

しかしその後、データ量の加速度的な増加によるパフォーマンス劣化や社内のBIツール利用拡大に伴うコスト増が顕在化しました。
対策として、BIツールとの相性やコストコントロールのし易さからSnowflake（SF）を追加導入。 BQでデータレイク/マートを構築し、BIが利用する一部のデータマートのみBQからSFへ連携する構成としました。

これにより一定のコスト削減はできましたがDWHが併存することで以下の新たな課題が発生しました。

  • システム構成の複雑化（ツール導入の障壁）
  • データサイロの発生
  • DWH二重管理による運用コストの増大

これらの課題を根本的に解決するため、DWHの一本化を決定し最適なDWHを選定するための検証を実施しました。

どのような検証を行ったか

最初に各DWHの機能について主に以下の５つの観点から機能比較を行いました。

# 観点 内容
1 シングルソース データソースの居場所に関わらずすべてのデータが一元管理されている状態
2 マルチワークロード ワークロード間の相互不干渉性が担保されている状態
3 データコラボレーション データのデリバリーやシェアが容易なこと
4 セーフティ 利用者が安全・安心して使える状態
5 アジリティ 参入障壁が低く、変化に対して柔軟かつ迅速に対応できること

このうち、1のシングルソース〜4のセーフティの観点に関しては、BigQuery・Snowflakeそれぞれ充分な機能が備わっており互角と判断。
一方でアジリティに関しては以下の詳細な観点で機能比較を行い、どちらのDWHに優位性があるか判断しました。
結果は以下の通りです。

観点 比較結果
外部サービスからのデータ連携の容易さ・豊富さ 両DWHに差なし
継続的なクエリ改善のしやすさ Snowflakeに優位性 あり
開発のための学習コストが低さ Snowflakeに優位性 あり
可視化の手段の選びやすさ
(異なる複数のBIを選択・利用のしやすさ) 		BigQueryに優位性 あり
管理コストの低さ Snowflakeに優位性 あり

この結果を見るとSnowflakeの方が良さそうですが、これ以外の機能として例えばAI / ML・Googleサービスとの連携などはBigQueryの方が優位性があるなど、 どちらのDWHにもそれぞれ良い面があり、一概にどちらか片方のDWHが優れているか判断が難しい状況でした。

よってこの結果から純粋にDWHの「パフォーマンス」と「コスト」の２つの観点で検証を行う方向となりました。 それぞれについて具体的にどのような検証を行ったか説明したいと思います。

パフォーマンス検証

検証内容

パフォーマンス検証はBIツールから実行されるクエリ＝ダッシュボードのレスポンス速度を比較・検証しました。
理由としてはBIツールは必ずしもSQLの習熟度が高くないメンバーも利用します。加えて、BIツールが自動生成するクエリは実行者側での個別チューニングが困難なケースが多くあります。そのため、DWHの純粋なパフォーマンスがUXに最も直結しやすいBIツール（Tableau）を介したレスポンス速度を検証対象としました。

検証方法

  1. 弊社で実際に業務利用しているTableauワークブックを用意し、データソースをBigQueryとSnowflakeそれぞれに接続。
  2. 該当のダッシュボードを開きBigQueryに接続した場合とSnowflakeに接続した場合のレスポンス時間を計測・比較。

具体的なテストパターンとしては以下の通りです。

パフォーマンス検証テストパターン

それぞれDWHのキャッシュの有無や、加えてパフォーマンス検証専用のテスト環境と通常業務で利用している環境でパフォーマンスの違いも確認しました。 また Snowflakeについては３パターンのウェアハウスの異なるサイズで検証しました。

コスト検証

DWH移行のコストを評価するため、「データ転送」「データ変換」「アドホッククエリ」の3つの観点で、BigQueryとSnowflakeの利用コストを比較検証しました。

データ転送コスト

検証内容

現行の各データソース → BigQueryのLakeテーブルの転送をSnowflakeへ切り替えを行った場合のコストを比較を行いました。
とはいえ、実際の転送処理をSnowflakeに切り替えてテストを行うにはそれにかかる時間やコストの観点から難しいため、 既存の「BigQuery → Snowflake」連携処理にかかるウェアハウス稼働時間をサンプルにコストを試算し比較を行いました。
具体的な検証方法は以下の通りです。

検証方法

  1. 現行の「データソース → BigQuery」で処理されている総転送データ量を算出する。
  2. 既存の「BigQuery → Snowflake」連携処理の「1GBあたりのデータ転送にかかるウェアハウス稼働時間」を算出する。
  3. 以下の試算式からSnowflakeのウェアハウスの推定総稼働時間を試算する。
(1）の総転送データ量(単位:GB) x (2)のウェアハウス稼働時間

この(3)の推定総稼働時間とSnowflakeの時間あたりクレジット単価からコストを試算しました。

データ変換コスト

検証内容

現行のBigQueryで行っているデータマートを作成・更新する処理をSnowflakeに切り替えた場合にかかるコストを試算・比較しました。 こちらも全てのデータ変換処理を検証することは難しいため、現行のBigQueryのデータ変換処理のクエリから代表的なクエリをいくつかピックアップ。そのクエリをSnowflakeで実行し、総コストを試算しました。
具体的な検証方法は以下の通りです。

検証方法

検証するクエリは「スロット消費量（スロット消費時間）」を基準にその量が大きいクエリをピックアップしました。 スロット消費量を基準とした理由としては、Snowflakeの課金体系がクエリを実行する「ウェアハウスの稼働時間」であるため「ウェアハウス」の概念に近い「スロット」を基準にするのが試算として妥当ではないかという仮説のもと決定しました。
具体的な試算方法は以下です。

  1. 現行のBigQueryの検証対象クエリのスロット消費量とデータ変換処理全体のスロット消費量を求める。
  2. Snowflakeで検証クエリを実行し、ウェアハウスの稼働時間を求める。
  3. 以下の試算式でSnowflakeでのデータ変換にかかるウェアハウスの総稼働時間(=xの値)を求める。
(2)の[Snowflake]のウェアハウス稼働時間 : x  =  現行の検証クエリのスロット消費量 : 現行の総スロット消費量

この推定総稼働時間とSnowflakeのクレジット単価から、全体のデータ変換コストを試算し現行のコストと比較しました。

アドホッククエリコスト

３つの観点のコスト検証のうち最も大事なのがこのアドホッククエリのコスト検証です。 アドホッククエリ(BIツールまたはデータサイエンティスト・エンジニアによる手動実行クエリ)は定常的に実行されるデータ変換・データ転送のクエリとは異なり、同一クエリの実行頻度が不規則であるため、このクエリコスト試算の精緻さがDWH一本化後の本運用のコストに大きく影響します。

検証内容

BigQuery・Snowflakeそれぞれで実行されているアドホッククエリを片方のDWHで全て実行した場合のコストを試算しました。
先の２つのコスト検証と同様に全てのクエリをそれぞれのDWHで実行検証することは難しいため、こちらもクエリをピックアップして検証する形としました。
ピックアップにあたって、アドホッククエリはクエリの性質(データスキャン量/クエリの複雑さ)がバラバラであるため、クエリを「リソース消費量（コスト）」に基づいてタイプ分けし、クエリの複雑さも考慮しながらそれぞれ複数ピックアップし、それを基に試算するアプローチを取りました。

試算方法

  1. クエリのタイプ分け 過去のクエリ実行ログをそれぞれ分析しリソース消費量（BigQueryは消費スロット数、Snowflakeはウェアハウス稼働時間）に応じてそれぞれ５タイプに分類。
    BigQueryのクエリタイプ
    Snowflakeのクエリタイプ

2.タイプ別コスト単価の算出
各タイプを代表するクエリの1回あたりの実行コストを算出。

3.各タイプ毎の総コスト試算
該当のクエリタイプの過去１ヶ月の実績実行回数から以下の試算式でコストを試算。

(2)の1回あたりの実行コスト * 該当のクエリタイプの過去１ヶ月の実績実行回数)

この各クエリタイプのコスト試算結果を合計し、コストを比較しました。

検証中に発生した問題・課題と対応

問題1: データ変換コスト試算結果が仮説に合わなかった

問題

当初「データ変換コスト」の検証では、Snowflakeの課金体系（ウェアハウス稼働時間）と類似するBigQueryの「スロット時間（消費スロット数）」を基準に検証クエリを選定しました。 しかし、両者のコストを比較した結果、スロット時間とSnowflakeの実行コストとの間に明確な相関が見られないことが判明しました。

対応と結果

この仮説が崩れたため、クエリ選定の基準を「データスキャン量」・「ジョブ実行時間」に変更し、それぞれ再選定、再検証を実施しました。
結果、両方の基準ともSnowflakeのコスト試算と相関が見られ、特に「ジョブ実行時間」を基準とした試算結果が最も妥当であると結論付けました。

問題2: 特定クエリにおける極端なパフォーマンス低下

問題

現行のBigQueryのクエリをSnowflakeで実行した場合、またその逆の場合もそれぞれ極端なパフォーマンス低下が発生しました。 原因は、現行ではそれぞれのDWHの機能・性能により正常に実行できていたものが、もう片方のDWHではその性能差分により問題が起きた形です。

対応と結果

この結果自体はコスト比較に有用な情報ですが、この事象に限らずDWH一本化後にクエリの改善は行う必要があることは明確であるため、 それぞれクエリやテーブル設定を変更し、どの程度コストやパフォーマンスが改善するかも検証・コスト比較を行いました。

結論

今回実施したパフォーマンス・コスト検証の結果としては、どちらか一方のDWHに決定的な優位性は見られませんでした。 ですが前述の改善余地の部分やそれぞれのDWHの一本化する上で発生するタスクと工数や各DWHの機能面などから総合的に判断し、アソビューではBigQueryに一本化する方向と決めました。

今回の対応にあたって特にコストの検証についてはロジックに曖昧さがあると、実運用開始後に思わぬコストの発生につながるため 入念に議論・検証設計を行い、Google Cloud・Snowflakeの両技術担当の方にも度々レビューをいただきました。 また前述の通り想定外の問題が重なったものの、これについてもGoogle Cloud・Snowflakeの技術担当の方から多々お力添えいただいたお陰で、最終的な結論を導き出すことができました。
今回の検証はGoogle CloudとSnowflakeのご協力がなければ結論が出なかったと思います。この場を借りて感謝します。
この検証方法を全て当てはめることはできないと思いますが、一部でも参考になれば幸いです。

最後に

アソビュー株式会社では新しいメンバーを随時募集していますので、ご興味がある方はお気軽にカジュアル面談にご応募ください。

www.asoview.co.jp