データエンジニアリング — BigQuery コスト99%削減 × dbt incremental × partition pruning × clustering

概要

問題

Looker Studio に表示される「クーポン施策別 直近30日売上ダッシュボード」が、1回更新あたり 1.4TB スキャン・月額約 $21,000 という非常に高コストな状態。レポート表示まで平均45秒かかり苦情多発。dbt モデルを再設計してコスト99%削減＋応答1秒以下を実現せよ。

制約・前提条件

• BigQuery（リージョン: asia-northeast1）、dbt Core 1.8+
• raw.orders.order_date は STRING（'YYYY-MM-DD' 形式）で保存されている
• 上流の raw.orders テーブル定義は変更できない（外部システムが書き込み）
• 営業時間中（JST 9:00〜21:00）のダッシュボード遅延ゼロが必須
• 目標: 1回スキャン 100MB 以下・月額コスト $1,000 以下

悪いコード (Before)

📋-- ❌ materialized='view' で毎回再計算
{{ config(materialized='view') }}

SELECT
    o.order_date,
    c.coupon_code,
    c.campaign_name,
    SUM(oi.quantity * oi.unit_price) AS gross_sales,
    SUM(oi.quantity * oi.unit_price
        - oi.discount_amount)         AS net_sales,
    COUNT(DISTINCT o.order_id)        AS order_count,
    COUNT(DISTINCT o.user_id)         AS unique_users
FROM `prj-prod.raw.orders` o          -- 350GB
LEFT JOIN `prj-prod.raw.order_items` oi
    ON o.order_id = oi.order_id       -- 1.2TB
LEFT JOIN `prj-prod.raw.coupons` c
    ON o.coupon_id = c.coupon_id
-- ❌ CAST(order_date AS DATE) で
--    partition pruning が無効化
WHERE CAST(o.order_date AS DATE)
    >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'),
                INTERVAL 30 DAY)
GROUP BY 1, 2, 3
-- ❌ ビュー内 ORDER BY は呼び出し側で
--    再ソートされ無意味かつ高コスト
ORDER BY o.order_date DESC,
         gross_sales DESC

📋-- ❌ ビュー越しに raw を毎回フルスキャン
-- ❌ WHERE order_date 指定がないため
--    上流の 30日フィルタしか効かず
--    実質 350GB + 1.2TB を毎回読む
SELECT *
FROM `prj-prod.marts.mart_coupon_daily_sales`
WHERE campaign_name = @campaign

-- 結果:
--   1回スキャン: 1.4 TB
--   料金:       $7 / 回
--   1日 100 回 × 30 日 = $21,000 / 月
--   表示時間:   平均 45 秒

ヒント（段階的開示）

{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={
        'field': 'order_date',
        'data_type': 'date',
        'granularity': 'day'
    },
    cluster_by=['campaign_name', 'coupon_code']
) }}

SELECT
    order_date, coupon_code, campaign_name,
    SUM(gross_sales) AS gross_sales,
    ...
FROM {{ ref('int_coupon_orders_joined') }}
{% if is_incremental() %}
WHERE order_date
    >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'),
                INTERVAL 3 DAY)
{% endif %}
GROUP BY order_date, coupon_code, campaign_name

問題点分析

アーキテクチャ図 — Before（view 1段）vs After（3層 + incremental）

模範解答 — dbt モデル群

1. staging層: stg_orders.sql

📋{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={
        'field': 'order_date',
        'data_type': 'date',
        'granularity': 'day'
    },
    cluster_by=['user_id', 'coupon_id'],
    on_schema_change='append_new_columns'
) }}

-- 目的: raw.orders（STRING型 order_date）を DATE 型に正規化し、
--       partition pruning が効く形に再パーティション化する
SELECT
    PARSE_DATE('%Y-%m-%d', order_date) AS order_date,  -- ✅ STRING → DATE
    order_id,
    user_id,
    coupon_id,
    order_status,
    created_at
FROM {{ source('raw', 'orders') }}
{% if is_incremental() %}
-- 遅延データ対応で3日分を再計算（insert_overwrite で重複排除）
WHERE PARSE_DATE('%Y-%m-%d', order_date)
    >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 3 DAY)
{% endif %}

2. staging層: stg_order_items.sql

📋{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={'field': 'order_date', 'data_type': 'date', 'granularity': 'day'},
    cluster_by=['order_id']
) }}

-- order_items を order_date でパーティション化（結合時の pruning 用）
SELECT
    oi.order_id,
    PARSE_DATE('%Y-%m-%d', o.order_date) AS order_date,
    oi.product_id,
    oi.quantity,
    oi.unit_price,
    oi.discount_amount
