実務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日かかっていました
変更時の影響範囲が不明
手動テスト・ドキュメント管理の負荷
- Treasure Dataで検証後、Excelの定義書・テスト項目書を手動更新
- SQL変更と定義書の同期が取れているか、常に不安です
- テスト実施とドキュメント更新に1日かかります

dbtに期待したこと

私は、dbtの公式ドキュメントを読みました。そこには、私が求めていたものが全てありました。

ビジネス視点で重視した3つのポイント：

「誰が使うか」が見える設計
- staging層で「生データを整える」
- intermediate層で「分析の切り口を作る」
- marts層で「最終成果物を作る」
- 各層の役割を明確にすることで、マーケティング部門との会話が楽になります
データリネージで依存関係を可視化
チーム全体で使えるドキュメント
- 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学習を検討している方の参考になれば幸いです。

公開成果物

dbtドキュメント: https://sato1046.github.io/docs/dbt/
GitHubリポジトリ: https://github.com/sato1046/sato1046.github.io/tree/main/dbt_learning