dbt Semantic Layer (MetricFlow) の理解を深める and roots株式会社　谷口 健太 ( たぬ ) 2024-06-21 Fri © and roots, Inc. All rights reserved. Tokyo dbt Meetup #9

自己紹介 © and roots, Inc. All rights reserved. 1 Tokyo dbt Meetup #9 谷口 健太（Kenta Taniguchi） and roots 株式会社 / アナリティクスエンジニア 経歴: ● マーケティング ( 3 年 ) ● データアナリスト ( 2 年 ) ● アナリティクスエンジニア ( 3 年 ) 事業紹介: ● 通信販売事業 ● コンサルティング事業 ● コールセンター事業 ● フォトスタジオ事業 ● 保育事業 趣味: ● カレー、コーヒー、料理 ● スプラトゥーン ( 3000 時間 )、Minecraft ( 1000 時間 ) ● キャンプ、温泉 2024 年は、ものづくり（自作 PC、自作キーボード、電子工作、3D プリンターなど）に挑戦中 💪 ＠tanuhack

データの偉大さを再認識した話 アイスブレイク © and roots, Inc. All rights reserved. 2 Tokyo dbt Meetup #9

大学を卒業して、30kg 体重が増えた 毎年、コンスタントに 3 〜 4 キロずつ増えていった。 アイスブレイク © and roots, Inc. All rights reserved. 3 Tokyo dbt Meetup #9 2016 2024 58 88

何をしても体重は減らなかった アイスブレイク © and roots, Inc. All rights reserved. 4 Tokyo dbt Meetup #9 1 日 5 km ランニング ( 50 日 ) パーソナルジム ( 2 年 ) 1 日 10,000 歩 ( 1 年 ) 流行り病で習慣が乱れ中断 ひざを壊し中断 ひたすらバルクアップ ( B100、S120、D140 )

データを生業としていることを思い出した 困難な課題（減量）に立ち向かうには、データドリブンアプローチしかない！と思い立ち、 徹底的に摂取カロリー（PFC バランス）の観測を行った。 アイスブレイク © and roots, Inc. All rights reserved. 5 Tokyo dbt Meetup #9

開始して 1 か月、6 キロの減量に成功 やはりデータ....！！データは全てを解決する....！！ アイスブレイク © and roots, Inc. All rights reserved. 6 Tokyo dbt Meetup #9

アジェンダ 1. セマンティックレイヤー速習 2. dbt Semantic Layer 速習 3. まだ dbt Semantic Layer を採用できない理由 © and roots, Inc. All rights reserved. Tokyo dbt Meetup #9

dbt Semantic Layer について、まったく触ったことがない方から、チュートリアルを完了さ れた方まで、幅広い方々の理解を深めるためのガイドを目指しています。 LT の目的と想定読者 © and roots, Inc. All rights reserved. 7 Tokyo dbt Meetup #9 低 低 高 高 セマンティックレイヤーの理解度 dbt スキル

アジェンダ 1. セマンティックレイヤー速習 2. dbt Semantic Layer 速習 3. まだ dbt Semantic Layer を採用できない理由 © and roots, Inc. All rights reserved. Tokyo dbt Meetup #9

セマンティックレイヤーとは DWH（データ）とエンドユーザー（データ利用者）の間の「橋渡し」となる存在。複雑で理 解が難しいデータ構造を、ビジネスで馴染みのある用語や概念に変換することで、データサ イエンティストや SQL を書ける人に頼ることなく、データにアクセスし、活用できる。 1. セマンティックレイヤー速習 © and roots, Inc. All rights reserved. 8 Tokyo dbt Meetup #9 セマンティックレイヤーなし セマンティックレイヤーあり DWH select count(distinct fct_order.order_id) as order_count, sum(fct_order.sales_amount) as sales_amount, from fct_order left join dim_date on fct_order.date_key = dim_date.date_key where dim_date.year_and_month = "2024.06" DWH ①SQL ②結果 order_count sales_amount 10 20000 ②SQL ③結果 ①条件 order_count sales_amount 10 20000 select count(distinct fct_order.order_id) as order_count, sum(fct_order.sales_amount) as sales_amount, from fct_order left join dim_date on fct_order.date_key = dim_date.date_key where dim_date.year_and_month = "2024.06" ● Dimensions: なし ● Facts: ○ 注文件数 ○ 売上 ● Filters: 今月

セマンティックレイヤー黎明期 ( 2010 年代 ) さまざまな BI ツールが乱立し、各ツールが独自のメトリクスレイヤーを持つようになった。 1. セマンティックレイヤー速習 © and roots, Inc. All rights reserved. 9 Tokyo dbt Meetup #9 DWH BI ツール 1 ダッシュボード ダッシュボード ダッシュボード ダッシュボード ダッシュボード ダッシュボード ダッシュボード ダッシュボード ダッシュボード BI ツール 2 BI ツール 3

黎明期の課題 各 BI ツールでメトリクスレイヤーを定義できるということは、ビジネスロジックの解釈の差 分により、ダッシュボードによって表示されている数値が異なるといった課題も発生するよ うになった。また、データ利用の拡大に伴って BI ツールだけではなく、Notebook や LLM、 表計算ソフトなど、様々なアプリケーションから接続したいというニーズも高まった。 1. セマンティックレイヤー速習 © and roots, Inc. All rights reserved. 10 Tokyo dbt Meetup #9 課題 1:データカオス sales_amount 20000 課題 2: BI ツールを越えたニーズの増加 sales_amount 21000 Notebooks Frontend Apps AI Platforms sales_amount 22000 BI Tools

