セマンティックレイヤってなんなのをBigQueryで確認する

はじめに

「セマンティックレイヤ（Semantic Layer）」という言葉を最近よく聞く。

「指標の一元管理ができる」「ビジネスユーザーが迷わない」といったメリットはよく語られるが、「で、結局裏側では何が起きてるの？」 という部分がいまいちよくわからない。
今回は、dbt Cloudを使って実際にセマンティックレイヤを構築し、Google スプレッドシートからデータを引き出した時に、裏側のBigQueryで一体どのようなクエリが実行され、どのように処理されているのかを検証してみた。

結論から言うと、セマンティックレイヤは「複雑な論理をBigQueryの圧倒的な計算リソースに丸投げするアーキテクチャ」 だった。

なお、dbt Cloudの設定やGoogle スプレッドシートのアドオンの使い方については本記事では言及しない。公式ドキュメントを参照されたい。

検証構成

なるべく無料で、かつ誰でも再現できるように以下の構成で行った。

• Data: BigQuery Public Data (thelook_ecommerce)
• Logic: dbt Cloud (Trial)
• Interface: Google Sheets
• Compute: Google BigQuery

準備: dbtで「売上」を定義する

まずはdbt側でSemantic Layerの定義を行う。 使用するのは bigquery-public-data.thelook_ecommerce のデータ。
ここでのポイントは、あえて 「前月比（MoM）」のような、SQLで書くと面倒な指標 を定義に含めている点。

semantic_models:
  # --------------------------------------------------
  # 1. 物理データ(Table)を論理概念(Model)にマッピングする
  # --------------------------------------------------
  - name: orders_semantic
    description: "注文データのセマンティックモデル"
    model: ref('orders') # ここで実体となるdbtモデル(物理テーブル)を参照
    defaults:
      agg_time_dimension: ordered_at # デフォルトの時間軸を指定
    
    # Entity: データの「結合キー」を定義する場所
    # これが定義されていることで、dbtが自動的にJOINパスを解決できる（Semantic Graph）
    entities:
      - name: order_id
        type: primary
      - name: user_id
        type: foreign # usersテーブルのprimary keyと結合可能になる

    # Dimension: 分析の「切り口」を定義する場所
    dimensions:
      - name: ordered_at
        type: time
        type_params:
          time_granularity: day
        # BigQueryの型厳密性への対策（Time Spineとの型合わせ）
        expr: DATETIME(ordered_at)
        
      - name: status
        type: categorical

    # Measure: 指標の「素材」となる集計計算
    # ユーザーが直接見るものではなく、Metricsを作るための部品
    measures:
      - name: revenue_sum
        description: "売上金額の合計"
        expr: revenue  # 物理カラム名
        agg: sum       # 集計方法

  - name: users_semantic
    description: "ユーザー属性データ"
    model: ref('users')
    entities:
      - name: user_id
        type: primary # ここがorders側のforeign keyと繋がる
        expr: user_id
    dimensions:
      - name: country
        type: categorical
        description: "国"
      - name: age
        type: categorical
        description: "年齢"
      - name: gender
        type: categorical
        description: "性別"

  # --------------------------------------------------
  # 2. 時間計算のための「背骨(Spine)」定義
  # --------------------------------------------------
  # 前月比や累積などを計算するために必須となるカレンダーテーブル
  - name: metricflow_time_spine
    model: ref('metricflow_time_spine')
    defaults:
      agg_time_dimension: date_day
    entities:
      - name: date_day_key
        type: primary
        expr: date_day
    dimensions:
      - name: date_day
        type: time
        type_params:
          time_granularity: day

# --------------------------------------------------
# 3. ビジネスユーザーに見せる「指標(Metrics)」の定義
# --------------------------------------------------
metrics:
  # シンプルな指標：Measureをそのまま見せるだけ
  - name: total_revenue
    label: "売上合計"
    description: "全期間の売上合計"
    type: simple
    type_params:
      measure: revenue_sum
  
  # 派生指標(Derived Metric)
  # SQLで書くと面倒な「前月比」などを、数式(expr)として定義するだけでよい
  - name: revenue_mom_growth
    label: "売上_前月比(MoM)"
    description: "売上の前月比成長率"
    type: derived
    type_params:
      expr: (current_rev - prev_rev) / prev_rev
      metrics:
        - name: total_revenue
          alias: current_rev
        - name: total_revenue
          offset_window: 1 month # 1ヶ月ずらしたデータを自動取得
          alias: prev_rev

これで「売上（Revenue）」という指標が、コードとして定義される。

スプレッドシートからセマンティックレイヤを体験する

「dbt Semantic Layer for Google Sheets」アドオンを使うと、エンジニアがSQLを書かなくても、スプレッドシートのサイドバーから「Revenue（売上）」と「Created At（日付）」を選ぶだけでデータが参照可能。

YAMLで定義した 売上合計 や 売上_前月比(MoM) が、スプレッドシート上で簡単に取得できる。これがセマンティックレイヤの便利なところ。

BigQueryの「実行グラフ」でセマンティックレイヤの裏側を覗く

スプレッドシートで「データ取得」ボタンを押したその瞬間、裏側のBigQueryでは何が起きていたのか？
Google Cloudコンソールの「ジョブ履歴」から、実際に発行されたクエリを覗いてみる。

WITH sma_10001_cte AS (
  SELECT
    DATETIME_TRUNC(DATETIME(ordered_at), month) AS order_id__ordered_at__month
    , user_id
    , revenue AS __total_revenue
  FROM `PROJECT`.`semantic_layer_demo`.`orders` orders_semantic_src_10000
)

, rss_10003_cte AS (
  SELECT
    country
    , user_id
  FROM `PROJECT`.`semantic_layer_demo`.`users` users_semantic_src_10000
)

