dbt × BigQuery 実戦ハマりどころ集 — データが静かに消える・型が合わない・VIEWが壊れる

dbt のコードは正しいのにデータがおかしい——どこを見るべきでしょうか。答えはコードの外です。BigQuery 側の設定と仕様が原因のトラブルは、dbt のモデルをいくら読み直しても見つかりません。この記事では、GA4 エクスポートや CRM データを扱う実案件で私が実際に踏んだ3つの「静かな壊れ方」——データが60日で消える・JOIN が型で落ちる・VIEW が ambiguous column を生む——を、症状から逆引きできる形で整理します。合計で10時間超を溶かしたデバッグの記録なので、同じ穴を数分で抜けるための地図として使ってください。

dbt 連載（プロジェクト構造〜運用）が「正しく設計する」話だとすれば、本稿は「正しく設計したのに壊れた」ときの話です。原因の層が違うので、探し方も違います。

症状→原因の逆引き表

まず全体の地図です。3つとも共通するのは「dbt はエラーを出さずに成功している（または、エラーの文面がコードの間違いに見える）」こと。症状から引いてください。

ケース1: データが60日で静かに消える — default expiration

症状

マートの日次テーブルが、なぜか直近60日分しかない。ソースにはもっと古いデータがある。dbt run は成功しているし、dbt test も通っている。--full-refresh で作り直すと一瞬すべての期間が入る——ように見えて、翌日確認するとまた60日分に戻っている。

私はここでコードを疑い続けました。incremental の where 句、lookback の日数、unique_key の設定。どれも正しく、どれを直しても症状は変わりませんでした。

原因

原因はコードではなく、データセットに設定された既定の有効期限でした。BigQuery のデータセットには default table expiration（テーブル自体が作成から N 日で消える）と default partition expiration（N 日より古いパーティションが消える）があり、設定されているとそのデータセット内に新しく作られるテーブルへ自動的に継承されます。dbt はテーブル作成時に有効期限を明示しない（既定では「無期限」を意図する＝何も設定しない）ため、データセット側の既定がそのまま効きます。

「--full-refresh しても戻る」のがこの原因の指紋です。パーティション有効期限はパーティションが表す日付から起算されます。たとえば60日の期限下で全期間を書き直しても、60日より古い日付のパーティションは書き込み直後に期限切れと判定され、また削除されます。何度作り直しても、まったく同じ形に壊れる——コードのバグはこういう壊れ方をしません。

確認と恒久対処

確認は1コマンドです。

bq show --format=prettyjson my-project:analytics | grep -i expiration
#   "defaultPartitionExpirationMs": "5184000000",   ← 60日（60 × 86400 × 1000 ms）
#   "defaultTableExpirationMs": "5184000000"

値が出てきたら、データセット既定を解除します。

ALTER SCHEMA analytics
SET OPTIONS (
  default_table_expiration_days = NULL,
  default_partition_expiration_days = NULL
);

ここで終わらせないのが重要です。既定の解除はこれから作られるテーブルにしか効きません。既存テーブルは継承済みの有効期限を個別に持ったままなので、解除後に --full-refresh で作り直すか、テーブル単位で期限をクリアします。消えたデータそのものは、ソース側に残っていれば再構築で戻ります（GA4 エクスポートのようにソースが履歴を保持していれば救済可能。ソースも同じデータセット設定下にあった場合は戻りません）。

教訓 — 「何度直しても同じ形に壊れる」はインフラのサイン

この件で最も高くついたのは、削除されたデータではなく診断の順序でした。「データがおかしい→コードを読む」という反射で数時間を使いましたが、振り返ると最初の30分で気づけるサインが出ていました。

• ビルド（--full-refresh 含む）を繰り返しても、同じ行数・同じ日付範囲に戻る
• staging の出力は正常なのに、書き込んだ先だけが少ない