ユニバーサル・セマンティックレイヤーの登場 ユニバーサル・セマンティックレイヤーとは、従来のメトリクスレイヤーの機能を拡張し、 データの信頼性向上、パフォーマンス向上と DWH のコスト削減、データ民主化の加速を実 現する概念。 1. セマンティックレイヤー速習 © and roots, Inc. All rights reserved. 11 Tokyo dbt Meetup #9 従来のセマンティックレイヤー Notebooks Frontend Apps AI Platforms BI Tools ユニバーサル・セマンティックレイヤー Notebooks Frontend Apps AI Platforms BI Tools Metrics Layer Metrics Layer Access Control Caching APIs

様々な別名が存在する セマンティックレイヤーは「メトリクスレイヤー」や「メトリクスハブ」、「Headless BI」 などとも呼ばれることがある。しかし、これらの名称はセマンティックレイヤーの本質的な 役割を十分に表現していないと考えられるため「セマンティックレイヤー」と呼ぶべき。 1. セマンティックレイヤー速習 © and roots, Inc. All rights reserved. 12 Tokyo dbt Meetup #9 理由 1: メトリクスの管理以外にも多くの役割を持つ Metrics Layer 理由 2: BI の枠を越えた汎用性の高い技術 Access Control Caching APIs BI Tools Notebooks Frontend Apps AI Platforms

セマンティックレイヤーを提供するサービス データ変換レイヤーの dbt Semantic Layer、機能や接続先の種類、数でユニバーサル・セマ ンティックレイヤー界を一歩リードしている Cube、他には Google 製品とのシナジーが期待 され独自路線を走りそうな Looker、ファーストパーティのセマンティックレイヤーとして注 目が高まっている Snowflake がある。 1. セマンティックレイヤー速習 © and roots, Inc. All rights reserved. 13 Tokyo dbt Meetup #9 Application DWH dbt Cube Looker Snowflake

アジェンダ 1. セマンティックレイヤー速習 2. dbt Semantic Layer 速習 3. まだ dbt Semantic Layer を採用できない理由 © and roots, Inc. All rights reserved. Tokyo dbt Meetup #9

できること dbt Semantic Layer は現時点 ※2024.6 で、メトリクスレイヤー、キャッシュ、API 機能を正式に サポートしている。アクセス制御も近い将来、導入することが決定している。 2. dbt Semantic Layer 速習 © and roots, Inc. All rights reserved. 14 Tokyo dbt Meetup #9 dbt Semantic Layer Notebooks Frontend Apps AI Platforms BI Tools Metrics Layer Access Control Caching APIs

前提条件 2. dbt Semantic Layer 速習 © and roots, Inc. All rights reserved. 15 Tokyo dbt Meetup #9 ● dbt Cloud の Team または Enterprise アカウントを使用している ○ dbt Core または Developer アカウントはメトリクスを定義できるが、動的に クエリを実行することはできない ● 本番環境と開発環境の両方で、dbt のバージョンが 1.6 以上 ● 本番環境のみクエリを実行できる（開発環境は近日公開予定） ● DWH が Snowflake、BigQuery、Databricks、Redshift のいずれか dbt Semantic Layer をダウンストリームのアプリケーションで使用するための条件は、以下 の通り。

obt_order major_category intermediate_category minor_category product order_line order_coupon order_header user user_rank 前提条件 2 2. dbt Semantic Layer 速習 © and roots, Inc. All rights reserved. 16 Tokyo dbt Meetup #9 データモデルは、OBT または Star Schema から選択することになる。属性が重複すると Measures や Metrics の命名に苦労するため、個人的に Star Schema がオススメ。 column dtype rank_cd string rank string column dtype user_id string name string rank_cd string column dtype order_id string order_date datetime order_status string user_id string order_price int64 shipping_cost int64 column dtype order_id string order_coupon_no int64 coupon_discount int64 column dtype order_id string order_line_no int64 product_id string item_quantity int64 item_price int64 column dtype product_id string product_name string unit_price int64 minor_category_cd string column dtype minor_category_cd string minor_category_name string intermediate_category_cd string column dtype intermediate_category_cd string intermediate_category_name string major_category_cd string column dtype major _category_cd string major_category_name string column dtype order_id string order_line_no int64 order_header_count int64 order_line_count int64 order_date datetime order_status string shipping_cost int64 product_id string product_name string unit_price int64 item_quantity int64 item_price int64 coupon_discount int64 minor_category_cd string minor_category_name string intermediate_category_cd string intermediate_category_name string major_category_cd string major_category_name string user_id string name string lifetime_value int64 rank_cd string rank string ❌ 3NF（Third Normal Form, 第 3 正規形） ✅ OBT（One Big Table, 大福帳テーブル） ✅ Star Schema dim_date column dtype date_key int64 full_date date year string year_and_month string month_of_year string day_of_month string dim_user column dtype user_key int64 user_id date name string lifetime_value int64 rank_cd string rank string dim_product column dtype product_key int64 product_id date product_name string unit_price int64 minor_category_cd string minor_category_name string intermediate_category_cd string intermediate_category_name string major_category_cd string major_category_name string dim_order_junk column dtype order_junk _key int64 order_status string order_header_count int64 order_line_count int64 fct_order column dtype order_key int64 date_key int64 product_key int64 user_key int64 order_junk_key int64 order_id string order_line_no int64 order_date datetime item_quantity int64 item_price int64 coupon_discount int64 shipping_cost int64

dbt Semantic Layer 2. dbt Semantic Layer 速習 © and roots, Inc. All rights reserved. 17 Tokyo dbt Meetup #9 dbt Semantic Layer は、セマンティックレイヤーの概念を具体的に実現するための機能。 MetricFlow を中核とする複数のコンポーネントで構成されている。 dbt Semantic Layer（MetricFlow） セマンティックレイヤー 実装 dbt Semantic Layer（dbt Metrics） セマンティックレイヤー 実装 廃止

