dbtの考え方とプロジェクト構造 ― ELTのTを宣言的に書く

ローデータの山を、どんな順序と単位で「使える形」に変換していけばいいのか。

答えは、staging → intermediate → marts の3層に積み上げ、各モデルの依存を source と ref で宣言することです。手続き的なスクリプトの寄せ集めではなく、「それが何であるか」を宣言した変換の集合として土台を組みます。

この回では、dbtの中核概念（ELTのTを宣言的に書く・source/ref・3層）と、題材のローデータ（GA4 BigQuery Export・広告・Shopify・CRM）をどの層に切るかの設計方針を示します。

前提を一つ揃えます。計測設計の連載（なぜ計測は静かに壊れるのか ― 計測設計という土台）は「データが生まれる前」――収集・同意・識別・送信の設計を扱いました。本連載はその一段下流、「データが入った後」、つまりローデータを使えるマートへ変換する層を扱います。接合率（成果が手前の行動まで辿れる割合）は、計測設計の連載が設計し、本連載がマートに保持してテストで監視する、と役割を分けます。

題材は連載共通の架空D2Cブランド「SOLNA」（過去の受託案件をもとに設計・スキンケアの定期購入モデル）です。コードはSOLNAで実演し、設計判断の勘所は受託で入った実案件の観察（匿名化・数値はレンジ）で補強します。

ELTのTを宣言的に書くということ

dbtの仕事は、ELT（Extract → Load → Transform）の最後のT、つまり倉庫に入ったローデータを使える形へ変換する工程です。かつてのETLは、倉庫に入れる前に専用ツールで手続き的に変形していました。データウェアハウスが安く速くなった結果、いまは加工しないまま入れてから倉庫の中で変換する（ELT）のが主流で、その変換を担うのがdbtです。

ここで効いているのは「手続き的」から「宣言的」への発想の転換です。手続き的なスクリプトは「まずAを作り、次にBを更新し、その後Cを……」と手順を書きます。実行順を人が管理するので、依存を一つ見落とすと壊れます。dbtでは、一つのモデル（.sqlファイル）が一つのSELECT文で、それがそのままテーブルかビューになります。書くのは手順ではなく「このモデルは何であるか（どういうSELECTの結果か）」だけ。手順はdbtが依存関係から自動で決めます。

宣言的に書けることの実利は地味ですが大きい。SELECTの集合とその依存を宣言しておけば、version管理（Gitでdiffが取れる）・テスト（後の回）・モジュール化（モデルを部品として再利用）・単一の真実（定義が一箇所）という、ソフトウェア工学の作法をそのまま分析に持ち込めます。

誇張は避けます。dbtは魔法ではありません。やっていることは「SQLの変換に、コードとしての規律を載せる」だけです。逆にいえば、設計の良し悪しはそのまま結果に出ます。良いプロジェクト構造は良い土台になり、雑な構造は秘伝のタレを別の場所に移すだけに終わります。

依存を宣言する（source と ref）

dbtのバックボーンは、モデル同士・モデルとローデータの依存を、表名のハードコードではなく関数で宣言することにあります。ローデータの入口は source()、別のモデルへの参照は ref() です。

# models/staging/shopify/_shopify__sources.yml
version: 2
 
sources:
  - name: shopify
    description: ShopifyのECローデータ（注文・顧客）
    tables:
      - name: orders
      - name: customers

-- models/staging/shopify/stg_shopify__orders.sql
-- ローデータに触れるのはこの層だけ。改名・型・正規化に徹する。
select
    id             as order_id,
    customer_id,
    created_at     as order_at,
    total_price    as amount,
    product_id,
    source_channel as last_touch_channel
from {{ source('shopify', 'orders') }}
 
-- models/marts/orders.sql（最小構成）
-- 下流は ref() で繋ぐ。表名をハードコードしないので依存グラフが自動で立つ。
select
    order_id, customer_id, order_at, amount, last_touch_channel
from {{ ref('stg_shopify__orders') }}

schema.table を直に書く代わりに source() と ref() で繋ぐと、副産物として依存グラフ（DAG）が自動で立ちます。これが効くのは三点です。第一に、ソーステーブルの物理名や接続先が変わっても、直すのは source 定義の一箇所だけ。第二に、実行順を人が管理しなくてよい――どのモデルが何に依存するかをdbtが知っているので、上流から順に、独立したものは並列に走らせます。第三に、後の回で扱う系譜（lineage）が、追加の作業なしに依存の宣言から立ち上がります。

3層モデル（staging / intermediate / marts）の役割

変換を一枚岩の巨大なクエリで書くと、どこを直すと何が壊れるか分からなくなります。そこで責務ごとに層を分けます。各層の責務は一文で言えます。

staging（ステージング）は、ローデータに最初に触れて正規化する層です。改名・型の統一・タイムゾーン・NULL方針を揃えるだけで、ビジネスロジックは持ちません。原則は「ローデータに触れるのはここだけ」。

intermediate（中間）は、複数のstagingをまたいで再利用される結合・集計の置き場です。粒度（1行が何を表すか）を宣言し、二重計上を起こさない単位に整えます。

marts（マート）は、事業のことばで表現した最終形です。customers・orders・sessions のように、下流が直接使えるテーブルを置きます。ここが他の全連載（予測・因果・活性化）の入口になります。

データの流れと、その先で誰が使うかを一枚にすると次のようになります。

ローデータ              整える層（本連載）                          下流の連載
GA4 BQ Export ──┐
広告(Meta/Google)─┤
Shopify ─────────┼─→ staging ─→ intermediate ─→ marts ─┬─→ 予測（Kaggle連載）
CRM ─────────────┘                                     ├─→ 因果（因果連載）
                                                        └─→ 活性化（活性化連載）

