把 Integrity Constraint 寫成可執行的資料品質規則

Integrity constraint 描述 database 允許存在的 state。做 cleaning 時，反過來寫通常更實用：讓 query 回傳所有違規 row 或 tuple combination；正確結果應該是 empty set。

從文字規則到 violation query

規則：每個 resolved city 都必須記錄取得方式。

SELECT id, canonical_city, city_source
FROM menu_clean
WHERE canonical_city IS NOT NULL
  AND city_source IS NULL;

規則：每筆 paper-author relationship 都必須指向存在的 paper。

SELECT pa.paper_id
FROM paper_author AS pa
LEFT JOIN paper AS p ON p.paper_id = pa.paper_id
WHERE p.paper_id IS NULL;

即使最終 database 會用 NOT NULL 或 FOREIGN KEY 強制，migration 期間仍需要這些 query。Constraint 阻止新的 bad state；violation query 衡量既有問題。

白話來說： Rule 描述乾淨房間；violation query 指出每一件放錯位置的物品。

四種常見規則形狀

有些規則適合 declarative database constraint；依賴歷史 context、external vocabulary 或 aggregate evidence 的規則則適合 validation job。

Missing 不自動等於 invalid

CHECK (year BETWEEN 1850 AND 2012)

SQL 的 CHECK 在 expression 為 TRUE 或 UNKNOWN 時都可能通過，因此 nullable year 可以過關。若 missing 也不允許，兩個條件都要寫：

year INTEGER NOT NULL CHECK (year BETWEEN 1850 AND 2012)

品質報告也應分開：有值但不可能的 date 是 validity violation；沒有 date 是 coverage gap。混在一起會誇大一項 metric，又掩蓋另一項。

誠實做 before／after

每條 rule 應報告：

• 適用 population；
• violating row 與 distinct entity 數；
• null／unknown 的處理；
• raw data 原本可檢查，或只是新 schema 的 post-condition；
• example 與 repair disposition。

不要把 not applicable → 0 宣稱成 improvement。Raw schema 沒有 derived provenance column，相關 constraint 可以是有價值的 post-condition，但不是歷史改善。

Constraint 順序會影響 repair

一個 change 可能揭露或解決另一個 violation。先 canonicalize ID 再檢查 FK 可能接回 row；還沒選 survivor 就 deduplicate parent，可能製造 orphan。Pipeline 可依序：

1. parse／normalize representation；
2. 建立 identity 與 canonical value；
3. repair reference；
4. 檢查 cross-row 與 business constraint；
5. enforce stable post-condition。

複習卡

• Clean data 上的 violation query 應回傳零 row。
• Stable local invariant 用 database constraint；contextual rule 用 validation job。
• Missing 與 invalid 是不同品質狀態。
• 報告 rule population、null semantics 與 entity count。
• 新 post-condition 不是 historical improvement。
• Constraint 會互相影響，所以 repair order 很重要。