BigQuery の NOT ENFORCED キーは結合の最適化にどのように影響するか？

プロンプトと範囲

あなたは BigQuery のスタースキーマを担当しています。store_sales はファクトテーブルであり、customer はディメンションテーブルです。チームは、オプティマイザが一意性とリレーションシップのメタデータを利用して結合（Join）を削減できるように、主キーと外部キーの宣言を求めています。面接官は次の 3 つの質問を投げかけます。強制されない制約（unenforced constraint）が何を保証するのか、どの等価な書き換え（rewrite）が安全なのか、そしてデータの乖離（drift）が発生した際にサイレントなエラーをどう防ぐのか、です。

クエリはファクトテーブルのカラムのみをプロジェクション（射影）し、ファクトテーブルの外部キーは NULL 許容であり、ディメンションの主キーは一意かつ非 NULL であると仮定します。Google Cloud のドキュメントには、BigQuery はこれらの制約を強制（enforce）しないと記載されています。データの有効性を維持する責任は所有者にあり、制約に違反したデータに対するクエリは誤った結果を返す可能性があります。

面接官が評価するポイント

オプティマイザのメタデータと書き込み時の整合性チェックを区別できているか。
キーを単なるインデックスと呼ぶのではなく、一意性とオプショナルなマッチングから結合の排除（join elimination）を導出できているか。
NOT ENFORCED が重複キーや孤立した外部キー（orphan foreign keys）を拒否しないことを理解しているか。
DDL の定義だけで終わらせず、その契約（contract）をロードゲート、モニタリング、ロールバックへと落とし込んでいるか。

不十分な回答は、「主キーは一意であり、外部キーはそれを参照する」と述べるにとどまります。優れた回答は、オプティマイザが誤った宣言を利用してクエリを書き換える可能性があり、その結果としての障害は単にクエリが遅くなるだけでなく「データが誤ってしまう」ことであると指摘し、すべての宣言に対して再現可能な検証エビデンスが必要であると説明します。

回答前の確認事項

これは BigQuery のネイティブテーブルですか、それとも外部テーブルですか？制約のサポート状況や書き換えルールが異なるため、まず回答の範囲を明確にします。
クエリは左側のカラムのみをプロジェクションしていますか？右側のカラムを選択すると、通常は結合の排除が行えなくなります。
外部キーは NULL 許容ですか？ NULL はマッチが必須ではないことを意味し、等価な書き換えにおけるフィルタが変わります。
制約は単一のパイプラインで維持されていますか、それともシステム間のレプリケーションですか？システム間ロードでは、テーブル書き込みの前後両方でチェックが必要です。

これらの回答によって結果が変わります。右側のカラムの選択、主キーの重複、または非 NULL の孤立キーがあると、結合の排除は安全ではなくなります。結果整合性しか得られない場合は、チェック結果をリリースのゲート（判定基準）にする必要があります。

30秒の回答フレームワーク

「BigQuery のキーは宣言的なメタデータであり、デフォルトで NOT ENFORCED です。書き込み時に重複する主キーや孤立した外部キーを拒否することはありません。その価値は、オプティマイザに一意性とリレーションシップのファクトを提供することにあります。例えば、ファクトカラムのみを返す結合は、非 NULL フィルタに削減できる場合があります。データが宣言を満たしている必要があり、そうでなければ書き換えによって誤った結果が返る可能性があります。私はまずプロジェクションと NULL のセマンティクスを確認し、毎回のロード後に重複キー、孤立キー、レコード件数の整合性チェックを実行します。チェックが失敗した場合は、制約のリリースをブロックするか、バッチをロールバックします」

ステップバイステップの解決策

1. キーの 2 つの役割を切り分ける

主キー宣言は、各行が一意であり非 NULL であることを意味します。外部キー宣言は、すべての非 NULL 値が参照先の主キーに存在すべきであることを意味します。BigQuery は最適化のためにこれらの宣言を読み取ることができますが、書き込み時の検証は行いません。NOT ENFORCED は明示的な契約であり、障害の原因は無効な構文ではなく、無効なデータです。

2. 内部結合の排除を導出する

ファクトカラムのみを選択するクエリを考えます。

sql

SELECT ss.*
FROM store_sales AS ss
JOIN customer AS c
  ON ss.sales_customer = c.customer_name;

customer.customer_name が一意で非 NULL の主キーであり、すべての非 NULL の ss.sales_customer が 1 件の顧客にマッチする場合、この結合によってファクトの行が重複することはありません。オプティマイザは次のように書き換えることができます。

sql

SELECT *
FROM store_sales
WHERE sales_customer IS NOT NULL;

この書き換えは 2 つの事実に依存しています。すなわち、非 NULL の外部キーにはマッチが存在すること、そしてそのマッチが高々 1 行であることです。クエリが右側のカラムを必要とする場合、マッチしない行を区別する必要がある場合、または主キーが重複している場合には等価にはなりません。

3. 外部結合と結合順序の境界

右側の結合キーが一意であり、左側のカラムのみがプロジェクションされている場合、左外部結合（LEFT OUTER JOIN）も削除できる場合があります。マルチジョインクエリでは、キーのメタデータが結合の並べ替え（join reordering）のためのカーディナリティ情報を提供できます。これらはメタデータに基づく推論であり、BigQuery は一意性を証明するために実行時に右側のテーブルをスキャンすることはありません。

4. ロードゲートに正確性を組み込む

各バッチで少なくとも 3 つのチェックを実行します。

sql

