データエンジニアリング — BigQuery パーティション × クラスタリング最適化

概要

問題

毎朝10分以上かかるレポートクエリを改善してください。

遅いクエリ

📋 コピー-- レポートチームが書いたクエリ（BigQuery）
SELECT
    DATE(ordered_at) AS order_date,
    campaign_id,
    COUNT(*) AS order_count,
    SUM(sales_amount) AS total_sales,
    SUM(discount_amount) AS total_discount
FROM `project.mart.fct_orders`
WHERE ordered_at >= '2025-01-01'
  AND ordered_at < '2026-01-01'
GROUP BY 1, 2
ORDER BY 1, 2;

fct_orders の仕様

ヒント（段階的開示）

{{
    config(
        materialized='incremental',
        unique_key='order_id',
        partition_by={
            "field": "ordered_at",
            "data_type": "timestamp",
            "granularity": "day"
        },
        cluster_by=["campaign_id"]
    )
}}

問題点の特定

ETLパイプライン図 — dbt Lineage

模範解答

📋 コピー{{
    config(
        materialized='incremental',
        unique_key='order_id',
        on_schema_change='fail',
        partition_by={
            "field": "ordered_at",
            "data_type": "timestamp",
            "granularity": "day"
        },
        cluster_by=["campaign_id"],
        require_partition_filter=False
    )
}}

SELECT
    order_id,
    ordered_at,
    campaign_id,
    sales_amount,
    discount_amount
FROM {{ ref('int_orders_enriched') }}

{% if is_incremental() %}
    -- 前日分のみ差分投入（バッファとして2日前から取得）
    WHERE ordered_at >= TIMESTAMP_SUB(
        TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY),
        INTERVAL 2 DAY
    )
{% endif %}

📋 コピー# 既存テーブルにパーティション・クラスタリングを追加するにはfull-refreshが必要
# ※本番では夜間バッチ前に実施し、影響範囲を確認してから実行
dbt run --select fct_orders --full-refresh

# 運用手順:
# 1. 夜間バッチ（Argo Workflows）を停止
# 2. full-refresh 実行（テーブル再作成）
# 3. データ件数・サンプルデータを確認
# 4. バッチ再開

📋 コピー-- パーティションプルーニングが効くか確認
SELECT
    DATE(ordered_at) AS order_date,
    campaign_id,
    COUNT(*) AS order_count,
    SUM(sales_amount) AS total_sales,
    SUM(discount_amount) AS total_discount
FROM `project.mart.fct_orders`
WHERE ordered_at >= '2025-01-01'
  AND ordered_at < '2026-01-01'
GROUP BY 1, 2
ORDER BY 1, 2;

-- BigQuery上でプルーニングが効いているか確認:
-- → クエリ実行後「このクエリで処理されたバイト数」を確認
-- → パーティション数 × 1日分のデータ量と合うか検証
-- → Before: 1.5億行分のスキャン
-- → After:  約365パーティション × 1日分のスキャンのみ

ポイント解説

実務への応用

「キャンペーンIDごとに先月の売上集計」は毎朝レポートチームが実行するケースが多く、フルスキャンが重なると費用と時間が両方膨らむ。fct_orders にパーティション+クラスタリングを設定するだけで、10分→30秒以下になることも多い。

また、dbt Mesh（cross-project refs）を使っている場合は、fct_orders を公開モデルとして定義し、downstream projectからはそのままクラスタリング恩恵を受けられる。

次のステップ

• 参考: BigQuery公式「パーティション分割テーブルの概要」
• 参考: dbt docs「Incremental models - BigQuery configurations」

今日のまとめ

自己評価

自分の回答

気づき・メモ