データエンジニアリング — dbt + BigQuery コスト最適化・Materialized View・Unit Tests

問題

dbt + BigQuery で運用している販促効果分析基盤の問題を分析・改善せよ。
月次レポートバッチが毎回 45分以上かかり、BigQuery クエリコストが月 60万円に達している。

テーブルの実態

現状の問題

1. mart_promotion_effect は毎日フルリフレッシュで TABLE として再作成
2. BigQuery オンデマンド料金: $5/TB
3. レポートで参照されるのは「直近30日間」のデータのみ（なのに全件スキャン）
4. stg_orders は日次 append だが、全件フルスキャンが走っている
5. dbt test がほぼ未設定（primary key テストのみ）

回答すべき4問

問題のある mart_promotion_effect.sql

📋 コピー-- 販促効果サマリ: 全期間の注文を対象に販促コードごとの効果を集計
WITH orders_with_promo AS (
  SELECT
    o.*,                       -- ← SELECT * でスキャンコスト最大化
    c.customer_segment,
    p.promotion_name,
    p.discount_rate
  FROM `project.dataset.stg_orders` o    -- ← パーティションなし・全件スキャン
  LEFT JOIN `project.dataset.stg_customers` c ON o.customer_id = c.customer_id
  LEFT JOIN `project.dataset.stg_promotions` p ON o.promotion_code = p.promotion_code
)
SELECT
  *,
  total_amount - (total_amount * discount_rate) AS net_revenue
FROM sales_summary
ORDER BY total_amount DESC

ヒント（段階的開示）

📋 コピー{{ config(
    materialized='incremental',
    partition_by={
      "field": "order_date",
      "data_type": "date",
      "granularity": "day"
    },
    incremental_strategy='insert_overwrite',
) }}

SELECT ...
{% if is_incremental() %}
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
{% endif %}

dbt レイヤー設計図

Q1. コスト試算

📋 コピー【現状スキャン量（1回あたり）】
stg_orders + stg_customers + stg_promotions
= 320GB + 4GB + 0.05GB ≒ 324GB ≒ 0.324TB/回

【月次コスト（30日稼働）】
0.324TB × $5/TB × 30日 = $48.6/月
≒ 約7,300円/月

※ 月60万円とかなり乖離あり
  → 実際には同モデルを複数のダウンストリームが参照
    （例: 10本のレポートモデルが各々フルスキャン
     0.324 × 10 × 30 × 150 = 約146万円）

【改善後スキャン量（直近30日フィルタ）】
stg_orders の30日分: 320GB / (3年 × 365日) × 30日
  ≒ 320GB / 1095 × 30 ≒ 8.8GB
合計: 8.8GB + 4GB + 0.05GB ≒ 12.85GB ≒ 0.013TB/回

【改善後月次コスト（単体）】
0.013TB × $5/TB × 30日 = $1.95/月 ≒ 約290円/月

【削減率】約96%（12.85GB / 324GB）

Q2. dbt モデル最適化

📋 コピー-- models/staging/stg_orders.sql（パーティション追加）
{{ config(
    materialized='incremental',
    partition_by={
      "field": "order_date",
      "data_type": "date",
      "granularity": "day"
    },
    cluster_by=["customer_id", "promotion_code"],
    incremental_strategy='insert_overwrite',
    on_schema_change='append_new_columns'
) }}

SELECT
    order_id,
    customer_id,
    promotion_code,
    order_amount,
    DATE(ordered_at) AS order_date,  -- パーティションキー
    ordered_at
FROM {{ source('raw', 'orders') }}

{% if is_incremental() %}
-- 差分取り込み: 昨日以降のデータのみ
WHERE DATE(ordered_at) >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY)
{% endif %}

📋 コピー-- models/intermediate/int_orders_last_30days.sql
-- 直近30日の注文のみを抽出（マートが参照するスコープを限定）
{{ config(materialized='view') }}

SELECT *
FROM {{ ref('stg_orders') }}
WHERE order_date >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 30 DAY)

📋 コピー-- models/marts/mart_promotion_effect.sql（最適化後）
{{ config(
    materialized='incremental',
    partition_by={
      "field": "report_date",
      "data_type": "date",
      "granularity": "day"
    },
    unique_key='promotion_code || "_" || customer_segment || "_" || report_date',
    incremental_strategy='insert_overwrite',
    on_schema_change='append_new_columns'
) }}

WITH orders AS (
    SELECT *
    FROM {{ ref('int_orders_last_30days') }}
    {% if is_incremental() %}
    AND order_date >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 30 DAY)
    {% endif %}
),