この2つが揃ったら、コードではなく「書き込んだ後に消されていないか」——有効期限・保持ポリシー・クォータ——を先に見るべきです。コードのバグは入力に応じて壊れ方が揺れます。インフラの設定は、毎回まったく同じ形に壊します。

ケース2: JOIN が型で落ちる — ID は両層で STRING 保証

症状

複数ソースを結合する intermediate で、こういうエラーが出ます。

select o.order_id, c.segment
from `raw_shop.orders` as o
join `raw_crm.customers` as c
  on o.customer_id = c.customer_id
-- Error: No matching signature for operator = for argument types: INT64, STRING

片方のソースでは customer_id が INT64、もう片方では STRING。同じ「ID」でも、EL ツールの型推論や BigQuery のスキーマ自動検出は、値が数字だけなら数値型に倒すことがあります。ソースを増やすたびに、どこかの JOIN で型が食い違う——これは事故ではなく、放っておけば必ず起きる構造です。

対処 — EL 層と dbt staging の「両方」で cast する

規約はシンプルです。ID は数値に見えても文字列。そして cast を置く場所は片方ではなく両方です。

まず dbt の staging で、すべての ID カラムを明示的に STRING にします。

-- models/staging/stg_crm__customers.sql
select
    cast(id as string)         as customer_id,
    cast(account_id as string) as account_id,
    segment,
    created_at
from {{ source('crm', 'customers') }}

次に、自前の EL スクリプトがあるなら、ロード時のスキーマでも STRING を明示します。

# BigQuery ロード時に型を固定（autodetect に任せない）
schema = [
    bigquery.SchemaField("id", "STRING"),
    bigquery.SchemaField("account_id", "STRING"),
]

なぜ片方だけでは足りないのか。EL 側だけ直しても、ロード済みの既存テーブルの型は変わりませんし、将来スクリプトを差し替えたときに推論へ戻る余地が残ります。dbt 側だけ直すと dbt を通る経路は安全になりますが、ソーステーブルを直接クエリする経路（BI ツール・その場しのぎの分析）が保護されません。層をまたいで同じ保証を二重に置く——冗長に見えて、これが再発を断つ最小構成でした。ID の型保証を staging に置く理由と staging 層の設計はstaging層を作る ― ローデータに触れるのはここだけで詳しく扱っています。

ケース3: VIEW が壊す ambiguous column

症状

触っていないマートのビルドが、ある日こう落ちます。

Column customer_id is ambiguous on left side of join

SQL を見ても customer_id は曖昧に見えません。各テーブルに1つずつしかないはずのカラムです。しかもエラーが指す行番号が、手元の SQL の行と一致しない。

原因 — VIEW はクエリのたびに展開される

BigQuery の論理ビューは実体を持たず、参照されるたびに定義クエリが実行されます。つまりマートを CREATE TABLE AS SELECT で作るとき、参照している view の intermediate は、その場でマートのクエリへインライン展開されてから解釈されます。展開後の巨大な SQL の中で同名カラムが同じスコープに重複すると、手元のコードでは曖昧でなかった参照が ambiguous になります。行番号が合わないのも同じ理由で、エラーは展開後の SQL を指しているからです。

dbt の既定では intermediate は view にすることが多く（プロジェクト構造の回参照）、この設計自体は正しい。壊れるのは「view を参照してテーブルを実体化する」特定の JOIN パスだけです。

対処 — USING をやめ、問題の intermediate だけ table にする

打ち手は2つで、どちらも局所的です。

第一に、view が絡む JOIN では USING ではなく ON で両側を明示します。

-- ⚠️ view の展開後、どの customer_id を指すか曖昧になりうる
left join {{ ref('int_customer_channel') }} as ch using (customer_id)
 
-- ✅ 両側を明示するので展開後も曖昧にならない
left join {{ ref('int_customer_channel') }} as ch
  on c.customer_id = ch.customer_id

第二に、それでも問題が残る JOIN パスでは、該当する intermediate だけを table として実体化します。

