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を使用して分析コードをモジュール化し、集約することで、通常ソフトウェアエンジニアリングのワークフローで見られるようなデータチーム向けのガードレールを提供できます。データモデルを共同で作成し、バージョン管理し、クエリをテストして文書化し、安全に本番環境に展開する前に、監視と可視性を備えています。


https://docs.getdbt.com/docs/introduction

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

# 試してみる

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

https://zenn.dev/foursue/books/31456a86de5bb4

## 前提

* Python を使える環境が必要です
* Docker を使います
* PostgreSQL の DB にアクセスするのに何らかのクライアントが必要です
    * psql や [Azure Data Studio](https://azure.microsoft.com/ja-jp/products/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 仮想環境の作成

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

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

## テストデータの作成

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

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

```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','yamada@example.com','11','2022-03-16'),
	('102','ziro','sato','satou@example.com','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` 内の記載と合わせる必要がある。

```sh
mkdir models analysis tests seeds macros snapshots targeｔ
```

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

```text
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
```

```sh
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` スキーマに作成されているはず！

```sh
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` で定義した内容のチェックが行われる。

```sh
dbt test
```

## ドキュメント

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

```sh
dbt docs generate
dbt docs serve
```

# ということで

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


以上です。