E 会計/ファイナンス・コスト効率 — GKE Autopilot × Argo Workflows GCPコスト 48%削減設計

📋 コピー-- models/mart/mart_campaign_result.sql
-- パーティション剪定 + incremental で 97%コスト削減
{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={
        'field': 'order_date',
        'data_type': 'date',
        'granularity': 'day'
    },
    cluster_by=['campaign_id']  -- GROUP BY 対象カラムでクラスタリング
) }}

{% set LOOKBACK_DAYS = 3 %}  {# 遅延データ考慮: 3日窓 #}

SELECT
    DATE(created_at)            AS order_date,   -- パーティションキー
    campaign_id,
    COUNT(*)                    AS order_count,
    COALESCE(SUM(revenue), 0.0) AS total_revenue  -- NULL安全: SUM(NULL)=NULL を防ぐ
FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
-- パーティション剪定: 直近3日のみスキャン（500GB → 15GB）
WHERE DATE(created_at) >= DATE_SUB(
    CURRENT_DATE(), INTERVAL {{ LOOKBACK_DAYS }} DAY
)
{% endif %}
GROUP BY 1, 2

📋 コピー-- INFORMATION_SCHEMA でジョブ別スキャン量監視
-- 「コスト爆弾ジョブ」を早期に特定するための日次クエリ
SELECT
  job_id,
  user_email,
  query,
  ROUND(total_bytes_processed / POW(1024, 4), 3) AS scanned_tb,
  ROUND(total_bytes_processed / POW(1024, 4) * 5, 2) AS cost_usd,
  creation_time
FROM `project.region-asia-northeast1`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND job_type = 'QUERY'
ORDER BY total_bytes_processed DESC
LIMIT 20;

📋 コピー# 削減試算 — BigQuery
現状: $5/TB × 500GB/回 × 1,024回/月 = $2,560/月

改善後:
  スキャン量: 500GB → 15GB（97%削減）
  月額: $5/TB × 15GB/回 × 1,024回 = $76.8/月

削減額: $2,560 - $76.8 = $2,483.2/月 ✅（97%削減）

📋 コピー// terraform/storage_lifecycle.tf
resource "google_storage_bucket" "argo_artifacts" {
  name     = "mops-argo-artifacts"
  location = "ASIA-NORTHEAST1"

  // 90日超 → Nearline に移行（アクセス頻度低下後）
  lifecycle_rule {
    action {
      type          = "SetStorageClass"
      storage_class = "NEARLINE"
    }
    condition {
      age                   = 90  # 日数
      matches_storage_class = ["STANDARD"]
    }
  }

  // 365日超 → Coldline へ（ほぼアクセスなし）
  // 注意: Coldline は最小保存期間 90日。90日未満で削除すると
  //       早期削除料金が発生するため、長期保存データにのみ適用
  lifecycle_rule {
    action {
      type          = "SetStorageClass"
      storage_class = "COLDLINE"
    }
    condition {
      age                   = 365
      matches_storage_class = ["NEARLINE"]
    }
  }

  // 3年超は削除（監査ログは別途 GCS Audit Log へ）
  lifecycle_rule {
    action { type = "Delete" }
    condition {
      age = 1095  # 3年 = 365 × 3
    }
  }
}

📋 コピー# 削減試算 — Cloud Storage
対象オブジェクト: 3,200 GB × 0.6（90日超率）= 1,920 GB

移行前（Standard $0.020/GB）: 1,920 × $0.020 = $38.4/月
移行後（Nearline $0.010/GB）: 1,920 × $0.010 = $19.2/月
削減: $19.2/月 ✅

TTL 1095日設定: 3年後に削除 → 長期的な青天井蓄積防止
将来月次削減（TTL効果）: 毎月追加される90日超分が順次削減

📋 コピー// terraform/artifact_registry.tf
// クリーンアップポリシー: 最新5世代 KEEP + 残りは DELETE
resource "google_artifact_registry_cleanup_policy" "keep_latest_5" {
  project    = var.project_id
  location   = "asia-northeast1"
  repository = "mops-images"
  policy_id  = "keep-latest-5"

  action = "DELETE"

  // 最新5件を KEEP し、残りを削除
  most_recent_versions {
    keep_count = 5  # 最新5世代を保持
  }
}

// 未タグ（ダングリング）イメージは7日後に削除
resource "google_artifact_registry_cleanup_policy" "delete_untagged" {
  project    = var.project_id
  location   = "asia-northeast1"
  repository = "mops-images"
  policy_id  = "delete-untagged-7d"

  action = "DELETE"

  condition {
    tag_state  = "UNTAGGED"
    older_than = "604800s"  # 7日 = 7 × 24 × 3600
  }
}

📋 コピー# 既存の古いイメージを即時一括削除（初回クリーンアップ）
# 30日以上前のイメージを全削除
gcloud artifacts docker images list \
  asia-northeast1-docker.pkg.dev/${PROJECT_ID}/mops-images \
  --format="get(version)" \
  --filter="createTime < $(date -d '30 days ago' +%Y-%m-%dT%H:%M:%SZ)" \
  | xargs -I{} gcloud artifacts docker images delete {} --quiet

📋 コピー# 削減試算 — Artifact Registry
削除対象: 500 GB（古世代イメージ）
Standard ストレージ単価: $0.10/GB/月

削減: 500 × $0.10 = $50/月 ✅
クリーンアップポリシーで今後の蓄積も自動防止

📋 コピー━━ リスクマトリクス（発生確率 × 影響度）━━

【高優先度: 即対応】

リスク①: Spot Node 停止でバッチが中断する
  発生確率: 中（Spot は24時間以内停止の可能性）
  影響: 大（本番バッチ失敗 → ビジネス影響）
  対策:
    - retryStrategy: limit=3, retryPolicy=OnError を全テンプレートに設定
    - バッチ完了チェック: Argo のアラート設定（DataDog で失敗通知 5分以内）
    - フォールバック: 朝06:00バッチは通常ノードで実行（重要度最大）
    - 段階展開: 最初は夜間 22:00 バッチのみ Spot に移行して検証

リスク②: BigQuery パーティション設計変更でジョブが失敗する
  発生確率: 中（dbt model の変更は既存ダウンストリームへの影響）
  影響: 大（mart テーブルが空になると Looker Studio がエラー）
  対策:
    - dbt test を CI（GitHub Actions）に追加
    - --full-refresh を staging 環境で先行実行・確認
    - ダウンストリーム依存 (ref()) を一覧化してから変更開始

【中優先度: Phase 2 以降で対応】

リスク③: Cloud Storage ライフサイクル移行後にデータ取り出し料金
  Nearline は 30日以内の削除で早期削除料金発生
  対策: 90日超データのみ移行条件に設定（条件を厳しくして確実に防ぐ）

リスク④: Artifact Registry クリーンアップで本番イメージが削除される
  対策: prod タグを別リポジトリに分離、または keep_count=10 で保守的に開始

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━