Skip to main content

Enable Delete Detection

Determines if BimlFlex applies a separate key load pattern that enables detection of hard deletes in the source.

What This Does​

When enabled, BimlFlex generates an additional pipeline step that compares the full set of keys from the source against the keys already loaded. Any key present in the target but absent from the source is flagged as a delete. This requires a full key extract from the source on each load cycle.

Metadata Requirements​

Each table with Delete Detection enabled must have at least one column flagged as Source Key, or at least one non-derived column flagged as Primary Key. These are the columns BimlFlex uses to identify rows that have been removed from the source — columns flagged as Derived (typically those whose values are computed by a DataflowExpression) cannot stand in on their own, because their values are computed during the load rather than materialised in the persistent table.

A common pattern: a derived business key column (e.g., one populated by FlexToBk(...)) is marked as both Primary Key and Derived, and at least one of the underlying natural columns is also marked as Source Key. The natural columns are what Delete Detection joins on; the derived business key is used elsewhere in the load.

When to Enable​

Enable delete detection when:

  • Your source system performs hard deletes (rows are physically removed, not soft-deleted with a flag)
  • You need to track which records have been removed from the source for audit or compliance purposes
  • Your Data Vault satellites need to record delete events

Do not enable when:

  • The source system uses soft deletes (a IsDeleted or DeletedDate column) — handle these with column mappings instead
  • You are doing initial loads only
  • The performance cost of a full key comparison is unacceptable for your data volumes

How the Three Settings Work Together​

SettingPurposeDefault
Delete Detection Enabled (this setting)Master switch — enables the key comparison patternN
Delete Detection Apply DVWhen Y, applies delete records to Data Vault satellitesN
Delete Detection Apply PSAWhen Y, applies delete records to the Persistent Staging AreaN

All three must be configured intentionally:

  • Setting DeleteDetectionEnabled = Y alone generates the key comparison but does not propagate deletes anywhere
  • You must also enable at least one of DeleteDetectionApplyDv or DeleteDetectionApplyPsa for the detection to have effect
  • Enabling both propagates delete records to both layers

Notes​

  • This setting is part of the Delete Detection settings category.
  • The default value for this setting is N.