演習：データ基盤アーキテクチャを設計しよう

## 現状データフロー図

[MySQL] → [cron + shell] → [CSV] → [cron + shell] → [Excel]
                                                        ↓
[Stripe API] → [手動エクスポート] → [CSV] ──────→ [Google Sheets]
                                                        ↓
[GA4] → [BigQuery Export] → [放置状態]            [経営会議資料]

## 課題一覧

| # | 課題 | 影響度 | 緊急度 | ビジネス損失 |
|---|------|--------|--------|------------|
| 1 | エラー検知の欠如 | 高 | 高 | 月2回のレポート欠損、意思決定の遅延 |
| 2 | 依存関係の不透明さ | 高 | 中 | 変更時に平均2日の調査工数 |
| 3 | 属人化（ドキュメントなし） | 高 | 中 | 担当者不在時に復旧不能 |
| 4 | データ品質チェックなし | 中 | 高 | 不正確なレポートによる誤判断 |
| 5 | スケーラビリティなし | 中 | 低 | データ量増加で処理時間が線形増加 |
| 6 | セキュリティ懸念 | 中 | 中 | 本番DB直接接続、CSV平文保存 |
| 7 | GA4データの未活用 | 低 | 低 | マーケティング分析の機会損失 |

## 新アーキテクチャ図

[MySQL] ────→ [Airbyte] ──→ [BigQuery Raw] ──→ [dbt Staging] ──→ [dbt Mart] ──→ [Looker]
[Stripe API] ─→ [Airbyte]        │                    │                │
[GA4] ─────────→ [Native Export]  │                    │                │
                                  └── Orchestration: Cloud Composer (Airflow) ──┘
                                  └── Observability: Elementary ────────────────┘

## ツール選定

| レイヤー | ツール | 選定理由 |
|---------|--------|---------|
| Ingestion | Airbyte (OSS) | 無料、MySQL/Stripeコネクタ対応、セルフホスト可 |
| Storage | BigQuery | GCP利用中、サーバーレス、GA4ネイティブ連携 |
| Transform | dbt Core | 無料、SQL中心でアナリストも参加可、テスト機能充実 |
| Orchestration | Cloud Composer | GCPマネージド、Airflow互換、運用負荷低 |
| Consumption | Looker | BigQuery親和性、セマンティックレイヤー |

## DWHレイヤー設計

### Raw Layer
- raw_mysql.orders
- raw_mysql.customers
- raw_mysql.products
- raw_stripe.payments
- raw_ga4.events

### Staging Layer
- stg_orders（型変換、NULL処理、重複排除）
- stg_customers（SCD Type 2対応）
- stg_products（カテゴリ正規化）
- stg_payments（通貨統一、ステータスマッピング）
- stg_page_views（GA4イベントのパース）

### Mart Layer
- mart_daily_sales（日次売上サマリ）
- mart_customer_ltv（顧客LTV計算）
- mart_product_performance（商品別パフォーマンス）
- mart_funnel_analysis（ファネル分析）

## 月額コスト概算

| 項目 | 月額 |
|------|------|
| BigQuery | 〜10万円（オンデマンド） |
| Cloud Composer | 〜15万円（最小構成） |
| Airbyte (GCE) | 〜5万円 |
| Looker | 〜15万円（5ユーザー） |
| 合計 | 〜45万円 |

## 移行計画

### Phase 1: 基盤構築（1ヶ月目）
- BigQuery環境のセットアップ
- Aibyteで MySQL → BigQuery のデータ同期を構築
- dbtプロジェクトの初期化、stg_ordersモデルの作成
- DoD: MySQL のordersテーブルがBigQueryで参照可能

### Phase 2: パイプライン移行（2ヶ月目）
- 全データソース（MySQL, Stripe, GA4）の同期設定
- dbt モデル全体の構築（Staging + Mart）
- Cloud Composerでの日次バッチスケジューリング
- DoD: 既存シェルスクリプトと同等のレポートがBigQueryから生成可能

### Phase 3: 旧基盤の廃止（3ヶ月目）
- Lookerダッシュボードの構築
- 既存のExcel/Sheetsレポートからの切り替え
- cronジョブの段階的停止
- データ品質テスト・アラートの設定
- DoD: 全cronジョブが停止、Lookerが唯一のレポーティング手段

## リスクと対策

| リスク | 影響度 | 対策 |
|--------|--------|------|
| データ不一致 | 高 | 並行運用期間に新旧の数値を日次比較 |
| チームの学習コスト | 中 | dbt/Airflowのハンズオン研修を実施 |
| Aibyteの同期障害 | 中 | 初期は日次＋手動チェック、アラート設定 |
| 予算超過 | 低 | BigQueryの予算アラートを設定 |

## 並行運用方針
- Phase 2の期間中、新旧両方のパイプラインを稼働
- 毎朝、主要KPI（売上・注文数・顧客数）の数値照合を実施
- 差異が1%以内を2週間継続したら旧パイプラインを停止対象に