dbt のレイヤード設計: staging / intermediate / marts を正しく切る

dbt のレイヤード設計は、ソース整形、ビジネスロジック集約、分析用スキーマを分離することで、変更に強くテストしやすいモデルグラフをつくる基本原則です。

本稿は Analytics Engineer 試験の観点と、日々の開発運用で役立つ具体的な実装を両立し、staging / intermediate / marts の境界を迷いなく引けるように整理します。

レイヤード設計の全体像

dbt のモデルは ref による依存関係グラフで実行順序が決まります。レイヤーを明確に分けると、モデルの責務が揃い、テスト配置や変更影響の範囲が読みやすくなります。特に staging / intermediate / marts を採用すると、型変換と命名の標準化、ビジネスロジックの集約、分析消費の最適化を分離できます。

Analytics Engineer 試験では、各レイヤーの目的、命名、粒度、マテリアライズの選択、テストとドキュメントの配置が問われます。以下の原則を押さえると、設計上の一貫性と実行効率を両立できます。

• 1モデル1責務: ソース整形、結合・集約、分析提供を混在させない
• 粒度の明確化: 行の一意性と主キーを各層で説明できること
• 安定APIの提供: marts は下流利用者に対する契約面を重視
• 変更の局所化: 上流変更は下流に波及させないための中間集約
• テストの重心: 一意性・非NULLは上流で、ビジネス検証は中間〜マートで

dbt レイヤード設計の流れ

プロジェクト構成例（抜粋）

# ディレクトリ
models/
  staging/
    sources.yml
    stg_customers.sql
    stg_orders.sql
  intermediate/
    int_orders_enriched.sql
    int_customers_current.sql
  marts/
    core/
      dim_customers.sql
      fct_orders.sql

# dbt_project.yml（レイヤー別のマテリアライズ方針を統一）
models:
  my_project:
    staging:
      +materialized: view
    intermediate:
      +materialized: table
    marts:
      +materialized: table

Staging 層: ソースの正規化

staging は生データに最も近い層で、列名の正規化、型変換、軽いフィルタや列の展開にとどめます。業務ロジックや集約は持ち込みません。主な目的は「下流が扱いやすい安定したカラム形と命名」を提供することです。

マテリアライズはビューが無難です。計算が重い、もしくは上流のソースが遅い場合のみテーブル化を検討します。ソースの一意キー、非NULL、リファレンス整合性などの基本テストはここで定義しておくと、異常を早期に検知できます。

• 命名規則: snaking_case、プレフィックスは stg_
• source() で外部ソースを参照し、カラム名を標準化
• 型の明示的キャストとタイムゾーン方針の統一
• 一意キーと非NULLテストを配置
• ビューを既定とし、重い場合のみテーブル化

stg_orders.sql と sources.yml の例

-- models/staging/stg_orders.sql
with src as (
  select * from {{ source('raw', 'orders') }}
)
select
  cast(order_id as varchar)       as order_id,
  cast(customer_id as varchar)    as customer_id,
  cast(order_ts as timestamp)     as order_timestamp,
  upper(status)                   as status,
  cast(total_amount as numeric(38,2)) as total_amount,
  updated_at
from src
where order_id is not null
;

# models/staging/sources.yml
version: 2
sources:
  - name: raw
    schema: raw
    tables:
      - name: orders
        description: 原始の受注テーブル
        loaded_at_field: updated_at
        freshness:
          warn_after: {count: 90, period: minute}
          error_after: {count: 180, period: minute}
        columns:
          - name: order_id
            tests: [not_null, unique]
          - name: customer_id
            tests: [not_null]
          - name: updated_at
            tests: [not_null]

Intermediate 層: ビジネスロジックの集約

intermediate は複数の staging を結合し、重複排除や粒度の統一、派生カラムの付与などビジネスロジックを集約する層です。marts の直前に位置し、変更の影響を吸収するバッファとして機能します。

更新頻度が高いテーブルでは incremental マテリアライズが有効です。is_incremental() ガードで新規・更新分のみを取り込み、実行時間を短縮します。重複排除は window 関数で最新版を選ぶのが安定的です。

• 複数ソースの結合と粒度の確定
• 重複排除や最新レコードの選定
• 派生指標・フラグの付与（送料込み金額、キャンセル判定など）
• incremental を活用し計算コストを抑制
• marts 側のスキーマ変更を吸収しやすいインターフェースを提供

int_orders_enriched.sql（incremental と重複排除）

-- models/intermediate/int_orders_enriched.sql
{{
  config(
    materialized='incremental',
    unique_key='order_id'
  )
}}

