Qlik Replicate: Implementing UPSERT and MERGE modes by applying a Conflicts Handling Policy

[Michael_Litz]

Click here for Video Transcript

Note: The concept ’ UPSERT MODE’ and 'MERGE MODE' is not documented in the User Guide. i.e. it is not a word you can search for in the User Guide and is not a key word in the Replicate UI.

What they do:

UPSERT MODE: Change an update to an insert if the row doesn't exists on the target
MERGE MODE: Change an insert to an update if the row already exists on the target

How to set

Use MERGE MODE: i.e. configure the task under: task setting --> Error Handling --> Apply Conflicts --> ‘Duplicate key when applying INSERT:’ UPDATE the existing target record

Use UPSERT MODE: i.e. configure the task under: task setting --> Error Handling --> Apply Conflicts --> ‘No record found for applying an UPDATE:’ INSERT the missing target record

How they work:

Batch Apply and Transactional Apply modes:

There is a big difference in how these Upsert/Merge settings work depending of whether the task is in 'Batch' or 'Transactional' Apply mode.

Batch Apply mode:

Either option (Upsert/Merge) does an unconditional Delete of all rows in the batch, followed by an Insert of all rows.

Note: The other thing to note is that with this setting the actual update that fails is inserted in a way that may not be obvious and could cause issue with downstream processing. In batch apply mode the task will actually issue a pair of transactions (1st a delete of the record and then 2nd an insert) this pair of transactions is unconditional and will result in a "newly inserted row every time the record is updated on the source.

Transactional Apply mode:

Either option (Upsert/Merge) - the original statement is run and if it errors out then the switch is done (try and catch). 

Insert in transactional apply mode,  the insert statement will be performed in a "try / catch" fashion. The insert statement  will be run and only if it fails will it be switched to an update statement .

In transactional apply mode, the update will be performed in a "try / catch" fashion. The update will be run and only if it fails will it be switched to an insert statement . 

Gotchas:

1. If the table has added fields that are used/modified by a downstream process then there is a good chance that this will cause a problem.
2. Oracle table must have supplemental logging all column set
3. Masking of possible underlying issue - why was the record not there for an update or why was it there already for and insert
4. I want to caution you that there may be other missing records (from a failed insert) that were not captured and this setting will only bring over missing records if they are updated on the source. There could be many records still missing on the target.

Possible use case:

1. Missing values in some fields/missing rows on target. With Upsert/Merge set a "fake" update can be issued on the source (Update field1 = field1) without a where clause will force every row in the source to refreshed to the target ( without losing all the target data like a reload would)
2. Prevent a task from switching out of bulk apply due to errors 'Duplicate PK or Record not found (appliance targets could benefit from this)
3. Could help the CDC only task of split table environment - Separate Full Load / CDC design.

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

• Transformation: Target Lookup

Environment

Qlik Replicate 

[jamshed]

Will it work without CDC ? i am trying but not working without CDC, kindly suggest me if you know any there ways to resolved this

[john_wang]

Hi @jamshed ,

This process is designed for Change Data Capture (CDC) only. It does not apply during the Full Load phase.

For the initial load, there are four options available:

1. DROP and CREATE Table: This will drop the existing table and recreate it before loading data.
2. TRUNCATE Before Loading: The existing table's data is cleared, but the table structure remains intact.
3. ARCHIVE and CREATE Table: The table is archived, and a new one is created before loading data. (Currently this option is only available for the Hadoop target endpoint.)
4. Do Nothing: Existing data and metadata of the target table will not be affected. New data will be added to the table.

If the initial load is interrupted and the task stops before completion, the next run will restart the entire initial load from the beginning.

Regards,

John.

[shyamkatika]

Hi @john_wang 

We are encountering an issue where an UPDATE operation is attempting to modify the Primary Key to a value that already exists in the target, resulting in the following error:

The 'UPDATE' for table 42 at stream position '00E26D692AB1C01BAA000001' changes PK to a PK already in use

Note: In Task Settings → Change Processing → Change Processing Tuning, we have enabled SQL MERGE.

Could you please help clarify:

1. With SQL MERGE enabled, how does Qlik Replicate handle PK updates when the new PK already exists?
2. Will the operation be skipped entirely, or is there any retry/alternative logic applied?
3. What is the recommended approach to handle such PK conflicts in CDC scenarios?

Thanks,

Shyam Sundar.

[john_wang]

Hello Shyam Sundar,  @shyamkatika 

Would you mind sharing the source and target endpoint types?

thanks,

John.

[shyamkatika]

Hi @john_wang 

Hi John,

The source endpoint is IBM Db2 for z/OS, and the target endpoint is Snowflake on Azure.

Thanks,
Shyam Sundar.

[john_wang]

Hello Shyam Sundar, @shyamkatika 

I tested multiple scenarios in my lab, but was unable to reproduce the behavior.

In general, for a standard UPDATE, Qlik Replicate generates a MERGE INTO statement. However, if the UPDATE modifies primary key values, Qlik Replicate will not use MERGE INTO; instead, it will apply the change as a DELETE + INSERT pair for that row.

To better understand the issue, I recommend opening a support ticket and providing the following information:

1. The source table creation DDL, along with sample DML operations to reproduce the issue
2. Enable verbose logging for both SOURCE_CAPTURE and TARGET_APPLY, reproduce the behavior, and upload the Task Diagnostics Package
3. Please also decrypt the verbose task log files and attach them to the case

Regards,

John.

[shyamkatika]

Hi @john_wang 

Thank you for the update.
We are currently using Qlik Replicate with Snowflake as the target endpoint, running on version 2025.05.

1. Although the recommendation is to use DELETE + INSERT instead of UPDATE for PK changes, this option is currently showing as inactive in our setup. Could you please help clarify why this option is disabled and how it can be enabled?
2. If we deselect the “Apply SQL MERGE” option, will updates (including primary key updates) be applied correctly without causing conflicts on the target? In other words, will this approach help avoid the PK conflict issue we are currently facing?
Appreciate your guidance on this.

Regards,
Shyam Sundar.

[john_wang]

Hello Shyam Sundar, @shyamkatika ,

1. If you are referring to the Apply Conflicts Handling Policy being grayed out and not editable, this is because the Apply changes using SQL MERGE option is enabled (as shown below).
   

   

2. Both MERGE INTO and standard DML operations (INSERT/UPDATE/DELETE) can handle changes involving primary key values. However, they may not cover all scenarios. For your current issue, further investigation is required.

Regards,

John.