topic Re: Can we set upsert mode in full load only task ? in Qlik Replicate

Can we set upsert mode in full load only task ?

gayatri235 — Fri, 16 Feb 2024 09:18:19 GMT

Hello All,

We need to schedule full load like every 20 mins which we did through Task Scheduler.

But can we do some setting Full Load only task to extract new records and update at target (Basically Upsert mode)

Regards,

Gayatri.

Re: Can we set upsert mode in full load only task ?

aarun_arasu — Fri, 16 Feb 2024 09:31:54 GMT

Hello @gayatri235 ,

Qlik Replicate doesn't directly support incremental loading, using pass-through filters to define loading conditions can be a viable alternative. Pass-through filters allow you to specify conditions for loading data into the target system based on certain criteria, which can help achieve incremental loading behavior.

Below is the reference article

https://community.qlik.com/t5/Official-Support-Articles/Filter-for-last-90-days-of-data-in-Qlik-Replicate/ta-p/1880761

Regards

Arun

Re: Can we set upsert mode in full load only task ?

SachinB — Fri, 16 Feb 2024 09:32:01 GMT

Hello @gayatri235 ,

Thanks for contacting Qlik community

Currently, the functionality to selectively fetch records during a full load task, where all records from the source are fetched, is not available. However, there's a potential solution: by setting flags for newly added records as "Open," we can implement a Fullload Passthru Filter to fetch only these flagged records during the full load process.

Here's how it works:

Set flags for newly added records as "Open" in the source system.
Utilize the Fullload Passthru Filter feature to filter and fetch only records marked as "Open" during the full load.
After the full load process is complete, reset the flags for these records to "Closed" using an internal script or mechanism.

This approach allows for a more selective and efficient full load process, focusing only on the records that meet the specified criteria. However, it requires coordination between setting and resetting the flags in the source system and configuring the Fullload Passthru Filter in the data integration process.

Regards,

Sachin B

Re: Can we set upsert mode in full load only task ?

DesmondWOO — Fri, 16 Feb 2024 10:12:50 GMT

Hi @gayatri235 ,

I would suggest writing a script to create a view. Before full load starts, re/create a view that contains new records only and let Replicate loads data from this view.

Regards,
Desmond

Re: Can we set upsert mode in full load only task ?

john_wang — Sun, 18 Feb 2024 02:14:45 GMT

Hello @gayatri235 ,

UPSERT mode is meaningful for CDC stage: The INSERT operation will convert to UPDATE operation in case of PK violation occurs; The UPDATE operation will convert to INSERT operation if the target row does not exists. There is a 'logic' to determine if the UPSERT applies.

However during Full Load initial stage, there is NO logic. Qlik Replicate utilizes LOAD, or COPY programs (depends on the various database functions) to 'upload' the history data records to target database to gain the best load performance, a logic of detecting target row(s) existence will slow down the load performance. UPSERT mode is not applicable for Full Load.

Hope this helps.

John.

Re: Can we set upsert mode in full load only task ?

deepaksahirwar — Sun, 18 Feb 2024 16:02:37 GMT

Hello @gayatri235 ,

In Qlik Replicate, a full load task by default drops the target table if it exists, creates it, then selects all rows from the source and performs inserts into the target table. This is for the base tables.

However, if you want to implement UPSERT and MERGE concept in Qlik Replicate, you can apply Conflicts Handling Policy settings to tasks. This allows you to handle situations where a record might not be there for an update or is already there for an insert.

Please note that 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.

Also, keep in mind that selecting this option could impact performance as Replicate instructs the source database to return the table records by Primary Key order and then removes any duplicate records.

I hope this helps! Let me know if you have any other queries.

If our response has been helpful, please consider clicking "Accept as Solution". 
This will assist other users in easily finding the answer. ‌

Best Regards,
Deepak Ahirwar

Re: Can we set upsert mode in full load only task ?

SushilKumar — Thu, 22 Feb 2024 11:47:55 GMT

Hello Team,

There is a big deference between full load/initial_load - have Different purpose. Full loads create table and insert the data sequentially onto the target side. So that table become identical on both side source and target side.

Upsert mode is used to overcome data mismatch where target table are not managed by Qlik replicate at Full load stage: - is something comes into picture when Qlik replicate is using SQL extract to apply changes onto target table. The INSERT operation will convert to UPDATE operation in case of PK violation occurs; The UPDATE operation will convert to INSERT operation if the target row does not exist.

However, performing the Full of a view based upon the table as per change data is a feasible solution.

Regards,
Sushil Kumar