層を分ける理由は、変更の影響を局所化できることに尽きます。媒体が仕様を変えても影響はstagingに閉じ、結合の作り方を変えてもintermediateに閉じる。テストをどこに置くか（後の回）も、層が決まっていれば自ずと決まります。フォルダ構成は責務をそのまま反映させます。

solna/
├── dbt_project.yml
├── models/
│   ├── staging/              # 生に最初に触れる層（1ソース1ステージング）
│   │   ├── ga4/
│   │   │   ├── _ga4__sources.yml
│   │   │   └── stg_ga4__sessions.sql
│   │   ├── ads/
│   │   │   ├── stg_meta__ads.sql
│   │   │   └── stg_google__ads.sql
│   │   ├── shopify/
│   │   │   ├── _shopify__sources.yml
│   │   │   ├── stg_shopify__orders.sql
│   │   │   └── stg_shopify__customers.sql
│   │   └── crm/
│   │       ├── stg_crm__subscriptions.sql
│   │       ├── stg_crm__messages.sql
│   │       └── stg_crm__reviews.sql
│   ├── intermediate/         # 再利用する結合・集計（粒度を宣言）
│   │   └── int_customer__behavior_joined.sql
│   └── marts/                # 事業のことばで表現した最終形（下流が使う）
│       ├── customers.sql
│       ├── orders.sql
│       ├── sessions.sql
│       ├── subscriptions.sql
│       ├── ad_spend.sql
│       ├── messages.sql
│       └── reviews.sql
└── tests/

marts の具体的な中身――ディメンションとファクトの考え方、結合キーの置き場、真のCAC/LTVと接合率の素地――は、本連載の基本設定として第5回でまとめて組みます。ここでは「下流が使うのはmartsだけ」という骨格だけ押さえておけば十分です。

SOLNAのローデータをどう層に切るか

設計の最初の一手は、手元のローデータの供給元を一つずつstagingに割り当てることです。原則は「1ソース1ステージング」――ローデータの供給元とstagingモデルを一対一で対応させ、混ぜない。SOLNAの4つのローデータの供給元は次のように切ります。

ここで二つの規律を置きます。第一に、stagingでは結合しない。各源を素直に正規化するだけで、源をまたぐ結合はintermediate以降の仕事です。第二に、stagingにビジネスロジックを書かない。「正味売上＝総額−返金」のような事業の定義は、改名・型を超えた判断を含むので、stagingではなく下流（intermediate / marts）に置きます。

結合キーの話は予告だけしておきます。SOLNAの背骨は customer_id（会員）と ga_client_id（行動）です。この二つをどこで・どう繋ぐかは、粒度の管理が必要なのでintermediate以降で扱います（第4回・第5回）。そして、繋いだ結果どれだけ辿れたか――接合率――を marts に保持し、テストで監視するのが本連載の担当です。計測設計の連載が接合率を「設計」し、本連載がそれを「恒常化」する、という棲み分けがここで具体化します。

DRYと単一の真実

層を分けても、同じ計算をあちこちに書いていれば定義はぶれます。dbtで土台を組む目的の核心は、DRY（Don't Repeat Yourself／同じことを二度書かない）を構造で強制することにあります。

「正味売上」の定義を一つのモデルに置き、それを使う下流はすべて ref() でそのモデルを参照する。こうすれば、定義の修正は一箇所で済み、参照しているすべてのモデルに同じ定義が伝播します。逆に、同じ計算を各クエリにコピーした瞬間、それは将来ぶれる予約をしたのと同じです。

これが「単一の真実（single source of truth）」です。第0回で挙げた「同じKPIなのに人とツールで数字が違う」という症状への、構造としての回答がこれにあたります。問題は誰かの不注意ではなく、定義が散逸できてしまう仕組みのほうにあります。定義を一箇所に集約し、参照で配る――この一点だけでも、整える層を持つ価値の相当部分が説明できます。

受託で繰り返し見たのは、たとえば「正味売上」という一つの定義が、レポートツール・分析クエリ・スプレッドシートにまたがって複数本、それぞれ微妙に違う計算で書かれている、という状態でした（匿名化・本数はレンジ）。誰も全部は把握しておらず、突き合わせると合わない。新しく入った人が定義を掴むだけで数日かかる。dbtは、この散逸を「定義は一つのモデル、参照は ref()」という規律で物理的に防ぎます。KPIの計算をさらに上流――BIやReverse ETLでも一本化する発想（セマンティックレイヤー）は、発展回の第8回で扱います。

章末チェック

• source() と ref() の役割を、それぞれ一文で言える（入口の宣言／モデル参照と依存グラフ）
• staging / intermediate / marts の3層の責務を区別できる（正規化／再利用される結合・集計／事業のことばの最終形）
• 自社のローデータの供給元を、1ソース1ステージングの原則で各stagingに割り当てられる
• stagingに結合やビジネスロジックを書かない、という線引きを持っている
• 「定義を一箇所に置き ref() で配る」がDRY・単一の真実の実装だと説明できる

設計の話に入る前に、「そもそも整える層に投資すべきか」という決裁の判断は、対になる決裁者向けの基幹記事データ基盤に投資すべきかに分けてあります。整えた土台の上で「何を測り、何を動かすか」を考える段階は、記述・予測編へ。

次回はstaging層を作る――1ソース1ステージング、命名規約、型の統一、そしてGA4 BigQuery Exportのネスト構造の展開と広告各媒体のchannel正規化を、SOLNAで具体化します。