MetricFlow 2. dbt Semantic Layer 速習 © and roots, Inc. All rights reserved. 18 Tokyo dbt Meetup #9 MetricFlow は、dbt Semantic Layer の中核となる機能で、ビジネスメトリクスのロジックを 定義・管理するためのツール。SQL クエリの構築を担い、dbt の Semantic Models と Metrics の仕様を定義する。MetricFlow = Semantic Models + Metrics MetricFlow アプリケーション ① SELECT user_dimension_src_10000.name AS user_dimension__name , SUM(order_fact_src_10000.item_price) AS sum_revenue FROM `{{ PROJECT_ID }}`.`{{ DATASET_ID }}`.`fct_order` order_fact_src_10000 LEFT OUTER JOIN `{{ PROJECT_ID }}`.`{{ DATASET_ID }}`.`dim_user` user_dimension_src_10000 ON order_fact_src_10000.user_key = user_dimension_src_10000.user_key GROUP BY user_dimension__name LIMIT 10 SQL 生成 Semantic Models Metrics DWH ② ③ ④

プロジェクトの構成方法 dbt Best Practices [↗] によると、dbt Semantic Layer を使用する場合、従来のプロジェクト の構成方法の基本原則が変更され、MetricFlow が最も柔軟に対応できるように 1 つのモデル に対して 1 つの構成ファイルを用意することが勧められている。 2. dbt Semantic Layer 速習 / MetricFlow © and roots, Inc. All rights reserved. 19 Tokyo dbt Meetup #9 # 従来の構成 models/marts ├── finance │ ├── _finance__models.yml │ ├── orders.sql │ └── payments.sql └── marketing ├── _marketing__models.yml └── customers.sql # dbt Semantic Layerを使用 models/marts ├── customers.sql ├── customers.yml ├── orders.sql ├── orders.yml ├── payments.sql └── payments.yml

構成ファイル models プロパティは DWH 、semantic_models, metrics プロパティはセマンティックレイ ヤー（MetricFlow）に対する設定を記述する場所と覚えておくと迷子になりにくい。 2. dbt Semantic Layer 速習 / MetricFlow © and roots, Inc. All rights reserved. 20 Tokyo dbt Meetup #9 version: 2 models: # 略 semantic_models: # 略 metrics: # 略 DWH アプリケーションに対する設定 セマンティックレイヤー（MetricFlow）に対する設定 DWH セ マ ン テ ィ ッ ク レ イ ヤ ｜ ア プ リ ケ ｜ シ ョ ン

Semantic Models 2. dbt Semantic Layer 速習 / MetricFlow © and roots, Inc. All rights reserved. 21 Tokyo dbt Meetup #9 Semantic Models は、Metrics でビジネスメトリクスを定義するための基礎となるデータ構造 （Dimensions、Measures）と関係性（Entities）を定義するもの。 semantic_models: - name: order_fact model: ref('fct_order') description: "" defaults: agg_time_dimension: order_date entities: - name: order_fact type: primary expr: order_key - name: date_dimension type: foreign expr: date_key - name: user_dimension type: foreign expr: user_key - name: product_dimension type: foreign expr: product_key - name: order_junk_dimension type: foreign expr: order_junk_key dimensions: - name: order_id type: categorical - name: order_line_no type: categorical - name: order_date type: time type_params: time_granularity: day measures: - name: record_count agg: sum expr: 1 - name: item_quantity agg: sum - name: item_price agg: sum - name: coupon_discount agg: sum - name: shipping_cost agg: sum 関係性 データ構造 fct_order MDL order_fact SEM

dbt Models と Semantic Models は 1:N の関係性 2. dbt Semantic Layer 速習 / MetricFlow / Semantic Models © and roots, Inc. All rights reserved. 22 Tokyo dbt Meetup #9 基本は 1:1 で定義することになるが、1 つの dbt Models から複数の Semantic Models を定義 することもできる。ただし、MetricFlow 内で名前が一意でなければならない。 semantic_models: - name: order_date_dimension model: ref('dim_date') description: "注文日ディメンション" entities: - name: order_date_dimension type: primary expr: date_key dimensions: - name: full_order_date type: time type_params: time_granularity: day expr: full_date - name: return_date_dimension model: ref('dim_date') description: "返品日ディメンション" entities: - name: return_date_dimension type: primary expr: date_key dimensions: - name: full_return_date type: time type_params: time_granularity: day expr: full_date dim_date MDL order_date_dimension SEM return_date_dimension SEM

応用: MDL と SEM の 1:N の活用方法 2. dbt Semantic Layer 速習 / MetricFlow / Semantic Models 23 スタースキーマのロールプレイングディメンションを dbt Models で View として管理するの ではなく、セマンティックレイヤーの中でSemantic Models として論理的に定義できる。 dim_date MDL dim_expiration_date MDL dim_return_date MDL dim_order_date MDL dim_payment_date MDL dim_shipment_date MDL dim_date MDL expiration_date_dimension SEM return_date_dimension SEM order_date_dimension SEM payment_date_dimension SEM shipment_date_dimension SEM dbt Models（ View ）として管理 Semantic Models として管理

Semantic Models に Measures が含まれる場合、Metrics にデフォルトで適用する時間ベース の属性を defaults:agg_time_dimension に指定しなければならない。スタースキーマの場 合、日付系のサロゲートキーを指定できないため、Degenerate Dimension を追加する。 Measures を持つ場合、時間ベースの属性が必須 2. dbt Semantic Layer 速習 / MetricFlow / Semantic Models © and roots, Inc. All rights reserved. 24 Tokyo dbt Meetup #9 semantic_models: - name: order_fact model: ref('fct_order') description: "注文ファクトテーブル " defaults: agg_time_dimension: order_date entities: - name: order_fact type: primary expr: order_key - name: date_dimension type: foreign expr: date_key - name: user_dimension type: foreign expr: user_key - name: product_dimension type: foreign expr: product_key - name: order_junk_dimension type: foreign expr: order_junk_key fct_order column dtype key order_key int64 SK date_key int64 FK product_key int64 FK user_key int64 FK order_junk_key int64 FK order_id string NK, DD order_line_no int64 NK, DD order_date datetime DD item_quantity int64 item_price int64 shipping_cost int64 coupon_discount int64 ※ BigQuery の場合、datetime 型にしなれけばならない

