C データエンジニアリング — 多段階リファラルボーナス集計 Bad→Good（ハードコード自己JOIN→WITH RECURSIVE再帰CTE(深さ制限+pathサイクルガード) × ARRAY<STRUCT>明細を先に注文粒度へ集約 × 別プロジェクトdim_memberハードコードFQN→dbt Meshクロスプロジェクトref() × 紹介関係重複→QUALIFY ROW_NUMBER() × 下流ダッシュボード依存未宣言→dbt exposures × table→incremental insert_overwrite+partition/cluster × bonus_amount無防備→dbt tests+model contract）（MOps 友達紹介キャンペーン）

概要

問題

ECサイト MOps チームでは「友達紹介キャンペーン」を運用しており、直接紹介（1次）だけでなく、紹介された会員がさらに別の会員を紹介した場合の2次・3次紹介まで遡って紹介ボーナスを支払う 多段階リファラルボーナス集計マート を BigQuery × dbt Core 1.9+ で構築している。以下の dbt モデルには 7つの設計・パフォーマンス・データガバナンス上の問題 が潜んでいる。

問題点を全て洗い出し、BigQuery WITH RECURSIVE 再帰CTE（深さ制限＋サイクルガード）× ARRAY<STRUCT> 明細の事前集約による行爆発回避 × dbt Mesh クロスプロジェクト参照 × 紹介関係の重複排除（QUALIFY）× dbt exposures × incremental + partition_by/cluster_by × dbt tests / model contract を活用した Bad→Good リファクタリングを行ってください。

制約・前提条件

• BigQuery Standard SQL（GA機能のみ。WITH RECURSIVE, QUALIFY, ARRAY_CONCAT, UNNEST 利用可）
• dbt Core 1.9+（Mesh のクロスプロジェクト ref()、exposures、model contract enforced が利用可）
• テーブル orders.order_events（order_id, member_id, order_date DATE, order_items ARRAY<STRUCT<product_id STRING, qty INT64, unit_price NUMERIC>>。partition: order_date）
• テーブル enrollment.referral_edges（member_id, referred_by_member_id, referred_at）。データ不整合で同一 member_id に複数の紹介者登録が紛れ込むことがある
• dim_member（会員ランク等）は 別の dbt プロジェクト shared_dw が管理・公開している共有モデル
• 紹介ボーナス率: 1次5% / 2次2% / 3次1%。3次より先は対象外
• このマートは Looker Studio の「紹介ボーナスダッシュボード」から参照される。BigQuery オンデマンド課金（$5/TB）、注文は日々積み上がる

悪いコード (Before)

📋 コピー{{ config(materialized='table') }}

-- 問題①: 1次/2次/3次を3本の自己JOINでハードコード（段数固定・循環紹介を検知不可）
WITH level1 AS (
  SELECT e1.member_id, e1.referred_by_member_id AS referrer_id, 1 AS level
  FROM `my-project.enrollment.referral_edges` e1
  -- 問題④: 重複紹介登録(同一member_idに複数referrer)を排除していない
),
level2 AS (
  SELECT e2.member_id, e1.referrer_id, 2 AS level
  FROM `my-project.enrollment.referral_edges` e2
  JOIN level1 e1 ON e2.referred_by_member_id = e1.member_id
),
level3 AS (
  SELECT e3.member_id, e2.referrer_id, 3 AS level
  FROM `my-project.enrollment.referral_edges` e3
  JOIN level2 e2 ON e3.referred_by_member_id = e2.member_id
),
all_levels AS (
  SELECT * FROM level1
  UNION ALL SELECT * FROM level2
  UNION ALL SELECT * FROM level3
),

-- 問題②: UNNEST明細をレベルとJOINする前に集約していない → 行爆発
order_lines AS (
  SELECT o.order_id, o.member_id, o.order_date, item.qty, item.unit_price
  FROM `my-project.orders.order_events` o, UNNEST(o.order_items) AS item
),

-- 問題③: 別プロジェクトのdim_memberをハードコードFQNで参照（dbt ref()を使わない）
member_dim AS (
  SELECT member_id, member_rank
  FROM `project-a.shared_dw.dim_member`
)

SELECT
  al.referrer_id,
  al.level,
  ol.order_id,
  SUM(ol.qty * ol.unit_price) AS order_amount,
  SUM(ol.qty * ol.unit_price)
    * CASE al.level WHEN 1 THEN 0.05 WHEN 2 THEN 0.02 ELSE 0.01 END AS bonus_amount
FROM all_levels al
JOIN order_lines ol ON al.member_id = ol.member_id   -- 明細行×レベル数だけ結合が膨張
JOIN member_dim md  ON ol.member_id = md.member_id
GROUP BY al.referrer_id, al.level, ol.order_id

📋 コピーversion: 2
models:
  - name: bad_referral_bonus
    # 問題⑤: Looker Studioダッシュボードがこのマートに依存しているが
    #        exposures が宣言されておらず影響範囲が誰にも見えない
    # 問題⑦: bonus_amount のNULL/マイナス値・一意性の防御なし
    columns:
      - name: referrer_id
      - name: bonus_amount

