ETL 資料擷取、轉換、載入系列（三）：轉檔步驟及中繼資料表規劃
上一篇文章我們介紹了 ETL（Extract-Transform-Load，提取轉換載入）資料轉換七步驟以及轉換注意事項。這次我們會針對轉檔步驟及中繼資料表的規劃進行解說。
ETL 資料轉換流程設計原則
進行資料轉換時，可分為三個基本步驟。這三個步驟的基本原則，我們在前面的文章中已經有詳細的說明，此處就不再贅述，只配合步驟規劃作簡單的說明。
- 資料擷取（Extract）：由資料來源處擷取所需之數據資料移轉到中繼資料庫，此步驟最重要的原則是在移轉過程中，只作媒體轉換，盡量不作內容或格式轉換，以方便後續作資料的驗證。
- 資料轉換（Transform）：針對所擷取出之數據資料，依照商業邏輯的需求，將數據資料作適當的轉換， 例如資料型別、欄位的分割合併、資料的加工….等作業。
- 資料載入（Load）：最後將已作適當轉換過的數據資料載入到目的地，在載入目的前，亦須保留一份對應至目的檔案或資料庫的內容，同樣的亦是只有媒體不同，但格式與內容需與輸出的目的端相同。
配合前面提到的原則，為了方便資料與目的端或資料源做比對，轉檔流程的第一步與最後一步，盡可能的將資料原封不動地從資料源萃取出來寫入中繼資料庫，或將要寫入目的檔的資料先保留一份在中繼資料庫，中間過程再依需求進行逐步彙整與轉換。
ETL 流程架構設計
一個完整的 ETL 轉檔流程架構是由多個目的不同的步驟組成，除了檔案傳輸的步驟外，每個步驟都由執行的程序與中繼資料表組成，共分為以下幾個步驟：
S0：取得資料源的檔案
此步驟是由資料源取得原始資料，對資料的格式與形式盡量不做更動，只進行檔案位置的轉移，如 FTP 取檔，由 ftp Server 取得前端系統提供的檔案。此為非必要步驟，若檔案可由資料源主動提供或由資料庫直接讀取，則此步驟可省略。
S1：將資料源完整複製到 ETL 中繼資料表
此步驟是將要轉換的資料寫入中繼資料表（S1 資料表）。若資料來源為資料庫，S1 資料表的設計應盡量將資料型態與格式維持與資料源相同，資料表及欄位都是 1 對 1 對應。
若資料源為一般檔案，為避免資料寫入時發生錯誤，欄位格式可使用可變動長度的文字型態，欄位長度亦可採用統一的幾種格式，如 nvarchar（16）、nvarchar（64）、nvarchar（1000）。文字檔 S1 資料表的結構應包含欄位的分解、轉碼及以一個 RAW 或 BINARY 的欄位保留原始資料，此步驟需將來源端的檔案逐筆解析，並寫入 S1 資料表。
S2：資料驗證，並回饋驗證結果
S2 主要目的是做資料的驗證，並且逐筆標記檢核的結果。驗證合格的資料將在步驟 S3 做後續處理，驗證不合格的資料，將在此步驟退回來源端。
S2 資料表的格式與長度可與 S1 相同，另外加上驗證結果及錯誤原因欄。此步驟執行的結果，可使用 View，將資料分成 S2_OK、S2_NG 兩類的資料。S2_OK 提供給下個步驟 S3 使用，S2_NG 則用於產生錯誤回饋資料。
S3：資料彙總，將關聯檔案彙整成各項資料主檔
S3 主要目的是將 S2_OK 的資料表，整理成後續要使用各種的資料檔。若一筆資料有多個資料源，在此步驟將依據優先等級，整理出所需要的檔案。S3 的資料表型態與長度，須依據所需的資料設計，例如：目的端要產出一份最完整的客戶資料，資料源有多個資料源，每個資料源可提供部分欄位或相同的欄位但內容不同，在步驟 S2 之前都是與資料源 1 對 1 的轉換，在此步驟則彙整成一筆資料且對於相同的欄位，會依資料源的優先等級決定個別欄位採用的資料源。
此步驟的 S3 資料庫，盡量保留所有資料源的欄位，以利於後續的擴充，欄位名稱可使用資料源的名稱以方便作對照。
S4：產出目的端所需資料名稱與格式
此步驟將 S3 整理完的各項資料檔，轉換為目的端系統的資料格式，寫入 S4 資料檔。此步驟的內容是以輸出端所需的資料為主，因此會包含各種代碼轉換及格式轉換，欄位名稱亦以輸出端為主。
S5：依目的端所需的介面檔，分別輸出對應的資料檔
將 S4 產出的資料檔分別寫入各系統所需的格式與欄位，如反洗錢的客戶資料，可分別提供 KYC、AML 使用。因為其所需要的欄位並不相同，所以這個步驟會分為輸出對應的終端資料表（FN 資料表），此步驟產生之資料仍為中繼資料表，但與目的端的檔案相互對應，後續如果有問題要追查原因時，亦可由此步驟所產出的 FN 檔來確認輸出的內容。
S6：輸出介面檔或寫入目的端資料庫
將 FN 資料表轉為目的端所需的介面檔格式（如純文字檔、XML、JSON…）或直接寫入目的端的資料庫。
S7：檔案傳送至檔案傳送平台或目的端
此為選擇性步驟，若輸出結果為文字檔，且需將資料傳送至目的端指定的位置，則可增加檔案傳輸步驟。
總結
由上面所設計的流程步驟，可以對應至 ETL 三個基本步驟：
資料擷取（Extract）：
- S0：取得資料源檔案（Optional）
- S1：載入中繼資料庫
- S2：資料驗證
資料轉換（Transform）：
- S3：彙整成中繼資料主檔
- S4：轉換為目的端資料主檔
- S5：輸出為對應目的端的檔案
資料載入（Load）：
- S6：輸出為目的端所需格式或寫入目的端資料庫
- S7：傳送至目的端（Optional）
流程中每一個步驟（S1-S7）都需設計為獨立步驟，因此判斷是否為工作日以決定執行的時機、檔案名稱變數，也必須獨立判斷或設定。此種作法的優點為各檔案可以由 S0 直接執行到 S6，亦可所有檔案的 S0 都處理完，再執行 S1、S2，待所有需要的資料都取得後，再執行 S3 彙整。這種設計架構，可增加其組合的彈性。
| 步驟 | 作業說明 | 資料表說明 | 備註 |
|---|---|---|---|
| Step 0 | 由資料源透過檔案傳輸工具（如FTP）取得資料檔 | 若資料源為資料庫則可省略 | |
| Step 1 |
|
S1 資料表欄位型態與內容與來源檔相同 | |
| Step 2 | 資料驗證 | S2 資料表為 S1 的欄位加驗證結果及異常原因 | |
| Step 3 | 將驗證 OK 的資料彙整成資料主檔 | 彙整成 S3 資料主檔，欄位與型態以輸入資料為主 | 以欄位名稱以輸入資料為主彙整，原則上保留所有輸入欄位 |
| Step 4 | 將輸入主檔彙整成輸出主檔 | 彙整成 S4 資料主檔，欄位與型態以輸出的資料為主 | 以欄位名稱輸出資料為主彙整，原則上只輸出必要欄位 |
| Step 5 | 以輸出主檔，產生對應輸出欄位的資料表 | 彙整成 FN 資料檔 | FN 資料檔應對應每一個目的端的資料表或檔案 |
| Step 6 |
|
||
| Step 7 | 將資料檔傳送至目的端指定的位置 | 若輸出為資料檔且由 ETL 主動傳送才需要 |
資料的轉檔除了前述暫存的中繼資料表設計之外，還需要控制資料表，以記錄執行情況，必要時可以重新執行。中繼資料表大部分都是暫存的資料，轉檔前需先清除，若有需要保存執行的結果則可將資料傳入 ODS（Operational Data Store）資料庫中。
ETL 檔案轉換，多步驟設計 6 大優點
將檔案轉換的流程分為多個步驟，除了讓流程中每個步驟的分工明確，亦有下列優點：
- 同一檔案多系統提供，可分批收檔、驗證。配合排程系統可指定應用系統檔案分別排程，以節省執行時間。
- 執行過的系統或檔案，不重複執行，配合資料處理的註記亦可清除重送。
- 多系統提供相同資料，優先序及資料覆蓋邏輯處理明確。
- 資料驗證錯誤，後續退回及重送後消除作業。
- 單一系統檔案錯誤不影響整體作業進行。
- 由資料源取得的資料及傳送至目的端前的最終資料得以完整保留，避免後續資料處理的爭議。
資料轉檔的流程分步驟處理，除了上述優點外，另一個重要的目的是為了確保資料經過轉換後，還是能維持原來的內容。因此很重要的一點是輸出的資料必須驗證與輸入資料相同，資料驗證的方法我們將在下一篇文章中說明。
