データモデリング

スター型スキーマは、分析用のテーブルを2種類に分ける設計です。真ん中にファクト（起きた出来事と、その数値）を置き、周りにディメンション（出来事を切り分ける切り口）を置きます。図にすると星の形になるのでこう呼びます。

こうしておくと、分析の SQL は「ファクトにディメンションをつないで、属性で GROUP BY して数値を SUM する」という同じ形になり、誰が書いても迷いにくくなります。

SELECT d.year_month, p.category, SUM(f.amount) AS sales
FROM fct_order_items f
JOIN dim_date    d ON f.date_key    = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
GROUP BY d.year_month, p.category;

最初に決める「粒度」

ファクトを作るとき、最初に粒度を決めるのが最も大事な手順です。粒度とは「ファクトの1行が何を表すか」で、「注文1件」なのか「注文の明細1行（商品ごと）」なのかで、持てる列も集計の結果も変わります。たとえば注文の粒度のテーブルに商品の列は持てません（1注文に商品が複数あるため）。迷ったら業務で起きる一番細かい単位（明細1行）にしておけば、あとから注文単位・日単位にはまとめられますが、逆はできません。

粒度は文章で書いてテーブルの説明に残します（「1行＝注文の商品1つ。キャンセルされた明細も含む」）。それと合わせて、何が一意か（主キー）をテストで守ります。

ファクトには、記録の仕方で3つの型があります。在庫や口座の残高のような「状態」を、出来事のファクトから毎回計算するのは重く間違えやすいので、型を使い分けます。

ディメンションは「分析で使う切り口」をまとめた表です。いくつか決まった作り方があります。

集計テーブルの指標は、どの方向に足してよいかが決まっています。間違えると「日別のコンバージョン率を足して月の率にする」のような、もっともらしいのに間違った数字が出ます。

集計テーブルには、率そのものではなく分子と分母を別々の列で持たせます。そうすれば、どの切り口でまとめても正しい率を計算し直せます。

ファンアウトは、1対多の JOIN で行が増え、上側の表の数値が何重にも数えられることです。エラーにならず、数字がもっともらしく膨らむので気づきにくい失敗です。

よくある失敗：

• 粒度の違うもの（明細の金額と、注文単位の送料）を1つのファクトに混ぜ、送料を明細の数だけ重ねて足してしまう。粒度ごとにファクトを分けます。
• ファクトどうしを直接 JOIN して行が増え、数字が膨らむ（ファンアウト）。それぞれ同じ粒度まで集計してからつなぎます。
• ディメンションのキーに業務 DB の ID をそのまま使い、履歴を持てなくなる。必要に応じて分析用のキー（サロゲートキー）を振ります（SCD）。

WITH pay AS (SELECT order_id, SUM(amount) AS paid FROM payments GROUP BY order_id),
     item AS (SELECT order_id, SUM(price * qty) AS item_total FROM order_items GROUP BY order_id)
SELECT o.order_id, o.shipping_fee, pay.paid, item.item_total
FROM orders o
LEFT JOIN pay  USING (order_id)
LEFT JOIN item USING (order_id);

見つけ方は、JOIN の前後で件数と合計を比べることです。JOIN のあとにキーの一意のテストを入れておくと、膨らんだ時点で止められます。

顧客の住所や会員ランクのように、ディメンションの属性は時間とともに変わります。SCD（Slowly Changing Dimension、ゆっくり変わるディメンション）は、その変化をどう持つかの決まった型です。よく使うのは Type 1 と Type 2 です。

たとえば顧客が 10月に「一般」から「ゴールド」に上がった場合、Type 1 だと 9月の売上も「ゴールド会員の売上」に数えられます。「ランク別の売上の推移」を見たいなら Type 2 にします。

当時の値で集計する JOIN と落とし穴

Type 2 のディメンションは、同じ顧客に複数の版（行）があります。ファクトとつなぐときは、キーだけでなく期間の条件も要ります。

当時の値で集計するには、ファクトの日時がディメンションの有効期間に入る行とつなぎます。

SELECT c.rank, SUM(o.amount) AS sales
FROM fct_orders o
JOIN dim_customer_scd2 c
  ON  o.customer_id = c.customer_id
  AND o.ordered_at >= c.valid_from
  AND o.ordered_at <  COALESCE(c.valid_to, TIMESTAMP '9999-12-31')
GROUP BY c.rank;

読み込みの時点で、ファクトに当時のディメンション行のキー（customer_sk）を入れておく方法もあり、こちらは集計の JOIN が単純になります。

落とし穴：期間を「以上・未満」で統一しないと、境目の時刻で2行に当たったり、どれにも当たらなかったりします。今の値だけほしい分析では is_current で絞り忘れると行が増えます。dbt の snapshot 機能を使うと、元テーブルの変化を Type 2 の形で自動で記録できます。ただし snapshot を取る間隔より短い変化は残りません（1日1回なら、1日の中の変化は最後の値だけ）。

正規化は、同じ情報を1か所にだけ持つようにテーブルを分けることです。業務 DB（OLTP）では、更新が速く、矛盾（ある行だけ住所が古い）が起きないことが大事なので正規化します。非正規化は、読みやすさと速さのために、あえて情報を複製してテーブルをまとめることで、分析（OLAP）ではこちらが基本です。

ワイドテーブル（OBT）の使いどころ

正規形の詳しい説明は SRE の「正規化」へ

ワイドテーブル（One Big Table、OBT）は、ファクトに必要なディメンションの列をすべてつないでおいた、列の多い1枚のテーブルです。列指向の DWH では使わない列は読まれないので、列が多くても大きな損はなく、BI ツールや SQL に慣れていない人にも使いやすくなります。一方で、属性が変わると全体の作り直しが要る、同じ列が何枚ものテーブルに散って定義がずれる、といった弱点があります。スター型スキーマを正として持ち、よく使う組み合わせだけワイドテーブルを作るのが扱いやすい形です。

分析用のデータの形の作り方には、よく知られた考え方がいくつかあります。今の実務では、dbt でスター型（Kimball）の mart を作る形が最もよく見られます。

面接で聞かれたら、手法の名前より「利用者がどう使うか、変更がどれくらいあるかで選ぶ」と答えられれば十分です。