topic Re: Qlik Replicate - Enable Delta Load even though Source is sending Full Load in Qlik Replicate

Qlik Replicate - Enable Delta Load even though Source is sending Full Load

AnitaPrasanna — Fri, 20 Oct 2023 15:22:53 GMT

I am receiving a full data load from an SQL system into Synapse via Qlik Replicate. We truncate the data and perform a full load. In this scenario, we do not have the primary keys of the deleted records. As we further process this data, the compose cannot handle it if the deletion flag is not set in the landing table.

Is there an option to switch to delta processing in Qlik Replicate even though we are reading the data from the source as a full load? Something like a Pseudo Delta

Appreciate your help .Thanks

Re: Qlik Replicate - Enable Delta Load even though Source is sending Full Load

john_wang — Sat, 21 Oct 2023 08:28:36 GMT

Hello @AnitaPrasanna ,

Welcome to Qlik Community and thanks for the post.

I guess I did not get the question well, please help me to understand it:

1. I am receiving a full data load from an SQL system into Synapse via Qlik Replicate.

It's a Full Load ONLY task from SQL Server to Synapse because of NO PK in the source table (so CDC is not supported). Is that correct?

2. We truncate the data and perform a full load.

You meant truncate the target side data first, then a Full Load process. Am I right?

3. In this scenario, we do not have the primary keys of the deleted records.

In which side (source and/or target)? From where and how was DELETE initiated?

4. Is there an option to switch to delta processing in Qlik Replicate even though we are reading the data from the source as a full load? Something like a Pseudo Delta

Do you mean a column value to indicate the operation type? for example 'INSERT' or 'FRESH' to represent the Full Load?

Any tips are welcome!

Regards,

John.

Re: Qlik Replicate - Enable Delta Load even though Source is sending Full Load

AnitaPrasanna — Sat, 21 Oct 2023 11:17:18 GMT

@john_wang - Suppose in SQL Source System i have 100 records (which is daily Truncate) , data is extracted to Landing Table (100 Records- Target System).

Next day SQL Source System data got truncated have 90 records, data is extracted to Landing (Truncate and Load 90 records)

The question is in Landing Table (Target System ) can i mark the yesterdays 10 records as Delete.

Currently i am not getting Operations of Insert,Update,Delete from SOurce system as we are doing Full Load.

Within Target System can i write custom code to set the Deletion Flag Y if todays full load have some missing records from yesterday

Point 1: PK is there

Point 2 Yes

3. As its full Load its truncate so we are not capturing deleted records

4.We have a deletion flag column in target System- Requires custom code which can update the Flag instead of truncate in Target System .Not getting any operations from Source Sytem

Re: Qlik Replicate - Enable Delta Load even though Source is sending Full Load

AnitaPrasanna — Wed, 25 Oct 2023 17:26:05 GMT

@john_wang - Any update on this please.Thanks

Re: Qlik Replicate - Enable Delta Load even though Source is sending Full Load

john_wang — Thu, 26 Oct 2023 04:30:30 GMT

Hello @AnitaPrasanna ,

Thanks for your patience.

If I understood correctly, this is a question in TARGET side DB only: how can we know some rows are 'deleted' compares to yesterday rows by PK.

Personally I do not think it's a good approach to handle it within Qlik Replicate tasks, instead, an easier way is using VIEW in Azure Synapse Analytics to show the soft 'deleted' rows.

Hope this helps.

Regards,

John.