無料ではじめる dbt 入門（4）| 基本ワークフローの全て

前回の記事では、dbt プロジェクトの作成までを行いました。今回は、dbt を用いた基本的なワークフロー全体の流れを解説します。

このワークフローを理解することで、体系的なSQLクエリ作成、ドキュメント整備の自動化、更には品質テストの自動化までを出来るようになります。

では、さっそくいってみましょう。

スポンサーリンク

ステージング クエリの作成

ステージング（staging）は、元データ（ソーステーブル）を整えて後続の処理に使いやすくするためのステップです。

例えば次のようにデータを整えるのがこのステップでの処理です。

  • 列名をプロジェクトで用いる用語に統一（例：date → 日付）
  • データ型の見直し（例：10桁数値型　→ 浮動小数数値型）
  • null を ゼロ（数値）や 空白文字列 などのデフォルト値に変換

dbt 上のSQLクエリが参照するデータベースのテーブルを依存関係グラフに表示するためには、この 構成定義ファイル にテーブル情報を定義しておく必要があります。

ですので、まずはここから作業していきます。

構成定義ファイルでソーステーブルを定義

構成定義ファイル とは models フォルダ内に作成する YAMLファイル で主に以下の内容を定義します。

  • データベース上の物理テーブル名
  • dbt で開発する SQLクエリのドキュメント、テスト内容

YAML 形式のルール

YAML形式のルールは以下の通りです。シンプルなルールで GitHub Actions や Docker Compose の設定ファイルなど広く利用されている形式です。ここで覚えておいて損はありませんね。

  • 拡張子は.ymlまたは.yaml
  • インデント　半角スペース 2つ or 4つ でそろえる（タブ文字は禁止）
  • コロン（:）　key: の後にスペース１つ、続けて値を書く
  • ハイフン（-）　リスト要素の前に付け、後ろにスペース１つ
  • シャープ（#）　コメントは `#

構成定義ファイルの例として models/staging/schema.yml というファイルを作成し、内容をYAML形式で以下のように記述します。

ちなみ、構成定義ファイルは YAML形式であれば、 models フォルダ内のどのフォルダにあっても、どのような名称であっても構いません。但し、定義が他のファイルと重複しないように気をつける必要があります。

version: 2

sources:
  - name: raw
    database: my_database
    schema: dbo
    tables:
      - name: customers

上記で記載した内容は以下の通りです。

  • 一連のソース群に「raw」という名前をつける（SQLクエリで参照する際に必要！）
  • データベースは「my_database」を指定
  • スキーマは「dbo」を設定（「dbo」 は SQL Server におけるデフォルトスキーマです）
  • テーブル名「customer」を記述（利用するテーブル名を記載します）

この定義により、データベース my_databe 、スキーマ dbo のテーブル customer を dbtから参照できるようになります。

物理テーブル（ソース）の呼び出し方

定義したテーブルは、dbt 上で作成するSQLクエリ内で source 関数 を使って呼び出します。

SELECT * FROM {{ source('raw', 'customers') }}

通常の SQLクエリ では直接テーブル名を書くところを source関数 で置き換えています。

source 関数の第一引数は構成定義ファイルで定義したソース群の名前で、第二引数はテーブル名です。

ここはやや面倒なところですが、こうして明示的にテーブル参照を定義することで後から dbt で依存関係グラフを作成してクエリやテーブルの関係性を図で確認することが可能になります。

ステージング クエリの作成

models フォルダ内のフォルダ構成 や SQLクエリの ファイル名の命名 については基本ルールを作って運用するのが洗練されたやり方です。

ルールがないままで SQLクエリ を沢山作成してしまうと、後から管理が難しくなくなってしまいます。

推奨ルールでは、ステージングクエリは、models/staging/ フォルダ 内に作成します。また、ファイル名の頭には stg_ をつけます。

このルールに従って models/staging/stg_customers.sql というファイルで以下のような SQLクエリを作成します。

SELECT
  id AS customer_id,
  name AS customer_name,
  created_at
FROM {{ source('raw', 'customers') }}

この例では、原則に基づいて次の処理をしています。

  • カラム名を標準化
  • 不要なカラムを除外

これにならってご自分の環境でもステージングクエリを作成してみて下さい。

ステージング クエリは最初は何も変換しなくても作成した方が良い理由

最初はステージング クエリでデータを整える必要性を感じないかもしれません。しかし、それでもステージング クエリを作成しておくことをお勧めします。つまり、そういう時は select * from table1 のようにテーブルを丸ごと呼び出すクエリを作成しておきます。

というのも、SQLクエリ 開発を進めていく中で最上流でデータを整えておきたいというニーズが後から発生することが良くあるからです。

その際に既に沢山の SQLクエリ から物理テーブルを直接参照してしまっていると、全ての SQLクエリ の参照先を修正しなくてはなりません。

しかし、ステージングクエリを作成しておけば ステージングクエリを修正するだけ でその他の SQLクエリ の修正はしなくても済むのです。

中間クエリの作成

ステージングしたデータを基に、必要なロジックを加えて変換していくのが中間クエリ（中間モデル）です。

中間クエリにも推奨ルールがあります。推奨ルールでは、中間クエリは、models/intermediate/ フォルダ 内に作成します。

また、後ほど紹介する ファクト は fct_ ディメンジョンは dim_ をファイル名の頭に付けます。

dbt の SQLクエリ の呼び出し方

dbt上で作成したクエリは、ref 関数 で以下のようにして参照します。

SELECT
  customer_id,
  COUNT(*) AS order_count
FROM {{ ref('stg_customers') }}
GROUP BY customer_id

source関数と同様に、この ref関数を使って SQLクエリを参照することにより、dbtで依存関係グラフを自動作成することが可能になります。

