Configure a delete detection behavior

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:
    
    

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:
    

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: