ZrobDev/data-cleaning

資料清理與核對

預設工具：DuckDB 2.0 SQL 或 Polars 2.x 惰性 API（scan_csv、scan_parquet）用於本地檔案。僅在函式庫強制要求時才使用 pandas。透過 uv 執行 Python，以 ruff 進行語法檢查。在依賴 Polars 2.0 或 DuckDB 2.0 的任何 API 細節之前，請先根據目前文件進行驗證（參閱 references/landscape-2026-11.md）。

絕不靜默修正

每一次強制轉換、捨棄、預設值或合併都必須被計數並回報。一個清理步驟是 (rows_in, rows_out, rows_changed, reason)。強制轉換失敗會變成 NULL 並計數，絕不捨棄列，也絕不猜測值。保持原始輸入資料原封不動；將清理後的輸出寫入新路徑。如果某個步驟的計數出乎你的意料，請停止並回報，而不是繼續執行。

程序

1. 剖析（唯讀）。2. 決定金鑰、粒度與型別。3. 以多個小型計數步驟的管線進行清理。4. 若有兩個來源則進行核對。5. 產出例外報告並附上包含所有計數的執行摘要。

1. 處理前先剖析

首先將每個欄位都讀取為文字（DuckDB 中為 all_varchar=true，Polars 中為 infer_schema=False），因為對樣本進行推論會悄悄將 00123 轉成 123，並將混合欄位轉為第一列的型別。 接著對每個欄位計算：列數、null/空字串比率、唯一值數量、最小/最大值（以文字和解析後形式）、最短/最長長度，以及符合某種型別模式（整數、小數、ISO 日期、其他）的值所佔比例。 DuckDB 的 SUMMARIZE 可以一次完成大部分工作。

記錄清理前的資訊：總列數、唯一候選金鑰數、每個金鑰的列數（最大值），以及找到的標題（結尾空白、BOM、重複名稱）。候選金鑰只有在正規化後唯一值數量等於總列數時才算是金鑰；請測試，不要假設。

檢查是否有：完全重複的列、檔案中重複的標題、資料中的頁尾/總計列、保留值（"N/A"、"-"、"0000-00-00"、9999-12-31）、混合編碼，以及其實是識別碼的數值欄位。

2. 型別與解析

• ID、郵遞區號、帳號和電話號碼一律視為字串。僅在已知寬度時才將其儲存為固定寬度的零填充規則（使用 lpad 補齊至已知寬度）；否則保持原樣。
• 日期：對每個欄位使用明確格式進行解析（try_strptime / str.to_date(format, strict=False)）。絕不讓引擎猜測日/月順序。沒有記錄順序的模糊值（03/04/2026）應視為例外，而不是猜測。明確決定時區處理方式。
• 金額：解析為整數小數單位或 DECIMAL(p,s)，絕不使用浮點數。根據地區規則移除貨幣符號和千分位分隔符號：1.234,56 與 1,234.56 代表不同地區，因此請根據來源決定，並斷言每個值都符合所選模式。括號和結尾負號代表負數。記錄每筆金額的貨幣單位；不要跨貨幣相加。
• 布林值和列舉：從明確的對應表中映射；未映射的值則歸入例外。
• 每次強制轉換後，計算「強制轉換前的非空值」減去「強制轉換後的非空值」。該數字即為強制轉換失敗計數，並應包含在報告中。

3. 正規化

套用於衍生的匹配金鑰，而不是原始欄位，以便保留原始值： Unicode NFKC、去除前後空白、壓縮內部空白、轉為小寫、移除零寬度及非斷行空格。 NFKC 是有損的（連字、全形、上標），這正是匹配所需要的，但顯示時則不恰當。 對於識別碼，也應移除分隔符號（-、/、空格），並明確決定 O/0 和 I/1 的處理方式。 DuckDB 核心 SQL 不支援 NFKC；請在 Polars/Python 中執行或註冊 UDF（請確認當前支援情況）。

4. 去重

