You can configure database ingestion and replication incremental load and combined initial and incremental load tasks to process delete operations on the source as soft deletes on the target.
Note:
The
Soft Deletes
apply mode is available for tasks that have any supported source type and an Amazon Redshift, Databricks, Google BigQuery, PostgreSQL, Snowflake, or SQL Server target.
A soft delete marks a deleted row as deleted without actually removing it from the database. The row is applied to the target with the value of "D" in the generated INFA_OPERATION_TYPE metadata column.
Important:
You can use Soft Deletes only if all of the source objects have primary keys and the source does not allow the primary key values to change in any row after the row is first created. If the primary key values change, duplicate rows might be written to the target, causing the target to become corrupted.
When you apply a soft delete, the job captures the incoming DELETE event from the source table, finds the active record in the target with the matching key where the INFA_OPERATION_TYPE column is null, and updates it to "D" to mark the record as deleted. However, if the source re-inserts or undeletes a row with the same key and then deletes it again multiple times, the target might reflect each DELETE event as a separate record.
Example scenario: Your organization wants to use soft deletes in a data warehouse to mark the rows that were deleted at the source while still retaining the rows for audit purposes.
To enable soft deletes, set the Apply Mode field to Soft Deletes on Target page of the task wizard when you configure the database ingestion and replication task.
Note:
If a combined initial and incremental load job captures an incremental insert change record during the initial unload phase, the job manufactures a delete for the same row to remove any duplicate that might have been obtained from the initial unload. This manufactured activity will be reflected in the soft delete apply mode.