dbt Semantic Layerのmeasures定義ガイド: 集計対象の設計と試験対策

dbtのSemantic Layerでは、semantic_model内にmeasures（集計対象）を定義し、後段のmetricsやBIツールから一貫した集計を提供します。

本稿は公式ドキュメントに基づく安定概念（name、agg、expr、代表的な集計種別など）に絞り、バージョン差異の影響が小さい設計原則にフォーカスします。

measuresの基礎: 役割と最小プロパティ

measureは、特定の列（または式）に対して定めた集計規則のことです。semantic_modelが参照する基礎モデルの行に対し、sum・avg・min・max・count・count_distinctなどの集計を定義します。metricsはしばしばこれらのmeasureを組み合わせて利用しますが、measure自体は“何をどう集計するか”を表す最小単位です。

定義はYAMLで行い、一般的にname（一意な名称）、agg（集計種別）、expr（対象列や式）を指定します。countのように行数を数える場合はexprを省略でき、sumやavgでは対象列をexprで明示します。これらはdbtの公式仕様で長く安定している概念です。

• name: モデル横断で再利用されるため命名は一貫性重視（例: revenue、order_count）
• agg: sum / avg / min / max / count / count_distinct
• expr: 集計対象の列名や式（countで省略時は行数カウントを意図）
• ディメンションやエンティティ（主キー）と併せて定義し、粒度の衝突を避ける

集計設計の落とし穴を避ける

よくある失敗は、粒度が不安定な基礎モデルにmeasureを置くことです。1レコード=1ファクト（例: 1注文、1明細、1日次スナップショット）を満たすモデルにmeasureを定義し、重複結合による二重計上を防ぎます。

NULLや重複の扱いも試験・実務で問われがちです。SUMはNULLを無視しますが、AVGは分母がNULLを除いた件数になる点を理解しておきます。COUNTはCOUNT(*)とCOUNT(col)で意味が違い、COUNT(col)はNULLを無視します。COUNT DISTINCTは非加法的であるため、上位粒度にロールアップすると値が一致しないことがあります。

• 複数テーブルJOINでmeasure側の粒度が崩れると二重計上が発生
• COUNTとCOUNT DISTINCTの混同に注意（特にユニークユーザ）
• 平均は非加法：集計を重ねると値が変わる。必要なら加重平均を検討
• 時間ディメンションと組み合わせる際は欠損日やタイムゾーンを確認

measureが利用される流れ（概念）

代表的な集計タイプの比較

集計タイプごとの加法性とNULLの扱いは、試験でも実務でも最重要ポイントです。下表で振る舞いを押さえ、ユースケースに適切なmeasureを選びます。

特に平均と重複排除（COUNT DISTINCT）は非加法で、上位粒度へのロールアップや他ディメンションとの同時集計時に直感と異なる結果になりがちです。

• SUMはNULLを無視する（多くのSQL方言でSUM(NULL)=NULLではなく、集計結果はNULLを除外）
• COUNT(*)は行数、COUNT(col)はNULLを除いた件数
• COUNT DISTINCTは非加法。期間やディメンションを跨ぐと値が一致しない
• AVGは再集計で値が変わる。必要に応じて分子（合計）と分母（件数）のmeasureから算出

粒度・エンティティ設計とmeasureの相性

measureは“どの粒度で足し合わせられるか”に直結します。semantic_modelが指す基礎モデルは、明確なエンティティ（例: order_id）や時間ディメンション（例: order_date day）を持ち、1行=1イベントまたは1スナップショットであることが理想です。

エンティティには主キー（primary）と外部キー（foreign）を適切に設定し、ディメンションにはtimeやcategoricalを割り振ります。こうすることで、count_distinctのような非加法measureでも、どの単位で解釈されるかを明確化できます。

• 1行=1ファクトのモデルにsum系measureを定義する（例: 明細単位の売上）
• ユーザ数などはcount_distinctだが、期間やセグメントを跨いだ再集計が非加法であることを明示
• 時間ディメンションは最小粒度（日など）を定義し、上位粒度へはロールアップ
• 平均・比率は、分子と分母を個別measureで保持し、必要時に計算

YAMLでのmeasure定義: 実務的な最小例

以下は、注文ファクトテーブルを参照するsemantic_modelに対して、典型的なmeasureを定義した例です。sum、count、count_distinctの3種を示しています。avgなどの非加法集計は、必要に応じて分子（sum）と分母（count）を別measureで持つのが安全です。

このレベルの定義は安定した仕様に基づくため、dbtのバージョン差異の影響を受けにくく、試験でも強く問われます。

• sumは対象列をexprで指定（数値型を推奨）
• countはexpr省略可（行数カウント）
• count_distinctは対象列をexprで明示
• 時間ディメンションの最小粒度を定めておく（dayなど）

semantic_model内でのmeasure定義（YAML）

semantic_models:
  - name: orders
    model: ref('fct_orders')
    entities:
      - name: order_id
        type: primary
      - name: customer_id
        type: foreign
    dimensions:
      - name: order_date
        type: time
        type_params:
          time_granularity: day
      - name: status
        type: categorical
    measures:
      - name: revenue
        agg: sum
        expr: total_amount
      - name: order_count
        agg: count
      - name: customer_count
        agg: count_distinct
        expr: customer_id

検証・テストと試験対策ポイント

measureの品質は基礎モデルの品質に依存します。主キーの一意性、外部キーの被参照整合性、金額列の型・スケール、時間ディメンションの欠損などをdbt testで確かめます。また、簡単な検証SQLでサンプル期間の期待値と突合し、JOINで水増しがないかを確認します。

試験対策としては、加法性、NULLの扱い、COUNTとCOUNT DISTINCTの違い、平均の再集計不可、measureの粒度要件（1行=1ファクト）を説明できることが重要です。

• dbt testでnot_null/uniqueを主キーに、accepted_valuesでステータス列を検証
• 金額列は数値型・通貨単位の統一、丸め位置をモデル側で明確化
• 検証SQLでSUMとCOUNTの期待値を小さなサンプルで手計算と照合
• 平均・率は分子/分母measureから派生させる癖を付ける

問題で確認

よくある質問