データ基盤をなんとかしなければ・・・

以前から触れていた、 dbt Projects on Snowflake について、ようやく運用にのせるための目処がついたので、今回はその設計の全体像についての記事です。よく見かける dbt x Terraform という構成を採用しており、今更 “モダンな設計” と呼べるものでもないので、視聴データを扱う弊社ならではの運用環境や実装のポイントを中心に記載します。

データ基盤刷新のねらい

弊社では視聴者非特定視聴データや動画配信ログの分析として 2022年から Snowflake を利用しています。

これまでいろんな課題を抱えながらもなんとか運用してきたのですが、元データの仕様変更や蓄えているデータ量の増加、人事異動に伴う担当者の変更、など様々な要因から日々の運用に振り回されているだけの状態になってしまっていました 😵

そんな中、昨今の AI の急成長をはじめとして、 Snowflake 自体にも Snowflake 以外の周辺サービスにもどんどん新しい機能が追加されています。データを十分に利活用できている状態とはとうてい呼べない現状を打破するため、データ管理者にとってもデータ利用者にとってもより負担が少なく運用でき、今後新たなサービスを取り入れやすくなるような設計を目指してデータ基盤を刷新することとしました。

刷新前に感じていた課題

先ほど「人事異動に伴う担当者の変更」と述べたとおり、かくいう私も途中参戦の身かつ担当が外れる期間もあったりで、運用に携わっていた期間はトータル 1 年半くらいです。決して長い期間ではありませんが、その中で特に強く感じていた課題は以下の通りです。

1. 運用面の課題

🔍 調査コストの増大: マーケティング部門からダッシュボードのデータ不備を指摘されるたびに、調査と修正に膨大な時間が必要
🐢 データ活用の低速化: マーケティング部門からダッシュボード追加の要望があったりアドホック分析の依頼があっても、リソース不足によりすぐに対応できない
📚 ドキュメントの形骸化: 資料が複数箇所に分かれており探すのが大変かつ更新漏れが発生、また「なぜこの仕様になっているのか？」という経緯が不明で資料の情報だけでは引き継ぎが困難

2. 技術面の課題

📜 バージョン管理の欠如: Snowflake コンソールで最新の SQL のみが閲覧できる状態
🏷️ 不適切な命名: TMP_... や TABLE_1, 2 といった意味を持たない CTE 名やカラム名が多く存在
⚡ パフォーマンスの限界: 適切なクラスタリングがされておらず、月次やクール次のスキャン量の多い集計処理でタイムアウトがしばしば発生
🕸️ VIEW の乱立: データの Transform に VIEW が多用されており、リネージが複雑化 (というか全体を通して見れない)
🏗️ 検証環境の未設計: 本番環境しか存在しないため、変更を加える場合には本番環境に TABLE や VIEW をはじめとするオブジェクトを新たに作成して検証

このような技術的な問題点がデータを十分に利活用しきれない要因の一つになっている状態は、いち早く解決しないといけないことはわかっていながらも、重労働になることが目に見えていたのでなかなか腰を上げることができないまま月日が流れていました・・・🌒 しかも、既存のデータ基盤を運用しながら改修を進めるとなるとデータへの影響や費用面も考慮しなければならないのでなおさらです。

そこで、まずは既存運用における不要なデータおよび不要な処理を洗い出し、その浮いた費用を充てて、既存スキーマとは別のスキーマとして新たに構築する方法で進めることとしました。

dbt x Terraform によるデータ基盤の再構築

せっかく新たに構築するので、 「少人数でまわせる」「属人化させない」 これらはマスト要件として設計に組み込まないといけません。となると AI を活用しない手はなく、できる限りコードに落とし込んで自動化できるものは自動化したいところです 🤖

dbt x Terraform を選択することにより、

インフラのコード管理
データ品質の確保
ドキュメントの自動生成
一貫した本番・開発環境の構築 & 運用
Transform 用に作成していた VIEW の削除

等々、前述の課題をだいぶ解決できるので躊躇なくこれを採用することにしました。

dbt の実行環境

ただ、ここで考えなければいけないのが「dbt をどこで実行するのか？🤔」です。少人数での運用は必須、できる限り費用も抑えたいです。

これを実現してくれる環境として dbt Projects on Snowflake を選択しました。他の実行環境として ① dbt Core をクラウド上に構築 / ② dbt Cloud を利用 も候補にあがりましたが、dbt Core の場合は、クラウド上のサーバをメンテナンスするのが面倒ですし、dbt Cloud の場合はどうしても費用がかさみます。それに対して dbt Projects on Snowflake の場合は Snowflake 内で完結するためインフラ管理やユーザ管理は不要、かつ dbt 実行による Snowflake の料金もコンパイルやテスト、ログ出力等によるウェアハウスの稼働時間が少し増えるくらいです。

あとは、 dbt Projects on Snowflake ならではの制限が運用に影響がない or 制限があっても別の方法で回避できるものか を確認するのみです。ありがたいことに制限事項および注意事項についてはドキュメントを用意してくれています。

