データエンジニアリング — BigQuery クエリ最適化

概要

問題

あなたはECサイトのMOpsチームのデータエンジニアとして、以下のBigQueryクエリを改善することになった。 このクエリは毎朝6時にArgo Workflowsで実行され、前日の販促キャンペーンごとの購入転換率（CVR）と売上を集計するものである。

テーブル情報

制約・前提条件

• BigQuery Standard SQL (GoogleSQL) を使用
• 対象は「前日1日分」のデータのみ
• CTEを使ってステップを分離すること
• コスト削減（スキャン量の削減）が最優先

現状クエリ (Bad)

📋 コピー-- 現状のクエリ（Bad）
-- 実行時間: 約45秒 / スキャン量: 230GB

SELECT
    c.campaign_id,
    c.campaign_name,
    c.start_date,
    c.end_date,
    COUNT(DISTINCT e.user_id) AS reached_users,
    COUNT(DISTINCT o.user_id) AS converted_users,
    ROUND(COUNT(DISTINCT o.user_id) / COUNT(DISTINCT e.user_id), 4) AS cvr,
    SUM(o.amount) AS revenue
FROM `project.raw.events` e
JOIN `project.raw.campaigns` c
  ON e.campaign_id = c.campaign_id
LEFT JOIN `project.raw.orders` o
  ON e.user_id = o.user_id
  AND o.created_at BETWEEN c.start_date AND c.end_date
WHERE e.event_type = 'impression'
  AND DATE(e.occurred_at) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY 1, 2, 3, 4
ORDER BY revenue DESC

ヒント（段階的開示）

-- Step 1: 前日のイベントだけフィルタ（パーティションを活かす）
WITH yesterday_impressions AS (
    SELECT campaign_id, user_id
    FROM `project.raw.events`
    WHERE occurred_at >= TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
      AND occurred_at <  TIMESTAMP(CURRENT_DATE())  -- パーティションプルーニング
      AND event_type = 'impression'                  -- クラスタリング活用
),
-- Step 2: 前日の注文を絞る
yesterday_orders AS (
    SELECT user_id, SUM(amount) AS amount
    FROM `project.raw.orders`
    WHERE created_at >= TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
      AND created_at <  TIMESTAMP(CURRENT_DATE())
    GROUP BY user_id
),
...

問題点分析

最適化フロー図 — Before vs After

模範解答

📋 コピー-- 改善後クエリ
-- 期待スキャン量: 約4GB（▲98%削減）
-- 期待実行時間: 約3秒

-- Step 1: 前日の impression イベントを取得（パーティション + クラスタリング活用）
WITH yesterday_impressions AS (
    SELECT
        campaign_id,
        user_id
    FROM `project.raw.events`
    WHERE occurred_at >= TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
      AND occurred_at <  TIMESTAMP(CURRENT_DATE())   -- パーティションプルーニング
      AND event_type = 'impression'                   -- クラスタリング活用
),

-- Step 2: 前日の注文をユーザー単位で集計（パーティションプルーニング）
yesterday_orders AS (
    SELECT
        user_id,
        SUM(amount) AS total_amount
    FROM `project.raw.orders`
    WHERE created_at >= TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
      AND created_at <  TIMESTAMP(CURRENT_DATE())
    GROUP BY user_id
),

-- Step 3: キャンペーンマスタと結合（小テーブルを先にフィルタ）
active_campaigns AS (
    SELECT campaign_id, campaign_name, start_date, end_date
    FROM `project.raw.campaigns`
    WHERE end_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
),

-- Step 4: impression × campaign の集計
impression_agg AS (
    SELECT
        i.campaign_id,
        APPROX_COUNT_DISTINCT(i.user_id) AS reached_users
    FROM yesterday_impressions i
    GROUP BY i.campaign_id
),

-- Step 5: 転換（impression → order）の集計
conversion_agg AS (
    SELECT
        i.campaign_id,
        APPROX_COUNT_DISTINCT(i.user_id) AS converted_users,
        SUM(o.total_amount) AS revenue
    FROM yesterday_impressions i
    INNER JOIN yesterday_orders o USING (user_id)
    GROUP BY i.campaign_id
)

-- 最終集計
SELECT
    c.campaign_id,
    c.campaign_name,
    c.start_date,
    c.end_date,
    imp.reached_users,
    COALESCE(conv.converted_users, 0)  AS converted_users,
    ROUND(
        SAFE_DIVIDE(COALESCE(conv.converted_users, 0), imp.reached_users),
        4
    ) AS cvr,
    COALESCE(conv.revenue, 0) AS revenue
FROM impression_agg imp
JOIN active_campaigns c USING (campaign_id)
LEFT JOIN conversion_agg conv USING (campaign_id)
ORDER BY revenue DESC

ポイント解説

WHERE DATE(e.occurred_at)
  = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
-- 関数をかけるとパーティション列が
-- 評価できず全スキャンになる

WHERE occurred_at >= TIMESTAMP(DATE_SUB(...))
  AND occurred_at <  TIMESTAMP(CURRENT_DATE())
-- 生のTIMESTAMP比較でプルーニングが機能

改善効果試算

実務への応用

• キャンペーン施策の翌朝レポートが遅い: 本問題と同じパターン。DATE(timestamp_col) = CURRENT_DATE() を書いてしまうと 全スキャンで月次コストが数万円膨らむ。
• コスト試算込みで提案: スキャン: 230GB → 4GB（$1.15/TB × 0.226TB = $0.26/日 → $0.004/日）。 チームに展開するときは「コスト試算込みで」提案すると刺さりやすい。
• APPROX使用の判断基準: CVR・リーチ数などのKPIレポートはAPPROX可。 請求・返金・課金系は必ず正確なCOUNT(DISTINCT)を使うこと。

次のステップ

• 参考: BigQuery documentation「Partition pruning」「Clustered table best practices」
• 参考: BigQuery Pricing（$6.25/TB オンデマンドスキャン）

今日のまとめ

自己評価

自分の回答

気づき・メモ