A rerunnable process can execute again after partial failure without code changes, data duplication, or loss of business-level consistency. section 12.5
Design an ETL process around three conditions:
- Retain the original source data, or make it available again through a configurable API or cached transactional data. section 12.5
- Parameterize dates instead of hardcoding expressions such as WHERE DataDate = GETDATE(). section 12.5
- Merge results into target tables rather than relying on INSERT statements that can create duplicate business keys across multiple transactions. section 12.5
The overnight ETL failure
An ETL process starts at 1 a.m., runs for five hours, and populates staging and star-schema tables through several transactions. If it fails after a 2 a.m. backup, rerunning an INSERT-based process can create new identity rows with duplicate business keys, even though each transaction remains valid. section 12.5
