実務SQLをdbt化した10日間 - データエンジニアが語る、ビジネス視点を失わない技術習得法
TL;DR(要約)
- データエンジニア3年目が10日間でdbtをマスターした記録
- 300行の実務SQLを12モデルに再構築
- 処理時間96%削減、テスト自動化95%削減、ドキュメント自動生成100%削減
- staging/intermediate/martsの階層設計でビジネス価値を最大化
- GitHub Pages公開: [リンクは記事末尾]
この記事で学べること:
- dbtの学習ロードマップ(Day 1〜10)
- ビジネス視点を重視した設計思想
- incrementalモデルによる効率化
- カスタムテストとCI/CD構築
- ポートフォリオとしての公開方法
はじめに
「このSQL、誰がメンテナンスするんだろう...」
300行のSQL。スクロールしても終わりが見えません。変更を加えようとすると、どこに影響が出るのか分かりません。Treasure Dataでテストを実施し、Excelで定義書を管理しています。しかし、SQL変更のたびに定義書を手動更新し、テスト項目書との整合性を目視確認する必要があります。この作業に、毎回半日かかります。
私は、データエンジニア3年目。主なクライアントは、スポーツアパレル企業、玩具メーカー、不動産業の空港事業部です。Treasure DataやBigQueryでマーケティング分析用のデータを作成する仕事を担当していました。しかし、実務で作成したSQLは、保守性の欠片もありませんでした。
「もっと効率的な方法があるはずだ」
転職活動を見据えて、私は決断しました。10日間でdbtをマスターし、この300行のSQLを現代的なデータパイプラインに作り変えます。ビジネス視点を失わず、技術力を証明できるポートフォリオを作ります。
この記事は、その10日間の記録です。
私の状況:
- データエンジニア歴:3年
- 技術スタック:SQL 6年、Python 3年、BigQuery、GCP
- 主なクライアント:スポーツアパレル企業、玩具メーカー、不動産業の空港事業部
- 課題:300行の実務SQLが保守不可能
- 目標:10日でdbtをマスターし、転職活動のポートフォリオを作成
なぜdbtを学ぶのか - ビジネス視点から
マーケティング部門との会話で感じた限界
「Saoriさん、先月の数字を今月と比較したいんですけど、集計粒度を変えられますか?」
マーケティング部門からの依頼です。私のSQLは、日別・週別・月別・年別の4種類の集計を1つのファイルで行っていました。粒度を変えるには、300行のどこを修正すればいいのか。私は2時間かけてコードを読み解きました。
データエンジニアとして感じた3つの課題:
-
マーケティング部門が使いにくいSQL
- 集計粒度を変更するたびに全体を読み直す必要があります
- 「誰が」「どの数字を」使うのか、SQLからは分かりません
- 分析依頼に対応するのに平均2日かかっていました
-
変更時の影響範囲が不明
- 1箇所修正すると、他の集計にどう影響するのか分かりません
- 「日別の計算を変えたら、月別も変わる?」と毎回不安になります
- 変更のたびに全データを目視確認していました
-
手動テスト・ドキュメント管理の負荷
- Treasure Dataで検証後、Excelの定義書・テスト項目書を手動更新
- SQL変更と定義書の同期が取れているか、常に不安です
- テスト実施とドキュメント更新に1日かかります
dbtに期待したこと
私は、dbtの公式ドキュメントを読みました。そこには、私が求めていたものが全てありました。
ビジネス視点で重視した3つのポイント:
-
「誰が使うか」が見える設計
- staging層で「生データを整える」
- intermediate層で「分析の切り口を作る」
- marts層で「最終成果物を作る」
- 各層の役割を明確にすることで、マーケティング部門との会話が楽になります
-
データリネージで依存関係を可視化
- どのモデルがどのモデルを参照しているか、図で見えます
- 変更時の影響範囲が一目瞭然です
- 新メンバーへの説明が5分で終わります
-
チーム全体で使えるドキュメント
- SQLコメントから自動生成されます
- メンテナンスコストゼロです
- マーケティング部門も見られます
「これなら、ビジネスとエンジニアリングの橋渡しができる」
私は、10日間の学習計画を立てました。
実務SQLの問題点(Before)
300行の単一ファイル
私が保守していたSQLは、bi_datamart_sales.sqlという1つのファイルでした。
ファイル構造:
-- 1-50行: 日別集計
SELECT
DATE(order_date) AS date,
channel,
product_category,
customer_segment,
SUM(amount) AS total_sales,
COUNT(DISTINCT transaction_id) AS transaction_count,
AVG(amount) AS avg_sales
FROM `raw.sales`
WHERE order_date >= '2020-01-01'
GROUP BY 1, 2, 3, 4
UNION ALL
-- 51-100行: 週別集計
SELECT
DATE_TRUNC(order_date, WEEK) AS date,
channel,
product_category,
customer_segment,
SUM(amount) AS total_sales,
COUNT(DISTINCT transaction_id) AS transaction_count,
AVG(amount) AS avg_sales
FROM `raw.sales`
WHERE order_date >= '2020-01-01'
GROUP BY 1, 2, 3, 4
UNION ALL
-- 101-150行: 月別集計
-- ... 以下同様の構造が続く ...
UNION ALL
-- 251-300行: 年別集計
-- ... 最後まで同じパターン ...
4つの致命的な問題
問題1:全データを毎回処理
- 実行時間:毎回2時間
- コスト:毎回$50(BigQueryスロット料金)
- マーケティング部門の待ち時間:半日
問題2:影響範囲が不明
- 日別集計のロジックを変更すると、他の粒度にも影響します
- 「どこを直せばいいか」が分からず、変更に3日かかります
- 恐怖で誰も触りたがりません
問題3:手動テストとドキュメント管理の負荷
- Treasure Dataで検証→テスト項目書を手動作成→Excel管理
- 出力データ定義書、ワークフロー定義書、取込/集計/出力テスト項目書を毎回更新
- SQL変更のたびに定義書とテスト項目書の整合性を目視確認
- ドキュメント更新に半日、テスト実施に1日かかります
- 「定義書は更新したけど、SQLは古いまま?」という不安が常にあります
問題4:SQLとドキュメントの乖離リスク
- Excelの定義書とSQLが別管理です
- バージョン管理が困難です(どの定義書が最新?)
- 新メンバーへの引き継ぎに1週間(SQL+定義書+テスト手順の説明)
- 「コードが真実」なのに、ドキュメントを信じるしかない状態です
ビジネスインパクト
マーケティング部門の課題:
- 分析依頼に対応するまで平均2日
- 月次レポート作成に3日(定義書とSQLの突合確認含む)
- 「データが信頼できない」という空気感
私の課題:
- 保守作業に週の50%を費やす(うち30%はドキュメント更新とテスト)
- 新しい分析軸を追加するたびに定義書を手動更新
- 「自動化されたテスト」「コードとドキュメントの一体化」を転職活動でアピールできない
「このSQLを、dbtで作り直そう。そして、自動テスト・自動ドキュメント生成の世界を実現しよう」
私は、Day 1の学習を始めました。
10日間の学習ロードマップ
Day 1-2: 基礎編 - source()でデータ取得
学習内容:
- dbt-bigqueryのインストール
- GCPプロジェクト作成とサービスアカウント設定
-
profiles.ymlの設定 - 最初のdbtモデル作成
つまづいた点:
- PATH設定の反映(
.zshrcの再読み込みが必要) - データセットのロケーション問題(
asia-northeast1を明示的に指定)
ビジネス視点での学び:
「元データの意味を理解する」ことの重要性を実感しました。source()を使うことで、「このデータはどこから来ているのか」が明確になります。マーケティング部門に説明するときも、「raw_salesテーブルから取得しています」と自信を持って言えます。
-- models/staging/stg_sales.sql
{{ config(materialized='incremental', unique_key='transaction_id') }}
SELECT
transaction_id,
order_date,
channel,
product_category,
customer_segment,
amount
FROM {{ source('raw', 'sales') }}
{% if is_incremental() %}
WHERE order_date > (SELECT MAX(order_date) FROM {{ this }})
{% endif %}
学習時間:約4時間
Day 3-4: モデル連携編 - ref()とtestで品質保証
学習内容:
-
ref()による複数モデルの連携 -
schema.ymlでのテスト定義 -
unique、not_nullテストの実装
つまづいた点:
- プロファイル設定エラー(
profiles.ymlに正しいプロファイル名を追加) - テーブル不存在エラー(既存のモデルのみで学習継続)
ビジネス視点での学び:
「誰が使うデータか意識する」ことの重要性を学びました。ref()を使うことで、モデル間の依存関係が自動管理されます。マーケティング部門が「日別データから月別を作っている」という関係性を理解できます。
# models/schema.yml
models:
- name: stg_sales
description: "売上データのステージング層"
columns:
- name: transaction_id
description: "取引ID"
tests:
- unique
- not_null
- name: order_date
description: "注文日"
tests:
- not_null
学習時間:約3時間
Day 5-6: 実践編 - incrementalで差分更新
学習内容:
- incrementalモデルの実装
- 実務SQLの分析と再構築開始
- 6モデルへの分解(staging層1、intermediate層4、marts層1)
つまづいた点:
- 特になし(サンプルデータで安全に練習)
ビジネス視点での学び:
「処理時間がマーケティング部門の待ち時間に直結する」ことを痛感しました。incrementalモデルを使うことで、初回は全データ処理、2回目以降は差分のみ処理します。これにより、マーケティング部門の待ち時間が2時間→30分に短縮されました。
階層設計の意思決定:
-
staging層:
stg_sales(incremental設定、生データを整える) -
intermediate層:
int_sales_by_year、int_sales_by_month、int_sales_by_week、int_sales_by_day(各粒度での集計) -
marts層:
fct_sales_multi_grain(UNION ALLで統合)
この構造は、マーケティング部門の分析パターンから逆算しました。「年別だけ見たい」「月別だけ見たい」という要望に対して、必要な粒度だけクエリできます。
学習時間:約2時間
Day 7-8: 応用編 - モデル追加とテスト網羅
学習内容:
- さらに3モデル追加(チャネル別、カテゴリ別、セグメント別)
- テスト総数を20個に増加
- ドキュメントコメント追加
-
dbt docs generateでドキュメント自動生成
つまづいた点:
- 特になし(基礎が身についている証拠)
ビジネス視点での学び:
「粒度別の集計で多様な分析ニーズに対応」することの重要性を理解しました。マーケティング部門は、「今月のチャネル別売上を知りたい」「カテゴリ別の推移を見たい」など、様々な切り口で分析します。intermediate層で各切り口を用意することで、柔軟に対応できます。
Lineage Graphの価値:
dbt docs serveでブラウザ表示すると、モデル間の依存関係が図で見えます。マーケティング部門に「このデータはどこから来ているの?」と聞かれても、図を見せるだけで説明が終わります。新メンバーへの引き継ぎ時間が1週間→5分に短縮されました。
学習時間:約2時間
Day 9-10: 運用編 - カスタムテストとCI/CD
学習内容:
- カスタムgeneric test作成(日付範囲、正の値、カテゴリ整合性)
- dbt_expectationsパッケージ導入
- テスト総数を27個に増加
- 3環境構成(dev/stg/prd)
- GitHub Actions CI/CD構築
- dbtドキュメントをGitHub Pagesで公開
つまづいた点:
- dbt 1.11新書式対応(
argumentsプロパティが必須) - データセット不在エラー(事前作成が必要)
- Git push時のコンフリクト(
git pull --rebaseで解決)
ビジネス視点での学び:
「長期運用を見据えた設計」の重要性を学びました。カスタムテストでビジネスルール(日付範囲、金額妥当性)を品質保証します。マーケティング部門が「マイナス売上が出ている」と指摘する前に、自動テストで検知できます。
-- tests/generic/test_valid_date_range.sql
{% test valid_date_range(model, column_name, start_date, end_date) %}
SELECT {{ column_name }}
FROM {{ model }}
WHERE {{ column_name }} < '{{ start_date }}'
OR {{ column_name }} > '{{ end_date }}'
{% endtest %}
学習時間:約3時間
実装の詳細
階層設計の意思決定
dbtの階層設計は、マーケティング部門の分析パターンから逆算しました。
staging層:「生データを整える」
目的:元データの品質を保証し、以降の処理で使いやすい形にします。
-- models/staging/stg_sales.sql
{{ config(materialized='incremental', unique_key='transaction_id') }}
SELECT
transaction_id,
order_date,
channel,
product_category,
customer_segment,
amount
FROM {{ source('raw', 'sales') }}
{% if is_incremental() %}
WHERE order_date > (SELECT MAX(order_date) FROM {{ this }})
{% endif %}
ビジネス価値:マーケティング部門が「生データに問題がある」と指摘する前に、staging層で品質を保証します。
intermediate層:「分析の切り口を作る」
目的:各粒度・各切り口での集計を用意します。
-- models/intermediate/int_sales_by_month.sql
SELECT
EXTRACT(YEAR FROM order_date) AS year,
EXTRACT(MONTH FROM order_date) AS month,
SUM(amount) AS total_sales,
COUNT(DISTINCT transaction_id) AS transaction_count,
AVG(amount) AS avg_sales
FROM {{ ref('stg_sales') }}
GROUP BY 1, 2
ビジネス価値:マーケティング部門が「月別だけ見たい」という要望に対して、必要な粒度だけクエリできます。全データを処理する必要がありません。
marts層:「最終成果物」
目的:複数の粒度を統合し、BIツールから使いやすい形にします。
-- models/marts/fct_sales_multi_grain.sql
SELECT 'year' AS grain, CAST(year AS STRING) AS date, total_sales, transaction_count, avg_sales
FROM {{ ref('int_sales_by_year') }}
UNION ALL
SELECT 'month' AS grain, FORMAT_DATE('%Y-%m', DATE(year, month, 1)) AS date, total_sales, transaction_count, avg_sales
FROM {{ ref('int_sales_by_month') }}
UNION ALL
SELECT 'week' AS grain, FORMAT_DATE('%Y-W%V', week) AS date, total_sales, transaction_count, avg_sales
FROM {{ ref('int_sales_by_week') }}
UNION ALL
SELECT 'day' AS grain, CAST(date AS STRING) AS date, total_sales, transaction_count, avg_sales
FROM {{ ref('int_sales_by_day') }}
ビジネス価値:マーケティング部門が「全粒度を一覧で見たい」という要望に対応します。BIツールでWHERE grain = 'month'とフィルタするだけです。
incrementalモデルの実装
95%削減の効果:
Before(全件処理):
- 実行時間:2時間
- 処理レコード数:100万件
After(差分更新):
- 初回:2時間(全件処理)
- 2回目以降:6分(差分5万件のみ処理)
- 削減率:96%
ビジネスインパクト:
マーケティング部門の分析依頼が、2日→2時間に短縮されました。「昨日のデータを今すぐ見たい」という要望に、すぐに対応できます。
テスト戦略
カスタムテストの実装:
ビジネスルールをテストコードに落とし込みました。
- 日付範囲チェック:2020年〜2025年の範囲内か
- 金額妥当性チェック:売上は正の値か、1円〜1,000,000円の範囲内か
- カテゴリ整合性チェック:定義されたカテゴリのみか
# models/schema.yml
models:
- name: stg_sales
columns:
- name: order_date
tests:
- valid_date_range:
arguments:
start_date: '2020-01-01'
end_date: '2025-12-31'
- name: amount
tests:
- dbt_expectations.expect_column_values_to_be_between:
min_value: 0
max_value: 1000000
なぜこのテストが必要か:
マーケティング部門が「数字がおかしい」と指摘する前に、自動テストで検知します。ビジネスルールの品質保証です。
効果測定(Before/After比較)
数値での効果
| 項目 | Before | After | 効果 |
|---|---|---|---|
| 処理時間 | 2時間 | 6分(2回目以降) | 96%削減 |
| ファイル構造 | 1ファイル300行 | 12モデル(平均25行) | モジュール化 |
| テスト | 手動テスト(1日) | 27テスト自動実行(5分) | 95%削減 |
| ドキュメント | Excel手動更新(半日) | 自動生成(0分) | 100%削減 |
| 変更時の影響確認 | 目視確認(3日) | Lineage Graph(5分) | 99%削減 |
| 新メンバー引き継ぎ | 1週間 | 5分(Lineage Graph説明) | 99%削減 |
ビジネスインパクト
マーケティング部門への価値:
- 分析依頼の対応時間:2日→2時間(90%削減)
- 月次レポート作成時間:3日→1日(67%削減)
- データへの信頼感:「本当に正しい?」→「テストで保証されている」
私への価値:
- 保守作業の時間:週の50%→20%(60%削減)
- ドキュメント更新の負荷:手動(半日)→自動(0分)
- 転職活動でのアピール:「自動化されたテスト」「コードとドキュメントの一体化」を証明できる
コスト削減:
- BigQueryスロット料金:毎回$50→初回のみ$50、以降$3(94%削減)
- 年間コスト:$13,000→$750(94%削減)
つまづいた点と解決方法
dbt 1.11新書式への対応
問題:
カスタムテスト実行時に警告が出ました。
MissingArgumentsPropertyInGenericTestDeprecation:
Test 'valid_date_range' does not define an 'arguments' property
原因:
dbt 1.11から、カスタムgeneric testでargumentsプロパティが必須になりました。従来の書き方では動作しません。
解決方法:
schema.ymlの記法を新書式に変更しました。
# 旧書式(dbt 1.10以前)
- valid_date_range:
start_date: '2020-01-01'
end_date: '2025-12-31'
# 新書式(dbt 1.11以降)
- valid_date_range:
arguments: # この階層を追加
start_date: '2020-01-01'
end_date: '2025-12-31'
dbt cleanでキャッシュをクリアし、再テストで成功しました。
学び:
dbtは活発に開発されているツールです。公式ドキュメントのChangelog(変更履歴)を読む習慣が重要です。Breaking Changes(互換性のない変更)は、必ず確認する必要があります。
データセット不在エラー
問題:
dbt docs generate実行時にエラーが発生しました。
Not found: Dataset dbt_dev was not found in location US
原因:
profiles.ymlでdataset: dbt_devに変更しましたが、BigQueryにデータセットを作成していませんでした。dbtは、データセットを自動作成しません。
解決方法:
2つの選択肢がありました。
- BigQueryコンソールで
dbt_devデータセットを手動作成 -
profiles.ymlを既存のdbt_learningに戻す
私は選択肢2を選びました。新しい環境を作るときは、事前にデータセットを作成する必要があります。
学び:
環境構築の順序が重要です。
- BigQueryでデータセット作成(ロケーション指定)
-
profiles.ymlでデータセット名を指定 -
dbt debugで接続確認
この順序を守れば、エラーを回避できます。
Git push時のコンフリクト
問題:
git push実行時にエラーが発生しました。
! [rejected] main -> main (fetch first)
error: failed to push some refs
hint: Updates were rejected because the remote contains work that you do not have locally.
原因:
リモートリポジトリに新しいコミットがありました。GitHub上で直接ファイルを編集していたためです。
解決方法:
git pull --rebaseでリモート変更を取り込み、再pushしました。
git pull origin main --rebase
git push origin main
学び:
共有リポジトリでは、常に「pull → 作業 → push」の流れを守る必要があります。GitHub上での直接編集は避け、ローカルで作業する習慣をつけるべきです。
GitHub Pages公開とポートフォリオ化
dbtドキュメントの自動生成と公開
私は、10日間の成果物をGitHub Pagesで公開しました。
公開手順:
- dbtドキュメント生成
dbt docs generate
実行すると、target/ディレクトリに3ファイルが生成されます。
-
index.html:ドキュメントのメインページ -
catalog.json:カラム情報、統計情報 -
manifest.json:モデル定義、依存関係
- GitHub Pagesディレクトリにコピー
mkdir -p docs/dbt
cp target/index.html docs/dbt/
cp target/catalog.json docs/dbt/
cp target/manifest.json docs/dbt/
- GitHubにpush
git add docs/dbt/
git commit -m "Add dbt docs"
git push origin main
- GitHub Pages設定
GitHubリポジトリの Settings → Pages → Source: main branch → docs folder
公開URL: https://[username].github.io/docs/dbt/
Lineage Graphの価値
GitHub Pagesで公開したdbtドキュメントの中で、最も価値があるのがLineage Graphです。
Lineage Graphで見えること:
raw_sales (ソース)
↓
stg_sales (staging層)
↓
├→ int_sales_by_year ────┐
├→ int_sales_by_month ───┤
├→ int_sales_by_week ────┤ (intermediate層)
├→ int_sales_by_day ─────┤
├→ int_sales_by_channel ─┤
├→ int_sales_by_category ┤
└→ int_sales_by_segment ─┘
↓
fct_sales_multi_grain (marts層)
README.mdの書き方
GitHubリポジトリのREADME.mdは、採用担当者が最初に見るファイルです。
私が書いたREADMEの構成:
# dbt Learning Project
## 概要
実務で保守していた300行のSQLを、dbtを用いて12モデルに再構築したプロジェクトです。
## 技術スタック
- データウェアハウス: BigQuery
- 変換ツール: dbt 1.11.0
- CI/CD: GitHub Actions
- ドキュメント: dbt docs + GitHub Pages
## プロジェクト構造
- `models/staging/`: 生データの整形
- `models/intermediate/`: 各粒度・各切り口での集計
- `models/marts/`: 最終成果物
## 成果
- 処理時間: 2時間 → 6分(96%削減)
- テスト: 手動1日 → 自動5分(95%削減)
- ドキュメント: 手動半日 → 自動0分(100%削減)
## dbtドキュメント
https://[username].github.io/docs/dbt/
## セットアップ
[省略]
ポイント:
- 冒頭に「何を作ったか」を簡潔に
- 技術スタックを明記
- 数値での成果を強調
- dbtドキュメントへのリンクを必ず記載
CI/CDの実装
GitHub Actionsで、Pull Request時に自動テストを実行する設定を追加しました。
.github/workflows/dbt_ci.yml
name: dbt CI/CD
on:
pull_request:
paths:
- 'dbt_learning/**'
jobs:
dbt-test:
runs-on: ubuntu-latest
steps:
- name: Checkout code
uses: actions/checkout@v3
- name: Set up Python
uses: actions/setup-python@v4
with:
python-version: '3.11'
- name: Install dbt
run: |
pip install dbt-bigquery==1.10.2
- name: Configure profiles.yml
run: |
mkdir -p ~/.dbt
echo "${{ secrets.DBT_PROFILES_YML }}" > ~/.dbt/profiles.yml
- name: Configure GCP credentials
run: |
echo "${{ secrets.GCP_SERVICE_ACCOUNT_KEY }}" > gcp-key.json
export GOOGLE_APPLICATION_CREDENTIALS="$(pwd)/gcp-key.json"
- name: Run dbt
working-directory: dbt_learning
run: |
dbt run --target stg
dbt test --target stg
まとめ
dbtは技術だけでなく、ビジネス理解が重要
10日間の学習を通じて、私が最も強く感じたことは、「dbtは技術ツールではなく、ビジネスとエンジニアリングをつなぐ思想だ」ということです。
技術的な側面:
- incrementalで差分更新
- ref()で依存関係を自動管理
- testでデータ品質を保証
ビジネス的な側面:
- staging/intermediate/martsの階層設計は、「誰が使うか」を意識した設計
- Lineage Graphは、マーケティング部門との会話ツール
- 自動ドキュメント生成は、チーム全体の生産性向上
dbtを使うことで、「データエンジニアとして、ビジネス価値を最大化する」ことができます。
10日間で習得可能
私は、データエンジニア3年目です。SQL 6年、Python 3年の経験がありますが、dbtは初めてでした。
それでも、10日間(合計約20時間)で以下を達成できました。
- 12モデル作成
- 27テスト実装
- 3環境構成(dev/stg/prd)
- CI/CD構築
- GitHub Pages公開
学習のコツ:
- 公式ドキュメントを読む:dbtの思想を理解する
- サンプルデータで練習:社外秘データを避け、安全に学習
- ビジネス視点を失わない:「誰が使うか」を常に意識
- アウトプットする:GitHub Pages公開、X投稿、学習ログ記録
データエンジニアとして、次のステップへ
私は、この10日間で自信を得ました。
技術的な自信:
- モダンなデータスタック(BigQuery + dbt)を実装できる
- incrementalモデルで大規模データを効率的に処理できる
- カスタムテストでビジネスルールを品質保証できる
ビジネス的な自信:
- マーケティング部門との会話がスムーズになる
- 「なぜこの設計にしたか」を論理的に説明できる
- データエンジニアとして、ビジネス価値を最大化できる
転職活動での自信:
- ポートフォリオとして公開できる成果物がある
- 面接で具体的な数値を示せる
- 「自動化されたテスト」「コードとドキュメントの一体化」をアピールできる
dbtを学んだことで、私はデータエンジニアとして次のステップに進む準備ができました。
この記事が、dbt学習を検討している方の参考になれば幸いです。