SELECT
  order_id__ordered_at__month AS order_id__ordered_at__month
  , user_id__country AS user_id__country
  , (current_rev - prev_rev) / prev_rev AS revenue_mom_growth
FROM (
  SELECT
    COALESCE(subq_8.order_id__ordered_at__month, subq_21.order_id__ordered_at__month) AS order_id__ordered_at__month
    , COALESCE(subq_8.user_id__country, subq_21.user_id__country) AS user_id__country
    , MAX(subq_8.current_rev) AS current_rev
    , MAX(subq_21.prev_rev) AS prev_rev
  FROM (
    SELECT
      sma_10001_cte.order_id__ordered_at__month AS order_id__ordered_at__month
      , rss_10003_cte.country AS user_id__country
      , SUM(sma_10001_cte.__total_revenue) AS current_rev
    FROM sma_10001_cte
    LEFT OUTER JOIN
      rss_10003_cte
    ON
      sma_10001_cte.user_id = rss_10003_cte.user_id
    GROUP BY
      order_id__ordered_at__month
      , user_id__country
  ) subq_8
  FULL OUTER JOIN (
    SELECT
      subq_19.order_id__ordered_at__month AS order_id__ordered_at__month
      , subq_15.user_id__country AS user_id__country
      , subq_15.__total_revenue AS prev_rev
    FROM (
      SELECT
        DATETIME_TRUNC(date_day, month) AS order_id__ordered_at__month
      FROM `PROJECT`.`semantic_layer_demo`.`metricflow_time_spine` time_spine_src_10000
      GROUP BY
        order_id__ordered_at__month
    ) subq_19
    INNER JOIN (
      SELECT
        sma_10001_cte.order_id__ordered_at__month AS order_id__ordered_at__month
        , rss_10003_cte.country AS user_id__country
        , SUM(sma_10001_cte.__total_revenue) AS __total_revenue
      FROM sma_10001_cte
      LEFT OUTER JOIN
        rss_10003_cte
      ON
        sma_10001_cte.user_id = rss_10003_cte.user_id
      GROUP BY
        order_id__ordered_at__month
        , user_id__country
    ) subq_15
    ON
      DATE_SUB(CAST(subq_19.order_id__ordered_at__month AS DATETIME), INTERVAL 1 month) = subq_15.order_id__ordered_at__month
  ) subq_21
  ON
    (
      subq_8.user_id__country = subq_21.user_id__country
    ) AND (
      subq_8.order_id__ordered_at__month = subq_21.order_id__ordered_at__month
    )
  GROUP BY
    order_id__ordered_at__month
    , user_id__country
) subq_22

かなり冗長かつ複雑なSQLに変換されている。
人間が書くなら LAG() 関数などでスマートに済ませるところだが、dbtは「今月の集計」と「先月の集計」をそれぞれ独立して計算し、最後に FULL OUTER JOIN で結合している。
これは、「データ欠損時でも計算が破綻しないようにする」 という、ツールならではの安全性重視のアプローチゆえの冗長性だ。

実際、Geminiに「リファクタして」というとかなりシンプルなSQLに書き換えられる

WITH monthly_revenue AS (
  -- 1. 注文とユーザーを結合し、月×国ごとの売上を集計
  SELECT
    DATETIME_TRUNC(DATETIME(o.ordered_at), MONTH) AS order_month,
    u.country,
    SUM(o.revenue) AS current_rev
  FROM `PROJECT.semantic_layer_demo.orders` AS o
  LEFT JOIN `PROJECT.semantic_layer_demo.users` AS u
    ON o.user_id = u.user_id
  GROUP BY
    1, 2
),

rev_with_prev AS (
  -- 2. LAG関数を使って「同じ国の1ヶ月前」の売上を取得
  SELECT
    order_month,
    country,
    current_rev,
    LAG(current_rev) OVER (PARTITION BY country ORDER BY order_month) AS prev_rev
  FROM monthly_revenue
)

-- 3. 前月比成長率を計算
SELECT
  order_month,
  country,
  -- ゼロ除算エラーを防ぐため SAFE_DIVIDE を推奨
  SAFE_DIVIDE(current_rev - prev_rev, prev_rev) AS revenue_mom_growth
FROM rev_with_prev
ORDER BY
  order_month DESC, country

「こんな複雑なクエリ、パフォーマンスは大丈夫なのか？」と思うところ。BigQueryの 「実行グラフ（Execution Graph）」 を確認してみる。

本来なら複数のステージに分かれて実行される（はず）の複雑なクエリだが、実行グラフは『S00』の1つだけ。
これはBigQueryがクエリを解析し、データの移動コストが最小になるよう、全ての演算を単一のステージに押し込めた（最適化した）結果と考えられる。
人間が書いた冗長なクエリを、BigQueryは瞬時に『最短経路』に書き換えて実行している。

また、約50万行のデータを読み込み、別テーブルと結合し、月次集計して1000行にまとめるという処理が、0.1秒で完了している。
セマンティックレイヤがどんなに複雑なSQLを生成しようとも、このレスポンス速度なら『Web画面（Looker Studioやスプレッドシート）』での体感速度は爆速のまま。

まとめ

セマンティックレイヤの実態は 「複雑性（Complexity）を人手によるSQL管理から、BigQueryのコンピュートリソースへとオフロードする技術」 だと言える。

• セマンティックレイヤは、SQLを自動生成する「コンパイラ」である
• 生成されるクエリは複雑になるが、BigQueryのオプティマイザと分散処理がそれを解決する

「論理を定義するツール」と「それを処理する強力なプラットフォーム（Google Cloud）」が組み合わさって初めて価値が発揮されると感じた。