題幹與適用情境
你維護一個 BigQuery 星型模型：store_sales 是事實表，customer 是維度表。團隊想在表上宣告主鍵與外鍵，讓最佳化器利用唯一性與關係資訊減少 Join。面試官要求你回答三件事：約束未執行時究竟保證什麼；最佳化器可以做哪些等價轉換；資料漂移時如何避免靜默錯誤。
假設查詢只選取事實表欄位，事實表外鍵允許 NULL，維度表主鍵應唯一且不可為 NULL。BigQuery 文件明確說明這些約束不會由引擎強制執行，維護者必須自行保證資料符合宣告；違反約束的查詢可能回傳錯誤結果。
面試官考察點
- 能否區分「最佳化器可相信的中繼資料」與「寫入時的完整性驗證」。
- 能否從唯一性和可選匹配推導 Join 消除，而不是籠統說「加索引會更快」。
- 是否知道
NOT ENFORCED不是軟性驗證，也不會自動拒絕重複鍵或懸空外鍵。 - 能否把約束治理落實在載入流程、異常監控和回滾，而非只給 DDL。
普通回答停在「主鍵唯一、外鍵關聯」。強回答會指出：最佳化器可能依據錯誤宣告重寫查詢，因此壞資料的後果是結果錯誤，而非單純效能下降；每個約束都需要可重複的驗證證據。
回答前需要釐清的問題
- 題目討論的是 BigQuery 原生表還是外部表？約束支援範圍與可用最佳化規則不同，先限定原生表。
- 查詢是否只投影左表欄位？若要回傳右表欄位，Join 消除通常不成立。
- 外鍵欄位是否允許
NULL？NULL表示沒有匹配要求，推導過濾條件時必須保留這一點。 - 約束由同一條資料管線維護，還是跨系統複製？跨系統情境需要把一致性檢查放在落表前與落表後。
這些問題會改變結論：投影右表欄位、重複主鍵或非空懸空外鍵都會使 Join 消除不安全；若只能接受最終一致性，就要把檢查結果作為發布門檻。
30 秒回答框架
「BigQuery 的主外鍵是宣告式中繼資料，預設 NOT ENFORCED，不會在寫入時攔截重複主鍵或懸空外鍵。它們的價值是提供唯一性與關係資訊給最佳化器，例如只取事實表欄位時可以消除不改變結果的內連接。前提是資料真的符合宣告；否則最佳化器可能產生錯誤結果。我的做法是先限定投影與 NULL 語義，再在每次載入後做重複鍵、懸空鍵與筆數對帳，失敗就阻止約束發布或回滾，而不是把約束當成驗證器。」
分步深入解答
1. 先分清約束的兩個角色
主鍵宣告表示「每列唯一且不可為 NULL」；外鍵表示「非 NULL 值應出現在被參照主鍵中」。BigQuery 可以讀取這些宣告來最佳化查詢，但不會替你執行寫入驗證。NOT ENFORCED 仍是明確契約，錯誤在於資料不符合契約，而不是語法無效。
2. 推導內連接消除
考慮只選取事實表欄位的查詢：
SELECT ss.*
FROM store_sales AS ss
JOIN customer AS c
ON ss.sales_customer = c.customer_name;若 customer.customername 是唯一不可為空的主鍵，且 ss.salescustomer 要嘛匹配一位客戶、要嘛為 NULL，連接不會複製事實列。對只選取 ss 欄位的查詢，最佳化器可以改寫成：
SELECT *
FROM store_sales
WHERE sales_customer IS NOT NULL;這個轉換依賴兩個事實：非 NULL 外鍵必有匹配，且匹配至多一列。若查詢選了 c 的欄位、需要區分無匹配資料列，或主鍵重複，轉換就不再等價。
3. 外連接與連接重排的邊界
左外連接在右表連接鍵唯一、且投影只來自左表時也可能被消除。多表查詢還能利用主外鍵推斷基數，調整連接順序。這些都是依據宣告的推導，不代表 BigQuery 執行時會掃描右表驗證唯一性。
4. 把資料正確性放進載入閘門
每個批次至少執行三類檢查：
-- 主鍵重複或空值
SELECT customer_name, COUNT(*) AS n
FROM customer
GROUP BY customer_name
HAVING customer_name IS NULL OR n > 1;
-- 懸空外鍵
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;再用批次筆數、非空外鍵數量與匹配數量做對帳。檢查結果寫入資料品質表，只有通過閘門的版本才發布新的約束宣告或讓下游查詢使用最佳化結果。
5. 選擇宣告、驗證或查詢改寫
宣告約束適合穩定且可驗證的維度鍵，收益是最佳化器能自動利用關係；應用層驗證適合需要儘早拒絕壞資料的入口；查詢改寫適合約束暫時不可信的遷移期，但會增加重複邏輯。三者可以並存：先在資料管線驗證，再宣告約束，最後用關鍵報表的結果對帳監控。
6. 設計失效保護
當檢查失敗時保留上一版可信表或檢視表，標記目前批次不可發布並告警資料負責人。不要只刪除約束來「修復」錯誤，因為這會隱藏根因；先找出重複鍵來源、複製延遲或刪除順序，再決定重播、去重或補齊維度。
高品質示範回答
「我會把 BigQuery 主外鍵當作最佳化契約，不當作交易約束。先確認查詢只取事實欄位、外鍵 NULL 的語義，以及維度鍵的唯一性。滿足這些條件時，內連接可以改寫成事實表的非空過濾，某些左連接也能消除，連接順序還能依據基數最佳化。關鍵風險是 BigQuery 不執行這些約束；重複主鍵或懸空外鍵會讓最佳化器依據錯誤中繼資料重寫查詢，結果可能靜默錯誤。
「上線前我會在每個批次檢查主鍵空值與重複、外鍵孤兒列，並做筆數和匹配數對帳；失敗批次不發布新表或約束，保留上一版。對遷移中的不可信約束，我會先關閉依賴宣告的查詢改寫或改用顯式 Join，直到連續批次通過檢查。這樣既得到中繼資料最佳化收益，也把正確性責任落實到可稽核的管線。」
常見錯誤
- 錯誤表現：說
PRIMARY KEY會拒絕重複列 → 失敗原因：BigQuery 文件明確為不強制執行 → 修正方法：把重複檢查放進載入閘門。 - 錯誤表現：看到外鍵就直接刪除 Join → 失敗原因：外鍵可能懸空，且查詢可能需要右表欄位 → 修正方法：先驗證資料與投影條件。
- 錯誤表現：只檢查一次歷史資料 → 失敗原因：增量批次、回填和複製延遲會重新引入壞鍵 → 修正方法：按批次持續檢查並保留指標。
- 錯誤表現：發現結果異常就刪除約束 → 失敗原因：失去最佳化資訊卻沒有修復資料源 → 修正方法：凍結發布、定位根因、恢復可信版本。
追問及應對
維度表當天出現重複主鍵，已發布的查詢怎麼辦？
先暫停依賴約束的最佳化查詢，切換到顯式去重或可信快照，標記受影響批次並回溯結果。修復維度表後重新跑對帳，再恢復約束宣告。
外鍵允許 NULL 時，為什麼改寫要加非空過濾？
NULL 表示事實列沒有客戶匹配；內連接會丟掉該列，而原查詢也只保留有匹配的列，所以改寫必須保留 WHERE sales_customer IS NOT NULL，否則會改變結果。
如何證明最佳化器的 Join 消除沒有改變結果？
在代表性分割區並行執行原查詢與改寫查詢，比較筆數、主鍵集合與聚合結果；同時記錄約束檢查版本。只有資料品質檢查和結果對帳都通過，才把最佳化查詢推廣到全量。
什麼時候寧願不用約束最佳化？
約束來自延遲高或無法稽核的複製源、回填頻繁且沒有批次閘門時，先不用依賴宣告的改寫。多掃描一次但結果可信，比在錯誤中繼資料上追求省成本更重要。
BigQuery 能否用約束取代跨表交易？
不能。約束不執行寫入一致性，也不提供跨表原子提交；需要交易語義時應在上游系統或載入編排中實作，並把最終驗證結果傳遞到倉庫。
參考資料
- Google Cloud Documentation：BigQuery primary and foreign keys。
- Google Cloud Blog：Join Optimizations with BigQuery Primary and Foreign Keys。