はじめに
Snowflakeでデータ変換のSQLが増えてきた。
最初はSnowflakeのTASKで定期実行していたが、SQLが増えるにつれて「このSQLがどのテーブルに依存しているか」「変更したらどこに影響するか」が追いにくくなってきた。そこで調べたのがdbt(data build tool)。
「SQLをコードとして管理する」という発想で、GitでSQLをバージョン管理して、依存関係を自動で解決して、テストも書ける。データエンジニアリングの世界ではデファクトスタンダードになりつつある。
dbtとは何か
従来のデータ変換:
SQL → SnowflakeのTASKやストアドプロシージャ
→ バージョン管理しにくい
→ 依存関係が不明確
→ テストを書く仕組みがない
dbt:
SQL → dbtモデル → Snowflakeのテーブル/ビュー
→ Gitでバージョン管理
→ 依存関係を自動で解決(DAGを生成)
→ スキーマテストが書ける
→ ドキュメントが自動生成される
dbtはSELECT文だけを書く。CREATE TABLE/VIEWは書かない。dbtが自動でやってくれる。
インストールとセットアップ
pip install dbt-snowflake
# プロジェクトを初期化
dbt init my_project
対話形式で設定を入力:
Which database would you like to use? → snowflake
account: xxxx.ap-northeast-1.aws
user: myuser
password: mypassword
role: my_role
warehouse: my_warehouse
database: my_database
schema: dbt_dev
threads: 4
生成されるディレクトリ構造:
my_project/
├── dbt_project.yml # プロジェクト設定
├── profiles.yml # 接続設定(~/.dbt/profiles.yml に生成される)
├── models/ # SQLモデルを置く場所
│ └── example/
├── tests/ # カスタムテスト
├── macros/ # 再利用可能なSQL
├── seeds/ # CSVデータ
└── snapshots/ # スナップショット
profiles.yml — 接続設定
# ~/.dbt/profiles.yml
my_project:
target: dev
outputs:
dev:
type: snowflake
account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
user: "{{ env_var('SNOWFLAKE_USER') }}"
password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
role: my_role
warehouse: my_warehouse
database: my_database
schema: dbt_dev # 開発用スキーマ
threads: 4
prod:
type: snowflake
account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
user: "{{ env_var('SNOWFLAKE_USER') }}"
password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
role: my_role
warehouse: my_warehouse
database: my_database
schema: dbt_prod # 本番用スキーマ
threads: 8
env_var()で環境変数を読める。接続情報をYAMLに直書きしなくて済む。
dbt_project.yml — プロジェクト設定
# dbt_project.yml
name: my_project
version: '1.0.0'
profile: my_project
model-paths: ["models"]
seed-paths: ["seeds"]
test-paths: ["tests"]
macro-paths: ["macros"]
models:
my_project:
staging: # staging層の設定
+materialized: view # デフォルトはビュー
+schema: staging
marts: # marts層の設定
+materialized: table # テーブルとして作成
+schema: marts
モデルの書き方
基本形
dbtのモデルはSELECT文だけを書くSQLファイル。
-- models/staging/stg_orders.sql
SELECT
order_id,
user_id,
product_id,
amount,
status,
created_at
FROM {{ source('raw', 'orders') }} -- rawスキーマのordersテーブル
WHERE status != 'cancelled'
-- models/staging/stg_users.sql
SELECT
user_id,
name,
email,
region,
created_at
FROM {{ source('raw', 'users') }}
-- models/marts/mart_order_summary.sql
WITH orders AS (
SELECT * FROM {{ ref('stg_orders') }} -- stg_ordersモデルを参照
),
users AS (
SELECT * FROM {{ ref('stg_users') }} -- stg_usersモデルを参照
)
SELECT
u.region,
u.name AS user_name,
COUNT(o.order_id) AS order_count,
SUM(o.amount) AS total_amount,
AVG(o.amount) AS avg_amount
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
GROUP BY u.region, u.name
{{ ref('モデル名') }}が重要。モデル間の依存関係をdbtが解決する。stg_usersより先にstg_ordersを作るといった実行順序をdbtが自動で決定する。
sourceの定義
# models/staging/sources.yml
version: 2
sources:
- name: raw
database: my_database
schema: raw_data
tables:
- name: orders
description: "注文データ(外部から取り込んだ生データ)"
columns:
- name: order_id
description: "注文ID"
tests:
- unique
- not_null
- name: amount
description: "注文金額"
tests:
- not_null
- name: users
description: "ユーザーデータ"
columns:
- name: user_id
tests:
- unique
- not_null
source()で参照するテーブルをここで定義する。テストも一緒に書ける。
マテリアライゼーション
dbtのモデルをSnowflakeにどのような形で作るかの設定。
-- viewとして作成(デフォルト)
{{ config(materialized='view') }}
SELECT ...
-- tableとして作成
{{ config(materialized='table') }}
SELECT ...
-- incrementalモデル(差分のみ追加)
{{ config(
materialized='incremental',
unique_key='order_id',
on_schema_change='sync_all_columns'
) }}
SELECT
order_id,
amount,
created_at
FROM {{ source('raw', 'orders') }}
{% if is_incremental() %}
-- 増分実行時は新しいデータだけ取得
WHERE created_at > (SELECT MAX(created_at) FROM {{ this }})
{% endif %}
マテリアライゼーションの使い分け
| 種類 | 特徴 | 向いているケース |
|---|---|---|
view |
毎回SQLを実行 | 軽い変換・頻繁に変わるデータ |
table |
結果を物理テーブルに保存 | 重い集計・高速なアクセスが必要 |
incremental |
差分だけ追加 | 大量データの追記処理 |
ephemeral |
CTEとして展開(テーブルを作らない) | 中間処理・使い捨ての変換 |
テストの書き方
dbtにはビルトインのテストと、カスタムテストがある。
ビルトインテスト
# models/marts/schema.yml
version: 2
models:
- name: mart_order_summary
description: "注文サマリー"
columns:
- name: user_name
tests:
- not_null # NULLがないこと
- name: region
tests:
- not_null
- accepted_values: # 特定の値のみ許可
values: ['東京', '大阪', '福岡', '名古屋']
- name: total_amount
tests:
- not_null
カスタムテスト
-- tests/assert_positive_amounts.sql
-- total_amountが負にならないことをテスト
SELECT
user_name,
total_amount
FROM {{ ref('mart_order_summary') }}
WHERE total_amount < 0
SELECTの結果が0件ならテスト成功、1件以上あればテスト失敗。シンプルでわかりやすい。
dbtのコマンド
# モデルを実行(全て)
dbt run
# 特定のモデルだけ実行
dbt run --select stg_orders
# 依存するモデルも含めて実行(+は上流、+は下流)
dbt run --select +mart_order_summary # mart_order_summaryとその上流全て
dbt run --select mart_order_summary+ # mart_order_summaryとその下流全て
# テストを実行
dbt test
# 特定のモデルのテストだけ
dbt test --select stg_orders
# ソースのデータ鮮度チェック
dbt source freshness
# ドキュメントを生成してブラウザで確認
dbt docs generate
dbt docs serve
# コンパイルのみ(SQLを生成して確認)
dbt compile
# 全部まとめて実行
dbt build # run + test + snapshot + seed をまとめて実行
ディレクトリ構成のベストプラクティス
models/
├── staging/ # 生データをそのまま整形する層
│ ├── sources.yml # sourceの定義
│ ├── stg_orders.sql
│ └── stg_users.sql
├── intermediate/ # 複数のstagingを組み合わせる中間層
│ └── int_orders_with_users.sql
└── marts/ # ビジネスロジックを含む最終的なデータマート
├── schema.yml
├── mart_order_summary.sql
└── mart_daily_sales.sql
staging: 生データの型変換・リネーム・フィルタのみ
intermediate: 複数テーブルのJOIN・中間集計
marts: ビジネス指標の集計・最終アウトプット
この3層構造を意識すると「どこに何を書くか」が明確になって管理しやすい。
マクロ — SQLの共通処理を関数化する
-- macros/cents_to_jpy.sql
{% macro cents_to_jpy(column_name) %}
ROUND({{ column_name }} / 100, 0)
{% endmacro %}
-- モデルで使う
SELECT
order_id,
{{ cents_to_jpy('amount_cents') }} AS amount_jpy
FROM {{ source('raw', 'orders') }}
Jinjaテンプレートで書くPythonの関数のようなもの。共通のSQLロジックを1箇所にまとめられる。
よく使うビルトインマクロ
-- 現在の日時
{{ run_started_at }}
-- 環境変数
{{ env_var('SNOWFLAKE_DATABASE') }}
-- データ型変換
{{ dbt.type_string() }}
{{ dbt.type_int() }}
{{ dbt.type_timestamp() }}
-- 文字列の安全な処理
{{ dbt.safe_cast('amount', api.Column.translate_type('integer')) }}
Seedsを使ったマスタデータ管理
# seeds/regions.csv
region_code,region_name,area
TK,東京,関東
OS,大阪,近畿
FK,福岡,九州
# seedをSnowflakeにロード
dbt seed
-- モデルからseedを参照
SELECT
o.*,
r.area
FROM {{ ref('stg_orders') }} o
LEFT JOIN {{ ref('regions') }} r ON o.region_code = r.region_code
マスタデータをCSVでGit管理してdbtでロードする。Snowflakeに手動で入れる必要がなくなる。
Pythonから実行する
dbtはCLIで実行するのが基本だが、Pythonから呼び出すこともできる。
# run_dbt.py
import subprocess
import os
from datetime import datetime
def run_dbt_model(model_name: str, target: str = "dev") -> bool:
"""dbtモデルを実行する"""
result = subprocess.run(
["dbt", "run", "--select", model_name, "--target", target],
capture_output=True,
text=True,
cwd="/path/to/my_project",
)
if result.returncode == 0:
print(f"dbt run 成功: {model_name}")
return True
else:
print(f"dbt run 失敗: {model_name}")
print(result.stderr)
return False
def run_dbt_test(model_name: str) -> bool:
"""dbtテストを実行する"""
result = subprocess.run(
["dbt", "test", "--select", model_name],
capture_output=True,
text=True,
cwd="/path/to/my_project",
)
return result.returncode == 0
def run_full_pipeline(target: str = "dev"):
"""ビルド → テストを実行"""
print(f"=== dbt pipeline開始: {datetime.now()} ===")
result = subprocess.run(
["dbt", "build", "--target", target],
capture_output=True,
text=True,
cwd="/path/to/my_project",
)
if result.returncode == 0:
print("パイプライン成功")
else:
print(f"パイプライン失敗:\n{result.stderr}")
raise RuntimeError("dbt build failed")
CI/CDへの組み込み
# .github/workflows/dbt.yml
name: dbt CI
on:
push:
branches: [main]
pull_request:
branches: [main]
jobs:
dbt-run:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- name: Set up Python
uses: actions/setup-python@v4
with:
python-version: '3.12'
- name: Install dbt
run: pip install dbt-snowflake
- name: Run dbt
env:
SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
SNOWFLAKE_USER: ${{ secrets.SNOWFLAKE_USER }}
SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}
run: |
cd my_project
dbt deps
dbt build --target prod
SnowflakeのTASKとdbtの使い分け
| 観点 | Snowflake TASK | dbt |
|---|---|---|
| バージョン管理 | しにくい | Gitで管理 |
| 依存関係 | 手動でAFTER句を管理 | 自動でDAGを生成 |
| テスト | なし | ビルトインでテストが書ける |
| ドキュメント | なし | 自動生成 |
| 差分処理 | STREAM連携 | incrementalモデル |
| 学習コスト | 低い | 中くらい |
| 外部サービス依存 | なし | dbt Cloud or CLI |
複雑な変換ロジックや複数テーブルの依存関係がある場合はdbtが向いている。シンプルな定期実行はTASKで十分。
まとめ
- dbtはSELECT文だけ書けばいい。CREATE TABLE/VIEWはdbtが自動でやる
-
{{ ref() }}でモデル間の依存関係を定義するとDAGが自動生成される - staging → intermediate → martsの3層構造で管理する
- ビルトインテストでnot_null・unique・accepted_valuesが書ける
-
dbt buildでrun+test+seed+snapshotをまとめて実行できる - マクロで共通SQLロジックを関数化できる
「SQLもコードとして管理する」という発想がなかったが、dbtを使い始めてからデータパイプラインの見通しが格段によくなった。特にlineage(依存関係の可視化)は「このSQLを変えたらどこに影響するか」がわかってリファクタリングへの心理的ハードルが下がった。