-- 問題の JOIN パスに入る intermediate のみ
{{ config(materialized='table', on_schema_change='sync_all_columns') }}

過修正の告白 — 全部 table 化してはいけない

当時の私は原因の切り分けが済む前に、「核オプション」として intermediate 全体を table 化しました。エラーは消えましたが、これは過修正でした。イベント系の大きな intermediate（GA4 のセッション全体など）が table になると、実行のたびに全量を書き込むことになり、コストと実行時間が跳ね、上流を incremental にした恩恵（増分処理の回）が半減します。あわせて実施した「無関係な JOIN の USING を一括で ON に変換」も、いま見れば不要でした。型が揃った table 同士の USING は安全で、意味があるのは view が絡むパスだけです。

エラーが消えたことと、正しく直したことは別物です。全体設定を変えてエラーが消えると原因の特定が終わった気になりますが、トレードオフごと引き受けたことに後で気づきます。既定は view のまま、壊れた箇所だけ table——これが釣り合いの取れた着地でした。

診断の順序 — staging と mart の乖離をどう追うか

3つのケースに共通する型を、手順として一般化しておきます。症状が「上流にあるはずのデータが下流に無い」なら、上流から下流へ、各層で行数と日付範囲を確認しながら二分探索します。

1. ソーステーブル   … 行数・日付範囲は想定通りか（そもそもデータはあるか）
2. staging           … ソースと乖離していないか（view なら直接クエリする）
3. 問題箇所の CTE     … 単独で実行してロジックを確認する
4. 書き込み先（mart） … ここだけ少なければ「書いた後に消えている」疑い
5. インフラ設定       … 有効期限・クォータ・権限（4 まで正常ならほぼここ）

ポイントは2つあります。第一に、1〜3 が正常で 4 だけ欠ける場合、コードを読む工程を飛ばして 5 へ進んでよい。「変換の途中で失われた」のではなく「書き込んだ後に消された」構図であり、それはコードの外の仕業だからです。

第二に、「dbt test が通っている＝壊れていない」ではないことです。unique や not_null は「存在する行」の品質を検査しますが、「存在すべき行が消えた」ことは検知しません。テストはビルド時点のスナップショットに対して走るので、その後に有効期限が行を削っても次のビルドまで誰も気づかない。行数や日付範囲の下限を監視するテスト（フレッシュネス・volume 系）を足す判断も含め、テスト設計はテストとデータ品質の回で扱っています。本稿の3ケースはいわば「テストの外」で壊れる話で、両方が揃って初めて守りになります。

まとめ — 新規データセット初期設定チェックリスト

同じ穴を踏まないための、プロジェクト開始時（および「データが少ない」症状が出たとき）のチェックリストです。

• bq show --format=prettyjson project:dataset で defaultTableExpirationMs / defaultPartitionExpirationMs がともに未設定であることを確認したか
• ID カラムは「数値に見えても STRING」の規約を置き、EL 層と staging 層の両方で cast しているか
• materialization は「intermediate は view が既定、ambiguous が出た JOIN パスだけ table」の方針か（全 table 化はコストと実行時間の別問題を生む）
• view が絡む JOIN は USING でなく ON で両側を明示しているか
• 「作り直しても同じ形に壊れる」はインフラのサイン、という診断順序をチームで共有したか

なお本稿の BigQuery の挙動（expiration の継承・起算、VIEW のインライン実行）は 2026-07-07 時点の公式ドキュメントで確認しています。挙動が版や時期で変わりうる領域なので、frontmatter の sources に確認日つきで残してあります。

この記事は「整える層を作ったあと、それでも壊れる」話でした。壊れにくい層をどう設計するかは staging 層の作り方とテスト・データ品質へ。そして「そもそもこの土台に投資すべきか」を決裁者の言葉で判断する話は、対になるビジネス版の基幹記事データ基盤に投資すべきかで扱っています。