ZrobDev/データモデリング

データモデリング は何をしますか？

対応DB: PostgreSQL, BigQuery, Snowflake。変換: dbt。ローカルプロトタイピング: DuckDB 2.0。 dbtの仕組み（モデル設定、テストYAML、スナップショット、セマンティックレイヤー、v2移行）については、このリポジトリのde-vendorsスキルで取り込まれている公式のdbt Labsスキルを使用してください。このスキルは「何をモデル化するか」を決定するものであり、dbtの記述方法を指示するものではありません。

手順

1. ビジネス上の質問と利用者（ダッシュボード、ML特徴量、財務クロージング）を明確にします。
2. カラムを定義する前に、粒度を一文で記述します：「1行＝注文明細1日あたり」。もし文に「または」が必要な場合、それは2つのテーブルであることを意味します。
3. ファクトタイプ（後述）を選択し、その粒度で成立するディメンションをリストアップします。
4. 粒度キー（自然キーのカラム、またはそのハッシュ）を命名します。このキーにはユニーク制約と非NULL制約を適用します。
5. サンプルデータでDuckDBを使用してプロトタイプを作成し、ファンアウトチェックを実行した後、本番環境に移植します。
6. 「テストすべき項目」にリストされたテストを、完了宣言の前に追加します。

粒度

• 粒度を先に決め、カラムはその後です。すべてのメジャーとすべての外部キーは、その粒度で単一値でなければなりません。そうでないカラムは別のテーブルに属します。なぜなら、そのカラムは静的には行数を増加させるからです。
• ソースがサポートする最低の粒度を維持します。集計値は詳細から導出可能ですが、詳細は集計値から導出できません。ロールアップは別のマートとして構築します。
• 1つのテーブル内で異なる粒度を混在させないでください（例：注文ヘッダーの合計を注文明細行に繰り返す）。ヘッダーの金額を明細行で合計すると二重計上になります。ヘッダーと明細のファクトは別々にモデル化します。

ファクトとディメンション

• ファクト：イベントまたは測定値。数値型で、可能な限り加算可能。ディメンションキーと縮退ディメンション（注文番号など）によってキーが構成されます。
• ディメンション：フィルタリングとグループ化に使用する説明的なコンテキスト。幅広く非正規化された状態で問題ありません。
• 各メジャーを分類します：
  • 加算可能（すべてのディメンションで合計可能）
  • 半加算可能（残高：時間軸では合計不可）
  • 非加算可能（比率：分子と分母を保存し、集計後に除算）
• ファクトタイプ：
  • トランザクションファクト：イベントごとに1行。デフォルトの選択。
  • 定期スナップショット：エンティティごと、期間ごとに1行（日次残高）。状態が重要で、イベントがまばらまたは遅延する場合に使用。
  • 累積スナップショット：プロセスインスタンスごとに1行で、マイルストーンごとの日付カラムを持ち、その場で更新。注文（発注、出荷、配達）のような既知のライフサイクルがあるパイプラインに使用。更新を想定し、ロード処理は冪等にします。
  • ファクトレスファクト：メジャーを持たないイベントまたはカバレッジ（出席、資格）。行数をカウントします。

スキーマ形状

• デフォルトはスタースキーマ：1つのファクトとフラットなディメンション。スノーフレーク（正規化されたディメンション）は、サブディメンションが共有されて大規模であるか、別チームによって管理されている場合のみ使用します。追加の結合は可読性とBIツールの互換性を損ないます。
• 準拠ディメンション：1つのdim_customer、dim_dateをすべてのファクトで共有し、同じキーと属性の意味を持ちます。一度構築すれば十分です。ルールが異なる2つ目の顧客テーブルは欠陥であり、クロスファクトレポートで不一致が生じます。
• ロールプレイングディメンション（注文日、出荷日）は、別々のFKカラムを介して1つのdim_dateを再利用します。
• ジャンク/ミニディメンションは、低カーディナリティのフラグに使用します。ファクトテーブルに8つのブール値をそのまま残さないでください。
• 不明メンバー：各ディメンションに行（キー-1または'unknown'のハッシュ）を保持し、遅延到着ファクトが行を保持できるようにします。ディメンションへの内部結合はファクト行を静的に削除するため、左結合と不明キーへのCOALESCEを使用します。

キー

• 自然キー：ソースシステムのビジネス識別子。常に属性として保持します。
• サロゲートキー：自然キーが複合キーである、変更可能である、複数のソースから来る、またはディメンションがSCD2（1つのエンティティに複数のバージョンがある）の場合に追加します。dbtスタイルのウェアハウスでは、シーケンスよりも自然キー（SCD2の場合はvalid_fromを加える）の決定論的ハッシュを優先します。これにより、再実行や並列ビルドでも一貫性が保たれます。
• サロゲートキーをビジネスユーザーに識別子として公開しないでください。再構築時に変更されます。

緩やかに変化するディメンション（SCD）

