dbt セマンティックレイヤーの JOIN の挙動を確認してみる

モダンデータスタック(MDS)

安原朋紀

2026.03.21

はじめに

dbt セマンティックレイヤーでは、MetricFlow が YAML で定義された entity の関係をもとに JOIN を自動生成します。
実際のモデルとクエリで挙動を確認した際の内容を記事としました。

MetricFlow の JOIN ロジック

MetricFlow の JOIN ルールについては以下に記載があります。

MetricFlow では entity 型（primary/foreign）の組み合わせによって、JOIN 種別と許可/不許可が判断されます。
代表的なルールは以下のとおりです。

起点モデルの entity 型	結合先モデルの entity 型	JOIN 種別	挙動
Foreign	Primary	LEFT OUTER JOIN	OK
Primary	Primary（別 fact を同時クエリ）	FULL OUTER JOIN	OK
Primary	Foreign	—	エラー（fan-out 防止）

また、multi-hop JOIN（複数テーブルをまたぐ結合）については最大 2 hops（3 テーブル）までサポートされています。

本記事では以下のパターンを実際に試してみました。

パターン1：fact → dim（Foreign → Primary）
パターン2：fact × fact の同時クエリ
パターン3：fact → fact（Primary → Foreign）
パターン4：2-hop JOIN
パターン5：3-hop JOIN（上限超過）

前提条件

検証環境

以下の環境を使用しています。

DWH：Snowflake
dbt platform
- セマンティックレイヤーの記法は dbt Fusion エンジンおよび dbt platform Latest リリーストラックで利用可能な新しい YAML 記法を使用しています

サンプルデータや作成済みの各種モデルなどは、以下の記事の環境をそのまま使用しています。

サンプルデータ

上記の記事では、公式クイックスタートをベースに、以下のテーブルを用意しています。

スキーマ	テーブル	内容
`raw.jaffle_shop`	`customers`	顧客マスタ（1行=1顧客）
`raw.jaffle_shop`	`orders`	注文明細（1行=1注文）
`raw.stripe`	`payment`	決済明細（1行=1決済、1注文に複数あり）

今回の検証のためにstg_customers（顧客 dim）と stg_payments（決済 fact）を追加しました。

stg_customers.sql

select
    id as customer_id,
    first_name,
    last_name

from {{ source('jaffle_shop', 'customers') }}

stg_customers.yml

version: 2

models:
  - name: stg_customers
    semantic_model:
      enabled: true

    columns:
      - name: customer_id
        entity:
          type: primary
          name: customer

      - name: first_name
        dimension:
          type: categorical

      - name: last_name
        dimension:
          type: categorical

stg_payments.sql

select
    id          as payment_id,
    orderid     as order_id,
    paymentmethod,
    status,
    amount / 100.0  as amount,
    created     as payment_date

from {{ source('stripe', 'payment') }}

stg_payments.yml

version: 2

models:
  - name: stg_payments
    semantic_model:
      enabled: true
    agg_time_dimension: payment_date

    columns:
      - name: payment_id
        entity:
          type: primary
          name: payment

      - name: order_id
        entity:
          type: foreign
          name: orders

      - name: payment_date
        granularity: day
        dimension:
          type: time

      - name: paymentmethod
        dimension:
          type: categorical

      - name: status
        dimension:
          type: categorical

    metrics:
      - name: payment_count
        type: simple
        label: 決済数
        agg: count
        expr: 1

      - name: total_payment_amount
        type: simple
        label: 合計決済金額
        agg: sum
        expr: amount

各種ファイルを用意した後、開発環境でモデルをビルドしておきます。

パターン1：Foreign → Primary（LEFT OUTER JOIN）

前提として、stg_ordersとして以下のセマンティックモデルを定義しています。

stg_orders.yml

version: 2

