intermediate層とモデリング設計 ― どこで結合し、どこで集計するか

結合と集計は、3層のどこに置くのが正しいのか。答えは、再利用される中間処理は intermediate 層に切り出す、です。そして切り出すときの唯一にして最大の宣言が「このモデルの1行は何を表すか」、つまり粒度です。この回では、intermediate 層の役割と、粒度を宣言しないと起きるファンアウト（行の水増しによる二重計上）を、過去の受託案件をもとに設計した架空のD2C企業SOLNAの顧客×行動×注文の結合で具体化します。

前回まで、staging でローデータを1ソースずつ正規化し（第2回）、テストで壊れたデータを下流に流さない仕組みを置きました（第3回）。この回は、その整った staging を「どこで結合し、どこで集計するか」という設計の話です。コードの量より、置き場と粒度の判断が主題になります。

intermediate層は何のためにあるか

3層の責務を一文ずつ確認します。staging はローデータに最初に触れて正規化する窓口で、1ソース1モデル。marts は事業のことば（顧客・注文・契約）で表現した最終形。その間にある intermediate は、複数の staging をまたぐ結合や、再利用される集計の置き場です。

intermediate は3層のなかで唯一「必須ではない」層です。単純な変換なら staging から marts へ直接でも成立します。では、いつ切り出すのか。判断は単純で、同じ結合や集計が2回以上現れたら、または1本のクエリが頭の中で追えなくなったら切り出します。理由は3つあります。

• 再利用。顧客と行動の突合のように複数の marts が同じ中間結果を必要とするとき、一度だけ定義して ref で何度も使う。これが連載の通奏低音である DRY（同じ計算を二度書かない）であり、定義のぶれを構造で封じる手段です。
• テスト可能性。中間成果物にも粒度や非NULLのテストを置ける。巨大な1本のクエリの内部では検査できなかった途中状態を、独立したモデルにすると第3回のテストで守れます。
• 可読性。marts が「整った中間結果を組み合わせるだけ」になり、事業ロジックが読めるようになる。

逆に言えば、intermediate を作りすぎるのも設計の失敗です。一度しか使わない自明な変換まで層を増やすと、系譜が無駄に深くなる。「2回以上の再利用」か「複雑さの隔離」のどちらかが理由になるときだけ切り出す、を原則にします。

結合の置き場と再利用

結合の場所には規律があります。staging では結合しない。staging は1ソースの正規化が責務なので、ここに別ソースとの結合を持ち込むと「ローデータに触れるのは staging だけ」という前回の規律（変更耐性の源）が崩れます。したがって、ソースをまたぐ結合はすべて intermediate 以降に置きます。

SOLNA で最初に必要になる結合は、行動を会員に紐づける突合です。sessions は行動の単位 ga_client_id（ブラウザ）を持ち、ログイン済みのセッションにだけ会員の customer_id が付きます。orders や customers は customer_id の世界です。この2つの世界を一本に繋ぐ突合は、ほぼすべての marts（真のCAC、LTV、コホート）が前提にします。だからこそ、これは marts のどこかに散らして書くのではなく、intermediate に一度だけ定義して再利用するのが正解です。

この「ga_client_id を customer_id に解決する」中間モデルこそ、本連載が繰り返し触れてきた接合率（spine coverage）が物理的に宿る場所です。接合率とは、成果のうち手前の行動まで一本で辿れる割合のこと（測定の上限を決める指標。詳細は記述・予測編）。第3回ではこれをテストで監視する話をしましたが、監視対象のテーブルそのものを作るのがこの intermediate です。設計は計測設計の連載（なぜ計測は静かに壊れるのか ― 計測設計という土台）、保持と監視は本連載——その保持の実体がここにあります。

粒度を宣言する（ファンアウト/ファンインの罠）

粒度（grain）とは「1行が何を表すか」です。customers なら1行＝1顧客、orders なら1行＝1注文、ad_spend なら1行＝日×channel×region。これはモデル設計の最重要宣言で、粒度を口に出して言えないモデルは、ほぼ確実にバグっています。