ヒント（段階的開示）

-- 再帰CTEの骨格（BigQuery WITH RECURSIVE）
WITH RECURSIVE
referral_chain AS (
  -- 基底: 1次紹介
  SELECT member_id, referrer_id, 1 AS level,
         [member_id, referrer_id] AS path        -- 修正①: サイクル検出用の経路
  FROM referral_edges_dedup

  UNION ALL

  -- 再帰: 紹介者のさらに上位を辿る（深さ制限 + サイクルガード）
  SELECT rc.member_id, re.referrer_id, rc.level + 1,
         ARRAY_CONCAT(rc.path, [re.referrer_id])
  FROM referral_chain rc
  JOIN referral_edges_dedup re ON rc.referrer_id = re.member_id
  WHERE rc.level < 3                              -- 修正①: 3次まで
    AND re.referrer_id NOT IN UNNEST(rc.path)      -- 修正①: 循環紹介ガード
)
-- 修正②: order_totals は「先に注文粒度へ集約してから」referral_chain とJOINする
-- 修正③: member_dim は {{ ref('shared_dw', 'dim_member') }} で参照する

問題点分析（7点）

再帰チェーンの仕組み — WITH RECURSIVE + path サイクルガード / 事前集約

模範解答

📋 コピー{{ config(materialized='table') }}

WITH level1 AS (                                     -- 問題①
  SELECT e1.member_id, e1.referred_by_member_id AS referrer_id, 1 AS level
  FROM `my-project.enrollment.referral_edges` e1
),
level2 AS (
  SELECT e2.member_id, e1.referrer_id, 2 AS level
  FROM `my-project.enrollment.referral_edges` e2
  JOIN level1 e1 ON e2.referred_by_member_id = e1.member_id
),
level3 AS (
  SELECT e3.member_id, e2.referrer_id, 3 AS level
  FROM `my-project.enrollment.referral_edges` e3
  JOIN level2 e2 ON e3.referred_by_member_id = e2.member_id
),
all_levels AS (
  SELECT * FROM level1
  UNION ALL SELECT * FROM level2
  UNION ALL SELECT * FROM level3
),
order_lines AS (                                     -- 問題②
  SELECT o.order_id, o.member_id, item.qty, item.unit_price
  FROM `my-project.orders.order_events` o, UNNEST(o.order_items) AS item
),
member_dim AS (                                      -- 問題③
  SELECT member_id, member_rank
  FROM `project-a.shared_dw.dim_member`
)
SELECT
  al.referrer_id, al.level, ol.order_id,
  SUM(ol.qty * ol.unit_price) AS order_amount,
  SUM(ol.qty * ol.unit_price)
    * CASE al.level WHEN 1 THEN 0.05 WHEN 2 THEN 0.02 ELSE 0.01 END AS bonus_amount
FROM all_levels al
JOIN order_lines ol ON al.member_id = ol.member_id
JOIN member_dim md  ON ol.member_id = md.member_id
GROUP BY al.referrer_id, al.level, ol.order_id

📋 コピー-- models/marts/mart_referral_bonus.sql
-- dbt Core 1.9+ | BigQuery Standard SQL
{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',   -- 修正⑥
    partition_by={
        'field': 'order_date', 'data_type': 'date', 'granularity': 'day'
    },
    cluster_by=['referrer_id'],                -- 修正⑥
    on_schema_change='fail'
) }}

WITH RECURSIVE

-- 修正④: 1会員につき有効な紹介者は最新1件のみ採用
referral_edges_dedup AS (
    SELECT member_id, referred_by_member_id AS referrer_id, referred_at
    FROM {{ ref('stg_referral_edges') }}
    WHERE referred_by_member_id IS NOT NULL
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY member_id ORDER BY referred_at DESC
    ) = 1
),

-- 修正①: ハードコード3段JOIN → 再帰CTEで多段紹介チェーンをたどる
referral_chain AS (
    SELECT
        member_id, referrer_id, 1 AS level,
        [member_id, referrer_id] AS path            -- サイクル検出用の経路
    FROM referral_edges_dedup

    UNION ALL

    SELECT
        rc.member_id, re.referrer_id, rc.level + 1,
        ARRAY_CONCAT(rc.path, [re.referrer_id])
    FROM referral_chain rc
    JOIN referral_edges_dedup re ON rc.referrer_id = re.member_id
    WHERE rc.level < 3                              -- 3次紹介まで
      AND re.referrer_id NOT IN UNNEST(rc.path)      -- 循環紹介ガード
),

-- 修正②: 明細行は「先に注文粒度へ集約」してからJOIN（行爆発を回避）
order_totals AS (
    SELECT
        o.order_id, o.member_id, o.order_date,
        SUM(item.qty * item.unit_price) AS order_amount
    FROM {{ ref('stg_order_events') }} o, UNNEST(o.order_items) AS item
    {% if is_incremental() %}
      WHERE o.order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
    {% endif %}
    GROUP BY o.order_id, o.member_id, o.order_date
),

