結合キーの罠：JOIN したら行が突然1000倍に増えた

JOIN の結果、「あれ、行数が10倍になってる」── ほぼ全エンジニアが一度はやらかすミス。カーディナリティ爆発といって、1対多のテーブルを LEFT JOIN するだけで集計が膨らみます。

起きる仕組み

• 1対多テーブルとの JOIN：注文（1行）× 注文明細（複数行）を結合 → 注文金額が明細の数だけ重複
• 重複キー：マスタにユニーク制約がなく、同じ id が2行ある
• NULL 同士のマッチ：BigQuery / PG で `NULL = NULL` は FALSE だが、`USING (col)` で予期せぬ挙動も
• 多対多：商品×タグ・ユーザ×ロールなど。間に中間テーブルを挟まないと爆発
• date 結合の不一致：日次集計と週次集計を JOIN すると、行数が日数倍に

調査手順

-- ① JOIN 前の行数SELECT COUNT(*) FROM orders;
-- ② JOIN 後の行数SELECT COUNT(*)FROM orders oLEFT JOIN order_items i ON o.id = i.order_id;
-- ③ 重複しているキーの確認SELECT order_id, COUNT(*)FROM order_itemsGROUP BY order_idHAVING COUNT(*) > 1LIMIT 20;
-- ④ どの id がどれだけ膨らむかSELECT o.id, COUNT(i.id) AS item_countFROM orders oJOIN order_items i ON o.id = i.order_idGROUP BY o.idORDER BY item_count DESC LIMIT 20;

短期対処

• SUM の前に集計：`order_items` を先に `GROUP BY order_id` してから JOIN
• `SELECT DISTINCT` で重複除去（ただし重複の根本対処にはならない）
• LATERAL / CORRELATED SUBQUERY：1対多を「親1行に対して子集計1値」に集約
• dbt の `unique` test：JOIN の前提テーブルに必須

中長期対策

• スター・スノーフレークモデル：fact / dim を明確に分け、JOIN の方向を一意化
• Surrogate key：自然キーよりサロゲート（連番ID）を使い、ユニーク性を保証
• dbt 全 model に grain test：行のユニーク粒度を明示・検証
• SQL（Structured Query Language） レビュー文化：JOIN がある PR は必ずレビュー必須
• メトリクスレイヤー：定義済みの集計に絞り、生 SQL を書かせない

ふくふくの進め方

「JOIN 1個でレコード爆発」は dbt schema test と grain 検証ですぐ発見できます。1週間で全 fact テーブルに `unique`/`not_null` を当てて、現状の重複を一気に可視化するところから始めます。