資料工程面試：PostgreSQL 18 的 MERGE 為什麼會因重複來源列失敗？

題幹與適用場景

每日客戶快照表 stagingcustomer 透過 MERGE 同步到 customerdim。某批次中同一個 customerid 出現兩列：一列來自 CRM，一列來自人工修訂。目標表沒有重複鍵，語句卻拋出 cardinality violation。請說明 PostgreSQL 18 如何產生候選變更列、為什麼同一目標列不能被多個來源列再次修改，以及如何用 RETURNING mergeaction() 產出可稽核結果。

面試官考察點

• 能否區分來源表重複、目標表重複和 ON 條件過寬三種問題。
• 能否說明 MERGE 對每個候選變更列只執行第一個符合條件的 WHEN 分支。
• 能否在去重、拒絕批次和保留最新修訂之間做出可解釋選擇。
• 能否把 RETURNING 結果用於稽核，而不是把它當成交易提交日誌。
• 能否處理並發、重跑、權限與資料品質告警。

回答前需要釐清的問題

• customer_id 是業務唯一鍵，還是必須和租戶、有效日期組成複合鍵？
• 兩筆來源記錄是否有可信版本號、事件時間或修訂優先級？答案決定去重規則。
• 失敗批次要全量拒絕，還是允許先寫入已確定的列？這會改變交易與重放策略。
• 稽核需要記錄舊值、新值、來源欄位和執行動作，還是只有行數統計？
• 來源資料可能跨批次遲到嗎？目標是否允許軟刪除？

30 秒回答框架

「我先確認 ON 條件能讓每個目標鍵最多匹配一筆來源列。PostgreSQL MERGE 會先產生候選變更列，再依 WHEN 順序對每列執行一個動作；同一目標列被多筆來源列命中會觸發基數錯誤，交易整體失敗。修復時我會在來源端以版本或事件時間做確定性去重，無法判定就拒絕批次。RETURNING merge_action() 記錄插入、更新和刪除結果，另外保留批次狀態、唯一限制與重跑鍵。」

分步驟深入解答

先證明匹配基數

先用與 ON 條件完全相同的鍵做品質查詢，找出一個目標鍵對應多筆來源列的情況。不要只對來源表做 DISTINCT，因為不同屬性的兩列即使鍵相同也可能代表衝突事實。若業務鍵是 (tenantid, customerid)，MERGE 和品質查詢都必須使用兩欄。

讓去重規則具確定性

優先使用版本號；沒有版本號時使用事件時間、可靠來源等級和穩定的 tie-breaker。視窗函數可以選出唯一勝者：

WITH ranked AS (
  SELECT s.*, row_number() OVER (
    PARTITION BY tenant_id, customer_id
    ORDER BY version DESC, event_at DESC, source_priority DESC, ingest_id DESC
  ) AS rn
  FROM staging_customer AS s
)
SELECT * FROM ranked WHERE rn = 1;

如果兩個來源無法證明誰較新，應把衝突寫入隔離表並拒絕該批次，不要依賴資料庫任意挑一列。

設計 WHEN 與稽核輸出

WHEN 條件依書寫順序判斷，第一個為真的分支會執行。先寫保護性條件，例如版本較高才更新；再處理 NOT MATCHED 插入和必要的 NOT MATCHED BY SOURCE 清理。PostgreSQL 18 的 RETURNING 可以回傳來源欄位、目標舊值、目標新值與 merge_action()，但它只代表本語句實際改變的列，不取代批次控制表。

處理交易與並發

把單批 MERGE、稽核落表與批次狀態更新放在同一交易。為輸入批次設定冪等鍵，重跑時先識別已成功批次。並發執行時遵循資料庫隔離規則；來源查詢應保證每個目標列最多一個候選，避免把重複來源留給執行期才發現。

高品質示範回答

「這個錯誤不是目標表有重複鍵就能解釋；關鍵是 ON 條件把同一目標列連到多筆來源列。我會先用相同的租戶和客戶鍵檢查來源基數，再按版本、事件時間、來源優先級和攝入 ID 做確定性排序。無法判定的衝突進入隔離表並讓批次失敗。MERGE 的 WHEN 分支按順序只執行一個動作，我會讓高版本保護條件排在前面。PostgreSQL 18 的 RETURNING merge_action() 輸出每列的 insert、update 或 delete，以及舊新值；稽核表和批次控制表則放在同一交易裡，讓重跑、告警與回放有依據。」

常見錯誤

• 錯誤表現： 對來源表直接 DISTINCT → 失敗原因： 不同事實可能被錯誤合併 → 修正方法： 用版本和業務優先級定義唯一勝者，衝突進隔離區。
• 錯誤表現： 以為 MERGE 會隨機選一筆來源列 → 失敗原因： 同一目標列的多重修改會觸發基數錯誤 → 修正方法： 在來源端證明一對一匹配。
• 錯誤表現： 用 RETURNING 行數當批次成功標記 → 失敗原因： 沒有記錄零變更、失敗和重跑狀態 → 修正方法： 使用獨立批次控制表和交易狀態。
• 錯誤表現： 只測試單執行緒 → 失敗原因： 並發隔離、遲到資料和重放未被驗證 → 修正方法： 做並發、重跑和遲到批次測試。

追問及應對

如果同一鍵的一筆來源列是刪除標記，怎麼辦？

先把刪除與更新納入同一版本排序，只有最高版本勝出。若刪除沒有可比較版本，就隔離衝突，不讓一次批次隱式決定客戶狀態。

RETURNING 能記錄沒有匹配到的來源列嗎？

它回傳實際執行 INSERT、UPDATE 或 DELETE 的變更結果，不能取代來源端的「未匹配」品質報告。另跑反連接統計，或把候選與動作寫入預審表，再提交 MERGE。

為什麼不直接用 INSERT ... ON CONFLICT？

若需求只有依唯一鍵插入或更新，ON CONFLICT 可能更簡單；需要依來源匹配、刪除未出現列或多分支條件時才考慮 MERGE。兩者的並發和權限語義不能混用假設。

目標表有觸發器時要額外檢查什麼？

確認觸發器不會改變匹配鍵，或讓同一目標列在語句內再次成為候選。稽核應區分 MERGE 的動作與觸發器副作用，並在整合環境驗證回滾。

參考資料

• PostgreSQL Documentation 18：MERGE
• PostgreSQL Documentation 18：Merge Support Functions
• Greg Low：SQL Interview: 35 T-SQL Merge Statement Clauses
• Simplyblock：PostgreSQL MERGE tutorial

面試作答要點

先證明 ON 條件的一對一基數，再解釋 WHEN 順序和 cardinality violation，最後給出確定性去重、交易控制、RETURNING merge_action() 稽核與重跑策略。

一句話總結

高品質的 MERGE 答案要把來源資料基數、分支順序和稽核輸出連成一條可重放的資料契約。