E 会計/ファイナンス・コスト効率 — INFORMATION_SCHEMA.JOBS 診断 × Flat-Rate Slot 損益分岐点 × BigQuery パーティション94%削減（Bad→Good）

📋 コピー============================================================
損益分岐点計算: Flat-Rate Slot vs オンデマンド
============================================================

【前提条件】
  オンデマンド料金 : $5/TB
  Slot 予約 100 Slot: $1,700/月（asia-northeast1・固定）
  最重量クエリ     : 1.2 TB/回（現状 150 回/月）

【オンデマンドコスト（現状）】
  月間スキャン量: 1.2 TB/回 × 150 回 = 180 TB/月
  月次コスト   : 180 TB × $5/TB    = $900/月

【損益分岐点（月間スキャン量）】
  条件: オンデマンドコスト = Slot コスト
  → 月間スキャン量(TB) × $5 = $1,700
  → 月間スキャン量 = $1,700 ÷ $5 = 340 TB/月

【損益分岐点（月間クエリ回数）】
  → 340 TB ÷ 1.2 TB/回 = 283.3 回/月 ≈ 284 回/月

【現状の判断】
  現状クエリ回数: 150 回/月
  損益分岐点    : 284 回/月
  → 現状 150 回 < BEP 284 回
  → オンデマンド $900/月 < Slot $1,700/月
  → Slot 予約は割高: $800/月 余計にかかる

【改善後の判断（パーティション設定後）】
  改善後スキャン: 0.05 TB/回 × 150 回 = 7.5 TB/月
  改善後コスト : 7.5 TB × $5         = $37.5/月
  → Slot $1,700/月 と比べて 45 倍安い
  → パーティション改善後は Slot 予約を検討する余地なし

【Slot 予約が有利になる条件（今後の成長予測）】
  月間スキャン量 > 340 TB/月 かつ
  クエリパターンが予測可能で安定している場合
  ※ プロジェクト全体のスキャン量合計で判断すること
     (単一クエリの最適化後はほぼ到達しない)

📋 コピー-- ============================================================
-- Bad: 非パーティションテーブルへの TIMESTAMP フィルタ
-- ============================================================
-- 問題点:
--   1. order_events テーブルはパーティション未設定（18ヶ月・900 GB）
--   2. WHERE created_at は TIMESTAMP 型関数フィルタ → 剪定不可
--   3. 毎回 900 GB（実効 1.2 TB）フルスキャン確定
--   4. コスト: 1.2 TB × $5 × 150 回/月 = $900/月

SELECT
  order_id,
  campaign_id,
  SUM(amount)   AS total_amount,
  COUNT(*)      AS order_count
FROM `project.mops_dataset.order_events`   -- 非パーティション・900 GB
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  -- ↑ TIMESTAMP型フィルタ → パーティション剪定不可
GROUP BY order_id, campaign_id

📋 コピー-- ============================================================
-- Good: dbt incremental + DATE パーティション + クラスタリング
-- ============================================================
-- models/mart/mart_order_campaign_monthly.sql

{{
  config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',

    -- パーティション: event_date（DATE型）で日次分割
    -- → 30 日フィルタで 50 GB/回（900 GB の 5.6%）のみスキャン
    partition_by={
      'field': 'event_date',
      'data_type': 'date',
      'granularity': 'day'
    },

    -- クラスタリング: WHERE / GROUP BY で使うカラムを指定
    -- → パーティション剪定後さらにブロックスキャンを絞る
    cluster_by=['campaign_id', 'order_id'],

    on_schema_change='fail'  -- スキーマ変更は明示的に対応（サイレント変更禁止）
  )
}}

WITH source AS (
  SELECT
    -- パーティションキー（DATE型 → TIMESTAMP より確実に剪定が効く）
    DATE(created_at)                         AS event_date,
    order_id,
    campaign_id,
    amount,
    status,
    COALESCE(segment_id, 'unknown')          AS segment_id  -- NULL 保護
  FROM {{ ref('stg_order_events') }}

  {% if is_incremental() %}
  -- パーティション剪定: 直近 30 日分のみスキャン
  -- MAX(event_date) ベースにすることで追いつき遅延に対応
  WHERE DATE(created_at) >= DATE_SUB(
    (SELECT MAX(event_date) FROM {{ this }}),
    INTERVAL 30 DAY
  )
  {% endif %}
)

