C データエンジニアリング — セール当日リアルタイム売上ダッシュボード BI Engine高速化 × Materialized View（capacity reservation未作成 × SELECT *で列指向キャッシュ非効率 × 事前集計マート無し × 集計粒度が細かすぎ予約容量に非乗り切り × セール時の予約サイズ固定 × パーティションプルーニング非効 × ヒット率監視なし）（MOps セール当日ダッシュボード Bad→Good）

概要

問題

ECサイト MOps チームでは、セール当日に経営陣・現場責任者向けに Looker Studio で「リアルタイム売上ダッシュボード」（5分ごとに自動リフレッシュ、時間帯×購入チャネル別の売上・件数を表示）を提供している。直近のセールで、ダッシュボードの1クエリあたりのレイテンシが8〜12秒まで悪化し、かつ月間BigQueryコストも高騰した。調査すると、以下のTerraform（BI Engine予約構成）・dbtモデル（Materialized View）・Looker Studioが発行するクエリには7つの設計・コスト上の問題が潜んでいる。

問題点を全て洗い出し、BigQuery BI Engine capacity reservation（列指向インメモリキャッシュ）× Materialized Viewによる事前集計 × ダッシュボードに必要な粒度への集計設計 × パーティションプルーニングを効かせるWHERE句設計 × セール時のBI Engine予約サイズの動的引き上げ × BI Engineヒット率/スロット消費の監視を活用したBad→Goodリファクタリングを行ってください。

制約・前提条件

• BigQuery Standard SQL（GA機能のみ。BI Engine, Materialized View は共にGA機能）
• dbt Core 1.8+（Materialized Viewは materialized='materialized_view' で定義可能）
• Terraform 1.8+（google プロバイダー 5.x、google_bigquery_bi_reservation リソース使用可）
• ソーステーブル: orders.order_items（order_id, order_date DATE, order_ts TIMESTAMP, product_id, warehouse_id, channel, qty, unit_price。partition: order_date）。セール当日は日次数千万〜億行規模に達する
• BI Engine reservationは現在未作成（capacity 0GB）で、ダッシュボードの集計クエリは毎回フルスキャン相当のスロット処理が発生している
• Looker Studio側のクエリは orders.order_items を直接 SELECT * で参照し、WHERE order_date の範囲指定が無い
• BigQueryはオンデマンド課金（$5/TB）。セール当日はダッシュボードの閲覧者数（同時アクセス）も平常時の10倍に増える
• ダッシュボードで実際に必要な集計粒度は「5分バケット × 購入チャネル」の売上合計・件数のみで、商品別・倉庫別の内訳は不要

悪いコード (Before)

📋 コピー-- Looker Studioが5分ごとに発行するクエリ
-- 問題①: BI Engine reservationがcapacity 0GBで未作成
-- 問題②: SELECT *で不要列(product_id,warehouse_id,unit_price等)も読込
-- 問題③: 事前集計されたMaterialized Viewが存在せず生テーブルを直接集計
-- 問題⑥: order_dateの範囲指定が無くパーティションプルーニングが非効
SELECT *
FROM `my-project.orders.order_items`;

📋 コピー{{ config(materialized='materialized_view') }}

-- 問題④: 商品×倉庫まで持つ細かすぎる粒度
--        ダッシュボードは5分バケット×チャネルしか使わないのに
--        BI Engine予約容量(平常時10GB)に乗り切らずキャッシュミス多発
SELECT
    order_date,
    product_id,
    warehouse_id,
    channel,
    SUM(qty * unit_price) AS revenue,
    COUNT(DISTINCT order_id) AS order_count
FROM {{ source('orders', 'order_items') }}
GROUP BY order_date, product_id, warehouse_id, channel

-- 問題⑤: BI Engine予約サイズが平常時想定の固定値のまま
--        (infra/bi_engine.tf の size = 10GB 固定、セール向けの変数化なし)
-- 問題⑦: ヒット率・使用率を監視する仕組みが無い

ヒント（段階的開示）

-- Materialized View 集計粒度の骨格（5分バケット×チャネル）
{{ config(materialized='materialized_view') }}

SELECT
    TIMESTAMP_TRUNC(order_ts, MINUTE, 'Asia/Tokyo') AS minute_ts,
    channel,
    order_date,
    SUM(qty * unit_price) AS revenue,
    COUNT(DISTINCT order_id) AS order_count
FROM {{ source('orders', 'order_items') }}
GROUP BY 1, 2, 3

# BI Engine reservation の骨格
resource "google_bigquery_bi_reservation" "dashboard" {
  project  = var.project_id
  location = "asia-northeast1"
  size     = var.bi_engine_capacity_bytes  # セール時は変数を引き上げてapply
}

問題点分析（7点）

BI Engineキャッシュの仕組み — Bad vs Good の比較

模範解答

📋 コピー# infra/bi_engine.tf

variable "bi_engine_capacity_bytes" {
  description = "BI Engine予約容量（バイト単位）。平常時は10GB、セール開催時は変数を引き上げてapplyする運用"
  type        = number
  default     = 10 * 1024 * 1024 * 1024  # 10GB（平常時）
}

# 修正①: BI Engine capacity reservationを作成
resource "google_bigquery_bi_reservation" "dashboard" {
  project  = var.project_id
  location = "asia-northeast1"
  size     = var.bi_engine_capacity_bytes
}

# 修正⑤: セール開催カレンダーに合わせて容量を引き上げる運用は
#        tfvarsの切り替え（例: bi_engine_capacity_sale.tfvars で 50GB を指定）で対応。
#        Terraformコード自体は変数化しておくことで、セール前後の
#        capacity変更をコードレビュー可能な変更として履歴に残す。

# 修正⑦: BI Engineのメモリ使用率が予約容量に対して逼迫していないか監視
resource "google_monitoring_alert_policy" "bi_engine_utilization" {
  project      = var.project_id
  display_name = "BI Engine 予約容量の使用率が閾値超過"
  combiner     = "OR"
  conditions {
    display_name = "bi_engine/reservation/used_bytes が予約容量の90%を超過"
    condition_threshold {
      filter          = <<-EOT
        resource.type="bigquery_biengine_reservation"
        metric.type="bigquery.googleapis.com/biengine/reservation/used_bytes_ratio"
      EOT
      comparison      = "COMPARISON_GT"
      threshold_value = 0.9
      duration        = "300s"
    }
  }
  notification_channels = [var.oncall_notification_channel_id]
}

# 修正⑦: BI Engineでアクセラレートされなかったクエリの割合を監視
#        (INFORMATION_SCHEMA.JOBS_BY_PROJECT の bi_engine_statistics を
#         スケジュールドクエリで日次集計しモニタリングメトリクスにexport)
resource "google_bigquery_data_transfer_config" "bi_engine_hit_rate_report" {
  display_name          = "bi-engine-hit-rate-daily"
  data_source_id        = "scheduled_query"
  schedule               = "every day 07:00"
  destination_dataset_id = "monitoring"
  params = {
    query = <<-EOT
      SELECT
        DATE(creation_time) AS report_date,
        COUNTIF(bi_engine_statistics.bi_engine_mode = 'FULL') AS full_accel_count,
        COUNTIF(bi_engine_statistics.bi_engine_mode = 'DISABLED') AS disabled_count,
        COUNT(*) AS total_query_count
      FROM `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
      WHERE creation_time >= TIMESTAMP(CURRENT_DATE() - 1)
        AND statement_type = 'SELECT'
      GROUP BY report_date
    EOT
  }
}

📋 コピー{{ config(materialized='materialized_view') }}

SELECT
    order_date, product_id, warehouse_id, channel,
    SUM(qty * unit_price) AS revenue,
    COUNT(DISTINCT order_id) AS order_count
FROM {{ source('orders', 'order_items') }}
GROUP BY order_date, product_id, warehouse_id, channel
-- BI Engine予約容量(10GB)に乗り切らずキャッシュミス多発

📋 コピー-- models/marts/mart_sales_5min.sql
-- 修正③④: 生テーブルへの直接集計をやめ、ダッシュボードに
--        必要な粒度（5分バケット×チャネル）まで事前集計した
--        Materialized Viewとして定義する。商品・倉庫の内訳は
--        持たないため、同じBI Engine予約容量により多くの
--        期間のデータを収めることができる。
{{ config(
    materialized='materialized_view',
    on_configuration_change='apply',
    options={
        'enable_refresh': true,
        'refresh_interval_minutes': 5,   -- ダッシュボードのリフレッシュ間隔に合わせる
        'max_staleness': 'INTERVAL "0-0 0 0:10:0" YEAR TO SECOND',
    }
) }}

SELECT
    order_date,
    -- 5分バケットに丸める（ダッシュボードの表示粒度そのもの）
    TIMESTAMP_TRUNC(order_ts, MINUTE, 'Asia/Tokyo')
        AS minute_ts,
    DIV(EXTRACT(MINUTE FROM order_ts), 5) * 5
        AS bucket_offset_minutes,  -- 5分単位のオフセット
    channel,
    SUM(qty * unit_price) AS revenue,
    COUNT(DISTINCT order_id)  AS order_count
FROM {{ source('orders', 'order_items') }}
GROUP BY order_date, minute_ts, bucket_offset_minutes, channel

📋 コピーversion: 2

models:
  - name: mart_sales_5min
    description: >
      リアルタイム売上ダッシュボード専用の事前集計マート。
      5分バケット×購入チャネル単位のみを持ち、商品・倉庫の
      内訳は持たない（BI Engine予約容量に収まるサイズを維持するため）。
    columns:
      - name: order_date
        data_type: date
        constraints: [{ type: not_null }]
      - name: minute_ts
        data_type: timestamp
        constraints: [{ type: not_null }]
      - name: channel
        data_type: string
        constraints: [{ type: not_null }]
      - name: revenue
        data_type: numeric
      - name: order_count
        data_type: int64
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns: [order_date, minute_ts, channel]

📋 コピーSELECT * FROM `my-project.orders.order_items`;

📋 コピー-- 修正②: revenue/order_count/channel/minute_tsのみを参照し、
--        BI Engineのキャッシュ効率を最大化する
-- 修正⑥: order_dateの範囲指定でパーティションプルーニングを効かせ、
--        MV全体ではなく直近分のみをスキャン対象にする
SELECT
    minute_ts,
    channel,
    revenue,
    order_count
FROM `my-project.marts.mart_sales_5min`
WHERE order_date >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY)
ORDER BY minute_ts;

実行例（input → output）

input（改善前・生テーブル直接集計、セール当日のデータ量）:

orders.order_items は当日だけで8,000万行。Looker Studioは SELECT * を5分ごとに実行し、BI Engine reservationも存在しない。

output（Before・問題のある実装）:

毎回8,000万行に対して集計クエリのフルスキャンが走り、レイテンシ8〜12秒・1クエリあたり数百MB〜数GBのスキャン課金が発生する。閲覧者が10倍（同時100人）に増えると、同じ集計が並列に100回実行され、コストとスロット消費がほぼ線形に増加する。

output（After・改善後）:

mart_sales_5min は5分バケット×チャネル単位まで事前集計されているため、当日分でも数千〜数万行程度に収まる。10GB（平常時）〜50GB（セール時）のBI Engine予約容量に十分収まり、WHERE order_date >= ... の範囲指定と合わせてBI Engineの列指向インメモリキャッシュがヒットし、レイテンシは数百ms〜1秒程度まで改善する。同時閲覧100人がほぼ同一のクエリ（同じ集計・同じ範囲）を投げるため、BI Engineのキャッシュ共有効果によりスキャンコストの増加も抑えられる。

ポイント解説

実務への応用

MOpsチームが扱うダッシュボードの多くは「同じ集計クエリを、多数の閲覧者が短い間隔で繰り返し実行する」という特性を持つ。このパターンでは、個々のクエリを個別最適化するより、「事前集計した小さなマート × BI Engineのようなキャッシュ層」を用意し、集計クエリそのものを軽量化する設計の方が費用対効果が高い。特にセール・キャンペーン当日のようにアクセスが急増するタイミングでは、平常時の設計のまま迎えるとレイテンシ悪化とコスト急増が同時に起きやすいため、「イベント時にキャパシティを一時的に引き上げる」運用まで事前に仕組み化しておくことが実務上重要になる。

今日のまとめ

次のステップ

• 発展問題: 現在の設計は「5分ごとにLooker StudioがBigQueryへ直接クエリを投げる」ポーリング型だが、真にリアルタイム（秒単位）な更新が必要な場合、orders.order_items へのINSERTをトリガーにPub/Sub経由でダッシュボード側にプッシュ通知する設計に変えるとどうなるか、BI Engine/Materialized Viewとの役割分担も含めて整理せよ。また、Materialized Viewの max_staleness を極端に短く（例: 30秒）設定した場合、ベーステーブルの増分リフレッシュ頻度とコストにどう影響するかを試算せよ。
• 参考: BigQuery BI Engine（capacity reservation） / BigQuery Materialized View（max_staleness, 自動リフレッシュ） / INFORMATION_SCHEMA.JOBS_BY_PROJECT の bi_engine_statistics / パーティション分割テーブルとプルーニング / Looker Studio のBigQueryコネクタ

自己評価（あとで記入）