-- 修正③: 別dbtプロジェクト(shared_dw)をdbt Meshのクロスプロジェクトref()で参照
member_dim AS (
    SELECT member_id, member_rank
    FROM {{ ref('shared_dw', 'dim_member') }}
)

SELECT
    rc.referrer_id, rc.level, ot.order_id, ot.order_date, ot.order_amount,
    ot.order_amount * CASE rc.level
        WHEN 1 THEN 0.05 WHEN 2 THEN 0.02 ELSE 0.01
    END AS bonus_amount
FROM referral_chain rc
JOIN order_totals ot ON rc.member_id = ot.member_id
JOIN member_dim md   ON ot.member_id = md.member_id

📋 コピー# models/marts/_marts.yml
version: 2

models:
  - name: mart_referral_bonus
    description: "多段階(最大3次)紹介ボーナス集計マート。Looker Studio「紹介ボーナスダッシュボード」の参照元。"
    config:
      contract:
        enforced: true          # 修正⑦: スキーマ不一致・制約違反でbuildをfailさせる
    columns:
      - name: referrer_id
        data_type: string
        constraints:
          - type: not_null
      - name: level
        data_type: int64
        constraints:
          - type: not_null
        tests:
          - accepted_values:
              values: [1, 2, 3]
      - name: order_id
        data_type: string
        constraints:
          - type: not_null
      - name: order_date
        data_type: date
        constraints:
          - type: not_null
      - name: order_amount
        data_type: numeric
      - name: bonus_amount
        data_type: numeric
        tests:
          - dbt_utils.accepted_range:      # 修正⑦: マイナス値の混入をbuild時に検知
              min_value: 0
    tests:
      - dbt_utils.unique_combination_of_columns:   # 修正⑦: 二重支払いの元となる重複行を検知
          combination_of_columns:
            - referrer_id
            - order_id
            - level

# 修正⑤: 下流ダッシュボードの依存を明示（スキーマ変更時の影響範囲を可視化）
exposures:
  - name: referral_bonus_dashboard
    label: "紹介ボーナスダッシュボード"
    type: dashboard
    maturity: medium
    url: https://lookerstudio.google.com/reporting/referral-bonus-dashboard
    description: "MOpsチームが友達紹介キャンペーンの支払い状況を確認するダッシュボード"
    depends_on:
      - ref('mart_referral_bonus')
    owner:
      name: MOps Team
      email: mops-team@example.com

実行例（input → output）

input（referral_edges_dedup、簡略化）:

member_id  referrer_id
B          A
C          B
D          C     ← A(1次) ← B(2次) ← C(3次) の3段紹介チェーン
X          Y
Y          X      ← データ不整合: X と Y が互いを紹介し合う循環データ

再帰CTE展開結果（referral_chain、D起点）:

member_id  referrer_id  level  path
D          C            1      [D, C]
D          B            2      [D, C, B]
D          A            3      [D, C, B, A]

X/Y の循環データは、再帰項に到達した時点で re.referrer_id NOT IN UNNEST(rc.path) に引っかかり無限ループにならず1段で停止する。

output（mart_referral_bonus、Dの注文1万円分）:

referrer_id  level  order_id  order_amount  bonus_amount
C            1      O-100     10000         500   (5%)
B            2      O-100     10000         200   (2%)
A            3      O-100     10000         100   (1%)

ポイント解説

実務への応用

ECサイト MOps チームの友達紹介キャンペーン運用に直結する。多段階紹介ボーナスのような「グラフ構造を辿る集計」は、段数が増えるたびにJOINを手書きで増やすアプローチが破綻しやすく、再帰CTE化しておくことでキャンペーン仕様変更（3次→4次への拡張等）に level の上限値を変えるだけで対応できる。循環データへの防御は、紹介キャンペーンに限らず「組織階層」「カテゴリツリー」「返信スレッド」など木構造・グラフ構造を扱うあらゆるマートで再利用できるパターン。

dbt Mesh のクロスプロジェクト参照と exposures は、チームが複数（MOps / 会員基盤 / BI）にまたがる組織でのデータガバナンスの要。shared_dw のような共通ディメンションを持つ組織では、ハードコードFQN参照が「誰にも気づかれないサイレント破壊」の温床になりやすく、早期にMesh化しておくことが技術的負債の予防になる。

今日のまとめ

次のステップ

• 発展問題: 紹介者が退会済み（dim_member に存在しない、または is_active = false）の場合、その紹介者への以降のボーナス計上を止めつつ、さらに上位の紹介者への計上は継続する「孤立ノードのスキップ」ロジックを再帰CTEに組み込め。また、shared_dw 側の dim_member を dbt Mesh の model versions（v1/v2）で管理し、破壊的変更時に契約側へ段階移行させる方法を設計せよ。
• 参考: BigQuery WITH RECURSIVE（再帰CTE）公式ドキュメント / ARRAY_CONCAT / UNNEST とネスト配列集約のベストプラクティス / dbt Mesh（cross-project ref, model versions）/ dbt exposures / dbt_utils.unique_combination_of_columns / QUALIFY

自己評価（あとで記入）