関係性: Entities 2. dbt Semantic Layer 速習 / MetricFlow / Semantic Models © and roots, Inc. All rights reserved. 25 Tokyo dbt Meetup #9 MetricFlow の結合では、Semantic Models で定義された Entites を結合キーとし、エンティ ティタイプに基づいて適切な結合タイプを選択し、自動で左結合が行われる。 Model A Model B Join Type primary primary ✅Left Join primary foreign ❌Fan Traps foreign primary ✅Left Join foreign foreign ❌Fan Traps ※ unique と natural は有用性が不明なため割愛 semantic_models: - name: order_fact model: ref('fct_order') description: "" defaults: agg_time_dimension: order_date entities: - name: order_fact type: primary expr: order_key - name: date_dimension type: foreign expr: date_key - name: user_dimension type: foreign expr: user_key - name: product_dimension type: foreign expr: product_key - name: order_junk_dimension type: foreign expr: order_junk_key dimensions: - name: order_id type: categorical - name: order_line_no type: categorical - name: order_date type: time type_params: time_granularity: day measures: - name: record_count agg: sum expr: 1 - name: item_quantity agg: sum - name: item_price agg: sum - name: coupon_discount agg: sum - name: shipping_cost agg: sum 関係性 ● order_fact *= date_dimension ● order_fact *= user_dimension ● order_fact *= product_dimension ● order_fact *= order_junk_dimension

MetricFlow の結合のイメージ 2. dbt Semantic Layer 速習 / MetricFlow / Semantic Models © and roots, Inc. All rights reserved. 26 Tokyo dbt Meetup #9 semantic_models: - name: order_fact model: ref('fct_order') description: "注文ファクトテーブル " defaults: agg_time_dimension: order_date entities: - name: order_fact type: primary expr: order_key - name: date_dimension type: foreign expr: date_key - name: user_dimension type: foreign expr: user_key - name: product_dimension type: foreign expr: product_key - name: order_junk_dimension type: foreign expr: order_junk_key date_dimension SEM product_dimension SEM order_fact SEM こんな感じで可視化されると良いですね 😄 semantic_models: - name: date_dimension model: ref('dim_date') description: "日付ディメンション " entities: - name: date_dimension type: primary expr: date_key semantic_models: - name: product_dimension model: ref('dim_product') description: "製品ディメンション " entities: - name: product_dimension type: primary expr: product_key semantic_models: - name: order_junk_dimension model: ref('dim_order_junk') description: "ジャンクディメンショ ン" entities: - name: order_junk_dimension type: primary expr: order_junk_key semantic_models: - name: user_dimension model: ref('dim_user') description: "顧客ディメンション " entities: - name: user_dimension type: primary expr: user_key user_dimension SEM order_junk_dimension SEM

Multi-hop Joins 2. dbt Semantic Layer 速習 / MetricFlow / Semantic Models © and roots, Inc. All rights reserved. 27 Tokyo dbt Meetup #9 MetricFlow の結合は、Multi-hop joins と呼び、2 ホップ（ディメンションに到達するまでの 結合回数）までの結合がサポートされている。2 ホップ以内であれば、モデルを何個でも結 合できる。データモデルとして 3NF が使用できない理由。 ○ ○ ○ ○ ○ ○ ○ ○ × × 1 order_fact SEM 1 2 1 1 2 3 2 1 3 ○ 2

データ構造: Dimensions 2. dbt Semantic Layer 速習 / MetricFlow / Semantic Models © and roots, Inc. All rights reserved. 28 Tokyo dbt Meetup #9 Dimensions では Metrics をドリリングやスライス、ダイスするための属性を定義する。 Dimensions のタイプは categorical と time の 2 種類しかない。 パラメータ 説明 タイプ name Dimensions の名前。カラム名や SQL クエリ参照が異な り、expr パラメータで提供される場合、エイリアスとして も機能する。 必須 type Dimensions のタイプ。categorical と time の 2 つのタイプ がある。 必須 type_params Dimensions のタイプが time の場合、 type_params:time_granularity に粒度を指定する。 指定できる粒度は、day, week, month, quarter, year。粗い ものを選ぶとドリルダウンできない。 必須 description Dimensions の説明文。 オプション expr Dimensions の基礎となる列または SQL クエリを定義。 オプション label 下流ツールでの表示値を定義する推奨文字列。プレーン・ テキスト、スペース、引用符が使用できる。 オプション semantic_models: - name: order_fact model: ref('fct_order') description: "" defaults: agg_time_dimension: order_date entities: - name: order_fact type: primary expr: order_key - name: date_dimension type: foreign expr: date_key - name: user_dimension type: foreign expr: user_key - name: product_dimension type: foreign expr: product_key - name: order_junk_dimension type: foreign expr: order_junk_key dimensions: - name: order_id type: categorical - name: order_line_no type: categorical - name: order_date type: time type_params: time_granularity: day measures: - name: record_count agg: sum expr: 1 - name: item_quantity agg: sum - name: item_price agg: sum - name: coupon_discount agg: sum - name: shipping_cost agg: sum データ構造

