dbt をローカルでいろいろ試してみる

やりたいこと

dbt を触る機会があったのですが、それまで名前は知っていたものの使ったことがなかったので、いろいろと触って分かったことを記事にしてみます。これから触る方の参考になれば幸いです！

dbt とは

公式ドキュメントより。

dbt is a transformation workflow that helps you get more work done while producing higher quality results. You can use dbt to modularize and centralize your analytics code, while also providing your data team with guardrails typically found in software engineering workflows. Collaborate on data models, version them, and test and document your queries before safely deploying them to production, with monitoring and visibility.

(日本語訳)

dbtは、より多くの作業を効率よく行い、より高品質な結果を生み出すのに役立つ変換ワークフローです。dbtを使用して分析コードをモジュール化し、集約することで、通常ソフトウェアエンジニアリングのワークフローで見られるようなデータチーム向けのガードレールを提供できます。データモデルを共同で作成し、バージョン管理し、クエリをテストして文書化し、安全に本番環境に展開する前に、監視と可視性を備えています。

データエンジニアリングにおける ETL ツールの1つで、特に変換 (Transform) に特化している印象。データモデルを YAML で記述してその内容をドキュメントとして HTML で表示したり、変換クエリを書くとリネージュがその HTML で確認できたり、クエリによってデータのテストを行えたりとなかなか便利なツール。
個人的に今まで ETL 処理は GUI ベースのもの (Azure Data Factory など) を使っており、リネージュを作成しづらかったりテストをやりづらいなと思った経験もあったので、そう、これこれ！という感じ。

試してみる

以下の記事を参考にしました。

前提

• Python を使える環境が必要です
• Docker を使います
• PostgreSQL の DB にアクセスするのに何らかのクライアントが必要です
  • psql や Azure Data Studio など

PostgreSQL をコンテナ実行

以下の内容で docker-compose.yml を作成する

docker-compose.yml

version: '3'
services:
  postgres:
    image: postgres:latest
    restart: always
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: admin
    volumes:
      - ./postgres:/var/lib/postgresql/data

docker-compose up -d  # コンテナを起動

終了するときは、docker-compose stop。

(Optional) Python 仮想環境の作成

python3 -m venv .venv
source .venv/bin/activate

仮想環境を終了する場合は、deactivate コマンドを実行する。

テストデータの作成

dbt で扱うデータを PostgreSQL 上に作成する。psql や Azure Data Studio で PostgreSQL に接続しておくこと。ユーザとパスワードは docker-compose.yml に記載のもので OK。

まずは DB を作成する。今回は dbt_sample という名前。SQL クライアントから以下のクエリを実行する。

CREATE DATABASE dbt_sample;

実際のテストデータを作成する。以下のクエリを実行する。
dbt_sample データベースに raw スキーマが作成され、employees と jobs テーブルが作成される。

ingest_data.sql

CREATE SCHEMA IF NOT EXISTS raw;

CREATE TABLE IF NOT EXISTS "dbt_sample"."raw"."employees" (
	"employee_id" varchar(256),
	"first_name" varchar(256),
	"last_name" varchar(256),
	"email" varchar(256),
	"job_id" varchar(256),
	"loaded_at" timestamp
);

CREATE TABLE IF NOT EXISTS "dbt_sample"."raw"."jobs" (
	"job_id" varchar(256),
	"job_title" varchar(256),
	"min_salary" INTEGER,
	"max_salary" INTEGER,
	"loaded_at" timestamp
);

INSERT INTO "dbt_sample"."raw"."employees"
VALUES
	('101','taro','yamada','[e-mail]','11','2022-03-16'),
	('102','ziro','sato','[e-mail]','11','2022-03-16')
;

INSERT INTO "dbt_sample"."raw"."jobs"
VALUES
    ('11','datascientist',6000000,12000000,'2022-03-16'),
	('12','dataengineer',5000000,10000000,'2022-03-16')
;

dbt プロジェクトを構成する

dbt_project.yml という名前で dbt プロジェクトファイルを作成する。

各プロパティの意味はこちら: https://docs.getdbt.com/reference/dbt_project.yml