• 完全重複：捨棄並計數；保留存活列的來源列號。
• 依金鑰重複：首先判斷它們是真正的重複還是不同的事件（相同的發票號碼但金額不同是衝突，而不是重複）。
• 存活列的選擇必須是確定性的，並明確說明： ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC NULLS LAST, completeness DESC, source_row DESC) = 1（DuckDB QUALIFY）。 務必以唯一的中斷器（如來源列號）作為 ORDER BY 的最後一項，因為否則平手時會根據任意掃描順序決定。 在 Polars 中使用 sort(...) 然後 unique(subset, keep="first", maintain_order=True)。
• 模糊去重只是一個候選產生器，絕不能自動合併：以廉價金鑰（正規化郵遞區號、前 3 個字母、年份）進行分組，在組內進行評分，僅在閾值很高且有一個確切的次要欄位同意時才自動接受，中間帶則送交人工清單。回報閾值和計數。

5. 核對稽核

來源真理 = A，衍生結果 = B（例如系統匯出 vs OCR 文件）。在檢查是萃取還是金鑰錯誤之前，絕不要說"B 錯了"。

1. 首先檢查控制總計。 A 和 B 的列數以及每個金額/數量欄位的總和，加上唯一金鑰計數。不匹配告訴你是否會缺少列、多出列或金額偏移。總計相符但列數不同表示有錯誤互相抵消；繼續進行。
2. 金鑰匹配。 在兩側建立相同的正規化金鑰（第 3 節）。首先進行精確匹配，然後按照嚴格程度遞減的明確輪次進行（例如僅金鑰，然後金鑰加上日期範圍），並為每個匹配標記產生該匹配的輪次。絕不要未標記就混合輪次。
3. 雙向反連接。 A 不在 B（衍生結果中缺少）和 B 不在 A（衍生結果中意外的，通常是 OCR 金鑰錯字或重複）。兩者都回報；只看單向會隱藏一半的錯誤。在斷定某列缺失之前，先檢查其金鑰是否與任一端重複的資料衝突（多對多連接會膨脹總計）。
4. 對配對進行欄位比較。 在相同的正規化後，逐欄位進行比較。 金額容忍度：以小數單位進行比較，abs(a - b) <= tol，其中 tol 是事先聲明的（帳簿為 0；僅當來源四捨五入方式不同時，每行才允許一個四捨五入容忍度，例如 1 個小數單位，且絕不對小金額使用百分比）。 日期：精確，或聲明的日期範圍。 文字：正規化後的相等性，然後回報相似度分數，而不是通過/失敗。
5. 分類，而不只是比較差異。 每個例外都必須有一個原因代碼：MISSING_IN_B、EXTRA_IN_B、DUP_KEY_A、DUP_KEY_B、AMOUNT_MISMATCH、FIELD_MISMATCH、UNPARSEABLE_A、UNPARSEABLE_B、AMBIGUOUS_MATCH。無法解析的列是例外，不能悄悄從總計中排除。
6. 閉環驗證。 最終恆等式必須成立：|A| = 匹配 + missing_in_B + dup_excess_A + unparseable_A（以及對應的 B 恆等式）。如果沒有平衡，則稽核尚未完成。

用於輪次、反連接、容忍度和平衡檢查的 SQL 模式可在 references/reconciliation-recipes.md 中找到。

例外報告

每個例外一行，無需工具即可讀取： reason_code、key、A 來源檔案和列號、B 來源檔案和列號（掃描文件則加上頁碼）、field、a_value、b_value、delta、suggested_action。 按金額影響降序排序，然後按原因排序。 在開頭放置執行摘要：輸入檔案及其列數與校驗和、每個清理步驟及其計數、雙方的控制總計、平衡恆等式、所使用的容忍度。 為機器寫 CSV，為人類寫簡短的 markdown 或 xlsx 檢視；不要將數千行傾倒至對話中，而是按原因彙總計數並顯示前幾筆。

防護措施

• 使用斷言，而不是目視檢查：連接後的列數、去重後的金鑰唯一性、沒有用於金額的浮點數欄位。
• 對非唯一金鑰進行左連接會增加列數；在每次連接前後檢查列數。
• 不要覆寫 A。對來源真理的修正是一個人工決策，應列在報告中。
• 資料庫的機密從 /run/secrets/<name> 讀取，而不是環境變數或命令列。
• 聲明式檢查（soda-core、great_expectations）值得在規則穩定後加入；但首先應優先完成上述的剖析和計數。

從實際使用中累積的經驗教訓會收錄在 references/gotchas.md 中。