データ構造: Measures 2. dbt Semantic Layer 速習 / MetricFlow / Semantic Models © and roots, Inc. All rights reserved. 29 Tokyo dbt Meetup #9 Measures では、属性に対して実行する集計方法を定義する。これらは最終的な Metrics とし て使用することも、より複雑な Metrics の構成要素として使用することもできる。 主なパラメータ 説明 タイプ name Measures の名前。MetricFlow で一意でなければなら ない。 必須 agg 集計関数。sum, max, min, avg, median, count_distinct, percentile, sum_boolean。 必須 expr Metrics の基礎となる列または SQL クエリを定義。 オプション description Metrics の説明文。 オプション create_metric create_metric: True を設定して、Measures から simple Metrics を作成する。label と description は、 自動的に伝搬される。デフォルト: False オプション label 下流ツールでの表示値を定義する推奨文字列。プレー ン・テキスト、スペース、引用符が使用できる。 オプション non_additive_dimension 半加法ファクト（ Semi-Additive Facts ）の制御。 e.g. 在庫数、残高 オプション semantic_models: - name: order_fact model: ref('fct_order') description: "" defaults: agg_time_dimension: order_date entities: - name: order_fact type: primary expr: order_key - name: date_dimension type: foreign expr: date_key - name: user_dimension type: foreign expr: user_key - name: product_dimension type: foreign expr: product_key - name: order_junk_dimension type: foreign expr: order_junk_key dimensions: - name: order_id type: categorical - name: order_line_no type: categorical - name: order_date type: time type_params: time_granularity: day measures: - name: record_count agg: sum expr: 1 - name: item_quantity agg: sum - name: item_price agg: sum - name: coupon_discount agg: sum - name: shipping_cost agg: sum データ構造

Semantic Graph は、MetricFlow を可視化したもの。Semantic Models と Metrics との関係性 を表現したグラフ構造（DAG）。Semantic models や Metrics は DAG のノードとして表現さ れる。ゆえに Semantic Models 同士の関係性は可視化されない。 Semantic Graph 2. dbt Semantic Layer 速習 / MetricFlow © and roots, Inc. All rights reserved. 30 Tokyo dbt Meetup #9 fct_order MDL order_fact SEM Semantic Graph dim_date MDL date_dimension SEM dim_product MDL product_dimension SEM dim_user MDL user_dimension SEM dim_order_junk MDL order_junk_dimension SEM

Metrics 2. dbt Semantic Layer 速習 / MetricFlow © and roots, Inc. All rights reserved. 31 Tokyo dbt Meetup #9 Metrics は、Semantic Models で定義されたデータ構造と関係性を組み合わせ、ビジネスコン テキストに沿った形で具体的なメトリクスを定義する。5 個のメトリックタイプが存在。 metrics: - name: sum_revenue label: "収益合計" type: simple type_params: measure: name: item_price - name: sum_kitchen_revenue label: "キッチン用品の収益合計 " type: simple type_params: measure: name: item_price filter: | {{ Dimension('product_dimension__intermediate_category_name') }} = 'キッチン用品 ' - name: pct_kitchen_revenue label: "キッチン用品の数量比率 " type: derived type_params: expr: sum_kitchen_revenue / sum_revenue metrics: - name: sum_revenue - name: sum_kitchen_revenue Measure ( agg ) Metrics ( agg ) Measure ( agg ) Metrics ( agg + α ) simple cumulative Measure1 ( agg ) Metrics ( agg + α ) conversion Measure2 ( agg ) Metrics1 Metrics1 / Metrics2 ratio Metrics2 1 個以上の Metrics Metrics + α derived

Simple metrics 2. dbt Semantic Layer 速習 / MetricFlow / Metrics © and roots, Inc. All rights reserved. 32 Tokyo dbt Meetup #9 Simple metrics は、Measures で定義した集計方法をそのまま使用する。 measures: - name: item_quantity agg: sum metrics: - name: sum_item_quantity label: "数量合計" type: simple type_params: measure: name: item_quantity # fill_nulls_with: 0 # 欠損値補完 # join_to_timespine: true # 欠損した日付を埋める product item_quantity A 10 A 20 B 30 product item_quantity A 30 B 30

Simple metrics は省略できる 2. dbt Semantic Layer 速習 / MetricFlow / Metrics © and roots, Inc. All rights reserved. 33 Tokyo dbt Meetup #9 Measures の create_metric パラメータを True に設定すると、Metricsで定義しなくてよくな る。ただし、fill_nulls_with や join_to_timespine は使えなくなる。 measures: - name: item_quantity agg: sum metrics: - name: sum_item_quantity label: "数量合計" type: simple type_params: measure: name: item_quantity # fill_nulls_with: 0 # join_to_timespine: true measures: - name: item_quantity agg: sum create_metric: True label: "数量合計" =

Cumulative metrics 2. dbt Semantic Layer 速習 / MetricFlow / Metrics © and roots, Inc. All rights reserved. 34 Tokyo dbt Meetup #9 Cumulative metrics は Measures で定義した集計方法を Window 関数として集計することがで きる。 measures: - name: sum_item_quantity agg: sum expr: item_quantity - name: avg_item_quantity agg: avg expr: item_quantity metrics: - name: cumulative_item_quantity label: "数量累積和（全期間） " type: cumulative type_params: measure: name: sum_item_quantity - name: moving_avg_item_quantity label: "数量移動平均（3日）" type: cumulative type_params: measure: name: avg_item_quantity window: 3 days date item_quantity 2024-01-01 10 2024-01-02 20 2024-01-03 30 2024-01-04 40 2024-01-05 50 date cumulative_item_quantity moving_avg_item_quantity 2024-01-01 10 10 2024-01-02 30 15 2024-01-03 60 20 2024-01-03 100 30 2024-01-05 150 40

Conversion metrics（試していない） 2. dbt Semantic Layer 速習 / MetricFlow / Metrics © and roots, Inc. All rights reserved. 35 Tokyo dbt Meetup #9 複雑そうだったので、採用する！と決めたときに検証します。