dbt_project.yml

name: 'dbt_sample'
config-version: 2
version: '1.0.0'

profile: 'dbt_sample_dw'

model-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"
clean-targets: [target, dbt_packages]

models:
  dbt_sample:
    materialized_dwh:
      +materialized: table

PostgreSQL への接続用プロファイルを作成する。~/.dbt/profiles.yml に置いても良いが、プロジェクトのディレクトリ内でも可 (今回は dbt_project.yml と同じ階層)。

profiles.yml

dbt_sample_dw:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      user: admin
      password: admin
      port: 5432
      dbname: dbt_sample
      schema: public
      threads: 1
      keepalives_idle: 0 
      connect_timeout: 10

今まで作業していたディレクトリで、以下コマンドを実行して必要なディレクトリを構成する。上記 dbt_project.yml 内の記載と合わせる必要がある。

mkdir models analysis tests seeds macros snapshots targeｔ

ディレクトリ構成は以下のような形。

dbt_samples
├── analysis
├── dbt_project.yml
├── docker-compose.yml
├── macros
├── models
├── profiles.yml
├── seeds
├── snapshots
├── target
└── tests

実行環境の準備

Python の依存するモジュール類をインストールする。

requirements.txt

dbt-postgres==1.7.10

pip install -r requirements.txt

dbt でデータ変換を行う

models の下に materialized_dwh というディレクトリをさらに作成し、その中に employee_names.sql という名前で以下のクエリを作成する。
名字と名前を結合して、フルネームにする単純なもの。

models/materialized_dwh/employee_names.sql

select
    "employee_id",
    concat("first_name", ' ', "last_name") as full_name
from
    "dbt_sample"."raw"."employees"

また、SQL ファイルとモデル定義 (.yml) が必要なので、以下の内容で SQL と同じところに作成する。

modelｓ/materialized_dwh/employee_names.yml

version: 2

models:
  - name: employee_names
    columns:
      - name: employee_id
        tests:
        - unique
        - not_null
        - accepted_values:
            values: ['101', '102']
      - name: full_name
        tests:
        - not_null
        - accepted_values:
            values: ['taro yamada', 'ziro sato']

以下コマンドで dbt を実行。models 内の SQL ファイルが実行され、ファイル名と同じテーブルが public スキーマに作成されているはず！

dbt run

テストを行う

テスト用の SQL を以下の内容で、tests/ に作成する。期待値は、このクエリによって０件のレコードがヒットすること。今回のケースだと、employee_id に NULL の値がないことをテストする

tests/employee_names_test.sql

SELECT employee_id, full_name
FROM "dbt_sample"."public"."employee_names"
WHERE employee_id is null

以下のコマンドを実行すると、テストが行われる。
上記のクエリと、employee_names.yml で tests で定義した内容のチェックが行われる。

dbt test

ドキュメント

dbt では作成したモデルや事前に定義されているモデルをドキュメントとして確認することができる。各テーブルの定義 (カラム名や型など) やそのテーブルがどのテーブルから作成されているかのリネージュなどを確認できる。
以下コマンドを実行すると、target/catalog.json にモデルの情報が出力され、その内容に基づいたカタログがブラウザ上に表示される。

dbt docs generate
dbt docs serve

ということで

ざっくりですが、dbt の基本的な使い方をまとめてみました。ローカルで実行できるので気軽に試すことができます。
dbt はコード (.yml) でテーブル定義などを管理できるのが非常に便利です。スプレッドシートなどで管理することが多いと思いますが、その方法よりだいぶスマートで GitHub などで管理すればいつ誰が変更したかも追跡しやすくなります。
dbt test によって、品質を担保することもできますし、実際のデータとのずれが起きたときも早く気づくことができるのではと思います (GUI ベースで ETL パイプラインを構築する際はそのあたりが非常に面倒…)。
欠点になるのはインフラなどを自前で用意して管理する必要がある点でしょうか。ただ、それもマネージドなクラウドサービスを使えばデプロイするだけなので、それほど気にする必要はないかもしれません。
今後、まずは dbt を利用候補に挙げていきたいと思います！

以上です。