SELECT
  event_date,                                                    -- パーティションキー
  campaign_id,
  order_id,
  segment_id,

  SUM(amount)                                                    AS total_amount,
  COUNT(*)                                                       AS order_count,

  -- SAFE_DIVIDE: ゼロ除算ガード（全キャンセルで order_count=0 のケース対策）
  SAFE_DIVIDE(SUM(amount), COUNT(*))                             AS avg_order_value,

  -- ステータス別カウント（キャンセル率の監視用）
  COUNTIF(status = 'completed')                                  AS completed_count,
  COUNTIF(status = 'cancelled')                                  AS cancelled_count,

  CURRENT_TIMESTAMP()                                            AS _loaded_at
FROM source
GROUP BY event_date, campaign_id, order_id, segment_id

スキャン量削減の確認方法

📋 コピー-- dbt 実行前: --dry-run でスキャン量を事前確認
-- (dbt v1.8+ で experimental サポート)

-- BigQuery Console で直接確認する場合:
-- クエリ右上の「処理されるデータ」に表示されるバイト数を確認

-- INFORMATION_SCHEMA で改善効果を検証（改善後 1 週間後に実行）
SELECT
  DATE(creation_time)                                            AS run_date,
  ROUND(AVG(total_bytes_processed) / 1e12, 4)                   AS avg_tb_per_job,
  ROUND(AVG(total_bytes_processed) / 1e12 * 5, 4)               AS avg_cost_per_job
FROM `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS
WHERE
  DATE(creation_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY)
  AND LOWER(query) LIKE '%mart_order_campaign_monthly%'
  AND state = 'DONE' AND error_result IS NULL
GROUP BY run_date
ORDER BY run_date

施策別コスト試算サマリー

ROI 試算（全ステークホルダー向け）

📋 コピー【実施コスト（パーティション設定 + dbt 移行）】
  3 人日 × 8h × 6,000円/h = 144,000円（≈ $960）

【月次削減額】
  $862.5/月 × 150円/$ = 129,375円/月

【ペイバック期間】
  144,000円 ÷ 129,375円/月 = 1.1 ヶ月

【年間 ROI】
  年間削減額: $862.5 × 12 = $10,350（≈ 1,552,500円）
  年間 ROI  : ($10,350 - $960) / $960 = 977%

【ステークホルダー別の伝え方】
  CFO 向け:
    「$960 の実施コスト（3 人日）で年間 $10,350 の削減。
     年間 ROI 977%。S&P 500 の約 97 倍の投資効果。
     さらに Slot 予約（$1,700/月・割高）の誤採択を防ぎ
     年間追加 $10,200 の損失回避にもなる。」

  CTO 向け:
    「3 人日（dbt モデル追加 + --full-refresh）で月次 95.8%削減。
     ペイバック 1.1 ヶ月。INFORMATION_SCHEMA 監視ジョブも
     1 人日で追加でき、次回のコスト爆増を翌日に検知できる。」

  エンジニア向け:
    「dbt config に partition_by + cluster_by を追加し、
     WHERE フィルタを created_at → event_date DATE型に変更するだけ。
     --full-refresh 1 回でパーティション構造が再構築され
     翌日から 94% コスト削減が確認できる。」

【スキャン削減率の内訳】
  改善前スキャン: 900 GB（18 ヶ月分フルスキャン）
  改善後スキャン: 50 GB（30 日分のみ）
  削減率       : (900 - 50) / 900 = 94.4%

  ※ 1.2 TB/回 は BigQuery の実効スキャン量（
     結合後・列プロジェクション前）のため
     50 GB × (1.2/0.9) ≈ 67 GB が正確な改善後スキャン量
     → $5/TB × 0.067 TB × 150 回 = $50.3/月（94.4%削減）

リスクと対策