FROM {{ source('raw', 'order_items') }} oi
INNER JOIN {{ source('raw', 'orders') }} o USING (order_id)
{% if is_incremental() %}
WHERE PARSE_DATE('%Y-%m-%d', o.order_date)
    >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 3 DAY)
{% endif %}

3. intermediate層: int_coupon_orders_joined.sql

📋{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={'field': 'order_date', 'data_type': 'date', 'granularity': 'day'},
    cluster_by=['coupon_code', 'campaign_name']
) }}

-- 目的: orders × order_items × coupons を結合して mart 用の事前集計を作る
WITH orders AS (
    SELECT * FROM {{ ref('stg_orders') }}
    {% if is_incremental() %}
    WHERE order_date >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 3 DAY)
    {% endif %}
),
items AS (
    SELECT * FROM {{ ref('stg_order_items') }}
    {% if is_incremental() %}
    WHERE order_date >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 3 DAY)
    {% endif %}
),
coupons AS (
    -- 50万行のディメンション。マスタなので JOIN 全件 OK
    SELECT coupon_id, coupon_code, campaign_name
    FROM {{ source('raw', 'coupons') }}
)
SELECT
    o.order_date,
    o.order_id,
    o.user_id,
    c.coupon_code,
    c.campaign_name,
    i.quantity * i.unit_price                       AS gross_sales,
    i.quantity * i.unit_price - i.discount_amount   AS net_sales
FROM orders o
-- ✅ パーティション列を結合キーに含めて pruning 強化
INNER JOIN items   i USING (order_id, order_date)
LEFT  JOIN coupons c USING (coupon_id)

4. mart層（日次累積）: mart_coupon_daily_sales.sql

📋{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={'field': 'order_date', 'data_type': 'date', 'granularity': 'day'},
    cluster_by=['campaign_name', 'coupon_code']
    -- ✅ ORDER BY は削除。ソートはダッシュボード側で行う
) }}

-- 目的: クーポン×日別の売上を事前集計。過去全期間を保持。
SELECT
    order_date,
    coupon_code,
    campaign_name,
    SUM(gross_sales)           AS gross_sales,
    SUM(net_sales)             AS net_sales,
    COUNT(DISTINCT order_id)   AS order_count,
    COUNT(DISTINCT user_id)    AS unique_users
FROM {{ ref('int_coupon_orders_joined') }}
{% if is_incremental() %}
WHERE order_date >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 3 DAY)
{% endif %}
GROUP BY order_date, coupon_code, campaign_name

5. ダッシュボード参照面: mart_coupon_sales_last30d.sql

📋{{ config(
    materialized='table',  -- ✅ 30日窓の小テーブル（数MB）。毎日 dbt run で再生成
    cluster_by=['campaign_name', 'coupon_code']
) }}

-- 目的: Looker Studio が直接参照する固定窓テーブル。
--       mart_coupon_daily_sales から直近30日のみを切り出す。
SELECT
    order_date,
    coupon_code,
    campaign_name,
    gross_sales,
    net_sales,
    order_count,
    unique_users
FROM {{ ref('mart_coupon_daily_sales') }}
WHERE order_date >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 30 DAY)
  AND order_date <  CURRENT_DATE('Asia/Tokyo')

6. Looker Studio 側のクエリ（改善後）

📋-- ✅ 数MBの小テーブルを参照。
-- ✅ cluster_by が効いて campaign_name フィルタ時のスキャン量がさらに削減
SELECT *
FROM `prj-prod.marts.mart_coupon_sales_last30d`
WHERE campaign_name = @campaign

月額コスト試算（Before / After）

※ BigQuery オンデマンド料金 $5/TB として試算。リージョン: asia-northeast1。

ポイント解説

実務への応用

• 既存の Looker Studio ダッシュボードを棚卸しし、raw を直接参照しているものを mart に差し替える（コスト削減効果が大きい順）
• BigQuery の INFORMATION_SCHEMA.JOBS_BY_PROJECT を SUM して、過去30日のクエリコスト Top10 を抽出 → 改善対象を優先順位付け
• DataDog の BigQuery インテグレーションで bigquery.query.total_bytes_billed をユーザー別にダッシュボード化し、コスト所有者を明確化
• dbt の --full-refresh フラグはコストが膨大になるので 本番では原則禁止。スキーマ変更時のみ手動実行
• partition_by を後から付けるのは難しい（CREATE TABLE 時のみ設定可能）ので、新規モデルは必ず初回から付ける

今日のまとめ

自己評価

自分の回答

気づき・メモ