会計/ファイナンス・コスト効率 — BigQuery ストレージコスト 50%削減設計

概要

問題

ECサイトMOpsチームの BigQuery ストレージ月額コスト（$1,044/月）を $522 以下（50%削減）に抑えよ。過去6ヶ月で3.2倍に膨張しており、月次コストレビューで緊急対応が求められている。

現状の BigQuery ストレージ構成

追加調査で判明した課題

1. 全テーブルで「論理バイト課金（デフォルト）」を使用。圧縮率が高いテーブルが多い
2. dbt intermediate/ レイヤーに 180日以上未更新 テーブルが23個（合計 6.2 TB）
3. Argo Workflows ログテーブル（argo_log_events）に JSON 丸ごと INSERT → 月 3.2 TB 蓄積・削除なし
4. 分析クエリの 90%は 直近30日 のデータのみ参照

ヒント（段階的開示）

現状: 3.2 TB/月 蓄積 × 削除なし = 青天井

TTL 設定後（30日パーティション期限）:
  定常サイズ = 3.2 TB（30日分のみ保持）
  月額: 3,277 GB × $0.020 = $65.54/月
  削減: 約 $131/月（青天井が固定コストに）

スキーマ最適化（必要5カラムのみ）:
  4.2 KB/行 → 0.8 KB/行（81%削減）
  3.2 TB/月 → 0.61 TB/月
  定常30日分: 625 GB × $0.020 = $12.50/月
  さらに $53/月 削減

合計削減: $196.60 + $62 + $131 + $53 = $442.60/月
削減後月額: $1,044.48 - $442.60 = $601.88
→ 目標 $522 まであと $79.88 → 追加施策が必要

Argo ログ設計 — Bad vs Good（スキーマ最適化）

📋def insert_argo_log_bad(event: dict) -> None:
    """JSON全体を1行でINSERT。
    問題点:
    1. 1行 4.2 KB → 8億行/月 = 3.2 TB/月
    2. 検索・集計に不要なネスト構造
    3. スキーマなし → 型検証不可
    4. TTL（パーティション期限）未設定
       → 削除なしで青天井に蓄積
    """
    client.insert_rows_json(
        "project.dataset.argo_log_events",
        [{
            "raw_json": json.dumps(event),  # 全フィールドを1列に
            "inserted_at": datetime.utcnow().isoformat(),
        }],
    )

📋from dataclasses import dataclass
from datetime import datetime
from typing import Final
from google.cloud import bigquery

# テーブル名を定数化（マジックストリング排除）
ARGO_LOG_TABLE: Final[str] = (
    "project.dataset.argo_log_events_v2"
)

@dataclass(frozen=True, slots=True)
class ArgoLogRow:
    """BigQuery に書き込む Argo ログの最小スキーマ。
    frozen=True: 不変性を保証（誤上書き防止）
    slots=True:  メモリ効率向上（大量生成時に有効）
    """
    workflow_id: str  # ワークフロー識別子
    step_name: str    # ステップ名
    status: str       # Succeeded / Failed / Running
    message: str      # エラーメッセージ（最大2048文字）
    started_at: str   # ISO 8601 UTC

def insert_argo_log(
    event: dict,
    client: bigquery.Client,
) -> None:
    """必要な5カラムのみ抽出して BigQuery に書き込む。

    Args:
        event: Argo Workflows のイベント辞書（生 JSON）。
        client: 初期化済みの BigQuery クライアント。

    Returns:
        None。エラーは RuntimeError を送出。
    """
    row = ArgoLogRow(
        workflow_id=(
            event.get("metadata", {}).get("uid", "")
        ),
        step_name=(
            event.get("status", {})
                 .get("nodes", {})
                 .get("displayName", "")
        ),
        status=(
            event.get("status", {})
                 .get("phase", "Unknown")
        ),
        # 2048文字でトリム（カラムサイズ制御）
        message=event.get("message", "")[:2048],
        started_at=(
            event.get("status", {})
                 .get("startedAt",
                      datetime.utcnow().isoformat())
        ),
    )
    errors = client.insert_rows_json(
        ARGO_LOG_TABLE, [row.__dict__]
    )
    if errors:
        raise RuntimeError(
            f"BigQuery insert failed: {errors}"
        )

