データモデリングでよく利用するBigQuery SQLのクエリパターン

本記事は、データ推進室 Advent Calendar 2024 24日目の記事です

はじめに

こんにちは。HR領域でアナリティクスエンジニアのテックリードをしている山家雄介です。

アナリティクスエンジニアが担当する業務の一つに、データ利用者向けのいわゆるデータマートの設計・開発があります。これは、さまざまな仕様のデータソースを適切に組み合わせて、データ利用者の業務上の目的を達成することを助ける、平易に理解しやすく、ドキュメントも整備されたテーブル群を設計・開発し提供していく営みです。この業務を首尾よく進めていくには、dbtやDataformといったデータモデリングのツールの機能性を深く理解すると同時に、利用しているデータ分析基盤のSQLの仕様や機能性もよく理解しておく必要があります。

同じSQLという言語仕様の中でも、データマートの利用者によく利用される機能性と、それを開発するデータモデリングによく用いられる機能性は異なります。これは、プログラミング言語の仕様のなかでプロダクトの開発に直接利用される機能性と、それを助けるライブラリやフレームワークの開発に利用される機能性が異なるのと、似た関係にあると考えています。

※ この記事は、先日、同僚の木内、電電とともに参加した dbt Coalesce 2024 で私が聴講したセッションのゲストスピーカーとして招かれていた Benn Stancil氏が運営するニュースレター benn.substack 上で公開されている記事 The rise of the analytics pretendgineer に影響を受けています。

アーキテクチャはある、関数やライブラリはある、良いフレームワークは(まだ)普及していない

データモデリングにおける高レイヤーのアーキテクチャとしては、ディメンショナルモデリングとData Vaultが有名で、書籍も複数出版されています。

しかし、さまざまな仕様のデータソースを、目的とするデータマートの形式になるよう適切にレイヤリングし組み立てていく具体的な方法について、まとまって紹介されている事例はあまり見かけません。前項でご紹介した The rise of the analytics pretendgineer の記事でもBenn Stancil氏は「自分たちで関数を書いてうまく組み合わせることはできるけど、(Web開発でいうところの)フレームワーク相当のものは現状はない」という趣旨の議論をしています。

The rise of the analytics pretendgineer から引用:

I wrote it like this because I understand how to write functions, but I don’t know anything about programming frameworks.
(中略)
Roughly, dbt is functions without a framework. There are official best practices, but they’re more suggestions than guardrails — and nobody wants to read the manual anyway.

この記事を書いている時点(2024年12月)での私個人の実感もこれに近いです。

筆者が把握している中では、 AutomateDV というdbtパッケージが、dbtプロジェクトをData Vault 2.0 アーキテクチャでデータモデリングするのを助けるマクロ等を提供しており、前項の共通フレームワーク実装にもっとも近いものです。ただ、これが現時点で広く普及し、多くのdbtプロジェクトで採用されている状況ではないように思います。

このあたりの技術的な成熟度の段階は、過去のWebアプリケーション開発の現場において、提唱されているアーキテクチャはありつつも、その実現や品質の安定を助けるような標準的なフレームワーク実装が十分に普及しておらず、各自で関数やライブラリをうまく組み合わせて独自にフレームワークを作成し利用することもあった時期（2008年~2012年あたり）に近いかもしれません。

筆者がデータモデリングで用いるクエリのパターン

前項のような状況にあっても、我々がデータモデリングで用いた特有のクエリのパターンや知識を共有することには意味があるはずです。以降では、筆者がデータモデリングでよく利用するクエリのパターンをカテゴリ別にご紹介します。これはデータモデリングにおけるフレームワーク実装の部品以前の状態ではありますが、その下敷きとなりうるものだと考えています。

筆者がデータモデリングで用いるクエリのパターンは以下の通りですが、すべてをご紹介すると記事が非常に長くなってしまうため、今回は✅がついているパターンだけ紹介しようと思います。

1. レコード粒度の調整と検査
  ✅1.1. データソースのレコード粒度の調整
  ✅1.2. 物理削除されたレコードの過去パーティションからの補完
  ✅1.3. 想定しないレコード粒度の調査
  1.4 異なる集計粒度(日次、週次、月次、四半期)のreporting modelの実装の共通化
2. 集計とレポーティング
  ✅2.1. 指標の前月比と前年同月比の比較
  ✅2.2. 遅延日数が異なるデータソースのカラムを最新パーティションに集める
3. 異なるタイムゾーンに基づくデータソースへの対応
  ✅3.1. 異なるタイムゾーンに基づく取り込み時間パーティショニングテーブル同士のJOIN
4. データを横持ちから縦持ちにする
  4.1 横持ちで記録された売上目標スプレッドシートを取り込む
  4.2 横持ちで記録されたディメンションを縦持ち化する際の工夫
5. 変数、データソース、アウトプットの検証
  5.1 複数の変数間の整合性のチェック
  5.2 複雑なクエリに対する検算結果の添付
6. 欠損データや未計測データの補完
  6.1 データソースが欠損しているデータマートにオンラインパッチを当てる
  6.2 時間経過によって減衰するCVの予測

前提

利用するクエリエンジンはBigQuery SQL、データモデリングツールはdbtを想定しています
テーブル内の要素の正式名称は、BigQueryではフィールドですが、dbtではカラムなので、この記事ではカラムで統一します
紹介する内容が煩雑になるのを避けるため、実際にプロダクトで利用している materialized="incremental"に関する設定や、これに関連するWHERE句の条件などは省略しています
ご紹介するSQLに出てくるテーブルのスキーマは、データモデリングのノウハウを事例という仕立てで紹介するために作った架空のものであり、実際のプロダクトの実装を反映したものではありません

1. レコード粒度の調整と検査

概要: BigQuery SQLの QUALIFY句をデータモデリングで活用することで、データソースのレコード粒度を目的に合わせて調整したり、複数のパーティションから必要なレコードをピックアップしたり、想定と異なるレコード粒度を検査したりと様々なことができます。

1.1. データソースのレコード粒度の調整

課題：利用できるデータソースと、提供しようとしているデータマートの間でレコード粒度にミスマッチがあり、データソースから基準に合う一部のレコードだけ抽出したい

具体例：

広告キャンペーンの進捗が、累積値として1時間単位のスナップショットとして記録されたデータソーステーブルがある
利用者のニーズとしては日次単位でのデータマートが求められており、1時間単位のスナップショットから、各日の1日の最後の状態を取り出したい
他に利用できるデータソースがない

クエリの例:

-- stg_ad_campaign_metrics_daily
-- キャンペーンIDごとに成果の進捗を1日単位でモデリングしたテーブル
SELECT
  DATE(snapshot_hour_at, 'Asia/Tokyo') as report_date,
  campaign_id,
  -- 以下はデータソースにおいてcampaign_idに対する累積値が記録されているものとする
  clicks,  -- (3.)
  conversions,
  spending,
  remaining_budget,
  budget,
-- データソース: キャンペーンIDごとの成果に関する指標(累積値)が1時間単位で記録されているテーブル
FROM {{ source('ad_campaign_performance_report_hourly_snapshot') }}
QUALIFY
  ROW_NUMBER() OVER(
    PARTITION BY campaign_id, report_date
    ORDER BY snapshot_hour_at DESC
  ) = 1  -- (1.)

ポイント

QUALIFY句で (日付、広告キャンペーン) ごとに、その日の最後の時間のスナップショットのレコードのみフィルタして取り出している
1日の最後の状態を取り出す、が要件としてあるため、GROUP BYやSELECT DISTINCT, ANY_VALUE は利用できない
MAX_BY(metric, snapshot_hour_at) は5つの指標カラムに対して指定する必要があり煩雑

1.2. 物理削除されたレコードの過去パーティションからの補完

課題:

アプリケーションの仕様として、レコードが物理削除されるエンティティテーブルが、データソースの一部に用いられている
過去に物理削除済みのエンティティも対象にしたデータマートを提供したい

具体例:

ユーザーアカウントテーブルに、登録元の広告キャンペーンのコード(campaign_code)が記録されている
ユーザーアカウントの退会時には、テーブルのレコードが物理削除される
BigQueryの取り込み時間パーティショニング(日付)テーブルとして、各日付時点での全量のスナップショットが記録されている

クエリの例

-- stg_user_accounts__complement_retired
-- 登録ユーザーアカウントを退会者も含めてモデリングしたstaing model
SELECT
  DATE(created_at, 'Asia/Tokyo') as register_date,
  _PARTITIONDATE as partition_date,
  user_account_id_hashed,
  campaign_code, -- どの広告キャンペーンから会員登録したかを示すコード
-- データソース: ユーザーアカウントテーブルの日次スナップショットテーブル
FROM {{ source('user_account') }}
QUALIFY
  ROW_NUMBER() OVER(
    PARTITION BY user_account_id_hashed
    ORDER BY partition_date DESC
  ) = 1

ポイント: QUALIFY句で番号付け関数とウィンドウ関数を併用することで、退会によって過去に物理削除済みのアカウントについても、そのアカウントが最後に存在したパーティションから抽出できる

