dbt + Snowflake を組み合わせて データ変換を実装してみた 2024.7.23 データ事業本部 機械学習チーム 吉田奈々

Xへの投稿の際は、 ハッシュタグ #cm_odyssey でお願いいたします。 2 お願い

スピーカー紹介 名前：吉田奈々 所属：クラスメソッド株式会社 データ事業本部 機械学習チーム 触っているサービス：dbt,Amazon Athena,Amazon SageMaker ブログ： https://dev.classmethod.jp/author/n anayoshi/ 3

メインターゲット：dbt 初学者の方 • これから dbt + Snowflake を使ってデータ変換システム を構築したいと考えている初学者 • dbt + Snowflake で構築する際の詰まるポイントを 知りたい方 • dbt のメリット・デメリットが知りたい方 本セッションの対象者 4

• dbt と Snowflake の概要 • dbt のメリット・デメリット • 過去に触ったことのある ELT サービスと比較して、 メリットとデメリットを紹介 • dbt + Snowflake の構築手順 • 詰まったポイントと解消方法 本セッションで話すこと 5

目次 1. dbtとは 2. Snowflake とは 3. dbt のメリット・デメリット 4. dbt + Snowflake データ変換システム構築 6

本日のゴール 7 やりたいこと 1. ローカル環境に dbt-core をインストール 2. Snowflake にリソースを作成 3. 作成したリソース情報をもとに、dbt init コマンドで dbt プロジェクトを作成 4. 接続情報が間違っていないか dbt debug で確認 ※本環境ではテストのため、ローカルから実行しています Client Database Users ① ② ③ ④

１．dbt とは 8

概要 • dbt（data build tool）とは • SQL を用いて、データを変換し、データウェアハウス、 データマートを構築していくことができるツール • データウェアハウスに保存されているデータを変 換し、変換したデータをデータウェアハウスに書 き戻す部分で使用する • ELT の T（Transform：変換）を担う 9

概要 • ELT とは • データインテグレーションプロセスの一つ 10 Extract 抽出 Transform 加工・変換 Load 読み込み • データの抽出 • API、基幹システム、 CRM / 顧客管理シス テムなど • 抽出したデータをデ ータウェアハウスな どのストレージに読 み込む • データを必要に応じ て加工・変換を行う プロセス

概要 • dbt の領域 11 参考資料：https://classmethod.jp/partner/dbt/

概要 • dbt でのデータ処理の流れ 12

２．Snowflake とは 13

概要 • クラウド上で動作する SaaS 型データクラウド 14 特徴 • データベース・データ ウェアハウスの保存、 データ処理、分析が可能 • 従量課金制 • インフラ管理が不要 参考資料：https://www.Snowflake.com/ja/data-cloud/platform/

概要 • Snowflake の領域 15 参考資料：https://classmethod.jp/partner/dbt/

３．dbt のメリット / デメリット 16

dbt のメリット・デメリット • 他 ELT サービスとの比較 • Azure Data Factory • Azure Synapse Analytics 17

Azure Synapse Analytics • Azure の ELT サービスの一つ • 大規模なデータウェアハウス構築とビッグデータ分析を担う • GUI でデータパイプラインの構築が可能 18 参考URL：https://learn.microsoft.com/ja-jp/azure/synapse-analytics/overview-what-is

Azure Data Factory • Azure の ELT サービスの一つ • 異なるデータソースからのデータを収集、変換、移動させるために使用 • GUI でデータパイプラインの構築が可能 19 参考URL：https://learn.microsoft.com/ja-jp/azure/data-factory/introduction

メリット 1. SQL + α でデータ変換加工 2. テスト機能 3. ドキュメント生成機能 20

• dbt モデル実行の仕組み １．SQL + α でデータ変換加工 21 dbt プロジェクトフォルダ Analytics logs models ・ ・ ・ ./dbt プロジェクトフォルダ / models 配下に 格納された .sql ファイルが動作する ※dbtプロジェクトフォルダイメージ図

• {{ ref }} を使って他 dbt モデルを参照できる １．SQL + α でデータ変換加工 22 model_a.sql model_b.sql model_a.sql モデル を参照する

１．SQL + α でデータ変換加工 • jinja テンプレート機能：macros の使用 23 dbt プロジェクトフォルダ Analytics macros models ・ ・ ・

２．テスト機能 • テストの重要性 • データ品質が低い→ビジネス上の悪影響 が発生する 24

２．テスト機能 • dbt test • singular test：単一テスト • generic test：汎用テスト 25

２．テスト機能 • dbt test：singular test • テスト対象のデータセット内で不要な行を選択するSQL文を作成 • 不要な行が0件ならテスト成功 • 不要な行が含まれたらテスト失敗 26 singular_test.sql

２．テスト機能 • テスト実行結果 • コンソールで dbt test コマンドを実行 27 テスト失敗 テスト成功

２．テスト機能 • dbt test：generic test • テスト内容を記述した.ymlファイルを作成する • columns:ブロックの配下でカラム名を指定 • tests:ブロックで実行したいテスト名を指定 28 ./dbt_project/models/hoge.yml

