JOIN 膨張の事故を防ぐために QUALIFY で一意性テストを入れた話

🧩 実務で困ったこと

BigQuery で集計クエリを書いていると、JOIN したテーブル側に重複があると、JOIN 膨張が発生して集計結果が意図せず増えてしまう問題 が起きることがあります。

サンプルデータ

events

segment	user_id	created_at	page	device	action	item_id	item_category
A	u001	2025-11-01 08:00:00	home	pc	view	i001	drama
A	u002	2025-11-01 09:05:00	search	mobile	click	i002	anime
A	u003	2025-11-02 10:10:00	detail	mobile	view	i003	anime
B	u004	2025-11-01 20:00:00	home	tv	view	i004	news

(user_id, created_at, page, item_id, action) がすべて異なる組み合わせになってます。

item_master

item_id	category_name
i001	drama
i002	anime
i003	anime
i003	anime_replica
i004	news

i003 に2つのカテゴリが紐づいています。

サンプルデータ生成クエリ

-- events と item_master を同時に定義するサンプル
WITH
events AS (
    SELECT 
        *
    FROM 
        UNNEST([
            STRUCT('A' AS segment, 'u001' AS user_id,
                   TIMESTAMP '2025-11-01 08:00:00' AS created_at,
                   'home' AS page, 'pc' AS device, 'view' AS action,
                   'i001' AS item_id, 'drama' AS item_category),
        
            STRUCT('A', 'u002',
                   TIMESTAMP '2025-11-01 09:05:00',
                   'search', 'mobile', 'click',
                   'i002', 'anime'),
        
            STRUCT('A', 'u003',
                   TIMESTAMP '2025-11-02 10:10:00',
                   'detail', 'mobile', 'view',
                   'i003', 'anime'),
        
            STRUCT('B', 'u004',
                   TIMESTAMP '2025-11-01 20:00:00',
                   'home', 'tv', 'view',
                   'i004', 'news')
          ])
),

item_master AS (
    SELECT
        *
    FROM 
        UNNEST([
            STRUCT('i001' AS item_id, 'drama'        AS category_name),
            STRUCT('i002', 'anime'),
            STRUCT('i003', 'anime'),
            STRUCT('i003', 'anime_replica'), -- ← 意図的な重複
            STRUCT('i004', 'news')
        ])
),

例えば eventsテーブルに item_master を突合すると item_id = i003 の行が 2行になります。
それを放置したまま行数カウントすると事実とは異なる集計値が出てしまいます。

-- events テーブルに item_master を突合

WITH
events_attr AS (
    SELECT
        events.*,
        item_master.category_name
    FROM
        events
    LEFT JOIN
        item_master
    ON
        events.item_id = item_master.item_id
)

SELECT
    COUNT(1) AS cnt, -- events テーブルは 4行なのに、結果は 5となる
FROM
    events_attr

---

💭試したこと

• クエリレビュー時に JOIN ロジック周りを重点的にチェック
• JOIN 後のテーブルを出力して目視確認

をしていましたが、なかなか負荷のかかる作業でした。JOIN 事故を早期に検知できる仕組みが欲しいところでした。

🧮 最終的に採用した方法

QUALIFY で 一意性テスト を組み込む ことにしました。

events_attr AS (
    SELECT
        events.*,
        item_master.category_name
    FROM
        events
    LEFT JOIN
        item_master
    ON
        events.item_id = item_master.item_id
),
check_events_attr AS (
    SELECT
        *
    FROM
        events_attr
    QUALIFY
        IF(
            COUNT(1) OVER(PARTITION BY user_id, created_at, page, device, action) >= 2,
            ERROR("events_attr が重複してます"),
            TRUE
        )
)

SELECT
    *,
    -- COUNT(1) AS cnt,
FROM
    -- events_attr
    check_events_attr

このままだと重複が発生しているので、実行すると以下のようなメッセージが表示されます。

この場合はログテーブル側を守るため、item_master を item_id で一意化してから JOIN するのが安全です。category_name は配列にまとめておくことで、JOIN 膨張を防ぎつつ情報を保持できます。

events_attr AS (
    SELECT
        events.*,
        item_master.category_names,
    FROM
        events
    LEFT JOIN (
        SELECT
            item_id,
            ARRAY_AGG(category_name) AS category_names,
        FROM
            item_master
        GROUP BY
            item_id
    ) AS item_master
    ON
        events.item_id = item_master.item_id
),

これでエラーが出なくなりました。
QUALIFY による自動チェック導入で、JOIN 周りの事故を気にせず安全にクエリを修正できるようになりました。

🔗 参考

より信頼できるクエリを書くために、SQLでもテストを書く
こちらの記事では test CTE にテストを記述しています。