注意点: アカウント登録から短時間で退会したケースなど、どのスナップショットのタイミングにも存在しなかったアカウントはこの方法では取り出せないので、この方法が適さないユースケースもある

1.3. 想定しないレコード粒度の調査

課題: データソースのテーブルのユニークキーが想定と異なることがわかったため、素早く調査をしたい

具体例:

データソースとして利用しているユーザーアカウントテーブルにおいて、各日付パーティションごとの登録メールアドレス(ハッシュ化済み)は、その日付においてユニークキーであると想定していた
しかし、開発中におこなったテストによって、メールアドレス(ハッシュ化済み)が、常にユニーク制約を満たすとは限らないことがわかった
どのような場合にメールアドレス(ハッシュ化済み)の重複が起きるか簡単に確かめたい

クエリの例

-- 会員のメールアドレスが同一パーティション内で重複しているレコード全体を出力する
SELECT
  *,  -- (2.)
  _PARTITIONDATE AS partition_date,
-- ユーザーアカウントテーブルの日次スナップショットテーブル。ユニークキーに疑義がある
FROM {{ source('user_account') }}
QUALIFY  -- 1.
  COUNT(*) OVER(PARTITION BY partition_date, email_hashed) > 1

ポイント

QUALIFY句では、集計関数をウィンドウ関数をとともに利用でき、出力粒度を変化させないので、この特性を利用して特定の集計結果を満たすレコードを出力できる
カラムを限定せずに出力した結果から、レコード間に共通する特徴を見つけることで、コーナーケースを比較的容易に見つけることができる

※ 以下のようにGROUP BYとHAVINGを使う方法は、重複が起きている具体的なメールアドレスはわかるが、出力レコードがGROUP BYのキーによって集計されるので、どのカラムが想定していないユニークキーの一部かは分からないため、追加のクエリが必要となる

-- 会員のメールアドレスを同一パーティションの中で重複していれば出力する
SELECT
  _PARTITIONDATE,
  email_hashed,
  COUNT(*) AS dup
-- ユーザーアカウントテーブルの日次スナップショットテーブル。ユニークキーに疑義がある
FROM {{ source('user_account') }}
GROUP BY _PARTITIONDATE, email_hashed
HAVING dup > 1

2. 集計とレポーティング

概要: データマートの主要な利用形態の一つとして、BIツールやレポーティングツールを用いた可視化がある。このとき、BIツールやレポーティングツール側の機能性では実現が難しい指標については、追加のmodel layerでデータモデリングすることで、目的を達成できる。

2.1. 指標の前月比と前年同月比の比較

課題: 月次の売上や顧客数といった指標の前月比や前年同月比での比較をしたい

具体例:

あるプロダクトの重要指標が直近で変動した要因として、それが例年の季節変動による影響が大きいのか、直近でおこなったプロダクトの施策による影響が大きいのかを簡易的に把握したい
しかし、データマートの利用者が可視化に利用しているツールやサービスの機能的な制約により、希望する形式での可視化ができない
そのため、指標の前月や前年同月の値を分母にしたときの比率、つまり前月比や昨年同月比を、計算済みのカラムとしてデータマートに含めたい

クエリの例

-- rpt_sales_performance_monthly  -- (1.)
-- 月次売上モニタリング用テーブル(前月比、昨年同月比つき)
SELECT
  base.month,
  base.revenue,
  SAFE_DIVIDE(base.revenue, last_month.revenue) AS revenue_mom,  -- (2.)
  SAFE_DIVIDE(base.revenue, last_year.revenue) AS revenue_yoy,
FROM {{ ref('dim_sales_performance_by_monthly') }} AS base
  LEFT JOIN {{ ref('dim_sales_performance_monthly') }} AS last_month ON
    base.month = last_month.month + INTERVAL 1 MONTH  -- (3.)
  LEFT JOIN {{ ref('dim_sales_performance_monthly') }} AS last_year ON
    base.month = last_year.month + INTERVAL 1 YEAR

ポイント:

あらかじめ年月粒度で集計済みの指標を mart modelとして作成しておき、それをもとに reporting model (rpt_ prefix)をつくる
dbt プロジェクト内で一貫性のある前月比(_mom suffix), 前年同月比(_yoy suffix)のカラムをモデリングする
ベーステーブルに対応するJOIN先テーブルの内容が、期間が前月や前年同月になるよう、期間をずらしてself joinすることで、前月比や前年同月比を計算する