粒度が崩れる典型がファンアウトです。1対多の関係をそのまま結合すると、左側の行が右側の件数だけ増える。たとえば1人の顧客が注文を3件、セッションを10件持つとき、customers に orders と sessions を一度に結合すると、その顧客の行は最大で 3×10＝30 行に膨らみます。この状態で sum(amount) を取ると、各注文金額がセッション数の回数だけ足され、売上が10倍に水増しされます。

ここで強調したいのは、この水増しは確率的な誤差ではなく、機械的に決まる事実だという点です。二重計上の倍率は、結合先のカーディナリティ（顧客あたりのセッション数）そのものに等しい。だから「だいたい合っている」では済まず、構造で根絶する以外にありません。

防ぎ方は一つに集約できます。結合する前に、各テーブルを目的の粒度へ集約しておく。顧客単位の事実が欲しいなら、orders を顧客単位に集約した中間モデルと、sessions を顧客単位に集約した中間モデルを別々に作り、最後に顧客単位どうしで結合する。粒度が両側で揃っていれば、1対1の結合になり、ファンアウトは原理的に起きません。

対になるのがファンイン（多対一）で、こちらは多くの注文が1顧客を参照するような結合です。行は増えないので集計事故は起きにくいですが、別の罠があります。inner join で書くと、参照先が見つからない行（会員になる前の行動など）が黙って消え、これも接合率を下げる原因になる。**marts へ向かう結合は原則 left join で「左の粒度を保存する」**を既定にし、消える行があるなら意図して消す、を明示します。

SOLNAで: 顧客 × 行動 × 注文 の結合をどこに置くか

設計を SOLNA のコードで通します。DWH は GA4 BigQuery Export を前提に BigQuery 系の方言で書きます（countif など）。

まず、やってはいけないアンチパターン。粒度を宣言せず、顧客に注文とセッションを一度に結合してから集計する書き方です。

-- アンチパターン：粒度を宣言せず直接結合 → ファンアウトで二重計上
select
    c.customer_id,
    sum(o.amount)        as revenue_total,   -- セッション数だけ amount が水増しされる
    count(s.session_id)  as sessions_total   -- これは注文数だけ水増しされる
from {{ ref('stg_crm__customers') }} c
left join {{ ref('stg_shopify__orders') }}  o using (customer_id)  -- 1対多
left join {{ ref('stg_ga4__sessions') }}    s using (customer_id)  -- さらに1対多
group by 1

revenue_total も sessions_total も、互いの件数で水増しされた無意味な数字です。これを marts に置くと、下流の全分析が壊れます。

正しくは、再利用される突合と粒度の集約を intermediate に切り出します。第一に、行動IDを会員IDへ解決する identity 突合を、ga_client_id 単位（1行＝1ブラウザ）で作ります。接合率がここに宿ります。

-- models/intermediate/int_identity_map.sql
-- 粒度：1行 = 1 ga_client_id（ブラウザ）。ログインで判明した会員IDを束ねる。
with sessions as (
    select * from {{ ref('stg_ga4__sessions') }}
)
 
select
    ga_client_id,
    max(customer_id) as customer_id   -- ログイン済みセッションに付いた会員ID（無ければNULL）
from sessions
group by ga_client_id
-- 接合率 = customer_id が非NULLの ga_client_id の割合（第3回のテストがここを監視）

第二に、注文とセッションをそれぞれ顧客粒度へ集約した中間モデルを作ります。集約を済ませてから結合するのが、ファンアウトを原理的に断つ要です。

-- models/intermediate/int_customer_orders.sql
-- 粒度：1行 = 1 customer_id
select
    customer_id,
    count(*)                                          as orders_total,
    sum(amount)                                       as revenue_total,
    min(order_at)                                     as first_order_at,
    countif(order_type = 'first')                     as first_orders,
    countif(order_type = 'subscription')              as subscription_orders
from {{ ref('stg_shopify__orders') }}
group by customer_id
 
