將事務表記錄傳輸到數據倉庫的最佳 ETL 設計

我有 2 種類型的表來每天填充數據倉庫，查找表或配置表只有 100 條記錄，這很容易，我只需截斷並重新填充表。

但是對於有很多記錄的事務表，我通常會增加，即我每天執行 ETL 以添加昨天的記錄。

我有兩個我一直面臨的問題

1. 當工作因任何原因失敗時（我失去了 Days 交易）
2. 當出於任何原因作業執行兩次或我執行兩次時（我得到重複）

現在我正在嘗試設計一種方法來解決這兩個問題，並嘗試以這樣一種方式開發 ETL，以便在發生任何這些事件時它可以自動修復它。

我希望它檢查是否缺少日期並執行當天的 ETL，並檢查是否有重複並刪除它們。

以下是我認為 1. 我在過去 5 天採取的方法，ETL 每天執行，刪除最後 5 天並重新填充。2. 我檢查目標表是否缺少上個月的日期，然後查詢缺少日期的源。

請記住，源是生產環境中的一個巨大的表，我必須在向它請求時將我的查詢優化到最大。

謝謝

交易是否有審計時間戳？它必須是僅上升的（沒有遲到的事實。插入/更新審計時間戳對此有好處）

如果是這樣，您可以使用它來定義要提取的範圍。這是這類事情的常用技術：

• 對於每次提取，在開始時，確定要提取的範圍（我們稱之為最小時間戳 min_ts 和最大時間戳 max_ts）
• 在提取開始時，將 1 行放在一個單獨的表中（我們稱之為提取日誌並給它一個 PK），其中包含以下欄位：extraction_id = 唯一鍵、min_ts、max_ts、狀態 = ‘開始提取’。
• 使用 min_ts 和 max_ts 提取數據，可以在 1 go ( select * from where ts > min_ts and ts <= max_ts) 中提取數據，也可以在需要時分塊提取。
• 廣告成功提取結束，更新該行並將狀態設置為“已完成”

如何確定 min_ts 和 max_ts ？

• 您可以使用上次成功的 max_ts 從提取日誌中獲取 min_ts。select max(max_ts) from extraction_log where status = 'Finished OK'
• 您可以在提取開始時從源數據庫中獲取 max_ts 。select max(audit_ts) from source_table

這裡有替代方案。如果您將這些提取到臨時臨時表中（最佳實踐），您也可能需要太多時間，即過去 5 天，並且稍後在更新 ODS 中的條目時處理重複項。對於 max_ts，如果您絕對確定 dwh 和源之間的時鐘將保持同步（這是一個非常危險的假設 - 不推薦），您甚至可以使用 sysdate()

從技術上講，您可以用更少的努力擺脫困境。您實際上並不需要狀態或用於跟踪每個批次的 extract_log 表。但我發現擁有這樣的功能對以後的調試和故障排除有很大幫助。此外，如果您需要常式從載入中途失敗的條目中刪除條目，查找過去提取範圍的間隙等等，extraction_log 將有所幫助。您甚至可能希望將 extract_id 作為附加列包含在您的 ODS 中。

一些進一步的想法

• 如果您沒有一個好的時間戳候選，源系統中具有相同屬性的技術密鑰（必須只上升，沒有遲到的事實）也應該沒問題。
• 如果時間戳是由源應用程序生成的，並且存在不會同時插入 2 個具有完全相同時間戳的事務的風險（很常見），那麼使用稍微過去的 max_ts 會更安全（select max(max_ts) - 5 minutes from source_table.）
• 關於失敗的負載。如果您只關心清理上次失敗載入的條目，則可以將其添加為 ETL 流程的第一步。( delete from dwh_table where ts > min_ts) 這將在最後一次成功提取後從任何失敗（如果有）中刪除條目。它不會處理早期成功提取之間的失敗。

您能否向用於維護導入的事務表的數據倉庫添加一個額外的表？

維護表可以稱為：

ImportJob
   ImportID (primary key)
   TransactionDate

將 ImportID 列添加到事務表中，並在載入數據時進行設置。

導入表將為每天導入的交易價值提供一條記錄。TransactionDate 的唯一約束將阻止重新載入數據兩次。如果導入失敗，您還可以快速刪除具有原子性的事務。您可以一次查詢生產表以獲取一天的數據並填寫任何缺失的日期。

引用自：https://dba.stackexchange.com/questions/49068