コスト削減フロー図 — 施策別インパクト

模範解答

施策1: 物理バイト課金への移行（工数: 0.5人日）

📋# アクティブストレージ移行候補（8 TB 論理）
論理サイズ: 8,192 GB
物理サイズ: 8,192 × 0.26 = 2,130 GB
移行後コスト: 2,130 × $0.040 = $85.20/月
現状コスト:  8,192 × $0.020 = $163.84/月
削減額: $163.84 - $85.20 = $78.64/月 ✅

# 長期ストレージ移行候補（24 TB 論理）
論理サイズ: 24,576 GB
物理サイズ: 24,576 × 0.26 = 6,390 GB
移行後コスト: 6,390 × $0.020 = $127.80/月
現状コスト:  24,576 × $0.010 = $245.76/月
削減額: $245.76 - $127.80 = $117.96/月 ✅

施策1 合計削減: $78.64 + $117.96 = $196.60/月

📋-- 実施方法: DDL で課金モデル変更（即時反映）
ALTER SCHEMA `project.dataset_compressed`
SET OPTIONS (storage_billing_model = 'PHYSICAL');

-- 移行前確認: 圧縮率チェック
SELECT
  table_name,
  ROUND(active_logical_bytes / POW(1024, 4), 3)  AS active_tb_logical,
  ROUND(active_physical_bytes / POW(1024, 4), 3) AS active_tb_physical,
  ROUND(active_physical_bytes / NULLIF(active_logical_bytes, 0), 3) AS compression_ratio,
  -- 圧縮率 < 0.5 なら物理課金が有利（アクティブ: 物理$0.04 vs 論理$0.02）
  IF(active_physical_bytes / NULLIF(active_logical_bytes, 0) < 0.5,
     'PHYSICAL推奨', 'LOGICAL維持') AS recommendation
FROM `project.region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
ORDER BY active_logical_bytes DESC
LIMIT 50;

施策2: 不要中間テーブル削除（工数: 1.0人日）

📋# 180日以上未更新の intermediate/ テーブル
対象: 23テーブル、合計 6,200 GB 論理
長期ストレージ判定（90日超 = 全量が長期）

削減額: 6,200 GB × $0.010/GB = $62.00/月 ✅

📋-- 削除候補テーブルの特定
SELECT
  table_schema,
  table_name,
  TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), last_modified_time, DAY) AS days_stale,
  ROUND(active_logical_bytes / POW(1024, 4), 3)        AS active_tb,
  ROUND(long_term_logical_bytes / POW(1024, 4), 3)     AS longterm_tb,
  ROUND((active_logical_bytes + long_term_logical_bytes)
        / POW(1024, 4), 3)                             AS total_tb
FROM `project.region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE table_schema LIKE '%intermediate%'
  AND TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), last_modified_time, DAY) > 180
ORDER BY (active_logical_bytes + long_term_logical_bytes) DESC;

施策3a: Argo ログ TTL 設定（工数: 0.5人日）

📋resource "google_bigquery_table" "argo_log_events_v2" {
  dataset_id = "dataset"
  table_id   = "argo_log_events_v2"
  project    = var.project_id

  # 30日でパーティションを自動削除
  time_partitioning {
    type          = "DAY"
    field         = "started_at"
    expiration_ms = 2592000000  # 30日 × 24h × 3600s × 1000ms
  }

  schema = jsonencode([
    { name = "workflow_id", type = "STRING",    mode = "REQUIRED" },
    { name = "step_name",   type = "STRING",    mode = "REQUIRED" },
    { name = "status",      type = "STRING",    mode = "REQUIRED" },
    { name = "message",     type = "STRING",    mode = "NULLABLE" },
    { name = "started_at",  type = "TIMESTAMP", mode = "REQUIRED" },
  ])
}