見た感じ気になったのは、

Environment variables (for example, {{ env_var ('MY_ENV_VAR') }}) aren’t supported when running a dbt project object. As an alternative, use project variables (for example, --vars). For more information, see Project variables.

の部分くらいで、これもなんとか対処できそう (こちらの記事で詳しく書いています) だったので、dbt Projects on Snowflake で構築を進めてよいと判断しました。

Terraform による Snowflake オブジェクトの管理

dbt x Terraform で構築する際の悩み事として、両方で作成できるものはどっちで管理するか？があげられます。dbt のモデルに SQL を書いてしまえば、権限の許す限り何でも作成できてしまうわけですが、dbt は Terraform と違って「作って壊す」には向いていないので、 「スキーマを落とせばなくせるもの」に絞って dbt で管理、その他を Terraform で管理することとしました。

具体的に dbt 管理としたものは以下のとおりです。

TABLE
VIEW
FUNCTION
PROCEDURE
TABLE・VIEW の権限 (※)

※ dbt で TABLE や VIEW を作成することから Terraform では TABLE・VIEW の権限を付与するのが難しく、dbt で作成したついでに付与することにしています。

データ設計のポイント

ここまででインフラ周りの設計が決まったので、ここからはデータ設計についてです。

扱うデータの特徴は、なんといってもデータの中核を担う視聴データのデータ量が突出して多いことです。これを少人数かつ費用を抑えて運用できるようにしたいです。

そこで、スキャン量を節約することを第一に、属人化させないよう人にも AI にもわかりやすい形でできるだけ情報をコードに落とし込むことを第二に考えました 🤔

生データ専用のスキーマを用意

外部から送信される様々なデータはクラウド上のストレージで連携されるものがほとんどです。これらのデータを Snowflake 上に読み込むために、生データの取り込み専用のスキーマ (以下、 RAW スキーマ) を用意しました。このスキーマに STAGE と PIPE も作成して、全ての生データを変換せずにそのままの形で集約します。他スキーマからは VIEW でこれらを読み込む形ですね 🧐

こうすることで、Snowflake の強みであるゼロコピークローンを活かした開発環境の構築が可能になりました。クローン後に dbt run をせずとも本番環境と同じデータがそのまま扱えます。

各層における役割の明確化

staging / intermediate / marts 層での役割を以下の通りとしました。

staging 層: RAW スキーマを読み込む VIEW を基本とし、複数のデータソース間で異なるカラム名・型・フォーマットを統一 (JOIN はしない)
intermediate 層: marts 層で汎用的に使用しやすい形の TABLE を作成
marts 層: ダッシュボードをはじめとするデータ利用者が欲しいデータの形に整形

marts 層に配置するデータはどれも視聴データが絡む内容なので、スキャン量を節約するために intermediate 層では view または ephemeral ではなく、incremental をデフォルトとしています。もちろん merge 戦略にするとクエリ完了までとてつもなく時間がかかるので delete+insert 戦略です。

あと細かい話でいうと、将来の変更・拡張に備えて、取り得る値が決まっているものについてはstaging 層で ID 化し、marts 層で macros を用いてラベルに戻すようにしています。ただし、これは値を統一しやすい反面可読性に欠けるので、都道府県や市区町村などのあまりに数が多すぎるものには適用していません。

データ品質の確保

dbt の恩恵を最も感じるのがこの点です 👏 データ元から送られてくる除外すべき値や、データ更新の遅れにすぐに気づくことができるので、データの再読み込みを格段に減らせます。先ほどは記載するのを省きましたが、RAW スキーマでは、METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, METADATA$START_SCAN_TIME の 3 つのメタデータを付与するようにしているので、これも併せて利用することで、どこのデータがおかしいかをすぐに指摘することができるようになりました。

ここで注意しなければいけないのは、dbt test によるスキャン量の増加です。staging 層の段階でテストをしてその後変更のないものについてはテストしないようにしたり、 GROUP BY や DISTINCT でユニークを担保しているものは unique テストをしないようにしたり、当たり前の節約をしています。この辺りの面倒なコードチェックは AI がしてくれるのでだいぶいい時代になりましたね 🤖

データの洗い替えへの対応

視聴データに限らずですが、一部のデータが誤っていたり、トラブルにより所定の時刻に到着しなかったり、そもそも速報値を送信 → 後日確定したものを再送信となっていたり、と後からデータを修正しなければいけないことがあるので、期間を指定して特定期間のみデータを洗い替えれるようにしておいた方が何かと便利です。この点への対策として dbt コマンド実行時に --varsオプションで任意の期間を指定して洗い替えができるようにしています。

命名規則と description の徹底

