Soft Delete Workflow

The following steps show the soft delete workflow for the soft delete logic configuration.

1. The Primary Extract mappings (SDE_*_Primary) extract all current primary keys (INTEGRATION_ID) from the source system into the corresponding _PE tables.

2. The ETL process compares the warehouse records with the _PE tables to identify records that no longer exist in the source system.

3. The identified primary keys are loaded into the corresponding _DEL tables.

4. The ETL process updates the target fact (_F) and dimension (_D) tables by setting DELETE_FLG = 'Y' for all records identified in the _DEL tables.

This design ensures that deletions in the source system are accurately reflected in the data warehouse without physically removing historical records, thereby supporting data retention and maintaining referential integrity.