※ rpt_ prefixは dbt Best practices には言及がないmodel layerですが、 dbt_project_evaluator の Model Naming Convention では言及されています。筆者らはrpt_ prefixをプロダクトモニタリング用のレポートやダッシュボードと一対一対応するようなmodel layerに採用しています。

2.2. 遅延日数が異なるデータソースのカラムを最新パーティションに集める

課題: あるデータマートは複数のデータソースをもとに構築されているが、データパイプラインの仕様によって遅延日数がデータソースごとに異なる状況である。その一方で、データマートの利用にあたっては、最新の日付のパーティションだけを参照すれば、全カラム利用可能な最新のデータが利用できるようになっていてほしい。

具体例:

データマートが参照するデータソースの一部が、データパイプラインの仕様によって、他のデータソースより常に2~3日遅れてデータが連携されている
このままデータマートを構築すると、「最新の日付のパーティションで一部の指標が利用できるが、他の指標はその２日前のパーティションをあたる必要がある」という状況になり、利用しにくい
この問題の解決のため、提供するデータマートの最新の日付パーティションを参照すれば、各データソースでその時点で利用可能な最新の指標の値が得られるようにしたい

クエリの例:

-- rpt_key_metrics_by_company_account
-- 日付x企業アカウントごとのKPIサマリテーブル; 企業単位KPIダッシュボード向け
SELECT
  latest_metrics.report_date,
  latest_metrics.company_account_id,
  latest_metrics.company_name,
  latest_metrics.metric_a,
  LAST_VALUE(metrics_delayed.metric_b IGNORE NULLS) OVER (  -- (1.)
      PARTITION BY company_account_id
      ORDER BY report_date
      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS metric_b__complemented,  -- (2.)
-- 日付x企業ごとのKPI群のサマリテーブルその1; 前日までのデータが利用可
FROM {{ ref('dim_key_metrics_by_company_daily') }} AS latest_metrics
  -- 日付x企業ごとのKPI群のサマリテーブルその2; 2~3日前までのデータしか利用できない
  LEFT JOIN {{ ref('dim_other_metrics_by_company_delayed') }} AS metrics_delayed
    USING(report_date, company_account_id)

ポイント:

ナビゲーション関数 LAST_VALUE を利用して、企業アカウントID(company_account_id)ごとに、利用可能な最も新しい日付における指標の値を取り出している
利用時に 過去日から補完された指標の値であることを区別できるように、共通のsuffix __complemented をつける

注意点:

materialized='incremental' で更新する際には、最も遅延の大きいデータソースの日数に合わせてbackfillする必要がある
指標の性質によってはこの方法が適さない場合がある

3. 異なるタイムゾーンに基づくデータソースへの対応

概要: データマートのデータソースによっては、パーティションが異なるタイムゾーンに基づく日付で分割されている場合があります。利用者のタイムゾーンに合ったデータマートを提供したい場合に利用できるSQLパターンをご共有します。

3.1. 異なるタイムゾーンに基づく取り込み時間パーティショニングテーブル同士のJOIN

課題: データマートで利用予定の複数のデータソースが、異なるタイムゾーンに基づく取り込み時間パーティショニング(日付)でデータ分析基盤に連携されている。そのためパーティションの日付と各パーティションに含まれるレコードの期間の範囲がデータソース間で整合していない

具体例:

複数のデータソースが取り込み時間(日付)でパーティション分割されたテーブルとして提供されているが、あるデータソースは日本標準時に基づいた日付でパーティション分割されており、別なデータソースはアメリカ中部標準時に基づいた日付でパーティション分割されている
利用者向けには、日本標準時に基づいた日次のパーティショニングに統一してデータマートを提供したい

クエリの例:

-- int_user_accounts_with_clicks_daily
-- 日付x登録ユーザーアカウント単位のクリック数をモデリングしたテーブル
SELECT
  accounts.jst_date AS partition_date,
  accounts.user_account_id_hashed,
  IFNULL(COUNT(*), 0) as clicks,
-- 日付xユーザーアカウント単位のsmodel; パーティションの基準は日本標準時
FROM {{ ref('stg_user_accounts_by_jst_date') }} AS accounts
  -- ユーザーによるクリックログのmodel; パーティションの基準はアメリカ中部標準時
  LEFT JOIN {{ ref('stg_click_logs_by_cdt_date') }} AS click_logs ON
    accounts.jst_date IN (click_logs.cdt_date-1, click_logs.cdt_date)  -- (1.) (2.)
    AND accounts.jst_date = DATE(click_logs.click_at, 'Asia/Tokyo')  -- (3.)
    AND accounts.user_account_id_hashed = click_logs.user_account_id_hashed
GROUP BY partition_date, user_account_id_hashed