言わずもがな、コードの可読性をあげるための最重要ポイントの一つですね 💡 私個人的には、多少長くなっても曖昧さなく情報が伝わるように命名したいと考えているので、Snowflake の全オブジェクトの命名規則を細かく定め、カラム名や CTE 名も新しい担当者が読み取りやすいように心がけました。加えて description を充実させることで、今まで存在したテーブル定義書やジョブ定義書のようなドキュメントを作成しないでよいようにしました。自分の英語力のなさをひしひしと感じつつ 😭、ここでもまた AI にだいぶ助けられました。

苦労したポイント

データ基盤の刷新は予想通り一筋縄ではいかず、肝となるクラスタキーの設計を何度もやり直したり、Snowflake CLI のバージョンにハマったり苦労した点はたくさんありました。その中からいくつか苦労話をさせてください。

Terraform

Snowflake を Terraform で管理するにあたり、手戻りが最も多かったのが権限管理です。各オブジェクトの OWNERSHIP を熟考してから terraform apply した方がよいです。OWNERSHIP を変更する際には、一度その権限で terraform destroy してから変更後の権限で再度 terraform apply しなければいけません。私は権限を迷いに迷って何度も作り直しました。確定さえすれば alias を書いて profile でオブジェクトごとに切り替えるだけなので何も難しいことはありません。

そして、頭を悩ませたうえでうまく作り込めなかった点が 2 点あります。

1 点目は権限管理にも関係する点で、 RESOURCE MANAGER の作成です。GitHub Actions 等のパイプライン上で Terraform を実行するユーザにはそれ専用の ROLE を作成して付与したいですが、ここに ACCOUNTADMIN / SYSADMIN / SECURITYADMIN あたりの強い権限をそのまま与えたくありません。そこで、専用の ROLE を作成して必要な権限だけを GRANT していましたが、RESOURCE MANAGER の作成は ACCOUNTADMIN 以外できません。大人しく SQL で手動作成することにしました 😭

もう 1 点が NOTIFICATION INTEGRATION です。これを Terraform により作成しようとすると、どうしても循環参照が発生してしまいます。NOTIFICATION INTEGRATION に使用する AWS IAM ROLE には NOTIFICATION INTEGRATION 作成後に発番される external_id を許可したポリシーをアタッチしなければならず、結局、 external_id を dummy としたポリシーを作成 → NOTIFICATION INTEGRATION 作成後に external_id を確認して書き換えという 2 段階で terraform apply するようになってしまいました。

dbt Projects on Snowflake

前述の環境変数が jinja テンプレートの展開に対応していなかった点は実装前に気づいて対処できた反面、いざ実装を進めていると、 一部の dbt コマンドが Snow CLI からは実行できないことに気づきました。読み飛ばしてしまっていましたが、実行環境ごとに使用できる dbt コマンドについてドキュメントにまとめてくれていますね 🙇

例えば、 dbt source freshness は実行できませんので、dbt_utils.recency + dbt test を代用することになります。ただし、EXECUTE DBT PROJECT ... ARGS = '...' の ARGS 部分は、そのまま dbt Core CLI に渡される文字列ですので動作はするようです。サポートに確認すると「動作するはずだが、サポートとして案内するのは難しい」とのことで、運用に組み込むがどうかは判断が難しいところです。安心して使用できる、dbt_utils.recency + dbt test で回避するのが無難かもしれません…

GitHub Actions

CI / CD パイプラインには定番の GitHub Actions を使用しています。パイプライン上で「snow dbt deploy は実行するとして snow dbt execute seed や snow dbt execute build まで実行するのか？その場合、特定のモデルだけ select して実行するのか？」と、どこまで処理するかに非常に悩みました 🤯

結論としては、

ゼロコピークローンで開発環境を構築して必要な権限を付与
snow dbt deploy

の処理に加えて dbt docs generate を日次実行するのみにとどめました。

snow dbt execute build まで実行するとなると、非同期処理しなければ GitHub Actions の料金も発生してしまうことが最大の理由です。dbt build 後には結局データを確認したいですし、検証環境での動作確認時には--vars オプションで任意の期間を指定したくなるので、snow dbt execute build は手動実行で十分だったと実感しています。

dbt docs についても、どこに配置するか ( Streamlit in Snowflake or SPCS or GitHub ) 少し悩み、最終的に IP 制限をしたいことと費用の安さから S3 に落ち着きました。

おわりに

改めて思い返すと、dbt に初めて触れるところからmarts 層にデータが届くまでなかなかに骨が折れる作業でした 🦴 まだ本運用に乗ったわけではありませんが、1ヶ月ほどデータを取り込んでみて、元データの不備にもすぐに気付けますし、スキャン量も減っていますし、コード管理できたことでドキュメントもほぼ不要になり、運用面・費用面ともにガラリと変わったことを実感しています。何よりも、管理対象のオブジェクトが激減したことで心理的安全性が高まりました 😌

これでデータ管理者側の負担は減ったはずなので、これからは AI を活用してデータ利用者側が自らデータ分析をしやすい環境を提供できるように改善していきたいところです 💪