Qlik Replicate - Using UPSERT mode when writing to Snowflake

[NakulanR]

Hi Qlik Support,

When using an UPSERT error handling policy (as a result of enabling the "Apply changes using SQL MERGE" option) in a Replicate task which writes to Snowflake, is Replicate able to correctly prevent duplicate records from being written to the Snowflake target even though Snowflake is known to not enforce the uniqueness of primary keys.

Apologies if this question has already been addressed in a different community post.

Thanks,

Nak

[narendersarva · Accepted solution]

Hi @NakulanR 
UPSERT error handling policy and the use of SQL MERGE, effectively manages the prevention of duplicate records being written to Snowflake, even in the absence of primary key enforcement by Snowflake.

When an UPSERT policy is in place, Replicate attempts to insert new records into the Snowflake target. If a record with the same primary key already exists, Replicate will update the existing record instead of inserting a new one.

Hope this helps.

Thanks
Naren

[NakulanR]

Hi Naren,

Thanks for the prompt response. Appreciate your helpful feedback.

Regards,

Nak

[narendersarva]

You are welcome @NakulanR 

Thanks
Naren

[DesmondWOO]

Hi @NakulanR ,

When "Apply changes using SQL MERGE" option is enabled, the Apply Conflicts Handling Policy is not available. All apply conflicts options will be grey out.

Regards,
Desmond

[Heinvandenheuvel]

As per @DesmondWOO , once Snowflake SQL Merger is activated Replicate is no longer involved in the duplicate/missing target row handling. You'll have to carefully study the Snowflake documentation to understand the behaviour.... or just try!

@narendersarva @  >> When an UPSERT policy is in place, Replicate attempts to insert new records into the Snowflake target. If a record with the same primary key already exists, Replicate will update the existing record instead of inserting a new one.

This is NOT correct when using Replicate Error handling (as opposed to Snowflake SQL Merge).

When Replicate exception handling is set to 'update if target exists' Replicate does NO attempt to check for duplicates. It blindly pre-deletes any rows in the batch it is about to insert - ignoring failed deletes, and proceeds to blindly inserts that batch. 

Similar for exception handling 'insert when target not found' - just pre-delete the batch, and (re)insert.

Hein.