Delete Detection Source (Model Object)
Applies to
Platform: Microsoft SQL Server · Snowflake · Databricks · Fabric Lakehouse · Fabric Warehouse · Oracle
Architecture: Data Store
Delete Detection Source (DeleteDetectionSource) names a model object that lists business keys for the delete detection of a Store Entity. Use it when the source delivers a separate list of deleted keys, or a list of all valid keys while the load delivers changed rows only.
Where to set it
Set the property in the group Delete Detection when you edit a model object of type Store Entity. The value is a model object picker. The default is empty.
Meaning per method
What the list means depends on Delete Detection Method:
DeletedSet: the list holds the keys deleted in the source. A listed key is marked as deleted when it is stored or delivered. The property is required with this method.FullSet: the list holds all valid keys. A stored or delivered key that is not on the list is marked as deleted. Without a Delete Detection Source, the load itself is the list of valid keys.PartialSet: leave the property empty.PartialSetcompares with the delivered rows.None: the property has no effect.
Prerequisites
- The selected model object has a term with the same name as each business key term of the Store Entity.
- When a source of the Store Entity is loaded incrementally, set a Delete Detection Source. Validation otherwise warns, because
FullSetandPartialSetwould compare with the new rows only.
Validation reports a missing model object, a missing business key term and the combinations above, see the business rules under Related articles.
Effect on generated output
The delete-detection loader compares the stored and delivered keys with the keys of the selected model object. The load control runs this loader after the Delete Detection Source and before the Store Entity loader. The generated objects and an example are in Configure delete detection.
Example
The Store Entity dd_deletedset_scd_0_1_2 on Microsoft SQL Server has the business key CUSTOMER_ID and Delete Detection Method DeletedSet. Its Delete Detection Source is the Store Composite prep_deletedset, which also has the term CUSTOMER_ID. The delete-detection loader fills its table with the stored and delivered keys that are on the list:
SELECT CUSTOMER_ID FROM store.DS_SE_dd_deletedset_scd_0_1_2_Result UNION SELECT CUSTOMER_ID FROM store.DS_SE_dd_deletedset_scd_0_1_2_Source INTERSECT SELECT CUSTOMER_ID FROM store.DS_SC_prep_deletedset_Result;
The generated statement also writes the load timestamp and groups the two first selects in parentheses. With FullSet, EXCEPT takes the place of INTERSECT.
Related articles
- Reference: Delete Detection Method (Model Object), BG_IsDeleted
- See also: Configure delete detection, No delete detection source with a deleted set (Business Rule), Delete detection source exists (Business Rule), Delete detection source Business Key exists (Business Rule), No delete detection with an incremental load in the source (Business Rule), PartialSet delete detection settings (Business Rule)