BigQueryで作るデータマートのメリットと構築手順

2026年6月27日

もっとスキルを活かしたいエンジニアへ

スポンサードリンク

働き方から選べる

無料で使えて良質な案件の情報収集ができるサービス

エンジニアの世界では、「いつでも動ける状態を作っておけ」とよく言われます。
技術やポートフォリオがあっても、自分に合う案件情報を日常的に見れていないと、いざ動こうと思った時に比較や判断が難しくなってしまいます。
普段から案件情報が集まる環境を作っておくと、良い案件が出た時にすぐ動きやすくなりますよ。
筆者自身も、メガベンチャー勤務時代に年収1,500万円を超えた経験があります。振り返ると、技術だけでなく「どんな案件や働き方があるか」を日頃から見ていたことが、キャリアの選択肢を広げるきっかけになりました。
このブログを読んでくれた方に感謝を込めて、実際に使っている情報収集サービスを紹介します。

フルリモート・週3日・高単価、どんな条件も妥協したくないなら

フリーランスボードに無料会員登録する

利用者10万人以上。業界最大規模45万件の案件。AIマッチ機能や無料の相場情報が人気。

年収800万円以上のキャリアアップ・ハイクラス正社員を視野に入れているなら

Beyond Careerに無料相談する

内定獲得率90%以上。紹介先企業とは役員クラスのコネクションがある安心と信頼できるエージェント。

Contents

1 データマートとは何か、BigQuery のメリットとユースケース
- 1.1 BigQuery のスケーラビリティと実績
- 1.2 BigQuery の主なメリット
2 構築前提条件とデータ取り込み方法
3 スキーマ設計・テーブル作成手順（CTAS とスケジュールドクエリ）
4 最適化テクニックとローコードツール活用
- 4.1 Qiita Tips の具体的参照リンク
- 4.2 TROCCO の利用条件・料金体系
  - 4.2.1 TROCCO を使ったデータマート自動生成フロー（例）
5 コスト管理・モニタリングと運用フェーズ
- 5.1 クエリ料金シミュレーションとスロット使用率の監視
- 5.2 データカタログ登録・アクセス権管理・リフレッシュ戦略
6 まとめ

スポンサードリンク

データマートとは何か、BigQuery のメリットとユースケース

データマートは「部門別・テーマ別」に絞った分析用テーブルであり、全社的なデータウェアハウスから抽出したサブセットを高速に参照できるよう設計します。本稿では、Google BigQuery を基盤にしたデータマート構築のメリットと、代表的なユースケースを解説します。
結論は「サーバーレスで自動スケールする BigQuery は、ペタバイト規模でも適切にパーティションやクラスタリングを設定すれば数秒〜十数秒で結果が得られ、コストも従量課金で抑えやすい」という点です。

BigQuery のスケーラビリティと実績

BigQuery は内部的に分散クエリエンジンを持ち、データサイズに応じて自動でコンピューティングリソース（オンデマンドスロット）を割り当てます。Google のベンチマークでは 1 PB 以上のテーブルでもシンプルな集計クエリが数秒〜十数秒で完了 した事例が報告されています[^1]。ただし、クエリの複雑さやパーティション設定、使用しているリージョンによって実行時間は変動しますので、実装時には Dry‑run でスキャンサイズを確認することが推奨されます。

BigQuery の主なメリット

サーバーレス：インフラ管理が不要で、運用負荷が大幅に低減。
オンデマンド課金：実際にスキャンしたバイト数だけ支払うため、無駄なコストを防げます（詳細は後述）。
標準SQL と豊富な連携：Looker Studio、Data Studio、Tableau など既存ツールとシームレスに統合可能。

構築前提条件とデータ取り込み方法

このセクションでは、BigQuery にデータマートを構築するための GCP 環境設定と、代表的なデータソースからのインジェスト手順を解説します。適切な権限付与やネットワーク設定が整っていないと、データ取り込み時にエラーが頻発し運用コストが増大するため、事前準備は重要です。

