dbt Semantic Layer ( MetricFlow ) の理解を深める

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 スキル

セマンティックレイヤーとは 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

できること 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 | +------------------------------+----------------+

対応している 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

分析基盤をリリースして 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.

dbt Semantic Layer ( MetricFlow ) の理解を深める

dbt Semantic Layer ( MetricFlow ) の理解を深める

Other Decks in Technology

Featured

Transcript