with orders as (
  select * from {{ ref('stg_orders') }}
),
-- 顧客の最新属性を別モデル（例: int_customers_current）から参照
customers as (
  select * from {{ ref('int_customers_current') }}
),
-- 重複受注の最新版を採用
latest_orders as (
  select o.*
  from (
    select *,
           row_number() over(partition by order_id order by updated_at desc) as rn
    from orders
  ) o
  where o.rn = 1
),
joined as (
  select
    l.order_id,
    l.customer_id,
    c.customer_tier,
    l.order_timestamp,
    l.status,
    l.total_amount,
    case when l.status in ('CANCELLED','VOID') then 1 else 0 end as is_cancelled
  from latest_orders l
  left join customers c using (customer_id)
)
select *
from joined
{% if is_incremental() %}
  -- 直近更新のみ取り込み（updated_at の存在を前提）
  where joined.order_timestamp > (
    select coalesce(max(order_timestamp), '1900-01-01') from {{ this }}
  )
{% endif %}
;

Marts 層: 分析用スキーマの提供

marts は下流のダッシュボードや分析クエリが直接参照する層です。事業ドメインごとにスキーマ（例: core, marketing, finance）を分け、スター・スキーマの原則でディメンション（dim_）とファクト（fct_）を設計します。スキーマの安定性と可読性を最優先します。

ファクトは明確な粒度（例: 1行=1受注）で、ディメンションは属性の現在形を提供します。集計は二重計上を避けるため、できる限りファクトで行単位の事実を保持し、ダッシュボード側の集計と役割分担を明確にします。

• 命名規則: dim_*, fct_*
• marts は API 的役割。列と型を契約として固定する
• ファクトは測定値、ディメンションは記述属性を提供
• ドメイン別にサブフォルダを分割（core, marketing など）
• exposures で下流アセットの可視化と依存トラッキング

dim_customers / fct_orders（抜粋）

-- models/marts/core/dim_customers.sql
select
  customer_id,
  customer_name,
  customer_tier,
  signup_date
from {{ ref('int_customers_current') }}
;

-- models/marts/core/fct_orders.sql
select
  order_id,
  customer_id,
  order_timestamp,
  status,
  total_amount,
  is_cancelled
from {{ ref('int_orders_enriched') }}
where status not in ('TEST')
;

テスト・契約・ドキュメントの配置

dbt のテストは generic（not_null, unique, relationships など）と singular（任意SQL）の2系統があります。staging でキー整合を担保し、intermediate〜marts でビジネスルールを検証します。データ契約（model contracts）は列・型・必須性を固定し、下流破壊を防ぎます。

ドキュメントは description と docs ブロックで整備し、docs generate で閲覧可能にします。exposures を定義すると、ダッシュボードやレポートとモデルの依存を可視化できます。

• staging: not_null, unique, relationships を徹底
• intermediate: 重複排除やフィルタの整合を singular テストで確認
• marts: 契約（contract: true）で列と型を固定
• docs と exposures で利用実態と責任者を明示
• 失敗時は上流から原因切り分け

schema.yml（tests, contracts, exposures の例）

version: 2
models:
  - name: fct_orders
    description: 受注ファクト。1行=1受注
    config:
      contract: true
    columns:
      - name: order_id
        description: 受注ID（主キー）
        data_type: varchar
        tests: [not_null, unique]
      - name: customer_id
        data_type: varchar
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: total_amount
        data_type: numeric
        tests:
          - not_null
          - accepted_values:
              values:
                - "{{ 0 }}:inf"  # 非負を独自テストで代替可

exposures:
  - name: sales_dashboard
    type: dashboard
    maturity: high
    url: https://bi.example.com/dashboards/sales
    owners:
      - name: Analytics Team
        email: [email protected]
    depends_on:
      - ref('fct_orders')
      - ref('dim_customers')

実行戦略とパフォーマンス最適化

dbt は DAG に基づき並列実行します。セレクタ（--select, --state）で変更影響範囲のみを再実行し、開発～本番のサイクルを短縮します。レイヤーごとに既定のマテリアライズを決め、不要な再計算を避けます。

インクリメンタルは主キー・更新時刻の取り扱いを明確にし、フルリフレッシュの運用ガイドラインをチームで共有します。クラスタリングやパーティションなどウェアハウス固有の最適化は、dbt のモデル設定で明示的に管理すると安全です。

• --select + 標準タグで最小実行
• state:modified で差分ビルド
• フルリフレッシュは計画的に（夜間や低負荷帯）
• レイヤー既定のマテリアライズを dbt_project.yml で固定
• ドメイン別にフォルダ分割し並列性を高める

実行コマンドとセレクタ例

# 変更のあったモデルとその下流のみ
$ dbt build --select state:modified+ --state target/previous_run

# レイヤー別の実行（フォルダセレクタ）
$ dbt build --select models/staging
$ dbt build --select models/intermediate
$ dbt build --select models/marts

# 特定ドメインのみ再実行
$ dbt build --select marts.core+

# フルリフレッシュ（計画的に）
$ dbt build --full-refresh --select int_orders_enriched fct_orders

問題で確認

よくある質問