DWH構築入門ガイド
ビジネスKPIから逆算する要件定義
DWHは「何を早く・正確に決めたいか」から設計が始まります。売上・LTV・在庫回転など意思決定で使うKPIと、その計算に必要な最小の事実（イベント）を先に決めます。レポート要件を並べるより、「事実テーブルの粒度」を固定する方が後戻りが減ります。
3つの問いで要件を固める
- 粒度: 1レコードは何を表すか（例: 注文行、セッション、在庫スナップショット）
- 時間: どの時間軸で分析するか（発生時刻、処理時刻、営業日）
- 同一性: 一意キーは何か（自然キーかサロゲートキーか、再生成の可否）
要件チェックリスト
- SLA: 更新遅延の許容（例: 15分/4時間/翌日）
- 保持: 明細は24カ月、集計は36カ月などの方針
- 品質: 欠損率や重複率の許容値とアラート閾値
- 権限: 個人情報の列単位マスキング、部門別アクセス
最小構成アーキテクチャ（ELTで始める）
初期はETLよりELT（取り込み後に変換）が失敗しにくいです。構成はシンプルに「取り込みレイヤー（Raw）→標準化（Staging）→モデル（Mart）」の三層を推奨します。Rawはソースそのまま、Stagingは型揃えと命名規則、Martは分析用スター/ワイドテーブルです。変更履歴はRawに全て残します。
ロード戦略の原則
- フルロードは小規模テーブルのみ（1000万行未満が目安）
- 増分ロードは「更新時間/自動採番/高水位」のどれかで識別
- 履歴保持が必要ならCDC（削除/更新の差分）を検討
- スケジュールは「ソース更新直後＋バッファ10分」で衝突回避
セキュリティと権限の最小ルール
- 環境分離（dev/test/prod）とサービスアカウント発行
- 列単位マスキング（メール/電話/住所）と監査ログの保存
- クレデンシャルはKMS/Secret管理、SQL内に埋め込まない
スキーマ設計とモデリングの実務
事実テーブルは粒度を固定し、ディメンションは正規化しすぎないのがコツです。多対多はブリッジで表現し、時間はイベント時刻と処理時刻を分けます。ディメンションはSCD Type2（有効開始・終了）を基本に、更新頻度が高くない属性のみ履歴化します。
粒度・キー・時間の決め方
- 粒度: 迷ったら「注文行」「ページビュー」など一番細かい単位に寄せる
- キー: サロゲートキー（数値）を採用、自然キーは業務参照用に保持
- 時間: event_at, processed_at, snapshot_dateを分離し混同を防ぐ
パーティション/クラスタリング設計の目安
- 取り込みデータが日次10GB超なら日付パーティションを必須
- 集計でよく絞る列（customer_id, shop_idなど）でクラスタリング
- 小さいテーブルはクラスタ不要、代わりに索引代替の並び順を意識
- マテビューは「日次/顧客ID」など安定条件で、リフレッシュは夜間＋営業開始前
運用・コスト最適化と“身近な”改善例
運用は「壊れたら気づく」より「壊れる前に抑える」が基本です。データテスト（主キー一意、外部キー整合、件数ドリフト）はパイプラインに組み込み、失敗時はロールバックではなく「最新を止めて前回成功分を提供」する設計が安全です。コストはクエリのスキャン量をKPIに置き、週次でトップクエリを棚卸します。
よくある落とし穴と対策
- 集計先行で破綻: まず明細の粒度とキーを固め、集計は派生にする
- スキーマドリフト: Rawにスキーマレスで保持し、Stagingで厳格化
- 過剰再計算: 変化のあった日付分だけ再ビルド（パーティション限定）
- 権限肥大化: グループに権限を付与、個人付与は禁止
身近な企業活用例
広告費が増えるのにLTVが読めず、スプレッドシートとアプリDBを夜間ETLで集計。月末に処理が落ち、重複注文で指標がブレる失敗が続きました。見直しでは、ELT三層に再設計し、事実テーブルは「注文行」粒度、ディメンションは顧客と商品をSCD Type2で履歴化。注文日は日付パーティション、customer_idでクラスタ。増分はupdated_atで高水位管理。マテビューで日次LTVを更新し、テストで「注文ID一意」「金額>=0」を自動検証。結果、データ鮮度は72時間遅延から4時間遅延へ、広告入札は「初回7日LTV」を用いてCPAを10%削減。運用は失敗時に前回成功分をBIへ提供するフェイルセーフで、月末障害も解消しました。SQLの静的解析や説明にはChatGPTとGeminiを併用し、Copilotで変換ジョブの雛形とユニットテストを自動生成、ドキュメント整備の手間を30%縮小しました。
現場で効く地味だけど効くTips
- 命名規則: レイヤー接頭辞（raw_, stg_, mart_）＋領域＋テーマ
- スケジュール: 依存先の遅延を見越し「±10分のスライド窓」を設定
- コスト監視: パーティション未指定クエリは失敗させるガードを導入
- 監査: 誰がどの列にアクセスしたかを90日以上保持
DWHは豪華機能より「粒度の一貫性」「ELT三層」「テスト自動化」「コスト可視化」の4点をまず固めるのが近道です。これらは単発のBI案件ではなく、継続的に価値を生むデータ解析プラットフォーム事業の土台そのものであり、組織の意思決定を日々少しずつ賢くしていきます。