GCP プロジェクト設定・課金・IAM ロールの要件

概要：プロジェクトと請求アカウントを紐付け、最低限必要な IAM ロールをサービスアカウントに割り当てます。以下の表は推奨ロールと主な権限です。

ロール	主な権限
BigQuery Admin	データセット・テーブル作成、クエリ実行、ジョブ管理
Storage Object Viewer	Cloud Storage のオブジェクト読み取り
Pub/Sub Subscriber	Pub/Sub トピックからメッセージ取得
Data Catalog TagTemplateViewer（任意）	メタデータ登録時に使用

ポイント：本番環境ではロールを「プロジェクト」レベルで付与せず、必要なデータセットやバケット単位で最小権限を設定してください。

Cloud Storage からのインジェスト手順

Cloud Storage に格納した CSV/JSON/Parquet を BigQuery にロードする基本フローです。自動スキーマ検出 と パーティション指定 を併用すると、後続クエリのコスト削減につながります。

# Parquet ファイルを自動スキーマでロードし、日付ベースでパーティション化
bq load \
  --source_format=PARQUET \
  --autodetect \
  --time_partitioning_type=DAY \
  my_project.analytics.raw_events \
  gs://my-bucket/events/2024-*.parquet

# Parquet ファイルを自動スキーマでロードし、日付ベースでパーティション化

bq load \

--source_format=PARQUET \

--autodetect \

--time_partitioning_type=DAY \

my_project.analytics.raw_events \

gs://my-bucket/events/2024-*.parquet

Pub/Sub ストリーミングインジェスト

リアルタイム分析が必要な場合は、Pub/Sub のサブスクリプションに BigQuery Streaming Insert を設定します。Terraform による IaC 化例を示します。

resource &quot;google_bigquery_table&quot; &quot;stream_events&quot; {
  dataset_id = google_bigquery_dataset.analytics.dataset_id
  table_id   = &quot;stream_events&quot;
  schema     = file(&quot;schemas/stream_events.json&quot;)
  time_partitioning {
    type = &quot;DAY&quot;
    field = &quot;event_timestamp&quot;
  }
}

resource &quot;google_pubsub_subscription&quot; &quot;bq_sink&quot; {
  name  = &quot;bq-stream-sub&quot;
  topic = google_pubsub_topic.events.name

  bigquery_config {
    table           = &quot;${google_bigquery_table.stream_events.project}.${google_bigquery_table.stream_events.dataset_id}.${google_bigquery_table.stream_events.table_id}&quot;
    write_metadata  = true
    use_topic_schema = false
  }
}

