topic Re: Qlik Replicate - Using UPSERT mode when writing to Snowflake in Qlik Replicate

Qlik Replicate - Using UPSERT mode when writing to Snowflake

NakulanR — Wed, 24 Apr 2024 00:10:03 GMT

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

Re: Qlik Replicate - Using UPSERT mode when writing to Snowflake

narendersarva — Wed, 24 Apr 2024 00:25:09 GMT

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

Re: Qlik Replicate - Using UPSERT mode when writing to Snowflake

NakulanR — Wed, 24 Apr 2024 00:33:00 GMT

Hi Naren,

Thanks for the prompt response. Appreciate your helpful feedback.

Regards,

Nak

Re: Qlik Replicate - Using UPSERT mode when writing to Snowflake

narendersarva — Wed, 24 Apr 2024 00:38:45 GMT

You are welcome @NakulanR

Thanks
Naren

Re: Qlik Replicate - Using UPSERT mode when writing to Snowflake

DesmondWOO — Wed, 24 Apr 2024 04:36:30 GMT

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

Re: Qlik Replicate - Using UPSERT mode when writing to Snowflake

Heinvandenheuvel — Thu, 25 Apr 2024 03:36:55 GMT

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.