MetricFlow の derived_semantics で複合キーの entity を定義してみる
はじめに
dbt Semantic Layer（MetricFlow）の derived_semantics を使って複合キーを entity として定義した際に、セマンティックモデル間の JOIN がどのように行われるか確認した際の内容を記事としました。
derived_semantics
セマンティックモデルのcolumns:配下の entity 定義は単一カラムのみ対応しています。
事前に dbt モデル（SQL）でカラムを結合したキー列を生成済みであれば、columns:で定義できますが、セマンティックモデル側でも複数カラムの組み合わせや式で entity キーを表現したい場合は models: プロパティ直下の derived_semantics を使用できます。
具体的には、以下のように定義します。
derived_semantics:
entities:
- name: order_item
type: foreign
expr: "CAST(order_id AS VARCHAR) || '-' || CAST(item_id AS VARCHAR)"
ここでは、derived_semanticsによるエンティティでの集計、セマンティックモデル間の JOIN が実際に SQL でどのように解決されるかを見ていきます。
前提条件
検証環境
以下の環境を使用しています。
- DWH：Snowflake
- dbt platform
- セマンティックレイヤーの記法は dbt Fusion エンジンおよび dbt platform Latest リリーストラックで利用可能な新しい YAML 記法を使用しています
検証内容
ここでは2つのセマンティックモデルを以下の内容で定義しました。
| テーブル | キー列の持ち方 | entity 定義方法 |
|---|---|---|
stg_order_items |
SQL で order_item_key 列を生成 |
columns: の type: primary |
stg_item_reviews |
SQL に追加なし | derived_semantics の type: foreign + expr |
entity 名（order_item）が一致していれば、定義方法が異なる両モデルが JOIN されるかを確認します。
事前準備
サンプルデータは Seed として用意しました。
order_id,item_id,product_name,quantity,unit_price,order_date
1,1,Apple,3,100,2024-01-01
1,2,Banana,5,50,2024-01-01
2,1,Apple,2,100,2024-01-05
2,3,Cherry,4,200,2024-01-05
3,2,Banana,1,50,2024-01-10
3,3,Cherry,2,200,2024-01-10
注文 1 は明細 1（Apple）と 2（Banana）の2行を持つため、order_id 単独では一意になりません。
review_id,order_id,item_id,rating,review_date
1,1,1,5,2024-01-03
2,1,2,3,2024-01-03
3,2,1,4,2024-01-07
4,2,3,5,2024-01-08
5,3,3,2,2024-01-12
モデル定義
各 dbt モデルとセマンティックモデルも定義します。
stg_order_itemsでは、order_id 単独では一意にならないため、dbt モデル側で SQL でキー列を生成しました。
select
CAST(order_id AS VARCHAR) || '-' || CAST(item_id AS VARCHAR) as order_item_key,
order_id,
item_id,
product_name,
quantity,
unit_price,
order_date
from {{ ref('order_items') }}
dbt モデル側でキー列を単一カラムとして生成済みなのでcolumns:で SQL で生成した物理カラム order_item_key を primary entity として定義しています。
その他、例としていくつかディメンションやメトリクスも定義しています。
version: 2
models:
- name: stg_order_items
semantic_model:
enabled: true
agg_time_dimension: order_date
columns:
- name: order_item_key
entity:
type: primary
name: order_item
- name: order_date
granularity: day
dimension:
type: time
- name: product_name
dimension:
type: categorical
metrics:
- name: order_item_revenue
type: simple
agg: sum
expr: "quantity * unit_price"
- name: order_item_count
type: simple
agg: count
expr: 1
stg_item_reviews 側では、dbt モデル側でキー列を生成しないでおきます。
select
review_id,
order_id,
item_id,
rating,
review_date
from {{ ref('item_reviews') }}
セマンティックモデル側の定義は以下のようにしました。
SQL 側ではなく derived_semantics の expr に式を記述することで foreign entity を定義しています。entity 名は stg_order_items の primary entity と同じ order_item にそろえています。これにより、両モデル間はこの Entity 名で定義したキーで結合できるようになります。
version: 2
models:
- name: stg_item_reviews
semantic_model:
enabled: true
agg_time_dimension: review_date
derived_semantics:
entities:
- name: order_item
type: foreign
expr: "CAST(order_id AS VARCHAR) || '-' || CAST(item_id AS VARCHAR)"
columns:
- name: review_id
entity:
type: primary
name: review
- name: review_date
granularity: day
dimension:
type: time
metrics:
- name: avg_rating
type: simple
agg: average
expr: rating
- name: review_count
type: simple
agg: count
expr: 1
モデルをビルドしておきます。
dbt build --select "order_items item_reviews stg_order_items stg_item_reviews"
指標をクエリしてみる
derived_semantics で定義した複合キーで集計（order_item ごとの avg_rating）
Entity でも集計できるので、derived_semantics で定義した複合キーでグループ化してみます。この場合、単一モデル（stg_item_reviews）で完結するので、JOIN は発生しません。
コマンドと結果は以下のようになりました。
$ dbt sl query --metrics avg_rating --group-by order_item
+------------+------------+
| ORDER_ITEM | AVG_RATING |
+------------+------------+
| 2-1 | 4 |
| 3-3 | 2 |
| 2-3 | 5 |
| 1-1 | 5 |
| 1-2 | 3 |
+------------+------------+
order_item（entity キー）で group-by すると、expr で定義した複合キー値（order_id-item_id）ごとの avg_rating を取得できます。
コンパイル SQL も確認します。
dbt sl query --metrics avg_rating --group-by order_item --compile
SELECT
order_item
, AVG(__avg_rating) AS avg_rating
FROM (
SELECT
CAST(order_id AS VARCHAR) || '-' || CAST(item_id AS VARCHAR) AS order_item
, rating AS __avg_rating
FROM stg_item_reviews
) subq_3
GROUP BY
order_item
LIMIT 100
derived_semantics の expr がサブクエリ内で評価されて order_item 列が生成されており、この生成されたカラムで集約されていることがわかります。
derived_semantics で定義した複合キーでの JOIN（product_name ごとの avg_rating）
derived_semantics で定義した複合キーでの JOIN が発生する例として product_name ごとの avg_rating をクエリしてみます。
クエリ内容と結果は以下の通りです。
$ dbt sl query --metrics avg_rating --group-by order_item__product_name
+--------------------------+------------+
| ORDER_ITEM__PRODUCT_NAME | AVG_RATING |
+--------------------------+------------+
| Banana | 3 |
| Cherry | 3.5 |
| Apple | 4.5 |
+--------------------------+------------+
product_name は stg_order_items のディメンションです。また、avg_rating は stg_item_reviews 側のメトリクスなので、order_item entity 経由の JOIN が発生します。
コンパイル SQL を確認してみます。
dbt sl query --metrics avg_rating --group-by order_item__product_name --compile
SELECT
stg_order_items_src_10000.product_name AS order_item__product_name
, AVG(subq_1.__avg_rating) AS avg_rating
FROM (
SELECT
CAST(order_id AS VARCHAR) || '-' || CAST(item_id AS VARCHAR) AS order_item
, rating AS __avg_rating
FROM stg_item_reviews
) subq_1
LEFT OUTER JOIN
stg_order_items stg_order_items_src_10000
ON
subq_1.order_item = stg_order_items_src_10000.order_item_key
GROUP BY
stg_order_items_src_10000.product_name
LIMIT 100
JOIN の仕組みを整理すると以下の通りです。
- foreign 側（
derived_semantics）：サブクエリでexprを評価してorder_item列を生成 - primary 側（
columns:）：物理カラムorder_item_keyを直接参照 - JOIN 条件：
subq_1.order_item = stg_order_items_src_10000.order_item_key
entity 名（order_item）が一致することで JOIN が解決されています。また、定義方法が異なっていても JOIN キーの値が一致するため結合できています。複合キーの組み立ても MetricFlow がクエリ生成時に SQL へ展開して解決してくれていました。
結合方法としては、foreign → primary 方向なので、LEFT OUTER JOIN となっています。
さいごに
derived_semantics を使って複合キーを entity として定義し、セマンティックモデル間の JOIN の様子を確認してみました。
単一のキー項目が物理カラムとして存在しない場合でも、最終的にはセマンティックモデル側（YAML 側）で吸収も可能です。
こちらの内容がどなたかの参考になれば幸いです。