２．テスト機能 • dbt test：generic test のデフォルトテスト • 以下4種類がデフォルトで用意されている • 自分でジェネリックテストを作成することも可能 29 テスト名 概要 not_null 指定されたカラムが null を含まないこと unique 指定されたカラムのデータがすべてユニーク値であること accepted_values 指定されたカラムには、指定された値以外を含まないこと relationships 指定されたカラムの値が、 to に指定されたモデルの field に指定されたカラムに含まれ ていること

３．ドキュメント生成機能 30

３．ドキュメント生成機能 • dbt ドキュメント 実行方法 • dbt docs generate の実行： • dbt docs serve の実行： 31

３．ドキュメント生成機能 32

デメリット 1. GUIをサポートしていない • SQLに精通しているユーザー向けに設計されている • dbt cloud では GUI を使用して dbt モデルが作成できるが 別途料金がかかる 2. サーバの作成が必要 • サーバの管理が発生する • フルマネージドサービスとして dbt cloud があるが、別途料金が かかる 33

メリット・デメリット まとめ 34 dbt Azure Synapse Analytics Azure Data Factory 変換言語 • SQL + jinja テンプレート ※SQL jinja テンプレートのみを サポートするため、学習が必要 • GUIがベース • GUI でサポートしていない変 換が行いたい場合 PySpark を使用する • GUIがベース • GUI でサポートしていない 変換が行いたい場合 SQL を使用する テスト機能 • SQL / yml を記述することで テストが実行可能 • データパイプラインの テストが GUI で実行可能 • 単一テストは未実装 • データパイプラインの テストが GUI で実行可能 • 単一テストは未実装 ドキュメント 生成機能 • dbt docs generate、dbt doc serve でドキュメントが生成 可能 • 未実装 • 未実装 ETL のどの 部分を担うか • Transform（データの加工・ 変換）に特化 • データウェアハウスは別途 用意する必要がある • 全範囲をカバー • データウェアハウス構築～ データ加工・変換まで一貫し て行えるのがメリット • LとTに対応している • 様々なデータソースに対応し ている • データウェアハウスは別途 用意する必要がある

３．dbt + Snowflake で データパイプラインを構築 35

本日のゴール 1. dbt-core インストール 2. Snowflake のセットアップ 3. dbt init の実行 4. dbt debug の実行 36

構成図 37 やりたいこと 1. ローカル環境に dbt-core をインストール 2. Snowflake にリソースを作成 3. 作成したリソース情報をもとに、dbt init コマンドで dbt プロジェクトを作成 4. 接続情報が間違っていないか dbt debug で確認 ※本環境ではテストのため、ローカルから実行しています Client Database Users ① ② ③ ④

１．dbt-core インストール • ローカル環境に以下のコマンドで dbt と Snowflake を接 続するためのアダプターをインストールする 38

２．Snowflake セットアップ • dbt 接続先の Snowflake に以下のリソースを作成する • Snowflake コンソール上で SQL を実行 ２－１．ユーザー / ロール ２－２．データベース / スキーマ ２－３．ウェアハウス 39

２－１．ユーザー / ロール 作成 • Snowflake SQL ワークシート上で以下のSQLを実行する 40

２－２．データベース / スキーマ 作成 • Snowflake SQL ワークシート上で以下のSQLを実行する 41

２－３．ウェアハウス 作成 • Snowflake SQL ワークシート上で以下のSQLを実行する 42

３．dbt init の実行 • Snowflake リソース準備が完了したら、dbt init コマンド を実行する • コンソール上で対話形式で接続情報を入力 43

３．dbt init の実行 44 • dbt init 入力項目 項目名 入力内容 説明 プロジェクト名 任意のプロジェクト名 dbt プロジェクト名を入力する type Snowflake dbt と接続しているウェアハウスを入力する ex.Snowflake,athena,redshift account Snowflake 識別子名 Snowflake 識別子名 {locator}.ap-northeast-1.aws user ユーザー名 2.Snowflake のセットアップ で作成したユーザー名を入力 password パスワード 2.Snowflake のセットアップ で作成したユーザーの指定したパスワードを入力 role ロール名 2.Snowflake のセットアップ で作成した以下のリソース名を入力 • ロール • データベース • スキーマ • ウェアハウス database データベース名 schema スキーマ名 warehouse ウェアハウス名

３．dbt init の実行 • dbt init を実行すると、入力した接続情報が profile.yml に追加される 45

４．dbt debug の実行 • コンソールから dbt debug を実行 • dbt_project.yml と profile.yml の接続情報を確認 46

詰まったポイント① • dbt debug 実行時に、Snowflake の 権限が足りずエラーが発生する 47

解決手順① • 必ずリソースを作成したら、ロールを付与する • ロールを作成後にユーザー、データベース、 スキーマ、ウェアハウスを作成し、作成した ロールの権限を付与 • 以下の通り、grant all を実行する 48

詰まったポイント② 49 • dbt init 実行時に、Snowflake 識別子登録でエラーが 発生する • Snowflake 識別子が Snowflake のコンソール上で 深いところにある

解決手順② 50 • Snowflake 識別子の調べ方 コンソール→左下のアカウント名 からSnowflake 識別子が確認可能

まとめ • dbt を使用して、SQL と jinja テンプレートを使ったデータ変換の効率化 が可能 51

No content