ETL 資料擷取、轉換、載入系列（四）：資料轉檔後驗證
在前一篇文章中，我們提到把整個轉檔流程，分為多個步驟。除了讓轉檔的流程分工明確，同時也可以針對每個步驟作反向驗證，也就是將該步驟的資料與前一步驟的資料作比對，確認轉換前與轉換後，兩者應該相同。
這樣的做法可以減少資料經過多重邏輯處理後，無從比對的困擾。例如：在轉檔流程中原本浮點數，轉換時忘了加小數，輸出變成整數，但資料庫有開小數格式，所以在轉換過程中，可能不會出現錯誤，但小數點以下的資料已經不見了，如果透過反向資料的比對，就會發現兩個值不一樣。又如字串分割，將字串 A 分解為字串 B 與 C，驗證時可將 B＋C 與 A 作比對，確認資料是相同的，分割的位置無誤。
兩種資料驗證方式：資料比對、值域分析
在資料驗證過程中，我們可以利用以下兩個方法來進行。
- 資料比對：比對轉檔前與轉檔後的資料是否一致。
- 值域分析：確認資料的內容，是否符合使用者的需求。
透過各步驟的反向資料比對及值域分析，就可以組成一個完整的轉檔後資料驗證流程。資料轉檔完成後，再進行資料驗證流程，可以確保轉換的內容正確無誤。
一、資料比對
轉檔流程中，會分成多個步驟，資料比對的方法是將每個步驟的輸出資料，經過反向邏輯轉換後再與前一個步驟的輸出作比較，兩者需完全相同。除了內容比對外，也需要進行筆數的驗證。
在流程中，除了第一步的資料來源與最後一步的輸出，可能為不同的媒介或資料庫外，其餘的步驟都是利用同一個中介資料庫進行，基於效能及方便性考量，比對的方式盡量簡化。依照處理步驟與資料屬性，可分為以下幾個方法：
- 若為同質資料庫且可以直接連結（同一資料庫或允許使用 DB Link），則可以直接透過 SQL（Structured Query Language，結構化查詢語言）指令，將資料內容作反向轉換後，直接以 SQL 的差異比較指令（Oracle 的 MINUS 或 SQL Server 的 EXCEPT 或 INTERSECT）比對。若比對結果有差異，則可修改前述的 SQL 語法，加上有差異的鍵值，並將差異比較指令改為聯集（UNION ALL），同時顯示來源與輸出的資料內容，即可找出資料不同的欄位，再探討差異的原因。
- 若為異質資料庫或資料源無法直接透過資料庫間連結（DB link），則可透過逐筆讀取該步驟的輸出資料，再以鍵值讀取來源或目的資料檔，並比對所有欄位內容應該相同。兩個資料庫（同質或異質皆可）資料表比對在 ETL（Extract-Transform-Load，提取轉換載入）工具 Pentaho Data Integration 的 Kettle 中，有很好的元件可以使用，可以將來源資料表與中繼資料表分別排序，再透過合併檢查的元件比對，即可產出差異資料。
若為異質媒介，比對的原則是轉換為來源端或輸出端相同的媒介，再作比對，例如：資料源為 csv 檔，第一個步驟轉檔時將其寫入中繼資料表，驗證時，要先由中繼資料表，轉出 csv，再透過檔案比較工具，比對兩個檔案應該要完全一致。若輸出為欄位固定位置的文字檔，則要讀取文字檔，拆解出欄位並將資料寫入驗證資料表，再將驗證資料表與轉出文字檔時的來源資料表作比對，兩者應該完全相同，比對的方法可比照同質資料庫，以 SQL 比對。
若經過上述的方法比對資料內容與筆數，若兩者皆相同，則我們可以大致認為資料轉換過程應該沒有問題。原則上轉檔與驗證程式，應該由不同的開發人員撰寫，可同時驗證對規格的理解是否相同，若比對的結果不同時，則需要討論是轉檔或驗證的邏輯錯誤。
二、值域分析
透過資料比對只能驗證資料轉換前後的一致性，但並不能確保內容的正確性，例如：有個資料要做代碼轉換，要將 A、B、C 轉換為 1、2、3，但程式寫錯了，變成甲、乙、丙。透過資料比對的方式，將甲、乙、丙先轉換為 A、B、C，再與原始資料比對，兩者比較的結果，可能會相同，但其內容是錯的，這部分只能透過對資料內容有敏感性的使用者，才會比較容易發現，因此我們可以針對轉換後最終結果作資料內容的分析，將這些數據提供給使用者，讓他們做資料內容的檢視。
在我們做 ETL 資料轉換的過程中，最終結果的資料輸出到目的端前，都會先寫入對應的資料表，因此資料的分析可透過 SQL 指令做分群與統計。
資料分析的方法，大致分為以下幾種：
- 資料總筆數：計算資料筆數，做為比對的基礎。
- 最大值、最小值、平均值：數值欄位可以透過數值分析來判斷資料的合理性。
- 有資料、沒資料的空值：若有些欄位並未使用，但卻出現資料，就可以判斷欄位是否有搬錯，另外從有無資料的筆數也可以判斷資料的合理性。
- 各種值的筆數：以 SQL 的 COUNT 配合 GROUP BY 可以統計每個欄位各種資料值的筆數，由各種資料值的筆數，可以判斷資料內容與筆數分佈是否合理。另外若有些資料是可以有預設值，也可以透過分析將空值改為預設值，例如資料有 Y、N、空白，若空白等於 N，亦可透過轉檔程序修正資料。
- 唯一值欄位筆數（COUNT DISTINCT）：某些欄位雖未設為鍵值，但不能重複，將不重複數與資料總筆數比對，可以確認資料是否真的沒有重複。
上述資料分析的方式，每個欄位可視需要應用單一或多項分析方法，讓使用者可以更容易判斷資料內容的正確性。
總結
ETL 資料轉換的筆數常常高達數千萬筆以上，無法以人工的方式來檢視，資料轉檔後的驗證，是為了資料可以完整且無誤的移轉。透過資料的比對，可以確保資料轉換前後的一致，經過資料分析再由該領域的專家（使用者）進行資料內容的檢視，更可確保內容的正確性。不同的資料屬性，可以驗證的方式也會有所差異，本文主要是提供一種資料驗證的方向，作為轉檔驗證的參考，各位讀者可以依照自己的需求擴增驗證的方法。
