E 会計/ファイナンス・コスト効率 — GCP コスト最適化 Bad→Good 6点（BigQuery スキャン削減 × Cloud Run CPU idle × GKE Spot+Autoscaler × ROI 779% ペイバック 46日 × CFO 提案）

📋 コピー# BigQuery コスト最適化: dbt モデル変更例

# ── Bad: パーティション・クラスタリングなし ──────────────────
# models/mart/campaign_summary.sql（Bad）
{{
  config(
    materialized='table'
    # partition_by なし → 全スキャン確定
  )
}}
SELECT
  *  # 問題②: SELECT * で不要列もスキャン
FROM {{ ref('stg_campaigns') }} c
JOIN {{ ref('stg_orders') }} o ON c.campaign_id = o.campaign_id
WHERE c.status = 'active'  # partition pruning が効かない

# ── Good: partition_by + cluster_by + 必要列 SELECT ─────────
# models/mart/campaign_summary.sql（Good）
{{
  config(
    materialized='incremental',
    # 修正①: ingestion_time パーティションで partition pruning を有効化
    partition_by={
      "field": "report_date",
      "data_type": "date",
      "granularity": "day"
    },
    # cluster_by で同一パーティション内をさらに絞る
    cluster_by=["member_rank", "campaign_id"],
    on_schema_change='fail'
  )
}}
SELECT
  -- 修正②: 必要列のみ明示的に SELECT（列プルーニング有効）
  c.campaign_id,
  c.campaign_name,
  c.member_rank,
  DATE(o.created_at) AS report_date,  -- partition 列
  COUNT(o.order_id)  AS order_count,
  SUM(o.amount)      AS total_amount,
  AVG(o.amount)      AS avg_order_value
