CLOVE合同会社の竹村です。この記事では、データ変換ツール dbt Cloud を使用して、未整備のCSVデータをどのように整形し、実際に分析で使えるデータへと変えていくのかを解説します。⚠️ BigQueryとdbtを接続する手順については記事が長くなってしまうため、ここでは割愛します。こちらの記事が参考になります。1. テストデータの準備まずは今回使用するテストデータを作成していきます。BigQueryにて事前に今回の作業用のデータセットを作成しておいてください。1.1 raw_users_dirtyテーブルの作成今回使用するデータセットの右側の三点リーダーを押下して「テーブルを作成」を選択する。raw_users_dirty.csvをアップロードする。raw_users_dirty.csvスキーマを定義する。ヘッダー行を1行分スキップする。1.2 raw_orders_dirtyテーブルの作成上記と同じ手順でraw_orders_dirtyテーブルも作成してください。raw_orders_dirty.csv2. dbt cloud でのプロジェクトのフォルダ構成とYAML定義ここからはdbt Cloudを使用して、データの整形を行っていきます。※ 今回の作業用のプロジェクトは作成されている前提で進めていきます。2.1 プロジェクトのフォルダ構成最終的なフォルダ構成は下記のようになります。手順通り進めていく中でフォルダ構成がわからなくなった場合は下記画像を参考にしてください。2.2 dbt の YAML ファイルについてdbt プロジェクトでは、.yml ファイルを使って以下のようなメタデータを記述します。ソース定義（sources）BigQuery 上の既存テーブルを dbt モデルから参照できるようにする。モデル定義（models）モデルごとのカラム説明やテストを設定。ドキュメント生成dbt docs generate を実行すると、DAG やカラム説明をブラウザで確認できる。テスト定義not_null、unique、accepted_values などを宣言的に書ける。3. staging モデルの定義/作成/テスト3.1 生データの整理staging層は、生データを整形して扱いやすいデータにする層です。どの様に整形するかを決めるために、現在の生データの状態を一度整理します。raw_orders_dirtyorder_id | STRING | NULLABLEuser_id | STRING | NULLABLEorder_date | STRING | NULLABLEyy-mm-dd形式。yy/mm/dd形式。mm-dd-yy形式。total_amount | STRING | NULLABLE$が先頭にある。円が末尾にある。小数点以下がある。raw_users_dirtyuser_id | STRING | NULLABLEcountry | STRING | NULLABLEISO3文字形式。省略・記号混在形式。フルスペル形式。signup_date | STRING | NULLABLEyy-mm-dd形式。yy/mm/dd形式。mm-dd-yy形式。生データには以下のような問題点があります。カラム型がすべて STRING なので、集計や計算が直接できない。全てがNULL許容なのでNULL値が混ざっているかもしれない。日付（order_date, signup_date）の表記ゆれがある。国（country）の表記ゆれがある。金額（total_amount）の表記ゆれがある。3.2 models/staging/orders/source.yml を作成する外部データベース上の既存テーブルを参照するためにソース定義用の YAML ファイル を作成します。 これにより、BigQuery の生データを dbt モデルから利用できるようになります。まずは「raw_orders_dirty」テーブルのソース定義を追加します。source(orders).yml3.3 models/staging/users/source.yml を作成する同じように「raw_users_dirty」テーブルのソース定義を追加します。source(users).yml3.4 整形後データの整理上記で整理した生データの状態をもとに、整形後の状態も整理します。stg_orders.sqlorder_id | INTEGER | NULLは除外 | ユニークINTEGERにキャストする。user_id | INTEGER | NULLは除外INTEGERにキャストする。order_date | DATE | NULLは除外すべてyy-mm-dd形式で統一する。total_amount | NUMERIC | NULLは除外不要な文字を削除し、小数点第一位で四捨五入する。stg_users.sqluser_id | INTEGER | NULLは除外 | ユニークcountry | STRING | NULLは除外表記ゆれを修正する。signup_date | DATE | NULLは除外すべてyy-mm-dd形式で統一する。上記のように整形することで問題を解消することができます。カラム型がすべて STRING なので、集計や計算が直接できない。>> 各カラムに適切な型を付与。全てがNULL許容なのでNULL値が混ざっているかもしれない。>> NULL非許容にすることでデータの安全性を向上。日付（order_date, signup_date）の表記ゆれがある。>> 上記で整理した内容をもとに場合分けを行い、それぞれ適切な形に変換。国（country）の表記ゆれがある。>> 上記で整理した内容をもとに場合分けを行い、それぞれ適切な形に変換。金額（total_amount）の表記ゆれがある。>> 上記で整理した内容をもとに場合分けを行い、それぞれ適切な形に変換。3.5 models/staging/orders/stg_orders.yml の作成まずは整形後のデータ定義を行います。modelsフォルダ下にstagingフォルダ、ordersフォルダを作成後、ordersフォルダ下にstg_orders.ymlを作成してください。作成後、以下の内容をコピーして貼り付けてください。3.2で整理した整形後のデータを定義しています（NULL許容/非許容、ユニーク）。models/staging/orders/stg_orders.ymlstg_orders.yml3.6 models/staging/orders/stg_orders.sql の作成「stg_orders.yml」の定義をもとに、raw_orders_dirtyテーブル整形用のクエリを記述します。models/staging/orders/stg_orders.sqlstg_orders.sql3.7 models/staging/users/stg_users.yml の作成stagingフォルダ下にusersフォルダを作成後、usersフォルダ下にstg_users.ymlを作成してください。作成後、以下の内容をコピーして貼り付けてください。models/staging/users/stg_users.ymlstg_users.yml3.8 models/staging/users/stg_users.sql の作成「stg_users.yml」の定義をもとに、raw_users_dirtyテーブル整形用のクエリを記述します。models/staging/users/stg_users.sqlstg_users.sql3.9 staging モデルの単体実行・テストdbt cloudにはコマンドラインツールがあります。モデルの実行やテストはコマンドを入力して行います。Studioの下の方にあります。stg_ordersを実行dbt run -m stg_orders※ 成功すればBigQueryのデータセット内にVIEWが作成されます。※ -m オプションは「どのモデルを対象にするか」を指定するオプションです。stg_usersを実行dbt run -m stg_usersstg_ordersをテストdbt test -m stg_orders※ 先ほど .yml で定義したNULL、UNIQUEのtestsが実行されます。stg_usersをテストdbt test -m stg_users今回は初回だったので「run」と「test」を別々に実行しました。「build」を使用することで両方一緒に実行することができます。例dbt build -m stg_oders4. intermediate モデルの定義/作成/テスト4.1 models/intermediate/orders/orders_enriched.yml の作成intermediate層はstaging 層で整形された複数のテーブルを結合したり、追加の計算をしたりして「分析に使いやすい形に整える」層です。整形した「stg_orders」と「stg_users」ではユーザーIDでのリレーションが存在しています。今回は分析用に二つのテーブルをユーザーIDで結合し、注文ID・ユーザーの国コード・注文日・注文金額などが一覧で見れる中間テーブルを作成します。models/intermediate/orders/orders_enriched.ymlorder_enriched.yml4.2 models/intermediate/orders/orders_enriched.sql の作成「orders_enriched.yml」の定義をもとにクエリを記述します。models/intermediate/orders/orders_enriched.sqlorders_enriched.sql4.3 intermediate モデルの単体実行・テストstagingの際は「run」と「test」を別々に実行したので、今回は「build」で両方一気に実行します。dbt build -m orders_enrichedBigQueryの指定のデータセットにVIEWが作成され、テストが行われます。5. marts モデルの定義と定義/作成/テスト5.1 models/marts/revenue/revenue_by_country.yml にモデル・テスト定義を追加marts層は最終的にビジネスユーザーやアナリストが使う「集計済みの分析用データ」を提供する層です。今回は国別の注文数、注文合計金額、注文平均金額等が一覧で見れるテーブルを作成します。models/marts/revenue/revenue_by_country.ymlrevenue_by_country.yml5.2 models/marts/revenue/revenue_by_country.sql の作成「revenue_by_country.yml」の定義をもとにクエリを記述します。models/marts/revenue/revenue_by_country.sqlrevenue_by_country.sql5.3 marts モデルの単体実行・テスト「build」で作成とテストを同時に行います。dbt build -m revenue_by_countrycopyBigQueryの指定のデータセットにVIEWが作成され、テストが行われます。6. 全体的なビルド・テストとBigQuery上での確認6.1 クエリと各層の役割の再確認ここまでで staging 層、intermediate 層、marts 層の作成ができました。各層の役割についてもう一度まとめます。staging層>> 生データを整形してきれいなデータにする層。intermediate層>> 整形された複数のテーブルを結合したり、追加の計算をしたりして分析に使いやすい形に整える層。marts層>> 最終的にビジネスユーザーやアナリストが使う集計済みの分析用データを提供する層。記述したクエリについても再度まとめます。stg_orders.sql>> 生の注文データを整形するクエリ。stg_users.sql>> 生のユーザーデータを整形するクエリ。orders_enriched.sql>> stg_ordersとstg_usersを分析しやすくするために、ユーザーIDで結合するクエリ。revenue_by_country.sql>> orders_enrichedをもとに、国別の項目別分析（集計）結果を出力するクエリ。6.2 dbt build の実行コマンドラインに「dbt build」を入力後Enterを押し、もう一度プロジェクト全体をbuildします。実行後、BigQueryに移動し、「revenue_by_country」のクエリを実行して、分析結果を確認します。作成された「revenue_by_country」のVIEWを押下して、詳細タブからクエリをコピーする。新しいタブを開き、先ほどコピーしたクエリを貼り付け、実行ボタンを押下する。国別の注文数や注文合計金額が出力されることを確認する。以上が、BigQuery と dbt Cloud を使った「未整備データを信頼できるデータに変えるプロセス」です。dbt Cloud なら、煩雑なデータクリーニングを効率化し、モデル管理を通じて透明性と再現性のある基盤を構築できます。データ活用に課題を感じている方は、ぜひ dbt Cloud を導入してその効果を体感してみてください。CLOVE合同会社は、企業のデータ活用を推進するための戦略策定、データ基盤構築、データ分析、AI活用支援を提供するコンサルティング会社です。 データ活用の専門家として、マーケティング、営業、業務効率化など幅広い領域で支援を行っております。貴社の課題や目的に応じた最適な設計をご提案しますので、ご興味がありましたらぜひお問い合わせフォームからお気軽にご相談ください。