ファクト と ディメンジョン ／ 主キー と 外部キー

中間クエリでは、ビジネス上の意味を持つ「ファクト（事実）」「ディメンジョン（軸）」の形にデータを整理することが多いです。

ディメンジョン

ディメンジョンのデータは、キーに対する区分情報をまとめた ものです。

例えば 顧客ID ごとの 地区、業種、名前 のデータです。

このディメンジョンのデータを使って後で分析をかけることで、例えば 「関西地区 の 建設業顧客 からの注文が増加している」 などの分析結果を得ることができます。

SQLクエリ でデータを加工する際に、まずこのディメンジョンの SQLクエリ を作成しておくと後で様々な場面で活用できます。

この例の 顧客ID のような ディメンジョン の軸となるデータ列 のことを 「主キー」 と呼びます。

ファクト

ディメンジョン を使って分析するべき ファクト（＝事実） のデータも当然必要になります。

例えば 注文 や 売上 のデータです。

このファクトには必要最小限のデータを含めておきます。例えば 顧客 については 顧客ID のみ、営業担当については営業担当の 社員ID のみ を含めるようにします。

このように、ファクト・データに含まれる 顧客ID のデータ列のことを 外部キー と呼びます。

マート クエリの作成

マート（mart）とは、最終的に分析やレポートに使うためにデータを集約するクエリです。

マートでは主に中間クエリとして作成した ディメンジョン、ファクト を外部キー と 主キー で結合して作成します。

マートクエリは models/mart/ フォルダ 内に作成します。また、 mrt_ をファイル名の頭に付けます。

例えば、 models/marts/mrt_customer_summary.sql というファイルを下の内容で作成します。

SELECT
  c.customer_id,
  c.customer_name,
  o.total_orders,
  o.last_order_date
FROM {{ ref('dim_customers') }} c
LEFT JOIN {{ ref('fct_orders') }} o
  ON c.customer_id = o.customer_id

マートクエリでは、複数のファクトやディメンジョンを統合し、BIツールや分析用に最適化された形式に仕上げます。

ここまでで一通り分析用のマートクエリの作成までが完了しました。

dbt が動く Python の仮想環境下で以下のコマンドを実行することでデータベース上に ステージング、中間、マート のクエリがビューとして作成されます。

dbt run

テストの作成と実行

SQLクエリ開発の過程では様々な変更が適用されます。変更はある目的は満たしても、思わぬ別の場所で問題を起こす可能性があります。

人間が常に全ての影響を考慮するのは難しいです。そこで有用なのがテストです。

dbt のテストは比較的簡単な定義を記述をすることで SQLクエリ の必要な要件をテストすることが可能です。

変更を適用する度にテストを実行して SQLクエリ の品質を確認して作業を進めることで、プロジェクト全体の品質を確保することが可能です。

テストは構成定義ファイルにテストの内容を記述することで可能になります。

以下は、customer_id 列が以下の要件を満たすことをテストするための定義です。

  • 必ずデータがあること（＝ null でないこと）
  • 重複するデータが無いこと（= unique であること）
models:
  - name: stg_customers
    columns:
      - name: customer_id
        tests:
          - not_null
          - unique

この定義文は以下の意味になっています。

  • SQLクエリ stg_customers を指定
  • 列名 customer_id を指定
  • テストの定義として not_null と unique を指定

テストは以下のコマンドで実行できます。

dbt test

ドキュメント作成・表示

ドキュメント作成

構成定義ファイル にドキュメント情報も記述することで dbt でドキュメントを自動作成することができます。

ドキュメントは Webブラウザで閲覧できます。

構成定義ファイル にドキュメント情報も含めた例は次のようになります。

# models/staging/schema.yml
version: 2

models:
  - name: stg_customers
    description: >
      生データベースの customers テーブルを元にしたステージングモデル。
      カラム名を標準化し、分析やマート作成に利用しやすい形式に整備しています。

    columns:
      - name: customer_id
        description: 顧客を一意に識別するID
        tests:
          - not_null
          - unique

      - name: customer_name
        description: 顧客の氏名（フルネーム）

      - name: created_at
        description: 顧客が作成された日時
        tests:
          - not_null

構成定義ファイルの記述が完了したら、次のコマンドでドキュメントを自動生成します。

dbt docs generate

ドキュメント表示

以下のコマンドを実行するとデフォルトのブラウザが自動で起動して dbt のドキュメントを閲覧できます。

dbt docs serve

データモデルの構成やテスト状況を視覚的に確認できる優れた機能です。

依存関係の確認

dbt のドキュメントでは、各モデルがどのテーブルやモデルに依存しているかの「依存関係グラフ」も表示できます。

画面右下にあるアイコンをクリックすると画面に dbt 上で作成した SQLクエリ の依存関係を示した図が表示されます。

SQLクエリが多くなればなるほどこの機能は非常に役立ちます。例えば、ある変更を加えたい時にどの中間クエリを修正すれば良いかなど、この依存関係グラフを眺めることで判断を早くできるようになります。

まとめ

今回は、dbt の基本的なワークフローについて、以下のステップに沿って解説しました。

  • ステージング：　物理テーブルのデータを最初の処理として整える
  • 中間クエリ：　主に ファクト と ディメンジョン を意識して作成する中間処理
  • マート：　分析・可視化 のためにデータを集約
  • テスト：　プロジェクト全体の品質確保のためにテストで常に品質を監視
  • ドキュメント：　後から理解ができるようにドキュメントを整備したり、依存関係の視覚化 する

このワークフローを身につけることで、データの品質を担保しつつ、分析環境の再現性と効率を大幅に高めることができます。

是非皆さんも dbt による強力な開発基盤を手にしてより大きく、高い品質の SQLクエリ開発プロジェクトを進めてみてください。

タイトルとURLをコピーしました