前面幾期文章，我們介紹了 ETL（Extract-Transform-Load，提取轉換載入）的概念、轉檔步驟、中繼資料庫設計、資料驗證，最後也介紹了兩套 ETL 工具，今天我們就來談談實務經驗的應用案例與流程設計需要注意的地方。
本期介紹的案例是銀行反洗錢（Anti-Money Laundering，AML）系統的前置作業，將銀行核心系統的交易資料透過 ETL 的程序轉換為反洗錢系統所需要的介面資料檔。
反洗錢系統 ETL 應用案例一：中小型銀行
本案例是一家規模較小的銀行，因為系統比較新，所有的資料都已經彙整到 ODS（Operational Data Store，操作性數據庫），所以只要從單一資料庫擷取資料就可以，使用的 ETL 工具是 Microsoft SQL Server Integration Services（SSIS）。
因為核心 ODS 彙整了所有系統的資料，更新的頻率非我們所能控制。因此在本專案中，規劃一個反洗錢專用的 ODS，由核心 ODS 取得所有必要的資料表與欄位，以獨立的 ETL 程序轉檔，將反洗錢系統所需要的資料由核心 ODS 資料表與欄位，以 1 對 1 的方式對應至反洗錢 ODS 資料庫。這就是我們前面談過的轉檔步驟的第一步，將資料源完整複製到 ETL 中繼資料表。
因為本案的中繼資料庫是用 SQL Server，因此在資料彙整上是以三個資料表函數（Function table）分別來整理客戶、帳戶、交易所需要的欄位。資料彙整的邏輯就寫在資料表函數中，再以 SSIS 資料輸入元件，先將其寫入暫存資料表（Staging table），再進行資料檢核，檢核通過的寫入 Final 資料表，檢核失敗的寫入退件資料表，最後再將 Final 資料表寫成反洗錢系統所需的資料格式。
透過此案例我們可以發現 ETL 執行步驟中，第一步與最後一步，分別對應至資料源與目標資料，中間的步驟與資料表，可以依資料的屬性增減。以此例來說中間有兩個步驟，以資料表函數與暫存資料表來達成。
ETL 作業流程：
S1：由資料源（核心ODS）取得所需的資料表，寫入對應資料表（反洗錢_ODS）
S2 :由（反洗錢_ODS）透過資料表函數整理出所需的欄位
S3：由資料表函數讀取資料檢核後，寫入 Final 資料表
S4：讀取 Final 資料表，寫入反洗錢介面檔
反洗錢系統 ETL 應用案例二：大型銀行
此案例為一家大型銀行，資料來源複雜，客戶與帳戶都有多個資料來源，同一個客戶資料，可能有多個資料源且內容可能不一樣。因為需定義轉檔的優先順序，有超過 10 種類型的交易型態（現金、轉帳、信用卡…），每種類型的交易也有多個資料來源，資料源雖統一為固定格式的文字檔，但中文編碼並不相同，且含有俗稱難字的罕見中文字，因此實際的資料長度也會因為資料內容而有所差異，使用的 ETL 工具為 Pentaho Data Integration（PDI）。
在此案例中，我們採用完整的資料處理流程規劃。主要步驟如下：
S0：取得資料源的檔案
此案例客戶有統一的檔案交換機制，固定將檔案存放於 FTP（File Transfer Protocol，檔案傳輸協定）伺服器，因此首先由 FTP Server 取得前端系統提供的檔案。
S1：將資料源完整複製到 ETL 中繼資料表
由文字檔的資料源，將其進行欄位分割，分別寫入對應的資料庫欄位。另外未分割欄位的原始資料內容，亦獨立存放一個欄位。
PDI 預設的讀取資料元件，無法直接處理固定位數文字檔中的罕見中文字，因此改用 PDI 自行定義元件的功能，以 java 來撰寫讀取功能。
S2：資料驗證，並回饋驗證結果
此步驟會逐筆進行資料驗證，有問題的資料標示錯誤註記與錯誤原因，最後再把錯誤的資料產生退回資料檔，回饋給來源系統，沒有問題的資料，則當作下一個步驟的輸入資料。
資料檢核的方式有兩種，批次檢核與逐筆檢核。若需參考其它資料表作內容檢核，例如幣別、國別、代碼類別，則可使用 SQL 指令做批次檢核，並更新錯誤註記與錯誤原因，最後再逐筆讀取資料，進行資料檢核。
S3：資料彙總，將關聯檔案彙整成各項資料主檔
S3 是以各系統 S2 檢核成功的檔案，相同鍵值依照優先順序規則，整理成單一筆資料。一個簡單的作法是將所有關聯的資料組成 View，透過排序來決定相同鍵值（如同一個客戶 ID）的優先順序，先整理出主要資料後，再以 ETL 流程處理其它欄位資料。
S4：產出目的端所需資料名稱與格式
S4 是讀取 S3 產出的資料，轉換成目的端的格式與名稱。中介檔 S3 與 S4 都是屬於匯總後的主檔，兩者的差別在於，S3 是以輸入端的格式與名稱為主，S4 則是以目地端的格式與名稱為主。以代碼轉換為例，如果代碼是屬於原系統的代碼表，則可在 S3 進行轉換，若屬於目的端所需要的代碼，則在 S4 轉換。
S5：依目的端所需的介面檔，分別輸出對應的資料檔
S5 原則上是最後一個資料庫形式的中介檔，格式與欄位都是對應至目的端。
S6：輸出介面檔或寫入目的端資料庫
S6 是依目的端的需要產出介面檔案，若目的端為資料庫，則由此步驟將資料寫入目的端資料庫。
S7：檔案傳送至檔案傳送平台或目的端
此案例的輸出是固定格式的文字檔，因此配合客戶既有檔案交換派送機制，透過 FTP 將產生的反洗錢介面檔，傳送到 FTP 伺服器。為了便於後續資料問題的釐清，在此步驟中我們會將輸出的介面檔壓縮備份，若將來有問題時可以釐清產出的內容。
ETL 流程整合，提升作業效能
此系統來源檔案很多，各系統送來的時間並不一致，在流程設計上各系統檔案可以分別處裡，以節省時間。S0、S1、S2 都是以單一系統的資料源處理，可以每個檔案單獨作業，因此在這個步驟我們設計一個控制檔，標示哪些系統檔案已經處理完成，哪些系統檔案還沒送進來，如果通過檢核，這個檔案就不用再處理，如果沒有通過檢核，則通知來源系統重送。
在流程設計上可將每個系統或檔案的 S0、S1、S2，組成一個收檔、轉檔、驗證一氣呵成的流程 S0-S2，並且記錄每個步驟的處理時間與狀態，再將所有系統的 S0-S2 組成一個全部系統的 S0-S2_ALL，透過排程定時執行，同時在每個檔案執行前檢查是否已經處理完成，就可以達到分批處理的目的。
S3 以後的作業都是屬於彙整作業，將所有完成 S0、S1、S2 處理步驟的檔案彙整一起執行，S3-S7 可以整合成一個流程，若在 S3 執行時仍有系統未完成送檔，則列為異常處理。
除了當天可以控制已經處理過檔案不再重複處理外，也可以在每個檔案前面加一個執行日期的判斷，來決定該檔案是否今天要執行，這樣就可以設定檔案的執行日期規則，例如每星期幾、每月幾日，或平日執行，假日不執行…。
日期在銀行系統中是很重要的資料，在開發過程中需使用不同的日期測試。因此不要在程式中直接讀取作業系統日期，而是應該用參數方式傳遞日期，以方便模擬不同的作業日期。
總結
以分步驟與多個暫存資料檔可以讓處理流程更明確，容易驗證轉檔前後的資料正確性。在流程設計上須注意以下幾點：
- 流程中除了以控制檔記錄每個檔案執行狀態與筆數外，亦可增加一個步驟的記錄檔，詳細記錄每個步驟的執行時間與狀態，可作為效能調校的依據。
- 每個步驟前加一個執行日期控制，可控制檔案排程執行時間。
- 第一個步驟與最後一個步驟，除了對應至來源端與目的端的資料外，同時也要盡可能保留其它系統送過來的原始資料內容，作為未來釐清問題的依據。
銀行系統都有處理的時效，因此在流程設計上，可以單獨處理的步驟，應該要獨立出先執行，避免所有的作業同時執行，互相等待，耽誤作業時效。
延伸閱讀：
