Qlik Replicate: Apply conflicts do not apply to Store Changes tables

KellyHobson — Mon, 23 Jan 2023 19:59:12 GMT

Introduction

The Apply Conflict settings which can be configured under Task Settings -> Error Handling -> Apply Conflicts can vary based on your business needs. For example, UPSERT mode is a handy setting for preventing duplicates or inserting missing updates.

These settings apply only to changes captured with 'Apply Changes' task settings. They do not apply to changes captured with 'Store Changes' enabled. This is because Store Changes by design captures and treats any change as an INSERT.

In the example below, I'll show examples of the same behavior on a Apply Changes only vs a Store Changes only task with mySQL source and target.

Set up

Original source table:

Apply Changes Test

For the first part of the article, the task is configured with 'Apply Changes' only ON with defaults.

We have enabled UPSERT mode.

#test 'No record found for applying an UPDATE'
#insert a new value on source table
INSERT INTO kelly.Persons2 VALUES(36,'molly','2010-11-01 05:00:00');

#manually delete from target table
delete from replicate1.Persons2 where PersonID=36;

#update the source table
update kelly.Persons2 set lastname='WALLEY' where PersonID=36;

Source table post test:

Target table post test:

Store Changes Test

*Note deleted record with PersonID=36 from both tables before continuing.

For the second section the task is configured with 'Store Changes' only ON with defaults. Note UPSERT settings are still set but they will not do anything.

#test 'No record found for applying an UPDATE'
#insert a new value on source table
INSERT INTO kelly.Persons2 VALUES(36,'molly','2010-11-01 05:00:00');

#skip manual delete on target since we are not capturing changes on target table

#update the source table
update kelly.Persons2 set lastname='WALLEY' where PersonID=36;

From the output of the ct table, we see 3 records. The first is the original insert, the other 2 reflect the update activity but are treated as inserts. Thus, with UPSERT enabled, we will all records as inserts with unique header_stream_positions rather than only the last record with the UPDATE details.

Environment