BigQueryでSQLテストを書いてデータマートの品質を担保する

はじめに：壊れたデータで意思決定する怖さ

ダッシュボードの数字が、ある朝いきなり半分になっていた。原因をたどると、元データの取り込みが途中で止まっていて、集計テーブルが「昨日までの分しか入っていない状態」で更新されていた——中小ECやデータ基盤の現場で、わりとよく起きる事故です。

怖いのは、こうした不具合が「エラー」として表に出ないことです。クエリは正常に完了し、テーブルもできあがり、ダッシュボードもちゃんと表示される。ただ、中身が間違っているだけ。人間がグラフを見て「なんか変だな」と気づくまで、誰も止めてくれません。その間に、間違った数字を見て広告予算を増やしたり、在庫を発注したりしてしまうと、被害は地味に広がります。

これを防ぐ考え方が データのテスト です。アプリ開発でコードにテストを書くのと同じように、「このテーブルはこういう状態であるべきだ」というルールを明文化して、満たさなければ気づけるようにしておく。BigQueryなら、追加のツールを入れなくても、SQLだけでかなりのことができます。

この記事では、何をテストすべきかを整理したうえで、ASSERT や検査クエリの具体例、スケジュールクエリでの定期検査と失敗通知、そして dbt のテスト機能について順番に紹介します。

何をテストするか：品質チェックの5つの観点

「データをテストする」と言っても、何を見ればいいのか最初は迷うものです。中小ECのデータマートで効果が高いのは、次の5つの観点です。まずはここから始めれば十分です。

1. 件数（レコード数）

もっとも基本的で、もっとも効くのが件数チェックです。「毎日入るはずのデータが0件になっていないか」「想定より極端に少なく（多く）ないか」を見ます。冒頭の「取り込みが途中で止まっていた」事故は、件数を見ていれば真っ先に気づけたはずのものです。

2. NULL（欠損）

本来は必ず値が入るはずの列（注文ID・金額・日付など）に NULL が混ざっていないかを確認します。途中の結合（JOIN）で対応するレコードが見つからず、知らないうちに NULL が増えていることはよくあります。

3. 重複

「1注文につき1行」のはずのテーブルに、同じIDが2行ある——こうした重複は、集計の合計値をそのまま水増しします。主キーにあたる列が本当に一意になっているかを確かめます。

4. 参照整合（関連の整合性）

注文テーブルにある customer_id が、顧客マスタに存在するか。商品コードが商品マスタにあるか。いわゆる「親のいない子レコード」がないかを見る観点です。データを別々のシステムから集めていると、ここがずれがちです。

5. 範囲（値の妥当性）

金額がマイナスになっていないか、日付が未来になっていないか、割合が0〜1の範囲に収まっているか。「ありえない値」が入っていないかをチェックします。明らかな異常値は、たいてい上流の不具合のサインです。

ASSERTや検査クエリの例

ここからは具体的なSQLを見ていきます。対象は、日別の売上サマリーをまとめたデータマート myproject.mart.daily_sales と、その元になる注文テーブル myproject.raw.orders を想定します。

ASSERT文でテストを書く

BigQueryには ASSERT という構文があり、「条件を満たさなければエラーで止まる」テストをそのまま書けます。条件が偽（または結果が空でない、といった想定）になったときにジョブを失敗させられるので、スケジュール実行と組み合わせると「壊れていたら通知が飛ぶ」状態を作れます。

たとえば「昨日の売上データが1件以上あること」を確かめるテストは、次のように書けます。

ASSERT (
  SELECT COUNT(*)
  FROM `myproject.mart.daily_sales`
  WHERE order_date = DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY)
) > 0 AS '昨日分のdaily_salesが0件です。取り込みが止まっている可能性があります。';

ASSERT 条件 AS 'メッセージ' という形で、条件が満たされないときに指定したメッセージとともにジョブが失敗します。メッセージには「何がおかしいのか」「次に何を疑えばいいのか」を書いておくと、後で通知を見たときに動きやすくなります。

NULL・重複・範囲をまとめて検査する

ASSERT は1条件ずつ書くのが基本ですが、複数のルールをまとめて点検したい場合は、「違反した件数」を数える検査クエリを書くのが扱いやすいです。次のクエリは、NULL・重複・範囲の3つを一度に集計します。

SELECT
  -- NULL：必須列に欠損がないか
  COUNTIF(order_id IS NULL)        AS null_order_id,
  COUNTIF(amount   IS NULL)        AS null_amount,
  -- 範囲：金額が0以下のレコードがないか
  COUNTIF(amount <= 0)             AS invalid_amount,
  -- 範囲：未来日付が混ざっていないか
  COUNTIF(DATE(ordered_at, 'Asia/Tokyo') > CURRENT_DATE('Asia/Tokyo')) AS future_date,
  -- 重複：同じ order_id が複数あるか（後段で判定）
  COUNT(*)                         AS total_rows,
  COUNT(DISTINCT order_id)         AS distinct_order_id
FROM `myproject.raw.orders`
WHERE DATE(ordered_at, 'Asia/Tokyo') = DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY);

このクエリは「違反があれば0より大きい値が返る」ように作っています。total_rows と distinct_order_id が一致していなければ重複があるということです。結果を目視するだけでも有効ですが、ASSERT でくるんで自動判定にするとさらに安心です。たとえば重複チェックだけを ASSERT 化すると、次のようになります。