📋# TTL効果の計算
現状: 3.2 TB/月 蓄積 × 削除なし = 毎月増加（青天井）
TTL後: 定常サイズ = 3.2 TB（30日分のみ保持）

月額: 3,277 GB × $0.020 = $65.54/月
削減額: 約 $131/月 ✅（青天井→固定コストへ）

施策3b: スキーマ最適化（工数: 1.0人日）

上記 Bad→Good コード比較を参照。frozen dataclass で5カラムに絞ることで 4.2 KB/行 → 0.8 KB/行（81%削減）。

📋# スキーマ最適化後の月次蓄積
3.2 TB/月 × (0.8 KB / 4.2 KB) = 0.61 TB/月

# TTL（30日）と組み合わせた定常コスト
625 GB × $0.020 = $12.50/月
（TTLのみ: $65.54/月 → さらに $53.04 削減）

# 累計削減（TTL + スキーマ最適化）: $184/月 ✅

ROI試算 & フェーズ計画

施策まとめ

ROI試算

📋初期工数コスト:
  3日 × 8時間 × 6,000円 = 144,000円（= $960）

月次削減額:
  $442.60 × 150円 = 66,390円/月

ペイバック期間:
  144,000 ÷ 66,390 ≈ 2.2ヶ月

年間ROI:
  (66,390 × 12 - 144,000) / 144,000 × 100 ≈ 453%

フェーズ計画

📋Phase 1（Day 1-2, 0.5人日）: 物理バイト課金移行 — 即効性最大
  → ALTER SCHEMA × 2データセット（コンソール or TF apply）
  → コスト反映: 翌日から適用
  → 効果: $196.60/月削減（最小工数・最大効果）

Phase 2（Day 2-3, 1.0人日）: Argo ログ TTL設定 + テーブル移行
  → TF で argo_log_events_v2 作成（パーティション期限30日）
  → Argo Workflow の INSERT 先を v2 に変更
  → 旧テーブルは30日後に DROP（移行完了確認後）
  → 効果: $131/月削減

Phase 3（Day 4-5, 1.5人日）: 中間テーブル削除 + スキーマ最適化
  → INFORMATION_SCHEMA で削除候補リスト作成
  → dbt プロジェクトオーナーと削除確認 → DROP TABLE
  → ArgoLogRow dataclass に移行（型安全 + 軽量）
  → 効果: $115/月削減

合計: 3.0人日（実作業） / ペイバック: 2.2ヶ月 / 年間ROI: 453%

ポイント解説

実務への応用

• dbt 中間テーブル棚卸しの自動化: INFORMATION_SCHEMA.TABLE_STORAGE を週次クエリして「180日超・サイズ大」テーブルをSlack通知する Argo Workflows ジョブを作ると、定期的な棚卸しが習慣化できる
• frozen dataclass の設計思想: ArgoLogRow パターンは Argo ログに限らず、BigQuery INSERT する全テーブルに適用できる。型安全性・メモリ効率・スキーマドキュメント化を同時に達成できる
• 物理課金移行の Terraform 標準化: google_bigquery_dataset の storage_billing_model = "PHYSICAL" をデフォルト設定に統一することで、新規データセット作成時に自動で物理課金になり、個別対応が不要になる
• コストアラート設定: Cloud Billing の Budget Alert を月次予算の 80%・100%・120%で設定し、Slack通知を送ることで早期に異常を検知できる。3.2倍膨張も早期にアラートが鳴っていれば対処できた
• 次の発展問題: BigQuery Slot コミットメント（Reservations）の試算 — オンデマンド vs 月次コミット vs 年次コミットを年間クエリコスト$12,000/月のケースで比較

今日のまとめ

自己評価

自分の回答

気づき・メモ