dbtにおけるstaging / intermediate / martsの役割と責務分割

dbtではモデルを役割ごとに層分割することで、変更の影響範囲を小さくし、テスト容易性と運用性を高めます。本稿ではstaging、intermediate、martsの3層を中心に、責務の切り分けと設計原則を解説します。

dbt Analytics Engineerの試験対策観点では、各層で"やってよいこと／やってはいけないこと"を即答できること、命名規約とテストの配置基準を説明できることが重要です。

3層アーキテクチャの全体像と責務分割

3層分割の主目的は、データの正規化・統一化（staging）、横断的な整合化と前処理（intermediate）、分析消費に最適化された事実・次元（marts）という処理責務を明確にし、欠陥の局所化と変更容易性を得ることです。公式ドキュメントでも、モデルの粒度・責務・命名の一貫性が保守性を高めるとされています。

層をまたぐルールは単純です。下位層は上位層に依存しない。上位層ほどビジネス用途に近い表現を持つが、集計・派生指標の重複定義は避ける。テストは層に応じた性質で配置する（stagingは構造・一意性、intermediateは関係整合、martsはビジネス検証）。

• stagingは「型・命名・欠損・重複」を解消し、外部ソース差異を吸収する
• intermediateは「横断整合・結合・ロジックの標準化」を担う（過度な集計は避ける）
• martsは「事実（fact）と次元（dimension）」で用途別に最適化する
• 各層の命名は接頭辞で明示（例: stg_, int_, dim_/fct_）
• テストは層の目的に沿って種類と厳格度を変える

3層モデルのデータフロー（概念図）

stagingの責務：型・命名・重複の吸収に専念する

stagingは外部システム差異を吸収し、以降の層が"同じ前提"で扱えるようにします。許容される処理は、型変換、命名の正規化、トリムや単純な正規化、重複排除、レコード有効判定などです。ビジネス集計や指標は含めません。

テーブルは原則ソースごとに1:1で用意し、列名は分析で使う最終名に寄せます。ソース整合性はsource freshnessや受け入れ値テストで担保します。

• 命名: stg_<ソース>_<エンティティ>（例: stg_app_orders）
• やらないこと: 集計（SUM, COUNT集約）、複雑なウィンドウ指標、ビジネスロジックの埋め込み
• やること: 型揃え、列名正規化、重複排除、トリム/小文字化、簡易の無効レコード除外
• テスト: not_null/unique/accepted_values を列に付与。freshnessはsources.ymlで設定
• 外部キーは数値・文字列型を統一し、中間層で関係テストを通しやすくする

stg_orders.sql と schema.yml の例

-- models/staging/app/stg_orders.sql
with src as (
  select * from {{ source('app', 'orders') }}
),
renamed as (
  select
    cast(id as bigint)            as order_id,
    cast(customer_id as bigint)   as customer_id,
    cast(total_amount as numeric) as amount,
    date_trunc('second', created_at) as created_at,
    coalesce(status, 'unknown')   as status
  from src
),
dedup as (
  select *
  from (
    select *, row_number() over (partition by order_id order by created_at desc) as rn
    from renamed
  ) t where rn = 1
)
select * from dedup;

# models/staging/app/stg_orders.yml
version: 2
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - not_null
          - unique
      - name: customer_id
        tests:
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['paid','cancelled','pending','unknown']

# models/staging/app/stg_customers.yml（関係テストは中間層で）
# relationships テストは int 層で customer_id の参照整合に用いるのが安全です。

intermediateの責務：横断結合と整合ロジックの集約

intermediateは、stagingで整形済みのエンティティを横断的に結合し、キー整合やビジネスに依存し過ぎない前処理を集約します。異なるソースからの顧客IDマッピング、重複顧客の統合、イベントとディメンションの結合などを担います。

ここでは過度な集計や指標計算は避け、最終martsでの再利用可能性を意識します。スナップショット起点の有効期間の展開、サロゲートキーの安定化、結合条件の標準化を置くのが典型です。

• 責務: 結合、正引き/逆引きマッピング、期間展開、ビジネスキー正規化
• やらないこと: 部門固有のKPI算出、可視化都合のピボット固定化
• テスト: relationships（外部参照）、複合一意（例: order_id, line_number）
• 増分更新: 対象が大きい場合はincrementalだが、キー整合の完全性を壊さない条件設計が必須
• ファイル配置: models/intermediate/<domain>/int_<domain>_<entity>.sql

martsの責務：消費最適化とスター型スキーマ

martsは分析・可視化・配信（exposures）に最適化された最終形です。スター型（事実と次元）でモデル化し、ファクトはイベントやトランザクションの明細、次元はマスタやスローリーチェンジング（SCD）を担います。

派生指標の定義は重複を避け、中心的な事実テーブルに対する計算は可能ならビューやモデルとして一元化します。マテリアライゼーション選択はワークロードや更新頻度に合わせ、増分テーブルの再計算境界を明確にします。

• 命名: fct_<ドメイン>_<イベント>, dim_<ドメイン>_<主語>
• 事実は外部キーと測定値、次元は属性とサロゲートキー
• テスト: 外部キー整合、重複防止、ビジネスルール検証（例: amount >= 0）
• エクスポージャ: BIダッシュボードやMLパイプラインをexposuresで追跡
• 再利用性を高めるため、部門固有変形は派生martsへ分離

命名規約・テスト戦略・依存関係管理

命名は層・ドメイン・エンティティを接頭辞と順序で明示し、参照規約を一定に保ちます。テストは層ごとに厳格度を変え、失敗時の影響面がわかるように配置します。依存はref()/source()で明示し、上位層から下位層への参照を禁じます。

モデル契約（contracts）やカラム記述は、スキーマの逸脱を早期に検出するのに有効です。環境やdbtのバージョンによりサポート状況が異なるため、導入時は使用中のdbt Core/Cloudのドキュメントで確認してください。

• 命名例: stg_app_orders, int_sales_order_items, dim_sales_customer, fct_sales_orders
• testsの配置: 構造・一意はstaging、関係整合はintermediate、ビジネス検証はmarts
• ref()/source()のみで依存を表現し、直接スキーマ参照は避ける
• contractsとdocsでカラム仕様を固定し、破壊的変更を可視化
• スナップショットはdimに派生させ、履歴整合の責務を明確化

試験対策と実務の落とし穴

試験では"どの層で何をやるか"が頻出です。例えば、stagingでSUM集計を行うのは誤り、intermediateはキー整合や期間展開、martsは事実/次元と指標の最終定義という区別を押さえます。

実務では、指標が複数場所に定義される重複が最大のコスト源です。定義の所在をmarts側の単一モデルに寄せ、テストで逸脱を検知する流れを確立しましょう。増分モデルは便利ですが、再計算範囲や自然キーの重複規則を曖昧にすると品質を損ねます。

• アンチパターン: stagingで部門KPIを計算、martsで生データの型変換を初登場させる
• 良いパターン: 参照整合はrelationshipsテスト、集計はfct_*に一元化
• 変更時は下位層から上位層へ影響を読んでCIでテスト連鎖を確認
• freshnessはsourceに設定し、遅延の原因切り分けを容易にする
• 指標の表現はドキュメント化し、命名とスキーマを固定

問題で確認

よくある質問