You may want to activate the delete detection to follow which rows were deleted in your source data.
The benefits are:
- Improve data accuracy by identifying and removing obsolete records
- Enhanced data quality by maintaining up-to-date information
- Reduced storage costs by eliminating unnecessary data
We can configure the Delete Detection behavior for the following Model Objects:
- Store Entity
To configure it, you must:
- Choose the Delete Detection Method: FullSet or DeletedSet
- Set the Delete Detection Source if you are using a DeletedSet
To have a better understanding, let's take an example.
Source data
In this example, we have :
- A source table, CreditCard, contains all the Credit Cards
- A source table, CreditCard_ToDelete, contains only the business keys of the Credit Cards to delete.
- It is empty for the moment:
- It is empty for the moment:
Data Store model
Let's create the following object:
- A Stage named CreditCard (source data AdventureWorks2019.Sales.CreditCard)
- A Stage named CreditCard_ToDelete (source data Manual_Data.dbo.CreditCard_ToDelete)
- A Store Entity named CreditCard (Created from the Stage CreditCard)
- Set the Delete Detection Method Property to DeletedSet and the Delete Detection Source to CreditCard_ToDelete:
- Set the Delete Detection Method Property to DeletedSet and the Delete Detection Source to CreditCard_ToDelete:
Load of data
Generate, replace the placeholders, deploy, and load the project.
Then check the data for the CreditCard Store Entity:
SELECT * FROM [ODS].[DS_SE_CreditCard]
You should have all the Credit Cards valid:
Now, let's add 2 Credit Cards to delete in our source table:
Load the project.
The Credit Cards 1 and 4 are now marked as deleted: