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:

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: