イベント統合：Active User の SSOT を intermediate に作る

Stage 3 — 第1章 | dbt入門カリキュラム 推定学習時間：35〜45分 | 難易度：★★★☆☆

この章で学ぶこと

Stage 2 までで、1 source 表 → 1 staging モデルの 薄い整形 を学びました。 次は intermediate（中間）層 です。ここでは複数 staging を横断し、「この指標は何を数えるか」 を 1 箇所に固定します。

KPI が 1 つのテーブルに収まらないとき（例: アクティブユーザー、ファネル、複数チャネルのイベント統合）、mart ごとに同じ UNION や JOIN を書くと定義が散らばります。 intermediate は、その SSOT（Single Source of Truth） を担う層です。

まず dbt の作法を整理し、続けて DS Playground の int_user_activity_events を読み解きます。

この章を終えると、こんなことができるようになります：

• intermediate 層の 責務 を staging / marts と区別して説明できる
• UNION ALL で複数 staging を統合する 理由と限界 を説明できる
• 共通列（occurred_at, date_day, event_type）に揃える設計を読める
• 中間モデルが複数 mart の 共通入力 になる構造を mermaid で示せる

パイプラインの中で intermediate はどこか

データマネジメント入門の Medallion 三層 では、Staging → Intermediate → Mart の流れを学びました。

intermediate の入力は staging だけ が理想です。 mart から source() を直叩きしたり、staging で済ませきれない KPI 定義 を mart ごとにコピーすると、ワイルドマートと SSOT で学んだ「属人 SQL の乱立」と同型の問題が dbt でも起きます。

intermediate でやること・やらないこと

やること

• 複数 staging の統合 … UNION ALL で縦に積み、共通列名に揃える
• 指標定義の固定 … 「アクティブとは何か」「コンバージョンイベントはどれか」を 1 モデルに書く
• 共通語彙の付与 … event_type, occurred_at, date_day など横断列
• 最小限の行フィルタ … grain が成立しない行（キー NULL 等）の除外
• schema test による契約 … Stage 2 第3章 で学んだ enum / not_null

やらないこと

• 日次 KPI の最終集計（GROUP BY date_day で DAU を出す）→ marts
• セグメント別の内訳（プラン種別、地域別）→ 別 mart または下流
• BI 向けの列選択・リネーム → marts
• date spine によるゼロ埋め → marts（必要なら）

intermediate は 「何を数えるか」の正本、mart は 「どう見せるか（grain・セグメント）」 — この分離が dbt 設計の核心です。

UNION ALL によるイベント統合

複数ソースから「ユーザーが何かをした」行を 1 つの SSOT にまとめる典型パターンが UNION ALL です。

-- 概念形
          select user_id, order_placed_at as occurred_at, order_placed_date as date_day, 'order_placed' as event_type
          from {{ ref('stg_app__orders') }}

          union all

          select user_id, page_viewed_at as occurred_at, page_viewed_date as date_day, 'page_viewed' as event_type
          from {{ ref('stg_app__page_views') }}
          

設計上のポイントは 3 つです。

1. UNION ではなく UNION ALL … 重複排除は意図的に行いません。同一ユーザーが同日に複数イベントを起こせば複数行になります。DAU は count(distinct user_id) で数えるため、行の重複は問題になりません。

2. 列名の揃え方 … ソースごとにタイムスタンプ列名は異なります（attempted_at, completed_at など）。staging で *_date を派生し、intermediate では occurred_at / date_day に統一 します（Stage 2 第2章）。

3. フィルタは最小限 … 「会員だけ」「有料だけ」などのビジネス分割は mart 側 に寄せ、SSOT は観測された行動を忠実に残します。

int_user_activity_events

DS Playground では、アクティブユーザー指標の SSOT として int_user_activity_events が置かれています。 6 種類の staging から学習行動イベントを UNION ALL で縦に積み、共通 4 列に揃えます。

出力 grain: 1 行 = 1 ユーザーの 1 学習行動イベント。

YAML description には、Active User の定義が明示されています。

A user is active in a period when the user has at least one row in this model during that period.

SQL の読み方（概念）

with activity_events as (
            select
              user_id,
              attempted_at as occurred_at,
              attempted_date as date_day,
              'practice_problem_saved' as activity_type
            from {{ ref('stg_supabase__problem_attempts') }}

            union all

            select
              user_id,
              recorded_at as occurred_at,
              recorded_date as date_day,
              'mock_exam_saved' as activity_type
            from {{ ref('stg_supabase__exam_attempt_events') }}

            -- curriculum / skill_check / learning_goal / survey も同様
          )

          select
            user_id,
            occurred_at,
            date_day,
            activity_type
          from activity_events
          where user_id is not null
            and occurred_at is not null
            and date_day is not null
          

カリキュラム進捗は status = 'completed' and completed_at is not null のように、ソースごとに最小限のフィルタ だけが intermediate に入ります。 「terms 会員だけ」などの分割は mart 側の責務です（第4章）。

activity_type 語彙（6 種類）

DS Playground では、明示的な保存・完了・送信 だけを Learning Action として数えます。 パッシブなセッションやページビューは Active User に含めません。「ログインしただけ」では DAU は増えず、何かを保存・完了・送信したとき初めて 1 行が SSOT に載ります。 これは 指標定義 で学ぶ「何を数えるか」の具体例です。

activity_type には schema test の accepted_values が付き、新しい行動を追加するときは SQL・YAML・docs をセットで更新 します。

SSOT としての責務境界

int_user_activity_events を変更すると、依存する すべての activity mart の数字が変わります。 だからこそ メタデータとリネージ で学んだ lineage 記録が重要です。

よくある誤解

体系コラム：カリキュラム上の位置づけ

まとめ

複数 staging を mart ごとに結合すると KPI 定義が散らばります。 intermediate で UNION ALL + 共通列 + 最小フィルタにより 定義を 1 箇所に固定 し、下流 mart がその SSOT を共有する——これが Stage 3 の出発点です。

DS Playground では int_user_activity_events が DAU・エンゲージメント・incremental mart の共通入力になっています。

関連教材

• 前章: seeds カタログ
• 次章: mart 設計
• 関連: Medallion 三層

確認問題

問題 1

複数 staging を UNION ALL で統合する intermediate モデルの 主な目的 として最も適切なのはどれですか。

A. 重複行を自動的に削除するため
B. 各ソースの行を縦に積み、共通のイベント定義 SSOT を作るため
C. SQL の実行速度を必ず向上させるため
D. source() を使わずに済ませるため

正解: B

解説: UNION ALL は重複排除をしません。目的は 定義の一元化 です。重複排除や集計は下流の mart で行います。

問題 2

intermediate 層に 置くべきでない 処理として最も適切なのはどれですか。

A. 複数 staging の UNION ALL
B. 共通列名へのリネーム（例: occurred_at, date_day）
C. 日次 KPI の最終集計（GROUP BY date_day）
D. イベント種別列の付与（例: event_type）

正解: C

解説: 集計と BI 向け粒度の確定は marts の責務です。intermediate は 定義の固定 に留めます。

問題 3

ベースの指標 mart と、セグメント別 mart を 分ける 主な理由として最も本質的なのはどれですか。

A. SQL が長いから
B. grain と business question が異なるから
C. YAML が書けないから
D. incremental だから

正解: B

解説: 全体の faithful count と、セグメント別の内訳は 別の問い です。1 表に混ぜると grain が壊れやすくなります。

用語メモ（この章）