-- Duplicate or null primary keys
SELECT customer_name, COUNT(*) AS n
FROM customer
GROUP BY customer_name
HAVING customer_name IS NULL OR n > 1;

-- Orphan foreign keys
SELECT COUNT(*) AS orphan_count
FROM store_sales AS ss
LEFT JOIN customer AS c
  ON ss.sales_customer = c.customer_name
WHERE ss.sales_customer IS NOT NULL
  AND c.customer_name IS NULL;

バッチの行数、非 NULL の外部キー件数、マッチしたキー件数を照合（レコンサイル）します。結果をデータ品質テーブルに保存します。このゲートを通過したバージョンのみが、新しい制約宣言を公開したり、ダウンストリームのクエリが最適化に依存したりすることを許可されるべきです。

5. 宣言、検証、書き換えの選択

宣言は、安定的でテスト可能なディメンションキーに適しており、オプティマイザがリレーションシップを自動的に利用できるようにします。アプリケーションによる検証は、不正なデータを早期に拒否する必要がある取り込み（イングレス）パスに適しています。クエリの明示的な書き換えは、契約が信頼できない移行期間に適していますが、ロジックが重複するコストが発生します。これらは共存可能です。パイプラインで検証し、信頼できるリレーションシップを宣言し、重要なレポートを照合します。

6. 障害パスの設計

チェックが失敗した場合は、最後に信頼されたテーブルまたはビューを維持し、現在のバッチを公開不可としてマークし、データオーナーにアラートを送信します。唯一の「修正」として制約を削除することは避けてください。原因が隠蔽されてしまいます。重複キーの発生元、レプリケーションの遅延、または削除の順序を追跡し、再実行（リプレイ）、重複排除、またはディメンションの修復を選択します。

高品質な回答サンプル

「私は BigQuery の主キーと外部キーを、トランザクション制約ではなく、最適化のための契約として扱います。まずクエリがファクトカラムのみをプロジェクションしていることを確認し、NULL 許容外部キーの意味を定義し、ディメンションキーの一意性を検証します。それらの条件下では、内部結合を非 NULL のファクトフィルタに変換でき、一部の LEFT JOIN を除去でき、結合順序に宣言されたカーディナリティを使用できます。リスクは、BigQuery が契約を強制しないことです。主キーの重複や孤立した外部キーが存在すると、オプティマイザが誤ったメタデータを使用して書き換えを行ってしまい、結果がサイレントに変更される可能性があります。

リリース前には、バッチごとに NULL および重複した主キー、孤立した外部キー、行数の整合性をチェックします。失敗したバッチはテーブルや新しい制約を公開できず、以前の信頼できるバージョンがアクティブなままになります。メタデータが信頼できない移行期間中は、依存する書き換えを無効化するか、複数のバッチが成功するまで明示的な結合を使用します。これにより、正確性を監査可能に保ちながら、最適化のメリットを享受できます」

よくある間違い

間違い: PRIMARY KEY が重複を拒否すると答える → 失敗の理由: BigQuery のドキュメントで制約は強制されないと明記されているため → 対策: ロードゲートに重複検出を組み込む。
間違い: 外部キーを持つすべての結合を削除する → 失敗の理由: キーが孤立している可能性があり、クエリが右側のカラムを必要としている可能性があるため → 対策: 最初にデータとプロジェクションの条件を検証する。
間違い: 履歴データを 1 回だけチェックする → 失敗の理由: 増分ロード、バックフィル、レプリケーション遅延によって不正なキーが再混入する可能性があるため → 対策: バッチチェックを継続的に実行し、メトリクスを保持する。
間違い: 誤った結果が出た後に制約を削除する → 失敗の理由: 根本原因が残ったまま最適化メタデータが失われるため → 対策: リリースを凍結し、原因を突き止め、信頼できるバージョンを復元する。

フォローアップと回答

今日、ディメンションに重複キーが発生した場合はどうしますか？

その宣言に依存するクエリを一時停止し、明示的に重複排除されたスナップショットまたは信頼できるスナップショットに切り替え、影響を受けるバッチをマークして、照合を再実行します。ディメンションが修復され、チェックに合格した後にのみ宣言を復旧します。

なぜ NULL 許容の外部キーに非 NULL フィルタが必要なのですか？

NULL は、そのファクト行に一致する顧客が存在しないことを意味します。内部結合ではその行が除外されるため、等価な書き換えでは WHERE sales_customer IS NOT NULL を維持する必要があります。これを省略すると結果が変わってしまいます。

結合の排除が結果を保持することをどのように証明しますか？

代表的なパーティション上で、元のクエリと書き換えたクエリを並行して実行します。行数、主キーのセット、集計値を比較し、制約チェックのバージョンを記録します。データ品質チェックと結果の照合の両方に合格した後にのみ、書き換えを本番に適用します。

制約ベースの最適化を避けるべきなのはどのような場合ですか？

制約のソースが低速または監査不可能なレプリケーションである場合、バックフィルが頻繁に行われる場合、またはバッチゲートが存在しない場合は避けます。誤ったメタデータを信頼する安価なクエリよりも、1 回余分にスキャンするクエリのほうが安全です。

BigQuery の制約はテーブル間のトランザクションを代替できますか？

いいえ。書き込みの一貫性を強制したり、テーブル間でアトミックなコミットを提供したりすることはありません。トランザクションのセマンティクスはアップストリームシステムやロードオーケストレータに属し、最終的な検証結果がウェアハウスに持ち込まれます。

参考文献

Google Cloud ドキュメント: BigQuery の主キーと外部キー
Google Cloud ブログ: Join Optimizations with BigQuery Primary and Foreign Keys