ASSERT (
  SELECT COUNT(*) - COUNT(DISTINCT order_id)
  FROM `myproject.raw.orders`
  WHERE DATE(ordered_at, 'Asia/Tokyo') = DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY)
) = 0 AS 'orders に order_id の重複があります。';

参照整合をチェックする

「注文にあるが顧客マスタにいない customer_id」を探すには、LEFT JOIN で相手側が NULL になる行を数えます。これも ASSERT で「0件であること」を保証できます。

ASSERT (
  SELECT COUNT(*)
  FROM `myproject.raw.orders` AS o
  LEFT JOIN `myproject.raw.customers` AS c
    ON o.customer_id = c.customer_id
  WHERE c.customer_id IS NULL
) = 0 AS '顧客マスタに存在しない customer_id を持つ注文があります。';

LEFT JOIN の結果、右側（顧客マスタ）が NULL になる行は「親のいない注文」です。これがあると、顧客単位の集計から注文が漏れたり、不明顧客として変なグループに混ざったりします。

スケジュールクエリで定期検査＋失敗通知

テストは「書いて一度実行して終わり」では意味がありません。データは毎日更新されるので、テストも毎日自動で走らせ、壊れたときに気づける状態にして初めて役に立ちます。

BigQuery標準の スケジュールクエリ を使えば、追加のサーバーなしでこれを実現できます。具体的な設定手順は別記事の BigQueryのスケジュールクエリでデータマートを毎朝自動更新する設定と監視方法にまとめていますが、ここではテスト用途でのポイントを押さえます。

データマート更新の「後」にテストを走らせる

検査の鉄則は、「データマートを作り直したあとに、その結果をテストする」順番です。スケジュールクエリは実行時刻で並べる仕組みなので、次のように時間をずらして配置します。

6:00 — daily_sales を CREATE OR REPLACE TABLE で更新するスケジュール
6:30 — 上で紹介した ASSERT 群をまとめた検査用スケジュール

更新が終わるのを待ってから検査が走るよう、十分に間隔を空けるのがポイントです。複数の ASSERT は、1本のスケジュールクエリの中に並べて書いておけば、まとめて実行されます。どこかひとつでも失敗すればジョブ全体が失敗扱いになります。

失敗を通知で受け取る

ASSERT が失敗するとスケジュールクエリのジョブが失敗します。スケジュールクエリには失敗時の通知機能があるので、これを有効にしておけば「テストが落ちた＝データがおかしい」という連絡が自動で届きます。

メール通知 — まずはこれで十分です。指定したユーザーに失敗メールが届きます。
Pub/Sub通知 — Slackなどに流したい場合に使います。

「通知が届かない＝今日もデータは健全」という状態になるので、毎朝グラフをにらんで異常を探す必要がなくなります。なお、テストが落ちたときに備えて、ASSERT のメッセージには原因の見当をつけられる文言を入れておくと、復旧が早くなります。

実行履歴も確認できる

「テストがちゃんと毎日走っているか」は、INFORMATION_SCHEMA.JOBS で確認できます。検査用ジョブの成否や実行時刻が記録されるので、「いつの間にかテスト自体が止まっていた」という二次災害も防げます。

dbtのテスト機能にも触れておく

ここまではBigQuery単体（SQLとスケジュールクエリ）でできる範囲を紹介しました。データの種類が増えてテーブルの依存関係が複雑になってくると、テストを体系的に管理したくなります。そこで選択肢に上がるのが dbt です。

dbtはSQLでデータ変換を組み立てるためのツールで、テスト機能が標準で備わっています。代表的なものは設定ファイルに数行書くだけで使えます。

models:
  - name: daily_sales
    columns:
      - name: order_date
        tests:
          - not_null      # NULLがないこと
          - unique        # 一意であること
      - name: amount
        tests:
          - not_null

このように not_null や unique、別テーブルとの整合を見る relationships といったテストが用意されていて、この記事で手書きした ASSERT 相当のチェックを、宣言的に書けます。さらに、テーブル同士の依存関係を理解したうえで「正しい順番で更新し、そのつどテストする」といった運用も組みやすくなります。

ただし、dbtの導入はそれ自体が学習コストと運用の手間を伴います。テーブルが数個のうちは、本記事の ASSERT とスケジュールクエリで十分に回ります。「手書きのテストが増えすぎて管理が大変になってきた」と感じたら、dbtを検討する、という順番がおすすめです。

まとめ

データの品質は、「壊れていないことを祈る」のではなく、「壊れたら気づける」仕組みで守るものです。要点を振り返ります。

まずは 件数・NULL・重複・参照整合・範囲 の5観点から始める。中小ECならこれで大半の事故を捕まえられます。
BigQueryの ASSERT を使えば、「条件を満たさなければジョブが失敗する」テストをSQLだけで書ける。
データマート更新の後にテストをスケジュール実行し、失敗通知（メール／Pub/Sub）で受け取れば、毎朝グラフをにらむ作業から解放される。
テーブルが増えて管理が大変になってきたら、dbtのテスト機能を検討する。

集計の高速化を考える段階になったら、マテリアライズドビューでGA4の集計を速くするもあわせてどうぞ。品質と速さの両方が整うと、データを「信じて使える」状態に近づきます。