FROM {{ ref('stg_campaigns') }} c
JOIN {{ ref('stg_orders') }} o
  ON c.campaign_id = o.campaign_id
  -- partition pruning: WHERE に partition 列（report_date）を指定
  AND DATE(o.created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
WHERE c.status = 'active'
  AND c.member_rank IN ('platinum', 'gold', 'silver', 'bronze')

{% if is_incremental() %}
  -- incremental: 過去 3日分のみ再計算（フルスキャン回避）
  AND DATE(o.created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
{% endif %}

# スキャン量変化:
# Before: 800GB/クエリ × 20クエリ/日 × 30日 = 480TB/月 → $2,400
# After:  80GB/クエリ × 20クエリ/日 × 30日 = 48TB/月 → $240（-90%）
# SELECT * 廃止でさらに 30〜50% 削減可能

# INFORMATION_SCHEMA 監視クエリ（日次実行）
SELECT
  user_email,
  query,
  total_bytes_processed / POW(1024, 3) AS gb_scanned,  -- GB 換算
  total_bytes_processed / POW(1024, 4) * 5 AS cost_usd  -- $5/TB
FROM `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS
WHERE DATE(creation_time) = CURRENT_DATE()
  AND statement_type = 'SELECT'
ORDER BY total_bytes_processed DESC
LIMIT 10  -- Top 10 スキャン量クエリを監視

📋 コピー# ── Cloud Run: CPU idle 化 ────────────────────────────────
# terraform/cloud_run.tf（Bad → Good）

# Bad: CPU always-on
resource "google_cloud_run_v2_service" "campaign_api_bad" {
  template {
    containers {
      resources {
        cpu_idle = false  # always-on: アイドル中も CPU 課金
      }
    }
    scaling {
      min_instance_count = 3  # 常時 3台起動
      max_instance_count = 20
    }
  }
}

# Good: CPU idle + min-instances: 1
resource "google_cloud_run_v2_service" "campaign_api" {
  template {
    containers {
      resources {
        limits = { cpu = "2", memory = "1Gi" }
        cpu_idle          = true  # 修正③: アイドル中 CPU 無課金
        startup_cpu_boost = true  # 起動時のみ CPU ブースト（レイテンシ改善）
      }
    }
    scaling {
      min_instance_count = 1  # コールドスタート防止に 1台のみ常時起動
      max_instance_count = 20
    }
  }
}

# ── GKE: Spot + Cluster Autoscaler ──────────────────────────
# terraform/gke.tf（Bad → Good）

# Bad: On-Demand 固定ノードプール
resource "google_container_node_pool" "batch_bad" {
  node_count = 5  # 固定 5台
  node_config {
    machine_type = "n2-standard-8"
    # Spot なし / On-Demand
  }
}

# Good: Spot + Cluster Autoscaler
resource "google_container_node_pool" "batch_spot" {
  # 修正④: Cluster Autoscaler でバッチ時間帯のみスケール
  autoscaling {
    min_node_count = 0  # アイドル時はゼロ台
    max_node_count = 8  # バッチ最大 8台（余裕）
  }
  node_config {
    machine_type = "n2-standard-8"
    spot         = true  # Spot インスタンス（最大 91% 安）
    # Spot 中断時の taint で Argo Workflows が retryStrategy でリカバリ
    taint {
      key    = "cloud.google.com/gke-spot"
      value  = "true"
      effect = "NO_SCHEDULE"
    }
  }
}

# Argo Workflows: Spot 中断対応
# workflow.yaml
# retryStrategy:
#   limit: 3
#   retryPolicy: OnError  # Spot 中断（preemption）時も再試行
#   backoff:
#     duration: "30s"
#     factor: 2

# ── Cloud Storage: ライフサイクルルール ──────────────────────
# terraform/storage.tf

resource "google_storage_bucket" "mops_logs" {
  name     = "mops-logs-prod"
  location = "asia-northeast1"
  # 修正⑤: ライフサイクルルールで自動ダウングレード
  lifecycle_rule {
    condition { age = 30 }  # 30日後: Standard → Nearline
    action { type = "SetStorageClass"; storage_class = "NEARLINE" }
  }
  lifecycle_rule {
    condition { age = 90 }  # 90日後: Nearline → Coldline
    action { type = "SetStorageClass"; storage_class = "COLDLINE" }
  }
  lifecycle_rule {
    condition { age = 365 }  # 1年後: Coldline → 削除（ログのみ）
    action { type = "Delete" }
  }
}

# Cloud Functions: メモリ・インスタンス最適化
resource "google_cloudfunctions2_function" "event_processor" {
  build_config { ... }
  service_config {
    # 修正⑥: 2GB → 512MB、max-instances 100 → 20
    available_memory   = "512Mi"  # 実績ピーク: 320MB / マージン 60%
    max_instance_count = 20       # 実績最大 12 の 1.67倍
    min_instance_count = 0        # コスト最小化（コールドスタート許容）
  }
}

📋 コピー## ROI・ペイバック計算

### 削減施策サマリー
┌─────────────────────────────────────┬──────────┬──────────┬──────────┬──────────┐
│ 施策                                │ 現状/月  │ 最適化後  │ 削減額   │ 削減率   │
├─────────────────────────────────────┼──────────┼──────────┼──────────┼──────────┤
│ ① BigQuery partition_by + SELECT最適化│ $4,800   │ $960     │ -$3,840  │ -80%     │
│ ② Cloud Run cpu_idle + min-inst: 1  │ $1,240   │ $434     │ -$806    │ -65%     │
│ ③ GKE Spot + Cluster Autoscaler     │ $2,160   │ $648     │ -$1,512  │ -70%     │
│ ④ Cloud Storage Lifecycle           │ $890     │ $445     │ -$445    │ -50%     │
│ ⑤ Cloud Functions 512MB / max: 20   │ $340     │ $153     │ -$187    │ -55%     │
│ ⑥ Cloud Armor + LB（変更なし）       │ $280     │ $280     │ $0       │ 0%       │
├─────────────────────────────────────┼──────────┼──────────┼──────────┼──────────┤
│ 合計                                │ $9,710   │ $4,840   │ -$4,870  │ -50%     │
└─────────────────────────────────────┴──────────┴──────────┴──────────┴──────────┘

### 実装コスト内訳
BigQuery 最適化（dbt config + クエリ書き換え）: 3日
Cloud Run Terraform 変更 + カナリア検証:         1日
GKE Spot + Autoscaler + Argo retry 設定:        4日
Cloud Storage ライフサイクル設定:                1日
Cloud Functions リソース変更 + 負荷テスト:       1日
Cloud Billing Budget アラート設定:              0.5日
─────────────────────────────────────────────────
合計: 10.5日 ≒ 0.5ヶ月（エンジニア 1名）
実装コスト: $15,000 × 0.5 = $7,500

### ROI 計算（12ヶ月ベース）
年間削減額  = $4,870/月 × 12 = $58,440
ROI         = $58,440 ÷ $7,500 = 7.79 = 779%
ペイバック  = $7,500 ÷ $4,870 = 1.54ヶ月（約 46日）

### 優先順位（ICE ライクスコア）
施策            | Impact | Ease | Priority
BigQuery 最適化 |  高    |  中  | 最優先（削減額最大）
GKE Spot       |  高    |  中  | 次点（工数中）
Cloud Run idle  |  中    |  高  | 即実施（設定変更のみ）
Storage LF      |  中    |  高  | 即実施（Terraform 変更）
Functions       |  低    |  高  | 最後（削減額最小）