dbt BigQuery アダプターで使い切るパーティション/クラスタ設計

BigQuery のコストはスキャン量に直結します。dbt のモデルで partition_by と cluster_by を適切に指定できるかどうかは、パフォーマンスとコストの両面で決定的です。本稿では、dbt BigQuery アダプターの安定した基本機能をベースに、日付/タイムスタンプ・整数レンジのパーティション、クラスタの実務設計、増分戦略との組み合わせを解説します。

Analytics Engineer 試験でも、どの設定で何が起こるか（MERGE と INSERT OVERWRITE の使い分け、require_partition_filter の効果、プルーニングを阻害しない WHERE 句など）が問われやすい領域です。暗記ではなく、現場で通用する最小実装を押さえます。

前提と設計指針：dbt BigQuery のパーティション/クラスタ

dbt BigQuery アダプターでは、モデル単位で partition_by と cluster_by を宣言できます。partition はスキャン対象の大枠を物理的に絞り、cluster は同一パーティション内で近接配置によりさらに効率化を狙います。まずパーティションを正しく切ることが最優先で、クラスタは二段目の最適化です。

クラスタ列は最大4列まで、順序は重要（選択性の高い列を先頭）です。日付/タイムスタンプ列のパーティション粒度は実ワークロードに合わせて設定し、アドホック分析では require_partition_filter を有効化して無駄な全スキャンを防ぎます。

• 最初に決めるのはパーティション軸（通常はイベント発生時刻など単調増加しやすい列）
• クラスタはフィルタ/結合で頻出かつカーディナリティが高い列を先頭に最大4列まで
• どのモデルにも無条件でクラスタを付けない（読み方に合う所だけ）
• アドホック利用されるファクトは require_partition_filter を基本オン
• 変更が難しいため、初期設計で将来の粒度（day/hour）の妥当性を検討

partition_by と cluster_by の正しい指定方法

dbt ではモデル上部の config で宣言します。日付/タイムスタンプのパーティションに加え、必要に応じて require_partition_filter を有効化します。クラスタは頻出のフィルタ・結合キーを先頭に並べます。

タイムスタンプ/日時型のパーティションは granularity を指定できます（day が扱いやすい）。整数レンジ・パーティションもサポートされますが、利用する場合は使用バージョンの互換性とレンジ幅の設計に注意してください。

• date 型は日単位が基本。timestamp/datetime は day/hour 等の粒度を選択
• cluster_by の順序はクエリでの選択性・頻度順に並べる
• require_partition_filter を付けたら、モデルや下流クエリで必ずパーティション列に対する範囲条件を記述する

パーティションとクラスタの概念図

基本のモデル設定例（日付/タイムスタンプ・パーティション + クラスタ）

{{ config(
    materialized='table',
    partition_by={
      'field': 'created_at',
      'data_type': 'timestamp',
      'granularity': 'day'
    },
    cluster_by=['user_id', 'event_type'],
    require_partition_filter=true
) }}

select
  user_id,
  event_type,
  created_at,
  payload
from {{ ref('stg_events') }}

増分ビルドの戦略比較：MERGE と INSERT OVERWRITE

増分モデルは BigQuery でも基本は MERGE 戦略です。ユニークキーで同一行を更新し、パーティション列に範囲条件を入れて取り込み対象を絞ります。パーティションごとの全置換が適切な場合は INSERT OVERWRITE 戦略が有効です。該当パーティションだけを入れ替えられるため、再計算が頻繁なウィンドウ集計などで使いやすいです。

どちらの戦略でも、ソース側のクエリにパーティション列の範囲条件を明示して不要な再計算を避けます。INSERT OVERWRITE は触れたパーティションが丸ごと置き換わるため、WHERE 句で過去の影響範囲を明確に制限するのがポイントです。

• MERGE: 行単位の upsert。遅延到着の訂正に強い
• INSERT OVERWRITE: パーティション単位の完全再生成。ウィンドウ集計やしきい値変更に向く
• どちらでも partition_by/cluster_by は有効。SQL 側の WHERE で更新対象パーティションを必ず限定

増分モデルの実装例（MERGE と INSERT OVERWRITE）

-- MERGE 戦略（遅延到着を想定し、直近N日だけ読む）
{{ config(
    materialized='incremental',
    unique_key='event_id',
    partition_by={'field': 'created_at', 'data_type': 'timestamp', 'granularity': 'day'},
    cluster_by=['user_id'],
    require_partition_filter=true
) }}

with src as (
  select * from {{ ref('stg_events') }}
  {% if is_incremental() %}
    where created_at >= timestamp_sub(current_timestamp(), interval 3 day)
  {% endif %}
)
select * from src;

-- INSERT OVERWRITE 戦略（該当パーティションを丸ごと再生成）
{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={'field': 'created_at', 'data_type': 'timestamp', 'granularity': 'day'},
    cluster_by=['user_id'],
    require_partition_filter=true
) }}

select *
from {{ ref('agg_daily_metrics_source') }}
{% if is_incremental() %}
  where created_at >= timestamp_sub(current_timestamp(), interval 7 day)
{% endif %};

コスト最適化：フィルタ設計とプルーニングの効かせ方

プルーニングを効かせる鍵は、WHERE 句でパーティション列に対する範囲条件を直接書くことです。列を関数で包むとプルーニングが効きにくくなります。アドホック利用の多いテーブルは require_partition_filter を有効にして、明示的なパーティション条件なしの全スキャンを防ぎます。

クラスタは選択性の高い列を先頭に置くことで、同一パーティション内のスキャン量をさらに削れます。ただしクラスタは万能ではないため、まずパーティションで大枠を絞る前提を徹底します。

• 関数で列をラップしない（例: date(created_at) ではなく、境界値で比較）
• 時間境界は半開区間にする（>= 開始 かつ < 終了）とバグを避けやすい
• クラスタ列は4列まで。効果が薄い列は外す
• アドホック想定のテーブルには require_partition_filter=true を基本設定

プルーニングを阻害しないフィルタの例

-- 推奨（列を関数で包まない）
where created_at >= timestamp('2026-04-10 00:00:00 UTC')
  and created_at <  timestamp('2026-04-17 00:00:00 UTC')

-- 非推奨（プルーニングが効きにくいことがある）
where date(created_at) between date('2026-04-10') and date('2026-04-16')

スキーマ変更と運用：フルリフレッシュの判断

partition_by や cluster_by の定義を変更する場合、既存テーブルの物理定義に影響します。dbt の運用としては、定義変更を確実に反映するため該当モデルをフルリフレッシュするのが安全です。依存関係が大きい場合は下流も含めた計画的な実行が必要です。

クラスタ列の入れ替えは一部 DDL で更新できることもありますが、dbt のモデル定義に合わせて整合性を保つ観点では --full-refresh による再作成が明快です。

• 定義変更前に変更理由（クエリパターン/コスト）を記録
• 本番適用はメンテナンスウィンドウで実施
• 変更後の最初の実行は dbt run --full-refresh で明示的に再作成
• バックフィル範囲を小さく分割して段階適用（INSERT OVERWRITE を併用すると安全）

移行時のコマンド例

# 影響の大きいモデルを限定してフルリフレッシュ
$ dbt run --full-refresh -s fact_events

# 依存関係も含めて段階的に適用
$ dbt run --full-refresh -s +fact_events

検証と監視：テスト・メタデータ確認・運用ガード

dbt のテストでパーティション列の NOT NULL、増分キーの一意性を担保します。モデル定義とメタデータの整合性は、情報スキーマの照会で定期確認すると安心です。特に require_partition_filter の有効化とクラスタ列の順序は、定義ドリフトがないかを監視対象に含めます。

アドホックユーザー向けには、サンプルクエリにもパーティション条件を含めて配布し、意図せぬ全スキャンを減らします。

• tests: not_null on partition column, unique on incremental key
• 情報スキーマで partitioning_type / clustering_columns / require_partition_filter を確認
• モデルのドキュメントに推奨フィルタ例を併記
• 定期ジョブの監視にスキャン量のしきい値アラートを設ける

BigQuery メタデータの確認例（オプション）

-- テーブルオプション（require_partition_filter など）
select table_name, option_name, option_value
from `<PROJECT>.<REGION>.INFORMATION_SCHEMA.TABLE_OPTIONS`
where table_catalog = '<PROJECT>'
  and table_schema = '<DATASET>'
  and table_name = 'fact_events'
  and option_name in ('require_partition_filter', 'partitioning_type', 'clustering_columns');

問題で確認

よくある質問