Ratio metrics 2. dbt Semantic Layer 速習 / MetricFlow / Metrics © and roots, Inc. All rights reserved. 36 Tokyo dbt Meetup #9 Ratio metrics は 2 つの Metrics の比率を作成できる。しかし、Derived metrics でも同じこと ができてしまうので、わざわざ使う必要はないかもしれない。 measures: - name: item_quantity agg: sum create_metric: True label: "数量合計" metrics: - name: sum_a_item_quantity label: "Aの数量合計" type: simple type_params: measure: name: item_quantity filter: "{{ Dimension('product_dimension__intermediate_category_name') }} = 'A'" - name: pct_a_item_quantity label: "Aの数量比率" type: ratio type_params: numerator: sum_a_item_quantity denominator: item_quantity user product item_quantity Alex A 10 Alex B 20 Brown A 30 Cathy B 40 David A 50 David B 60 user sum_a_item_quantity pct_a_item_quantity Alex 10 0.33 Brown 30 0.43 Cathy null 0 David 50 0.45

Derived metrics 2. dbt Semantic Layer 速習 / MetricFlow / Metrics © and roots, Inc. All rights reserved. 37 Tokyo dbt Meetup #9 他の Metrics に式を適用し、新しい Metrics を作成する。BI ツールの計算式と同義。 measures: - name: item_quantity agg: sum create_metric: True label: "数量合計" metrics: - name: sum_a_item_quantity label: "Aの数量合計" type: simple type_params: measure: name: item_quantity filter: "{{ Dimension('product_dimension__intermediate_category_name') }} = 'A'" - name: pct_a_item_quantity label: "Aの数量比率" type: derived type_params: expr: sum_a_item_quantity / item_quantity metrics: - name: sum_a_item_quantity - name: item_quantity user product item_quantity Alex A 10 Alex B 20 Brown A 30 Cathy B 40 David A 50 David B 60 user sum_a_item_quantity pct_a_item_quantity Alex 10 0.33 Brown 30 0.43 Cathy null 0 David 50 0.45

Metrics のまとめ 2. dbt Semantic Layer 速習 / MetricFlow / Metrics © and roots, Inc. All rights reserved. 38 Tokyo dbt Meetup #9 Simple と Derived だけで、加法ファクト・半加法ファクト・非加法ファクトを定義できるた め、ビジネス要件の 90% 以上は対応できそう。 Measure ( agg ) Metrics ( agg ) Measure ( agg ) Metrics ( agg + α ) simple cumulative Measure1 ( agg ) Metrics ( agg + α ) conversion Measure2 ( agg ) Metrics1 Metrics1 / Metrics2 ratio Metrics2 1 個以上の Metrics Metrics + α derived あらかじめ作成しておいて、simple で代用可。 加法ファクト、半加法ファクトを定義できる。 試していないため、判断不可。 derived でも同じことが出来る 非加法ファクトを定義できる。

Semantic Graph 2. dbt Semantic Layer 速習 / MetricFlow © and roots, Inc. All rights reserved. 39 Tokyo dbt Meetup #9 fct_order MDL order_fact SEM Semantic Graph dim_date MDL date_dimension SEM dim_product MDL product_dimension SEM dim_user MDL user_dimension SEM dim_order_junk MDL order_junk_dimension SEM sum_revenue MET sum_kitchen_revenue MET pct_kitchen_revenue MET Semantic Graph は、MetricFlow を可視化したもの。Semantic Models と Metrics との関係性 を表現したグラフ構造（DAG）。Semantic models や Metrics は DAG のノードとして表現さ れる。ゆえに Semantic Models 同士の関係性は可視化されない。

MetricFlow Commands（dbt Cloud CLI） 2. dbt Semantic Layer 速習 / MetricFlow © and roots, Inc. All rights reserved. 40 Tokyo dbt Meetup #9 MetricFlow Commands を使用すると、開発環境でクエリを実行することが出来る。 ただし、現時点 ※2024.6 では、dbt Cloud CLI と dbt Core のみ対応しており、dbt Cloud IDE は 対応していない。 dbt sl query --metrics sum_item_price --group-by user_dimension__name +----------------------+----------------+ | USER_DIMENSION__NAME | SUM_ITEM_PRICE | +----------------------+----------------+ | Alex | 66500 | | Brown | 25000 | | Cathy | 23500 | | David | 24000 | +----------------------+----------------+ dbt sl query --metrics sum_item_price --group-by user_dimension__name --compile SELECT user_dimension_src_10000.name AS user_dimension__name , SUM(order_fact_src_10000.item_price) AS sum_item_price FROM `{{ PROJECT_ID }}`.`{{ DATASET_ID }}`.`fct_order` order_fact_src_10000 LEFT OUTER JOIN `{{ PROJECT_ID }}`.`{{ DATASET_ID }}`.`dim_user` user_dimension_src_10000 ON order_fact_src_10000.user_key = user_dimension_src_10000.user_key GROUP BY user_dimension__name LIMIT 10 dbt compile dbt sl query ––metrics {{ METRICS }} 主なパラメータ 説明 --metrics {{ METRICS }} メトリクス。 --group-by {{ DIMENSION }} グルーピングするディメンション。 entity__dimension で指定。 --where {{ CONDITION }} フィルタリング。ディメンションは {{ Dimension(entity__dimension) }} で指定。 --order-by {{ METRICS or DIMENSION }} ソート。降順は接頭辞 - を付ける。 --limit {{ n }} 出力制限。 --compile SQL を出力。