models:
  - name: stg_orders
    semantic_model:
      enabled: true
    agg_time_dimension: order_date  # metric_time として自動公開される

    columns:
      - name: order_id
        entity:
          type: primary
          name: orders  # "order" はSQL予約語のため "orders" を使用
                        # このname が --group-by の prefix になる（例: orders__status）

      - name: customer_id
        entity:
          type: foreign
          name: customer

      - name: order_date
        granularity: day  # 最小粒度。week/month/year への集計も可能
        dimension:
          type: time

      - name: status
        dimension:
          type: categorical

    metrics:
      - name: order_count
        type: simple
        label: 注文数
        agg: count
        expr: 1

      - name: distinct_customers
        type: simple
        label: ユニーク顧客数
        agg: count_distinct
        expr: customer_id

      - name: total_amount
        type: simple
        label: 合計金額
        agg: sum
        expr: amount

      - name: average_order_amount
        type: simple
        label: 平均注文金額
        agg: average
        expr: amount

stg_ordersのメトリクスorder_countを、顧客名（customer__first_name）ごとに集計してみます。

このときstg_ordersにはcustomerがforeign entityとして、stg_customersには同じcustomerがprimary entityとして定義されているため、MetricFlow でもstg_ordersからstg_customersへ JOIN できます。

出力は以下のようになり、顧客の first_name ごとの注文数が集計されます。

$ dbt sl query --metrics order_count --group-by customer__first_name
+----------------------+-------------+
| CUSTOMER__FIRST_NAME | ORDER_COUNT |
+----------------------+-------------+
| Fred                 |           1 |
| Johnny               |           1 |
| Virginia             |           1 |
| Sean                 |           3 |
・・・
| Jesse                |           1 |
| Frances              |           1 |
+----------------------+-------------+

生成される SQL を確認してみます。

dbt sl query --metrics order_count --group-by customer__first_name --compile

出力は以下のようになり、stg_ordersをベースにstg_customersを LEFT OUTER JOIN する形になっています。

SELECT
  stg_customers_src_10000.first_name AS customer__first_name
  , SUM(subq_1.__order_count) AS order_count
FROM (
  SELECT
    customer_id AS customer
    , 1 AS __order_count
  FROM dbt_sl_db.dbt_tyasuhara.stg_orders stg_orders_src_10000
) subq_1
LEFT OUTER JOIN
  dbt_sl_db.dbt_tyasuhara.stg_customers stg_customers_src_10000
ON
  subq_1.customer = stg_customers_src_10000.customer_id
GROUP BY
  stg_customers_src_10000.first_name
LIMIT 100

参考：entity 名が異なる場合

ここでは、結合する各セマンティックモデルで、name: customerとして同じ entity 名を指定していました。
この際、両側の entity 名が一致していなければ join path は存在しないと判定され、エラーとなります。

例として、stg_customersの entity 名をcustomerからclientに変更してみます。

stg_customers.yml

- name: customer_id
  entity:
    type: primary
    name: client   # stg_orders 側は "customer" のままなので名前が不一致

この状態でクエリを実行すると、以下のようにエラーになります。

dbt parse && dbt sl query --metrics order_count --group-by customer__first_name --compile

接続先モデルが見つからないため、customer__first_nameは候補に出て来なくなります。

No valid join paths exist from the measure to the group-by-item.

Suggestions:
  [
    "Entity('customer')",          # stg_orders 側の foreign entity "customer" は存在する
    "Entity('orders__customer')",  # 同上
    ...
  ]

パターン2：fact × fact（FULL OUTER JOIN）

2つの fact モデル（stg_orders、stg_payments）のメトリクスを同時にクエリしてみます。
内容としては、注文数、決済数を月別で集計しています。

dbt sl query --metrics order_count,payment_count --group-by metric_time__month --order-by metric_time__month
+-------------------------+-------------+---------------+
| METRIC_TIME__MONTH      | ORDER_COUNT | PAYMENT_COUNT |
+-------------------------+-------------+---------------+
| 2018-01-01T00:00:00.000 |          29 |            37 |
| 2018-02-01T00:00:00.000 |          27 |            32 |
| 2018-03-01T00:00:00.000 |          35 |            41 |
| 2018-04-01T00:00:00.000 |           8 |            10 |
+-------------------------+-------------+---------------+

生成される SQL は以下のようになります。

dbt sl query --metrics order_count,payment_count --group-by metric_time__month --compile

SELECT
  COALESCE(subq_5.metric_time__month, subq_11.metric_time__month) AS metric_time__month
  , MAX(subq_5.order_count) AS order_count
  , MAX(subq_11.payment_count) AS payment_count
FROM (
  SELECT
    metric_time__month
    , SUM(__order_count) AS order_count
  FROM (
    SELECT
      DATE_TRUNC('month', order_date) AS metric_time__month
      , 1 AS __order_count
    FROM dbt_sl_db.dbt_tyasuhara.stg_orders stg_orders_src_10000
  ) subq_3
  GROUP BY
    metric_time__month
) subq_5
FULL OUTER JOIN (
  SELECT
    metric_time__month
    , SUM(__payment_count) AS payment_count
  FROM (
    SELECT
      DATE_TRUNC('month', payment_date) AS metric_time__month
      , 1 AS __payment_count
    FROM dbt_sl_db.dbt_tyasuhara.stg_payments stg_payments_src_10000
  ) subq_9
  GROUP BY
    metric_time__month
) subq_11
ON
  subq_5.metric_time__month = subq_11.metric_time__month
GROUP BY
  COALESCE(subq_5.metric_time__month, subq_11.metric_time__month)
LIMIT 100

2つの fact を同時にクエリする場合、各 fact を先に集約し、その後で metric_time などの共通軸に対して FULL OUTER JOIN する構造となっていました。
なお、出力でpayment_countの方がorder_countより大きなっていますが、これは1注文に複数の決済が紐づいているためです。

参考：metric_time が異なるカラムでも集計できる点

stg_ordersのagg_time_dimensionはorder_date、stg_paymentsはpayment_dateと、参照する物理カラムが異なります。それでも metric_time__monthで集計できています。

これは、metric_timeが MetricFlow によって生成される時間軸なためです。
生成される SQL を確認すると、各モデルのagg_time_dimensionをmetric_time__monthという共通名に変換していることが確認できます。

-- stg_orders 側: order_date を metric_time__month に変換
DATE_TRUNC('month', order_date)   AS metric_time__month

-- stg_payments 側: payment_date を metric_time__month に変換
DATE_TRUNC('month', payment_date) AS metric_time__month

各モデルが変換した値で FULL OUTER JOIN するため、各モデルで異なる日付カラムを持っていても、metric_timeを経由することで同じ粒度で比較・結合できます。

パターン3：Primary → Foreign（エラー）

ここでは、stg_ordersのメトリクスorder_countを、stg_paymentsのディメンションpayment__paymentmethodで集計できるかを試してみます。

stg_ordersではordersがprimary entity、stg_paymentsでは同じordersがforeign entityとして定義されており、この経路は Primary → Foreign の関係にあたります。

dbt sl query --metrics order_count --group-by payment__paymentmethod --compile

この場合、以下のエラーとなりました。

Error #1:
  Message:

    The given input does not match any of the available group-by-items for
    SimpleMetric('order_count'). Common issues are:

      * No valid join paths exist from the measure to the group-by-item.
        (fan-out join support is pending).

    Suggestions:
      [
        "Dimension('customer__last_name')",
        "Dimension('customer__first_name')",
        "TimeDimension('metric_time', 'day')",
        "Dimension('orders__status')",
        "TimeDimension('orders__order_date', 'day')",
        "Entity('orders__customer')",
      ]

Suggestions に payment__ 系のディメンションが一つも含まれていない点が確認できます。stg_orders の1行（1注文）に対して stg_paymentsの行は複数存在しうるため、JOIN するとorder_countが重複カウント（fan-out が発生）されます。

そのため、Primary → Foreign の経路は、候補にもならずエラーとして処理されているようです。

パターン4：2-hop JOIN

ここでは、stg_paymentsを起点にstg_customersのfirst_nameを参照する 2-hop JOIN を試してみます。

stg_paymentsが直接持っているのはorder_idまでで、顧客名は持っていません。そのため、まずorder_idを使ってstg_ordersに JOIN し、注文に紐づくcustomer_idを取得します。さらに、そのcustomer_idを使ってstg_customersに JOIN することで、最終的に`first_name を参照できます。

出力は以下のようになり、first_nameごとの決済数が集計できました。

$ dbt sl query --metrics payment_count --group-by customer__first_name

+------------------------------+---------------+
| ORDERS__CUSTOMER__FIRST_NAME | PAYMENT_COUNT |
+------------------------------+---------------+
| Christina                    |             7 |
| Jack                         |             3 |
| Diana                        |             2 |
・・・
| Dennis                       |             1 |
| Norma                        |             3 |
+------------------------------+---------------+

SQLも確認してみます。

dbt sl query --metrics payment_count --group-by customer__first_name --compile

SELECT
  subq_10.customer__first_name AS orders__customer__first_name
  , SUM(subq_4.__payment_count) AS payment_count
FROM (
  SELECT
    order_id AS orders
    , 1 AS __payment_count
  FROM stg_payments stg_payments_src_10000
) subq_4
LEFT OUTER JOIN (
  SELECT
    stg_orders_src_10000.order_id AS orders
    , stg_customers_src_10000.first_name AS customer__first_name
  FROM stg_orders stg_orders_src_10000
  LEFT OUTER JOIN
    stg_customers stg_customers_src_10000
  ON
    stg_orders_src_10000.customer_id = stg_customers_src_10000.customer_id
) subq_10
ON
  subq_4.orders = subq_10.orders
GROUP BY
  subq_10.customer__first_name
LIMIT 100

内側のサブクエリでstg_ordersとstg_customersの LEFT OUTER JOIN を先に行い、その結果を外側でstg_paymentsと結合していました。

パターン5：3-hop JOIN（エラー）

さいごにstg_paymentsから地域名を参照する 3-hop JOIN を試してみます。stg_paymentsからは直接地域情報を参照できないため、stg_orders、stg_customersを順にたどり、最終的にstg_regionsに到達する経路を作ります。

なお、この検証用にstg_regionsを seed から作成した地域マスタとして用意し、stg_customersにはstg_regionsと結合するためのregion_id（foreign entity）を追加しています。

regions.csv

region_id,region_name
1,East
2,West
3,North
4,South

stg_customers.sql

select
    id as customer_id,
    first_name,
    last_name,
    (id % 4) + 1 as region_id  -- 1〜4 のダミーリージョン（multi-hop JOIN 検証用）

from {{ source('jaffle_shop', 'customers') }}

この上で、以下の処理を試してみます。

dbt sl query --metrics payment_count --group-by region__region_name --compile

Error #1:
  Message:

    The given input does not match any of the available group-by-items for
    SimpleMetric('payment_count'). Common issues are:

      * No valid join paths exist from the measure to the group-by-item.
        (fan-out join support is pending).

    Suggestions:
      [
        "Dimension('customer__first_name', entity_path=['orders'])",
        "Dimension('customer__last_name', entity_path=['orders'])",
        "Entity('customer__region', entity_path=['orders'])",
        "Dimension('payment__paymentmethod')",
        "TimeDimension('payment__payment_date', 'day')",
        "TimeDimension('orders__order_date', 'day')",
      ]

実行結果は上記のとおりエラーとなりました。

ただし、パターン3のように結合パス自体が見つからないケースとは異なり、今回は Suggestions にEntity('customer__region', entity_path=['orders'])が表示されていました。これにより、MetricFlow が orders を経由して customer__region という entity までは認識できていると考えられます。

ただし、地域名を取得するにはさらに stg_regions までたどる必要があり、この経路は 3-hop になります。そのため、multi-hop JOIN の上限である 2 hops を超過し、エラーとなっています。

さいごに

MetricFlow の JOIN ロジックについて、動作を確認してみました。
こちらの内容がどなたかの参考になれば幸いです。