-- models/intermediate/int_customer_sessions.sql
-- 粒度：1行 = 1 customer_id（int_identity_map で行動を会員へ解決してから集約）
with sessions as (
    select * from {{ ref('stg_ga4__sessions') }}
),
identity as (
    select * from {{ ref('int_identity_map') }}
),
stitched as (
    select
        coalesce(s.customer_id, i.customer_id) as customer_id,  -- ログイン優先、無ければ解決値
        s.session_id,
        s.session_start,
        s.engaged
    from sessions s
    left join identity i using (ga_client_id)
)
select
    customer_id,
    count(*)                  as sessions_total,
    countif(engaged)          as sessions_engaged,
    min(session_start)        as first_session_at,
    max(session_start)        as last_session_at
from stitched
where customer_id is not null   -- 会員に解決できなかった行動はここで意図的に落とす
group by customer_id

こうしておけば、marts 側は顧客粒度どうしの1対1結合になり、二重計上は起きません。

-- marts 側（次回の主題）：顧客粒度で揃った中間モデルを結合するだけ
select
    c.customer_id,
    o.orders_total,
    o.revenue_total,        -- もう水増しされない
    s.sessions_total,
    s.sessions_engaged
from {{ ref('stg_crm__customers') }} c
left join {{ ref('int_customer_orders') }}   o using (customer_id)
left join {{ ref('int_customer_sessions') }} s using (customer_id)

このSOLNAデータセット（連載共通の作業用データ・数値はレンジで丸めています）で実際に走らせると、顧客あたりのセッションは概ね6〜12件あり、先のアンチパターンでは revenue_total がその倍率ぶん、つまり真値の6〜12倍に膨れました。倍率がカーディナリティに一致するという、先ほどの機械的事実そのままです。また int_identity_map で測った接合率（customer_id に解決できた ga_client_id の割合）は流入経路差が大きく、全体で概ね**35〜55%**のレンジ。集約後の行数は、sessions の数十万行が int_customer_sessions では顧客数規模（数万行）に畳まれ、marts の結合が軽くなります。

モデルを小さく保つ（1モデル1責務）

ここまでを設計原則に畳むと、1モデル＝1粒度＝1責務です。int_identity_map は突合だけ、int_customer_orders は注文の顧客集約だけ。一つのクエリで突合も集約も属性付与も全部やる「ドリームクエリ」は、読めず、テストできず、再利用できません。小さく割るほど、各モデルに粒度テスト（その粒度キーで unique かつ not_null）を置けて、第3回の品質保証がそのまま効きます。

命名と層分けの具体的な作法（フォルダ構成や int_ 接頭辞の付け方など）には定番のガイドがありますが、ツール固有で版に依存するため、本文では原則だけ示し、手順は公式 docs に譲ります。ここで握っておくべきは、フォルダの切り方そのものではなく、**「結合は staging では書かない」「結合の前に粒度を集約する」「1モデル1責務」**という、ツールが変わっても動かない判断のほうです。

章末チェックリスト

• intermediate を切り出す基準（2回以上の再利用 or 複雑さの隔離）を言えるか
• 各モデルの粒度を「1行＝何か」で即答できるか
• ファンアウトによる二重計上を、結合前の集約で防げるか
• 接合率が宿る identity 突合を intermediate に一度だけ置いているか
• marts へ向かう結合で、左の粒度を保存する（既定 left join）を守れているか

ここまでで、結合と集計の置き場と粒度が決まりました。次回はいよいよ marts 層――この回で揃えた顧客粒度の中間モデルを組み合わせ、customers / orders / sessions / subscriptions / ad_spend / messages / reviews という、全連載が使う SOLNA スキーマの基本設定を作ります。結合キー customer_id × ga_client_id の最終的な置き場と、真のCAC/LTV・接合率を marts に保持する設計まで通します。

そもそも整える層に投資すべきかという決裁者の判断は、データ基盤に投資すべきかへ。整えたマートの上で「何を測り、何を動かすか」は記述・予測編へ続きます。