「Adamson, Christopher. "Star Schema: The Complete Reference". McGraw-Hill Osborne Media, July 7, 2010.」から引用 応用: ドリルアクロスに対応 2. dbt Semantic Layer 速習 / MetricFlow © and roots, Inc. All rights reserved. 41 Tokyo dbt Meetup #9 Metrics を 2 つ以上含むときの生成された SQL を見ると Fan Traps を起こさないように、 ドリルアクロスしていることがわかる。マルチファクトのクエリも安心して実行できる。 dbt sl query --metrics sum_revenue,sum_kitchen_revenue --group-by user_dimension__name --compile SELECT COALESCE(subq_13.user_dimension__name, subq_23.user_dimension__name) AS user_dimension__name , MAX(subq_13.sum_kitchen_revenue) AS sum_kitchen_revenue , MAX(subq_23.sum_revenue) AS sum_revenue FROM ( SELECT user_dimension__name, SUM(sum_revenue) AS sum_kitchen_revenue FROM ( SELECT product_dimension_src_10000.intermediate_category_name AS product_dimension__intermediate_category_name , user_dimension_src_10000.name AS user_dimension__name , order_fact_src_10000.item_price AS sum_revenue FROM `{{ PROJECT_ID }}`.`{{ DATASET_ID }}`.`fct_order` order_fact_src_10000 LEFT OUTER JOIN `training-bigquery-316203`.`dbt_playground`.`dim_product` product_dimension_src_10000 ON order_fact_src_10000.product_key = product_dimension_src_10000.product_key LEFT OUTER JOIN `{{ PROJECT_ID }}`.`{{ DATASET_ID }}`.`dim_user` user_dimension_src_10000 ON order_fact_src_10000.user_key = user_dimension_src_10000.user_key ) subq_9 WHERE product_dimension__intermediate_category_name = 'キッチン用品 ' GROUP BY user_dimension__name ) subq_13 FULL OUTER JOIN ( SELECT user_dimension_src_10000.name AS user_dimension__name, SUM(order_fact_src_10000.item_price) AS sum_revenue FROM `{{ PROJECT_ID }}`.`{{ DATASET_ID }}`.`fct_order` order_fact_src_10000 LEFT OUTER JOIN `{{ PROJECT_ID }}`.`{{ DATASET_ID }}`.`dim_user` user_dimension_src_10000 ON order_fact_src_10000.user_key = user_dimension_src_10000.user_key GROUP BY user_dimension__name ) subq_23 ON subq_13.user_dimension__name = subq_23.user_dimension__name GROUP BY user_dimension__name LIMIT 100 Phase 1 Phase 1 Phase 2

MetricFlow Commands 2 2. dbt Semantic Layer 速習 / MetricFlow © and roots, Inc. All rights reserved. 42 Tokyo dbt Meetup #9 metric_time または Metrics のデフォルト日時（agg_time_dimension）に、アンダースコア 2 つと粒度を指定して、データ集計の時間粒度を変更できる。 dbt sl query ––metrics {{ METRICS }} --group-by metric_time__{{ TIME_GRANULARITY }} # day, week, month, quarter, and year dbt sl query --metrics sum_item_price --group-by metric_time__month +--------------------------+----------------+ | METRIC_TIME__MONTH | SUM_ITEM_PRICE | +--------------------------+----------------+ | 2024-01-01T00:00:00.000Z | 58500 | | 2024-02-01T00:00:00.000Z | 37000 | | 2024-03-01T00:00:00.000Z | 43500 | +--------------------------+----------------+ dbt sl query --metrics sum_item_price --group-by metric_time__year +--------------------------+----------------+ | METRIC_TIME__YEAR | SUM_ITEM_PRICE | +--------------------------+----------------+ | 2024-01-01T00:00:00.000Z | 139000 | +--------------------------+----------------+ dbt sl query ––metrics {{ METRICS }} --group-by {{ AGG_TIME_DIMENSION }}__{{ TIME_GRANULARITY }} # day, week, month, quarter, and year dbt sl query --metrics sum_item_price --group-by order_fact__order_date__month +-------------------------------+----------------+ | ORDER_FACT__ORDER_DATE__MONTH | SUM_ITEM_PRICE | +-------------------------------+----------------+ | 2024-01-01T00:00:00.000Z | 58500 | | 2024-02-01T00:00:00.000Z | 37000 | | 2024-03-01T00:00:00.000Z | 43500 | +-------------------------------+----------------+ dbt sl query --metrics sum_item_price --group-by order_fact__order_date__year +------------------------------+----------------+ | ORDER_FACT__ORDER_DATE__YEAR | SUM_ITEM_PRICE | +------------------------------+----------------+ | 2024-01-01T00:00:00.000Z | 139000 | +------------------------------+----------------+

対応しているサービス一覧 Delphi（Text to SQL）が Cube に買収されてなくなった。 2. dbt Semantic Layer 速習 © and roots, Inc. All rights reserved. 43 Tokyo dbt Meetup #9 BI Notebook BI Notebook BI BI BI BI その他 その他

対応している Tableau 製品は、Tableau Desktop のみ。Tableau Server にはパブリッシュで きるが Tableau Cloud にはパブリッシュできない。また、Semantic Models 間の関係性は可 視化されず、MetricFlowで定義した label や description は反映されない。ワークブックを開 くたびトークンの入力を求められる。 Tableau 2. dbt Semantic Layer 速習 © and roots, Inc. All rights reserved. 44 Tokyo dbt Meetup #9

拡張機能（dbt Semantic Layer for Sheets）を追加して使用できる。MetricFlow で定義した label や description も反映される。まだ確認していないが、ログ情報が取れればコネクテッ ドシート機能を代替できそう。 Google Sheets 2. dbt Semantic Layer 速習 © and roots, Inc. All rights reserved. 45 Tokyo dbt Meetup #9

Exports（試していない） Exports は、MetricFlow でよく使用されるクエリの結果を DWH の Table または View として 保存し、dbt Semantic Layer とネイティブに対応していないツールと接続できるようにする 機能。要約したデータしか渡せないのは、セマンティックレイヤーを介して計算することが できないため、あらかじめ計算したものを使えるようにしていると解釈している。 2. dbt Semantic Layer 速習 © and roots, Inc. All rights reserved. 46 Tokyo dbt Meetup #9 DWH セ マ ン テ ィ ッ ク レ イ ヤ ｜ Exports ネイティブに対応 Often summarized

Caching（試していない） dbt Semantic Layer のキャッシュ機能には、Result caching ( DWH に依存 ) と Declarative caching (宣言型) の 2 種類が存在する。宣言型の方は、Exports 機能で集約された計算結果を DWH の Table として出力して、クエリ実行時にそれを読み込む。 2. dbt Semantic Layer 速習 © and roots, Inc. All rights reserved. 47 Tokyo dbt Meetup #9 DWH セ マ ン テ ィ ッ ク レ イ ヤ ｜ Exports Result Caching Often summarized Declarative Caching

アジェンダ 1. セマンティックレイヤー速習 2. dbt Semantic Layer 速習 3. まだ dbt Semantic Layer を採用できない理由 © and roots, Inc. All rights reserved. Tokyo dbt Meetup #9

分析基盤をリリースして 2 年が経過した 3. まだ dbt Semantic Layer を採用できない理由 © and roots, Inc. All rights reserved. 48 Tokyo dbt Meetup #9 CIF ( Corporate Information Factory ) Inmon Architecture を採用。 Data Warehouse: ● Normalized tables (3NF) ● Atomic data ● Current business object ● Soft business rules ● Reference data Staging Area: ● Many forms ● Atomic data ● Historical business object ● Hard business rules ● Meta data Data Marts: ● OBT ● Often summarized ● Often department ● Often target list Applications: ● Looker Studio ● Google Sheets ● その他 フロントルーム バックルーム BigQuery

● 全体の 10 〜 15% のビジネスユーザーが SQL を書き、BI ツールで可視化している ● 個人やチームが日常的に追っている指標は定点観測できている データを使えるようにはなったが … 着実にデータカオスへの道を歩んでいる。セマンティックレイヤーの導入にあたって課題の 一部は解決できそうだが... 3. まだ dbt Semantic Layer を採用できない理由 © and roots, Inc. All rights reserved. 49 Tokyo dbt Meetup #9 現 状 ● ビジネスメトリクスの定義（計算式、条件など）がバラバラ ● ビジネスメトリクスに優先順位が付けられていない（とりあえず見たい指標は、ミクロ からマクロまで全てダッシュボード化されている状態） ● ダッシュボードがあらゆる場所に点在し、管理が行き届いていない ● 属人化が進行し、ビジネスロジックの説明ができない、あるいは確認に時間がかかる 課 題

● セマンティックレイヤー導入の目的や期待される効果を説明し、上位層（経営層、事業 部長など）やキーパーソンの理解と支援を得て、プロジェクトの推進体制を整える ● 現場の建設的な意見や懸念を丁寧にヒアリングし、解決策を一緒に検討する ● ビジネスメトリクスの定義や優先順位、背景を明確にし文書化する ● 利用状況をモニタリングする仕組みを構築し、ビジネス要件や各部門のニーズに合わせ てダッシュボードを定期的に統廃合する ● データの利用体験を良くするための仕組み（データモデリング、データカタログ、ト レーニング等）をアップデートし続ける すでに DW/BI が稼働している場合は、ステークホルダーとの緻密な調整やケアが必要。 定義が変わるということは、現場が普段追いかけている指標の数値が変わるということ。 KPI の達成が個人やチームの評価に強く結びついている場合、簡単には受け入れてもらえな い可能性があることを考慮しなければならない。社内政治力が求められる。 セマンティックレイヤーは銀の弾丸ではない 3. まだ dbt Semantic Layer を採用できない理由 © and roots, Inc. All rights reserved. 50 Tokyo dbt Meetup #9 導 入 前 に や る こ と

データ分析基盤（future） データの利用体験を向上させるために Kimball’s DW/BI Architecture を採用。 © and roots, Inc. All rights reserved. 51 Tokyo dbt Meetup #9 Intermediate: ● Many forms ● Structural simplification ● Re-graining ● Isolating complex operations ● Soft business rules Staging Area: ● Many forms ● Atomic data ● Historical business object ● Hard business rules ● Meta data Presentation Area: ● Dimensional (Star Schema) ● Atomic and summary data ● Organized by business process ● Uses conformed dimension フロントルーム バックルーム BigQuery Applications: ● Tableau (仮) ● Mode (仮) ● Hex (仮) ● Google Sheets ● Push.ai (仮) ● その他 3. まだ dbt Semantic Layer を採用できない理由

機能も大事だが接続先も重要 © and roots, Inc. All rights reserved. 52 Tokyo dbt Meetup #9 3. まだ dbt Semantic Layer を採用できない理由 セマンティックレイヤーは機能での差別化は難しい（似たり寄ったりになる）ため、どんな アプリケーションで使用できるかが今後カギになってくると考えている。標準化されると嬉 しいが、買収（囲い込み）の動きが激化しそうと妄想している。 dbt Semantic Layer Cube LookML 接続先: 8 個 接続先: 25 個 接続先: 不明 BI AI & LLM Notebook BI AI & LLM Notebook BI AI & LLM Low Code Tool Frontend Framework Charting Library

参考文献 © and roots, Inc. All rights reserved. 53 Tokyo dbt Meetup #9 Appendix このスライドの作成にあたり、以下の URL と書籍を参考にさせていただきました。 セマンティックレイヤー: ● The Rise of the Semantic Layer: Metrics On-The-Fly | Airbyte ● The Semantics of the Semantic Layer | AtScale ● Rethinking Modern Business Intelligence with a Universal Semantic Layer | Cube ● Why we need a universal semantic layer | dbt ● Design Tip #158 Making Sense of the Semantic Layer | KIMBALL GROUP ● セマンティックレイヤー / Headless BIとは | Zenn, たくまんさん（@takumanken） dbt Semantic Layer: ● About MetricFlow | dbt ● Semantic models | dbt ● Metrics | dbt ● dbt Semantic Layer FAQs | dbt ディメンショナルモデリング: ● Kimball, Ralph, and Margy Ross. "The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling". 3rd Edition, Wiley, July 1, 2013. ● Adamson, Christopher. "Star Schema: The Complete Reference". McGraw-Hill Osborne Media, July 7, 2010.