ETL（抽取、轉換、載入）是 BI 專案的「心臟手術」，佔據專案 60-70% 的工時，直接決定數據倉庫的成敗。本文從架構選型、抽取策略、清洗轉換設計、載入優化四個維度，構建企業級 ETL 設計的標準化框架與最佳實踐指南。
| 架構模式 | 核心特點 | 優勢 | 劣勢 | 適用場景 |
|---|---|---|---|---|
| 純 ETL 工具 (SSIS/Informatica/OWB) | 可視化拖拽、內建連接器、封裝部署 | 開發快、維護易、非技術人員可讀、版本控管完善 | 複雜邏輯表達受限、效能調優黑盒、授權成本高 | 標準化數據整合、異構源同步、團隊技術棧統一 |
| 純 SQL/存儲過程 | Set-based 操作、原生效能、完全控制 | 效能極致、邏輯靈活、零授權成本、版本控管原生 | 開發慢、需高階 SQL 能力、除錯困難、文檔缺失 | 大數據量轉換、複雜業務邏輯、核心數據倉庫層 |
| 混合模式 (推薦) 工具編排 + SQL 核心邏輯 | 工具負責流程控制/錯誤處理/監控，SQL 負責核心轉換 | 平衡開發效率與執行效能、發揮雙方優勢 | 架構複雜、需明確分層規範、團隊技能要求較高 | 企業級 BI 專案首選、核心數據域建設 |
| 抽取模式 | 適用條件 | 實現機制 | 優/缺點 |
|---|---|---|---|
| 全量抽取 | 初始化、小表(＜10萬行)、無可靠增量欄位 | SELECT * FROM source_table | 簡單可靠 / 資源消耗大、耗時長 |
| 增量抽取 (時間戳) | 源表有 last_modified_dt、create_dt 且有索引 | WHERE last_modified_dt > @last_extract_time | 高效 / 依賴應用系統正確維護時間戳 |
| 增量抽取 (CDC/觸發器) | 源系統支援 CDC (SQL Server 2008+)、或可加觸發器 | 讀取 CDC 表 / 觸發器寫入變更表 | 近即時、無侵入 / 來源系統有額外負載 |
| 增量抽取 (日誌解析) | 極高即時性要求、源系統不可修改 | 第三方工具解析交易日誌 | 零侵入、即時 / 成本高、實現複雜 |
source_system_cd、batch_id、extract_dt、row_hash (MD5/SHA256 全行雜湊)NVARCHAR(MAX) 或對應最大長度儲存，轉換層再做類型收斂ETL_Error_Log 表，記錄錯誤碼、錯誤行、原始內容此層佔 ETL 總工時 2/3，核心是將「業務規則」轉化為「可執行、可測試、可維護」的技術邏輯。
Stage 1: 標準化清洗 (Standardization)
- 去除前後空格、非可見字符、統一大小寫
- 代碼值映射: 'M'/'F' → 'Male'/'Female', 國碼標準化
- 日期格式統一: '2024/01/01' '01-01-2024' → DATE '2024-01-01'
Stage 2: 業務規則驗證 (Business Rule Validation)
- 必填欄位非空、外鍵引用存在性、數值範圍合法性
- 跨欄位邏輯: 結帳日期 ≥ 開單日期、銷售金額 = 單價 × 數量
- 違規資料標記: 加載 _is_valid='N', _error_code='BR001', _error_desc='...'
Stage 3: 維度鍵替換 (Surrogate Key Lookup)
- 自然鍵 → 代理鍵: Customer_NK → Customer_SK
- 緩存維度表在記憶體 (SSIS Lookup Transform / SQL JOIN)
- 未匹配處理: 導向「未知成員」或拋入錯誤表
Stage 4: 事實表度量計算 (Fact Measure Calculation)
- 加法/半加法/非加法度量正確聚合
- 衍生指標: 毛利率 = (收入-成本)/收入、同比/環比計算
- 歷史快照處理: SCD Type 1/2/3 維度變更追蹤
鐵律：能用單條 SQL 解決的，絕不寫迴圈/游標/逐行處理。
反模式：游標迴圈 UPDATE 百萬行 → 耗時 4 小時
正模式：單條 MERGE / INSERT...SELECT / 批次 UPDATE → 耗時 3 分鐘
| 目標表類型 | 載入策略 | 關鍵參數 | 鎖控制 |
|---|---|---|---|
| 維度表 (小/中) | TRUNCATE + INSERT / MERGE UPSERT | 批次 5000 行、最小日誌記錄 | TABLOCK 提升大量插入速度 |
| 事實表 (大/巨大) | 分區表 + Partition Switch / BULK INSERT | 按日期分區、最小日誌、延遲索引建立 | 分區級鎖、線上索引重建 |
| 歷史快照表 | INSERT ONLY (不更新) | 批次提交、檢查點控制 | 無鎖衝突 |
ROLLBACK TRANSACTION + 通知告警架構師總結：ETL 不是「寫腳本搬數據」，而是「建立可信賴的數據供應鏈」。投資於標準化框架、自動化測試、元數據驅動，將讓 ETL 從專案瓶頸轉化為企業數據資產的核心競爭力。