-- stg_promotions は50MBのため自動ブロードキャストJOIN（<256MB）が適用
customers AS (
    SELECT customer_id, customer_segment, registration_date
    FROM {{ ref('stg_customers') }}
),

promotions AS (
    SELECT promotion_code, promotion_name, discount_rate, start_date, end_date
    FROM {{ ref('stg_promotions') }}
),

orders_enriched AS (
    SELECT
        o.order_id, o.customer_id, o.promotion_code,
        o.order_amount, o.order_date,
        c.customer_segment,
        p.promotion_name, p.discount_rate
    FROM orders o
    LEFT JOIN customers c USING (customer_id)
    LEFT JOIN promotions p USING (promotion_code)
),

sales_summary AS (
    SELECT
        CURRENT_DATE('Asia/Tokyo')  AS report_date,
        promotion_code, promotion_name, discount_rate, customer_segment,
        COUNT(*)                    AS order_count,
        SUM(order_amount)           AS total_amount,
        AVG(order_amount)           AS avg_amount,
        COUNT(DISTINCT customer_id) AS unique_customers
    FROM orders_enriched
    GROUP BY 1, 2, 3, 4, 5
)

SELECT
    *,
    total_amount * (1 - discount_rate) AS net_revenue,
    SAFE_DIVIDE(total_amount, unique_customers) AS revenue_per_customer
FROM sales_summary

Q3. Materialized View の活用

📋 コピー-- models/marts/mart_promotion_effect_all_time.sql
{{ config(
    materialized='materialized_view',
    on_configuration_change='apply',
    enable_refresh=true,
    refresh_interval_minutes=60
) }}

SELECT
    promotion_code, promotion_name, discount_rate, customer_segment,
    COUNT(*)                    AS order_count,
    SUM(order_amount)           AS total_amount,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM {{ ref('stg_orders') }} o
LEFT JOIN {{ ref('stg_promotions') }} p USING (promotion_code)
LEFT JOIN {{ ref('stg_customers') }} c USING (customer_id)
GROUP BY 1, 2, 3, 4

Q4. dbt テスト設計

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

models:
  - name: mart_promotion_effect
    description: "直近30日の販促効果サマリ（日次更新）"
    columns:
      - name: promotion_code
        description: "販促コード"
        tests:
          - not_null                          # テスト1: NOT NULL
          - relationships:                    # テスト2: 参照整合性
              to: ref('stg_promotions')
              field: promotion_code

      - name: order_count
        tests:
          - not_null
          - dbt_utils.expression_is_true:     # テスト3: ビジネスルール
              expression: ">= 1"

      - name: total_amount
        tests:
          - dbt_utils.expression_is_true:     # テスト4: 売上は正の値
              expression: "> 0"

      - name: discount_rate
        tests:
          - dbt_utils.accepted_range:         # テスト5: 割引率 0〜1
              min_value: 0
              max_value: 1
              inclusive: true

    tests:
      - dbt_utils.unique_combination_of_columns:   # テスト6: 複合ユニーク
          combination_of_columns:
            - promotion_code
            - customer_segment
            - report_date

  # dbt Core 1.8 Unit Tests
  - name: mart_promotion_effect
    unit_tests:
      - name: test_net_revenue_calculation        # テスト7: Unit Test
          description: "net_revenue = total_amount * (1 - discount_rate) の計算検証"
          model: mart_promotion_effect
          given:
            - input: ref('int_orders_last_30days')
              rows:
                - {order_id: "O001", customer_id: "C001", promotion_code: "SALE10",
                   order_amount: 10000, order_date: "2026-05-01"}
                - {order_id: "O002", customer_id: "C002", promotion_code: "SALE10",
                   order_amount: 5000, order_date: "2026-05-02"}
            - input: ref('stg_promotions')
              rows:
                - {promotion_code: "SALE10", promotion_name: "10%OFFセール",
                   discount_rate: 0.1}
            - input: ref('stg_customers')
              rows:
                - {customer_id: "C001", customer_segment: "premium"}
                - {customer_id: "C002", customer_segment: "standard"}
          expect:
            rows:
              - {promotion_code: "SALE10", customer_segment: "premium",
                 order_count: 1, total_amount: 10000, net_revenue: 9000.0}
              - {promotion_code: "SALE10", customer_segment: "standard",
                 order_count: 1, total_amount: 5000, net_revenue: 4500.0}

ポイント解説

今日のまとめ

自己評価

自分の回答

気づき・メモ