2025/10/18

SQL in the Wild : UNIQUEではない結合キーでJOINすると行数が爆発するRow Explosion

はじめに

Google BigQueryやSnowflakeをはじめとするデータウェアハウスにおいては、プライマリーキー制約を強制させることはできない。宣言は可能だがデータベース側が強制しないため、プライマリーキーをプライマリーたらしめるのは、データベースではなくユーザーの責任となる*1。結果として、一意だと想定していたキーが重複することは起こりうる。

技術的な理由は後述するが、一意ではない結合キーを使ってJOINを行うと、行数が想定していたより増える。データ屋としてデータパイプラインの信頼性維持に関わってきた人間なら、何度か遭遇したことはあるだろう。この現象は「Row Explosion」*2とか「Explosion」とか「爆発」と呼ばれている*3。

最近仕事を進めていく上で、この現象（以降JOIN爆発と呼称する）について説明することも多くなってきた。SQLについて詳しくない他のエンジニアにもわかるように、脳内をダンプしておきたい。

SQLのJOINって本当は何しているの

JOIN爆発について理解するためには、JOINの仕組みについて理解する必要がある。JOINといえばLEFT JOINが大人気すぎるせいで、今までSQLにおけるJOIN演算の仕組みをExcelのLOOKUP関数の類推で理解してきた人もいるだろう。実際のプロセスは別物だ。LEFT JOINのイメージが強すぎるのが悪い。

ざっくり言うと、SQLのJOIN演算を理解するためには、以下の3段階を理解すれば良い*4。

前提

• 2つのテーブルA、Bが存在しており、AとBをJOINしたいとする
• それぞれのテーブルは実態としてタプルであり、タプルの要素が行に当たると考える
• つまり、AとBは集合である

この前提を踏まえた上で、以下のようなプロセスでJOINは行われる

1. まず集合AとBの直積を作成する
2. 直積の各要素のうち、ON句の結合条件に合致する要素をフィルターする
3. LEFTやRIGHT, CROSSなどの条件を考慮してさらに絞る

つまりは、テーブルAの各行に対してテーブルBのすべての行と組み合わせを試し、結合条件に合致する組だけを残しているわけだ。

JOIN爆発の仕組み

JOIN爆発の本質はSQLの動作ではなく、想定と現実の間のずれにある。ON句に適合する条件の行が複数ある場合に行数が増えるのは、SQLにおいては想定通りの動作だろう。想定していないのは、結合キーがデータ設計者の想定を外れて重複したことだ。つまり、以下の二つが問題の原因である。

1. メンタルモデルと現実のデータの間にある不一致 : ユニークだと想定していた列が実際にはユニークではなかった
2. JOINの仕組みに対するメンタルモデルと、実際のJOINの動作の間にある不一致 : LEFT JOINはVLOOKUPのようなものだと考えていたが、実際には直積をフィルターする動作だった

2は「理解してください」となるだろう。まずは理解しよう。

1については、データがナマモノである以上常に起こりうる問題であり、いくつか対処法がある

• dbtやLookerなど、transformレイヤー以降のツール群には、カラムがユニークかどうかを継続的にテストする手段があるので、それを使う
• あるいは、ユニークになるようにQUALIFY句を使ったりするなど、SQLの中で工夫する

データはナマモノだ！一切信用してはいけない！！！

補足：実際にデータを見てみる

再現データをBigQueryで作ってみると以下のようになる。

-- user_events 作成（ORDER BY付き）
CREATE OR REPLACE TABLE `tmp-technology-test.sandbox.user_events` AS
SELECT
  event_created_at,
  user_id,
  event
FROM UNNEST([
  STRUCT(
    TIMESTAMP("2025-10-18T10:01:12.430000+00:00") AS event_created_at,
    1 AS user_id,
    "page_view" AS event
  ),
  STRUCT(
    TIMESTAMP("2025-10-18T10:01:13.120000+00:00"),
    2,
    "click"
  ),
  STRUCT(
    TIMESTAMP("2025-10-18T10:01:14.500000+00:00"),
    3,
    "login"
  ),
  STRUCT(
    TIMESTAMP("2025-10-18T10:01:15.000000+00:00"),
    100,
    "page_view"
  ),
  STRUCT(
    TIMESTAMP("2025-10-18T10:01:15.000000+00:00"),
    100,
    "click"
  )
])
ORDER BY event_created_at, user_id, event;


-- meta_user_events 作成（ORDER BY付き）
CREATE OR REPLACE TABLE `tmp-technology-test.sandbox.meta_user_events` AS
SELECT
  event_created_at,
  user_id,
  user_agents
FROM UNNEST([
  STRUCT(
    TIMESTAMP("2025-10-18T10:01:12.430000+00:00") AS event_created_at,
    1 AS user_id,
    "Mozilla/5.0" AS user_agents
  ),
  STRUCT(
    TIMESTAMP("2025-10-18T10:01:13.120000+00:00"),
    2,
    "Chrome/122.0"
  ),
  STRUCT(
    TIMESTAMP("2025-10-18T10:01:14.500000+00:00"),
    3,
    "Safari/17.0"
  ),
  STRUCT(
    TIMESTAMP("2025-10-18T10:01:15.000000+00:00"),
    100,
    "AttackAgent"
  ),
  STRUCT(
    TIMESTAMP("2025-10-18T10:01:15.000000+00:00"),
    100,
    "AttackAgent"
  )
])
ORDER BY event_created_at, user_id, user_agents;

テーブル情報については以下の通り

user_eventsテーブル

• ユーザーの行動ログを保存する
• event_created_at : イベントが作成された時刻を表すISO8601形式のデータ
• user_id : ユーザーに対して一意に振られるID
• event : page_view | click | login | logout

meta_user_events テーブル

• 行動ログのメタデータを保存する
• event_created_at : イベントが作成された時刻を表すISO8601形式のデータ
• user_id : ユーザーに対して一意に振られるID
• user_agents : ユーザーエージェントを表す文字列

テーブル設計者の想定

• 「m秒単位でイベントが重なることはないだろう」

以下のSQLでJOINを実施し、event = page_viewに絞り込んだ上で、user_idのユニーク数を絞り込んだところ、page_view数が想定より多くなる。

SELECT
  user_events.event_created_at,
  user_events.user_id,
  user_events.event,
  meta_user_events.user_agents
FROM `tmp-technology-test.sandbox.user_events` as user_events
LEFT JOIN `tmp-technology-test.sandbox.meta_user_events` as meta_user_events
  ON user_events.event_created_at = meta_user_events.event_created_at
 AND user_events.user_id = meta_user_events.user_id;

*1:https://cloud.google.com/blog/ja/products/data-analytics/join-optimizations-with-bigquery-primary-and-foreign-keys/

*2:https://community.snowflake.com/s/article/Recognizing-Row-Explosion

*3:僕は「JOIN爆発」と呼んでいる。呼び方は様々だが、とりあえず「爆発」は共通しているらしい。

*4:クエリオプティマイザの動きを含めた、実際のメモリ上の動きとは異なる。概念的な話である。