• タイプ1（上書き）：履歴に分析上の価値がない場合、または変更が訂正である場合。
• タイプ2（バージョンごとに新しい行、valid_from / valid_to / is_current）：イベント時点の属性値でファクトを報告する必要がある場合（購入時の顧客ティアなど）。
• スナップショットは、ソースが履歴を保持しておらず、今後発生する変更のみを取得する必要がある場合に十分です。スナップショットはキャプチャメカニズムであり、SCD2ディメンションはその上に構築されるモデルです。ソースに既に履歴やイベントログがある場合は、スナップショットを作成する代わりに、そこから有効範囲を導出します。
• ファクトをSCD2に結合するには、キーに加えてevent_ts >= valid_from AND event_ts < valid_to（半開区間、重複なし）を使用します。自然キーごとに範囲の重複やギャップがないことをテストします。
• タイプ3、または属性ごとのタイプ2：単一の「以前の値」カラムで質問に答えられる場合のみ。それ以外は使用しません。

ファンアウトと二重計上

原因：異なる粒度のテーブルを結合することで、「1」側が実際には多になっている。症状：合計がソースより大きい、左結合後にcount(*)が増加する、平均値がずれる。

• 検出：各結合の前後でcount(*)とcount(distinct grain_key)を比較します。ディメンションへの結合後もこれらが等しくなければなりません。ディメンション側の結合キーの一意性をテストします（select key from dim group by 1 having count(*) > 1）。
• 調整：マート内のメジャーの合計が、同じフィルター条件でソースの合計と一致することを確認します。
• 修正：結合前に多側をファクトの粒度に集約するか、重み付け係数を持つブリッジテーブルを介して結合します。結果をdistinctで隠蔽してはいけません。
• 多対多（顧客と口座、製品とカテゴリ）：重み付け係数を持つブリッジテーブル、またはプライマリを選択して明示します。
• これらのチェック用SQL：references/fan-out-checks.mdを参照。

レイヤリング

• raw/staging：ソーステーブルごとに1つのステージングモデル。リネーム、キャスト、重複除去を行い、結合やビジネスロジックは含めません。rawはイミュータブルに保ちます。
• intermediate：結合、再形成、2つ以上のマートで再利用される共通ロジック。BIには公開しません。
• marts：ファクトとディメンション。ビジネスプロセスごとに1つのマートで、ドキュメント化、テスト、コントラクトが適用されます。マートのみが公開されます。
• 依存関係は一方向に流れます。マートはrawを読み取らず、ステージングはステージングを読み取りません。
• ステージングはビューとして具体化し、マートはテーブル（大規模で粒度キーが安定している場合はインクリメンタル）として具体化します。

命名規則

• stg_<ソース>__<エンティティ>、int_<エンティティ>_<動詞>、fct_<プロセス>、dim_<エンティティ>。スネークケース。複数形または単数形を一貫して使用します（どちらかに決める）。
• キー：<エンティティ>_id（自然キー）、<エンティティ>_key（サロゲートキー）。タイムスタンプ：<イベント>_at（UTC）。日付：<イベント>_date。ブール値：is_/has_。金額：単位または通貨を含める（amount_usd）。

テストすべき項目

• 粒度キー：すべてのモデルでユニーク制約＋非NULL制約。複合粒度の場合は組み合わせをテスト。
• ファクト上のすべてのFK：非NULL（または不明メンバー）と、ディメンションへのリレーションシップテスト。
• ステータス/タイプカラムの許容値。新しい値が誤分類されるのではなく、確実に失敗するようにする。
• 各マートの行数と合計値のソースとの調整。許容誤差を明記（財務の場合はゼロ）。
• SCD2：重複する範囲がなく、自然キーごとに現在の行が正確に1つであること。
• ソースのフレッシュネス。サンプルデータで各テストを一度失敗させ、テストが機能することを確認する。

OLTPスキーマレビュー（PostgreSQL）

• トランザクションテーブルは3NFまで正規化。測定された読み取りパスのためだけに非正規化。すべてのテーブルに主キーを設定。
• 外部キーカラムにインデックスを作成。PostgreSQLは自動的には作成せず、インデックスがないFKは親テーブルの削除や結合時に子テーブルのスキャンを引き起こします。
• timestamptz、金額にはnumeric、任意のvarchar制限の代わりにtextを使用。ルールはアプリケーションコードだけでなく、NOT NULL、CHECK、UNIQUE、FK制約で強制します。
• 大規模な挿入が多いテーブルでは、ランダムなUUIDv4よりもbigint identityまたはUUIDv7を優先（インデックスの局所性）。
• マルチテナント：テナントが所有するすべてのテーブルにtenant_idを配置し、複合キーやインデックスの先頭カラムとし、FK（複合FK）に含めてクロステナント参照を不可能にし、行レベルセキュリティでバックアップします。デフォルトはtenant_idを持つ共有スキーマ。スキーマ単位またはデータベース単位のテナント分離は、厳格な分離またはテナントごとのリストアが必要な場合のみ使用。
• ソフトデリート：where deleted_at is nullの部分ユニークインデックスを使用して、一意性を維持します。

落とし穴と観測された障害モード：references/gotchas.mdを参照。