Delete Detection Partial Terms (Model Object)

Applies to
Platform: Microsoft SQL Server · Snowflake · Databricks · Fabric Lakehouse · Fabric Warehouse · Oracle
Architecture: Data Store

Delete Detection Partial Terms (DeleteDetectionPartialTerms) selects the Store Entity terms that identify the part of the data a load delivers. With Delete Detection Method PartialSet, a load checks only the stored keys whose values in these terms appear in the load.

Available since Generator 2.2.

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 selection of terms of the Store Entity. The default is empty.

Rules

The property has an effect only with Delete Detection Method PartialSet. This method needs at least one term.
Each term is compared on its own: a stored value matches when any delivered row has the same value in that term.
With several terms, Delete Detection Partial Operator decides whether every term or one term must match.
A NULL value never matches.
With SCD2 history, only the current version of a key is checked. Since Generator 2.4.1.

Validation reports a missing term and a term that does not exist on the Store Entity, see PartialSet delete detection settings (Business Rule).

Effect on generated output

The delete-detection loader takes the current stored rows whose partial-term values appear in the source view. From these keys it removes the delivered keys. The rest are marked as deleted.

Example

The Store Entity delete_detection_partialset_one_term_delete on Microsoft SQL Server has the partial term COUNTRY and SCD2 history. Shortened, its delete-detection loader selects:

SELECT CUSTOMER_ID
FROM store.DS_SE_<store entity name>_Result
WHERE BG_ValidToTimestamp = N'99991231'
  AND COUNTRY IN (SELECT DISTINCT COUNTRY
                  FROM store.DS_SE_<store entity name>_Source)
EXCEPT
SELECT CUSTOMER_ID
FROM store.DS_SE_<store entity name>_Source;

<store entity name> stands for the name of the Store Entity. Loads that delivered three sets of rows gave this result:

Load 1 delivers customers 1 and 2 from CH and customers 3, 4 and 5 from DE.
Load 2 delivers the same customers, but customer 3 is now in CH.
Load 3 delivers only customer 4 from DE. It marks customer 5 as deleted. Customer 3 stays unchanged, because its current version has CH.