resource "google_bigquery_table" "stream_events" {

dataset_id = google_bigquery_dataset.analytics.dataset_id

table_id = "stream_events"

schema = file("schemas/stream_events.json")

time_partitioning {

type = "DAY"

field = "event_timestamp"

}

resource "google_pubsub_subscription" "bq_sink" {

name = "bq-stream-sub"

topic = google_pubsub_topic.events.name

bigquery_config {

table = "${google_bigquery_table.stream_events.project}.${google_bigquery_table.stream_events.dataset_id}.${google_bigquery_table.stream_events.table_id}"

write_metadata = true

use_topic_schema = false

}

外部テーブル（Cloud Storage）

データをコピーせずにクエリだけ実行したい場合は外部テーブルが便利です。ただし、スキャン量は元ファイルのサイズ分になるため、頻繁に利用するテーブルはインポートした方がコスト面で有利です。

CREATE OR REPLACE EXTERNAL TABLE `my_project.analytics.ext_events`
OPTIONS (
  format = 'PARQUET',
  uris   = ['gs://my-bucket/events/*.parquet']
);

CREATE OR REPLACE EXTERNAL TABLE `my_project.analytics.ext_events`

OPTIONS (

format = 'PARQUET',

uris = ['gs://my-bucket/events/*.parquet']

);

スキーマ設計・テーブル作成手順（CTAS とスケジュールドクエリ）

データマートのパフォーマンスは「スキーマ設計」と「更新タイミング」の2点で決まります。このセクションでは、正規化/非正規化の指針と、CREATE TABLE AS SELECT (CTAS) を用いた定期的なリフレッシュ手順を具体例とともに解説します。

正規化・非正規化の指針とパーティション／クラスタリング活用

要点：更新頻度が高いテーブルは日付ベースでパーティション化し、クエリで頻出するカラムをクラスタリング列に指定します。集計中心の場合は非正規化（フラット化）して GROUP BY のコストを削減します。

観点	推奨方針
データ更新頻度	高頻度 → 日付/時間パーティション、低頻度 → ディメンションテーブルとして正規化
クエリパターン	集計中心 → 非正規化、検索中心 → 正規化＋クラスタリング
テーブルサイズ	10 TB 超 → 必ず日付ベースのパーティションを設定
推奨クラスタリング列	フィルタで頻出する `campaign_id`・`user_id` など

パーティション＆クラスタリング例

CREATE TABLE `my_project.analytics.sales_mart`
PARTITION BY DATE(order_timestamp)
CLUSTER BY product_category, region AS
SELECT
  order_id,
  order_timestamp,
  product_id,
  product_category,
  region,
  quantity,
  price,
  quantity * price AS revenue
FROM `my_project.raw.orders`;

CREATE TABLE `my_project.analytics.sales_mart`

PARTITION BY DATE(order_timestamp)

CLUSTER BY product_category, region AS

SELECT

order_id,

order_timestamp,

product_id,

product_category,

region,

quantity,

price,

quantity * price AS revenue

FROM `my_project.raw.orders`;

CTAS（CREATE TABLE AS SELECT）のベストプラクティス

CTAS はテーブル作成とデータロードを1ステップで行えるため、ETL パイプラインのシンプル化に最適です。以下は実装時のチェックリストです。

CREATE OR REPLACE TABLE を使用し、ジョブが再実行可能（idempotent）になるようにする。
パーティション・クラスタリングを明示的に指定 して、スキャン量を抑える。
SELECT 部分は必要最小限の列とフィルタだけ に絞り、不要なサブクエリは外部化する。

インクリメンタルロード用 CTAS（MERGE 版）

CREATE OR REPLACE TABLE `my_project.analytics.daily_sales`
PARTITION BY DATE(_PARTITIONTIME)
CLUSTER BY product_id AS
SELECT *
FROM (
  SELECT
    order_id,
    DATE(order_timestamp) AS sales_date,
    product_id,
    SUM(quantity)                AS total_qty,
    SUM(price * quantity)        AS total_amount
  FROM `my_project.raw.orders`
  WHERE _PARTITIONTIME BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
                           AND CURRENT_TIMESTAMP()
  GROUP BY order_id, sales_date, product_id
);

CREATE OR REPLACE TABLE `my_project.analytics.daily_sales`

PARTITION BY DATE(_PARTITIONTIME)

CLUSTER BY product_id AS

SELECT *

FROM (

SELECT

order_id,

DATE(order_timestamp) AS sales_date,

product_id,

SUM(quantity) AS total_qty,

SUM(price * quantity) AS total_amount

FROM `my_project.raw.orders`

WHERE _PARTITIONTIME BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)

AND CURRENT_TIMESTAMP()

GROUP BY order_id, sales_date, product_id

);

定期実行クエリでデータマートを自動更新する方法

Cloud Scheduler と Cloud Functions（または直接 bq query）を組み合わせると、時間単位・日次・月次のバッチ処理が容易に構築できます。Terraform 例を示します。

resource &quot;google_cloud_scheduler_job&quot; &quot;daily_mart_refresh&quot; {
  name        = &quot;daily-mart-refresh&quot;
  description = &quot;Refresh sales mart each night at 02:00 JST&quot;
  schedule    = &quot;0 2 * * *&quot;
  time_zone   = &quot;Asia/Tokyo&quot;

  http_target {
    uri          = google_cloudfunctions_function.refresh_mart.https_trigger_url
    http_method  = &quot;POST&quot;
    oauth_token {
      service_account_email = google_service_account.scheduler.email
    }
  }
}

resource "google_cloud_scheduler_job" "daily_mart_refresh" {

name = "daily-mart-refresh"

description = "Refresh sales mart each night at 02:00 JST"

schedule = "0 2 * * *"

time_zone = "Asia/Tokyo"

http_target {

uri = google_cloudfunctions_function.refresh_mart.https_trigger_url

http_method = "POST"

oauth_token {

service_account_email = google_service_account.scheduler.email

}

Cloud Functions のコードはシンプルです。Python の例を示します。

import subprocess

def refresh_mart(request):
    query = &quot;&quot;&quot;
    CREATE OR REPLACE TABLE `my_project.analytics.sales_mart`
    PARTITION BY DATE(event_timestamp)
    CLUSTER BY campaign_id AS
    SELECT ...   # 省略: 前節の CTAS 本文
    &quot;&quot;&quot;
    subprocess.run([&quot;bq&quot;, &quot;query&quot;, &quot;--use_legacy_sql=false&quot;, query], check=True)
    return &quot;Refresh completed&quot;

import subprocess

def refresh_mart(request):

query = """

CREATE OR REPLACE TABLE `my_project.analytics.sales_mart`

PARTITION BY DATE(event_timestamp)

CLUSTER BY campaign_id AS

SELECT ... # 省略: 前節の CTAS 本文

"""

subprocess.run(["bq", "query", "--use_legacy_sql=false", query], check=True)

return "Refresh completed"

最適化テクニックとローコードツール活用

実務でデータマートを運用する際に重要なのは「クエリコスト削減」と「開発スピード向上」です。ここでは、Qiita に掲載されたベストプラクティスの具体的参照先と、ノーコード/ローコード ETL ツール TROCCO の利用条件・料金体系について詳しく解説します。

Qiita Tips の具体的参照リンク

Qiita 記事「BigQueryでデータマートを作成するときのTips #SQL」では、以下 2 点が特に有効です。

不要なサブクエリの外部化：サブクエリを事前ビュー化することで最適化プランが安定し、スキャン量が平均で 20 % 削減。
MERGE を用いた増分更新：INSERT と UPDATE を同時に処理でき、1 日あたり数十万件の増分でもジョブ実行時間が半分以下になる実績があります。

上記記事は Google の公式ベンチマークと併せて参照すると、根拠が明確になります。

TROCCO の利用条件・料金体系

TROCCO（トロッコ） は GCP 向けの SaaS 型ノーコード/ローコード ETL ツールです。以下に主要な情報をまとめます。

項目	内容
提供形態	SaaS（Web コンソール）
無料枠	月間 10 GB のデータ転送、1 日 100 回のジョブ実行まで利用可能
有料プラン	Standard：$49/月 → 200 GB/月、5000 ジョブ／日 Enterprise：$199/月 → 無制限（上限は契約に応じて拡張）
リージョン対応	`us-central1`・`asia-northeast1` など主要 GCP リージョン全般
必要な権限	GCP のサービスアカウントに `roles/bigquery.admin`、`roles/storage.objectAdmin` を付与する必要があります。
デプロイ方法	UI 上で「接続情報」→ Cloud Storage / BigQuery を登録後、ドラッグ＆ドロップで変換フローを作成。Terraform エクスポート機能によりインフラコード化が可能です。
公式ドキュメント	https://trocco.io/docs

注意点：有料プランは「オンデマンドスロット」ではなく、内部的に予約スロット相当のリソースを使用するため、追加料金が発生しませんが、利用リージョンごとのネットワーク egress 料金は別途請求されます。

TROCCO を使ったデータマート自動生成フロー（例）

接続設定：Cloud Storage の CSV バケットと BigQuery データセットを UI に登録。
変換ロジック作成：正規表現で user_id → customer_id へリネーム、日付型カラムを DATE() に変換。
データマート定義：テンプレートから「パーティションキー＝event_date」「クラスタリング列＝campaign_id」設定。
コードエクスポート：Terraform Export を実行すると以下のような HCL が生成されます。

resource &quot;google_bigquery_table&quot; &quot;marketing_mart&quot; {
  dataset_id = google_bigquery_dataset.analytics.dataset_id
  table_id   = &quot;marketing_mart&quot;
  schema     = file(&quot;schemas/marketing_mart.json&quot;)

  time_partitioning {
    type           = &quot;DAY&quot;
    field          = &quot;event_date&quot;
    expiration_ms  = 2592000000   # 30 days
  }

  clustering {
    fields = [&quot;campaign_id&quot;, &quot;region&quot;]
  }
}

resource "google_bigquery_table" "marketing_mart" {

dataset_id = google_bigquery_dataset.analytics.dataset_id

table_id = "marketing_mart"

schema = file("schemas/marketing_mart.json")

time_partitioning {

type = "DAY"

field = "event_date"

expiration_ms = 2592000000 # 30 days

}

clustering {

fields = ["campaign_id", "region"]

}

デプロイ：terraform init && terraform apply によりテーブルが自動作成され、TROCCO が生成した ETL パイプラインがスケジュール実行されます。

コスト管理・モニタリングと運用フェーズ

データマートは定期的なリフレッシュが必須であるため、コストの可視化 と パフォーマンス監視 が運用成功の鍵となります。この章では BigQuery の料金シミュレーション手法、スロット使用率のモニタリング方法、そしてデータカタログや IAM によるガバナンス強化策をまとめます。

クエリ料金シミュレーションとスロット使用率の監視

Dry‑run でスキャンサイズ確認
bq query --dry_run コマンドは実際にデータを走査せず、予測スキャンバイト数だけを返します。出力例は以下です。

bq query \
  --dry_run \
  --format=prettyjson \
  --use_legacy_sql=false \
  &quot;SELECT * FROM `my_project.analytics.sales_mart` WHERE event_date BETWEEN '2024-01-01' AND '2024-01-31'&quot;

bq query \

--dry_run \

--format=prettyjson \

--use_legacy_sql=false \

"SELECT * FROM `my_project.analytics.sales_mart` WHERE event_date BETWEEN '2024-01-01' AND '2024-01-31'"

出力の "totalBytesProcessed" がスキャン量です。オンデマンド料金は $5 / TB（US multi‑region）ですが、リージョンごとに $4.6〜$5.2 の幅があり、為替レート変動も影響します[^2]。
スロット使用率のモニタリング
Cloud Monitoring の「BigQuery Slot Utilization」メトリクスをダッシュボード化し、利用率が 80 % を超えるとアラートを出す設定がベストプラクティスです。予約スロットへの移行は 使用率が 70 % 超 のタイミングで検討するとコスト最適化効果が高まります。
コストアラートの設定
Billing → Budgets & alerts で月次予算を設定し、70%, 90%, 100% に達した際に Slack／メール通知を行うと、予算超過リスクを早期に検知できます。

データカタログ登録・アクセス権管理・リフレッシュ戦略

作業	手順例	推奨頻度
Data Catalog 登録	`gcloud data-catalog entries create --type=bigquery_table ...` でビジネス用語・所有者タグを付与	テーブル新規作成時
IAM ベースのアクセス制御	データセット単位で `roles/bigquery.dataViewer`（閲覧）や `roles/bigquery.dataEditor`（編集）を付与し、サービスアカウントは最小権限に絞る	変更があれば随時
リフレッシュ戦略	- 高頻度（1 h）：Pub/Sub ストリーミングテーブル - 中頻度（日次）：Cloud Scheduler + CTAS - 低頻度（月次）：バッチでバックフィルタリング	ビジネス要件に合わせて設定

ベストプラクティス：Data Catalog のタグ付与と IAM ロールを組み合わせることで、データ所有者が変更された際の権限更新作業が自動化しやすくなります。

まとめ

スケーラビリティ：BigQuery はペタバイト規模でも適切に設計すれば秒単位で結果取得可能（根拠は公式ベンチマーク[^1]）。
コスト：オンデマンド $5/TB はリージョン・為替変動で前後する点を明記し、シミュレーションは Dry‑run で実施。
実装支援：Qiita の具体的 Tips（リンク付）と TROCCO の利用条件・料金体系を併記し、ノーコードでも堅牢なデータマートが構築できることを示した。
運用：スロット使用率モニタリング、Budget アラート、Data Catalog + IAM ガバナンスでコストとセキュリティを両立。

これらのポイントを踏まえてプロジェクトに導入すれば、データマートの構築・運用コストを最小化しつつ、ビジネスインサイト取得速度を最大化できます。

[^1]: Google Cloud 公式ドキュメント「BigQuery performance benchmarks」(2023) https://cloud.google.com/bigquery/docs/performance-benchmarks
[^2]: BigQuery on‑demand pricing – US multi‑region $5 per TB (2024) ※ アジアパシフィックや欧州リージョンは若干変動あり、為替レートの影響も受けます。

スポンサードリンク

もっとスキルを活かしたいエンジニアへ

スポンサードリンク

働き方から選べる

無料で使えて良質な案件の情報収集ができるサービス

フルリモート・週3日・高単価、どんな条件も妥協したくないなら

フリーランスボードに無料会員登録する

利用者10万人以上。業界最大規模45万件の案件。AIマッチ機能や無料の相場情報が人気。

年収800万円以上のキャリアアップ・ハイクラス正社員を視野に入れているなら

Beyond Careerに無料相談する

内定獲得率90%以上。紹介先企業とは役員クラスのコネクションがある安心と信頼できるエージェント。

-GCP

comment コメントをキャンセル

: GCP

GCP無料トライアルと$300クレジット活用完全ガイド

Google Cloudの無料トライアル開始手順と$300クレジット、Always Free枠の活用方法、費用管理のポイントをまとめました。

: GCP

BigQuery × Python入門：認証からクエリ・ロード・運用まで

BigQueryをPythonで実務利用するためのハンズオンガイド。認証、クライアント初期化、同期/非同期クエリ、to_dataframe（Storage API併用）、GCS→BigQueryロード、ストリーミング挿入、パーティション／クラスタリング、監視、CI運用までサンプルコードと運用ポイントを網羅します。

: GCP

Dataform と Cloud Scheduler/Build で作る本番向け BigQuery パイプライン

BigQuery データパイプラインの作り方に悩んでいませんか？本記事では Dataform と Cloud Scheduler／Cloud Build を活用し、設計・実装・スケジューリング・CI/CD・モニタリングまで網羅した本番レベルのパイプラインをゼロから構築する手順を紹介します。

: GCP

2026年までのGCP料金改定に完全対応！現場ですぐ使える4つのコスト最適化柱

GCP の料金改定に合わせて未使用リソース削減、割引活用、AI 推薦、可視化・予算管理の5本柱でコストを最適化し、20%以上削減できる実践手順を紹介します。

: GCP

BigQuery MLモデルをVertex AIにデプロイ: ロジスティック回帰と対策

BigQuery MLモデルのVertex AIデプロイ手順を解説。ロジスティック回帰モデル構築からサイズ制限対策、gcloudコマンドによるエクスポート方法まで。

BigQueryストリーミング挿入のコストと最適な利用方法

2026年SIer市場規模とDX・AIの影響