How to create a Qlik Replicate task to capture soft deletes and allow source tables to reuse primary key values

[Michael_Litz]

Task requirements

This task needs to capture soft deletes and allow the source table to reuse primary key values without a PK violation on the target.

Summary

Setting up this task requires four steps:

1. Add a table to the task and two fields to the target primary key.
2. Add transformations to the two new fields.
3. Configure the task to use upsert mode.
4. Show results of updates and deletes on the target table

Adding the two extra fields to the target side primary key and having the NOW timestamp ensures that the target record will be unique compared to the source record with only one field primary key.

This will allow the source to reuse a deleted records primary key without throwing a PK violation on the target. The transformations on the fields set unique values into the additional key segments.

Setting error handling and the apply conflicts handling policy to Upsert Mode (No record found for applying an Update: INSERT the messing target record) normally works by converting the source statement (whether Insert, Update, Delete) into a Delete followed by and Update. The unique three segment primary key is not able to be found during the delete which is why the records persist on the target.

The Steps

Add a table to the task and two additional fields to the target primary key

Example source table structure:

Note the single field Primary Key MyPK.

In our example, we have added Operation and OperationDateTime as the additional fields.

Example source table records:

In the Table Settings for your task, go to Transform and mark your newly added fields as target side primary keys.

In our example, we have marked Operation and OperationDateTime.

Add transformations to the two new fields

Transformation are added through the Expression Builder, located in the Table Settings. You will modify the fields marked as keys in the previous step, Operation and OperationsDateTime.

Operation: set the expression to INSERT, UPDATE or DELETE based on the source transaction. 

Transformation function:

CASE
WHEN $AR_H_STREAM_POSITION = ''
THEN "INSERTED"
ELSE operation_indicator("DELETED", "UPDATED", "INSERTED" )
END

OperationDateTime: set the expression to to NOW date time

Transformation function:

Datetime('now')

Configure the task to use Upsert mode

1. Go to Task Settings...
2. Open Error Handling and navigate to Apply Conflicts
3. Set No record found for applying an UPDATE to INSERT the missing target record
   
   
   

Show results of updates and deletes on the target table

Results in target table after updates and deleting source record #6 and reuse of primary key on source.

Target table after full load:

Target table results after updates and delete on source:

Target table after reuse of source primary key new record inserted.

Outcome

Results show that the target table is acting like an Audit table; all source transactions are stored.

The target table can have multiple records that have the same primary key on the source (MyPk 6).

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Related Content

Upsert/Merge
Soft Delete

Environment

Qlik Replicate