dbt(data build tool)

概要

• dbt はデータウェアハウス上の変換レイヤーを SQL + Jinja テンプレートで管理するツール
• ELT（Extract → Load → Transform）の Transform 部分を担う
• モデルごとに .sql ファイルを作成し、ref() 関数で依存関係を宣言すると DAG（有向非巡回グラフ）を自動生成する
• BigQuery, Snowflake, Redshift, DuckDB などの主要なデータウェアハウスに対応
• テスト・ドキュメント生成・スナップショット（SCD）などの機能が標準で含まれる
• dbt Core（OSS）を CLI から利用する

メダリオンアーキテクチャとの対応

dbt のレイヤー設計はメダリオンアーキテクチャ（Bronze / Silver / Gold）と対応させると整理しやすい

• staging はソース 1 テーブルに対し 1 モデルを作るのが原則
• intermediate は staging 同士を結合したり、共通の加工ロジックをまとめる中間層
• marts は fact / dim テーブルや集計テーブルとして最終成果物を提供する

セマンティックレイヤー

一般的な意味

• セマンティックレイヤーは、DWH のテーブル・カラムなどの物理構造と、ダッシュボード・ノートブック・アプリの利用者の間に置く意味の層であり、メトリクス（売上・コンバージョン率など）・ディメンション（日付・地域・商品カテゴリなど）・エンティティ間の結合関係をビジネス語彙として一度だけ定義し、下流ツールへ同じ定義を再利用させるための抽象である
• メトリクスレイヤーは「指標の計算式・集計ルール・粒度」を中心に据えた呼び方で、セマンティックレイヤーはそれに加えてラベル・説明・権限・結合パスなども含めて語られることが多く、用語は資料によって揺れる

dbt Semantic Layer と MetricFlow

• dbt では、既存モデル（多くは marts / ファクト・ディメンション）の上にメトリクスとセマンティックモデルを YAML 等で定義し、Looker や Tableau など BI ごとにバラバラだった指標定義をモデリング層へ寄せる仕組みを dbt Semantic Layer と呼ぶ
• メトリクス名・集計・ディメンションでのスライス・必要な結合を解釈して SQL を組み立てるエンジンが MetricFlow であり、定義の変更が下流のクエリや統合先へ一貫して伝わることを狙う
• 指標の定義を BI からモデリング層へ移すことで、部署やツールが違っても同じ「売上」などを共有しやすくなり、セマンティックレイヤー側でアクセス制御を設計できる点も強調される

本記事のメダリオンとの位置づけ

• メダリオンでいう Gold の marts は分析向けのテーブル・ビューという成果物を提供する層であり、セマンティックレイヤーはそのさらに上に、ビジネス指標の意味・集計・スライス軸をコードとして固定するレイヤーとして整理する

定義 YAML の例（概略）

• 以下はスタンドアロンの semantic_models と metrics を分けた例（書式は dbt の版で変わるため、Semantic Layer reference を参照する）

# models/semantic_models.yml
semantic_models:
  - name: transactions_semantic_model
    description: トランザクションデータのセマンティックモデル
    model: ref('fct_transactions')
    entities:
      - name: transaction_id
        type: primary
      - name: user_id
        type: foreign
    dimensions:
      - name: transaction_date
        type: time
        type_params:
          time_granularity: day
      - name: is_successful
        type: categorical
    measures:
      - name: total_revenue
        description: トランザクションの合計売上
        agg: sum
        expr: revenue_amount

# models/metrics.yml
metrics:
  - name: monthly_revenue
    description: 月間の総売上
    type: simple
    label: 月間売上高
    type_params:
      measure: total_revenue

• model は dbt モデル（例では fct_transactions）を指す
• measures の expr はファクト上の列名
• metrics の type_params.measure はセマンティックモデル側で定義した measure 名を参照する

ディレクトリ構造

my_project/
├── dbt_project.yml       # プロジェクト設定
├── profiles.yml          # 接続先設定（~/.dbt/ に置くことが多い）
├── models/
│   ├── staging/          # Bronze: ソースを型変換・リネームのみで加工
│   │   └── stg_orders.sql
│   ├── intermediate/     # Silver: staging を結合・中間集計する層
│   │   └── int_orders_with_users.sql
│   └── marts/            # Gold: ビジネスロジックを集約する最終成果物
│       └── fct_orders.sql
├── tests/                # カスタムテスト
├── macros/               # Jinja マクロ
├── snapshots/            # SCD (Slowly Changing Dimensions)
└── seeds/                # CSV を直接ロードするファイル

インストールと初期化

pip install dbt-bigquery   # アダプタに応じて変更（dbt-snowflake, dbt-duckdb 等）

dbt init my_project        # プロジェクト作成
dbt debug                  # 接続テスト

dbt_project.yml の例

name: my_project
version: "1.0.0"
profile: my_profile

models:
  my_project:
    staging:
      +materialized: view      # Bronze: view として作成
    intermediate:
      +materialized: view      # Silver: 基本は view（重い場合は table や ephemeral に変更）
    marts:
      +materialized: table     # Gold: table として作成
    +persist_docs:
      relation: true           # テーブル・ビューのコメントを DWH に反映
      columns: true            # カラムコメントを DWH に反映

モデルの書き方

staging モデル（stg_orders.sql）

-- models/staging/stg_orders.sql
WITH source AS (
    SELECT * FROM {{ source('raw', 'orders') }}
)
SELECT
    order_id,
    user_id,
    DATE(created_at) AS order_date,
    status,
    amount
FROM source

intermediate モデル（int_orders_with_users.sql）

staging モデルを結合・加工し、複数の mart モデルから再利用できる中間テーブルを作る

-- models/intermediate/int_orders_with_users.sql
WITH orders AS (
    SELECT * FROM {{ ref('stg_orders') }}
),
users AS (
    SELECT * FROM {{ ref('stg_users') }}
)
SELECT
    o.order_id,
    o.order_date,
    o.status,
    o.amount,
    u.user_name,
    u.user_segment
FROM orders AS o
LEFT JOIN users AS u USING (user_id)

• intermediate のモデルはプレフィックス int_ を付けるのが慣例
• staging をそのまま mart で結合すると mart が肥大化するため、共通の JOIN ロジックはここに切り出す
• マテリアライゼーションは view か ephemeral が多い（mart が参照する CTE として埋め込む場合は ephemeral）

mart モデル（fct_orders.sql）

intermediate を参照してビジネスロジックを追加する

-- models/marts/fct_orders.sql
WITH base AS (
    SELECT * FROM {{ ref('int_orders_with_users') }}
)
SELECT
    order_id,
    order_date,
    user_name,
    user_segment,
    amount,
    CASE
        WHEN status = 'completed' THEN amount
        ELSE 0
    END AS revenue
FROM base

• {{ source('schema', 'table') }} でソーステーブルを参照する
• {{ ref('model_name') }} で他のモデルを参照し、依存関係を自動解決する

スキーマファイル（schema.yml）

version: 2

sources:
  - name: raw
    database: my_project
    schema: raw_data
    tables:
      - name: orders

models:
  - name: stg_orders
    description: "受注データのステージングモデル"
    columns:
      - name: order_id
        description: "注文ID"
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ["pending", "completed", "cancelled"]

カラム説明の DWH への反映（persist_docs）

• schema.yml に書いた description は dbt docs にしか反映されないのがデフォルト
• persist_docs を有効にすると BigQuery や Snowflake のカラムコメントとして書き込まれる
• データカタログや BigQuery Studio 上でカラムの意味が確認できるようになる

dbt_project.yml での設定

models:
  my_project:
    +persist_docs:
      relation: true    # テーブル/ビュー自体の description をコメントとして保存
      columns: true     # カラムの description をコメントとして保存

モデル単位で個別に設定することも可能

{{
    config(
        persist_docs={"relation": true, "columns": true}
    )
}}
SELECT ...

• BigQuery では dbt run 後にテーブルのスキーマ欄でカラムの説明が確認できる
• relation: true のみでも効果があるが、カラムレベルまで反映するには columns: true も必要

主要コマンド

dbt run                        # 全モデルを実行
dbt run --select stg_orders    # 特定モデルのみ実行
dbt run --select +fct_orders   # fct_orders と上流モデルを実行
dbt run --select fct_orders+   # fct_orders と下流モデルを実行

dbt test                       # 全テストを実行
dbt test --select stg_orders   # 特定モデルのテストのみ実行

dbt docs generate              # ドキュメントを生成
dbt docs serve                 # ドキュメントをブラウザで確認

dbt seed                       # seeds/ の CSV をロードする
dbt snapshot                   # snapshots/ を実行する

dbt compile                    # SQL をコンパイルして target/ に出力（実行はしない）
dbt build                      # run + test + seed + snapshot をまとめて実行

マクロ（macros/）

再利用可能な Jinja マクロを定義できる

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name) %}
    ({{ column_name }} / 100.0)
{% endmacro %}

-- モデル内での使用
SELECT
    order_id,
    {{ cents_to_dollars('amount_cents') }} AS amount_dollars
FROM {{ ref('stg_orders') }}

スナップショット（SCD Type 2）

レコードの変更履歴を保持する Slowly Changing Dimensions に対応

-- snapshots/orders_snapshot.sql
{% snapshot orders_snapshot %}
{{
    config(
        target_schema='snapshots',
        unique_key='order_id',
        strategy='timestamp',
        updated_at='updated_at'
    )
}}
SELECT * FROM {{ source('raw', 'orders') }}
{% endsnapshot %}

BigQuery との組み合わせ

profiles.yml の設定例

my_profile:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: oauth
      project: my_gcp_project
      dataset: dbt_dev
      threads: 4
      timeout_seconds: 300

パーティション・クラスタリングの設定

{{
    config(
        materialized='table',
        partition_by={
            "field": "order_date",
            "data_type": "date",
            "granularity": "day"
        },
        cluster_by=["user_id"]
    )
}}
SELECT * FROM {{ ref('stg_orders') }}

dbt artifact

dbt はコマンド実行後に target/ ディレクトリへ JSON 形式のアーティファクトを出力する

manifest.json の活用

• dbt compile を実行すると target/manifest.json が生成される
• このファイルにはモデルの説明・カラム定義・型・テスト・依存関係・コンパイル済み SQL がすべて含まれる
• テーブル・カラムの意味・型・依存関係などが一つの JSON に集約されている
• LLM へのコンテキスト注入：manifest.json や必要なモデルだけを抜粋した JSON を LLM のプロンプトに渡すと、スキーマ知識を持った状態で自然言語からの SQL クエリ生成が可能になる
• catalog.json と組み合わせると型情報も補完でき、より精度の高いクエリ生成ができる

AI 参照用メタフィールド

schema.yml の meta ブロックに以下のフィールドを定義しておくと、LLM がモデルを安全・適切に利用するための判断材料になる

schema.yml への記述例

models:
  - name: fct_orders
    description: "受注ファクトテーブル"
    meta:
      approved_for_ai: true
      user_facing: true
      grain: "1行 = 1注文"
      business_name: "受注データ"
      join_keys: ["order_id", "user_id"]
      recommended_filters: ["order_date", "status"]
      forbidden_uses: "個人単位の与信判断には使用しないこと"
      pii: false
      owner: "data-team@example.com"

• これらは manifest.json の meta フィールドにそのまま出力されるため、LLM へのコンテキスト注入時に自動的に参照できる
• approved_for_ai: false のモデルをフィルタリングするだけで、意図しないテーブルを LLM に渡すリスクを減らせる
• forbidden_uses を明示しておくと LLM のシステムプロンプトに注意書きとして組み込める

manifest.json から特定モデルのメタデータを抽出する例

import json

with open("target/manifest.json") as f:
    manifest = json.load(f)

model = manifest["nodes"]["model.my_project.fct_orders"]
print(model["description"])
for col, meta in model["columns"].items():
    print(f"{col}: {meta['description']}")

マテリアライゼーションの種類

• view : ビューとして作成（デフォルト）
• table : テーブルとして毎回全量作成
• incremental : 差分のみ追記・更新する（大テーブルに有効）
• ephemeral : テーブルを作らず CTE として他モデルに埋め込む

incremental の例

{{
    config(
        materialized='incremental',
        unique_key='order_id'
    )
}}
SELECT * FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
    WHERE order_date > (SELECT MAX(order_date) FROM {{ this }})
{% endif %}