topic Re: Prevent deleting archived data from the Source table in Qlik Replicate

Prevent deleting archived data from the Source table

Attunity_user — Mon, 16 May 2022 22:17:24 GMT

Hello,

We set up tasks for the real-time refresh and also build historical data in the target.

If we select not to truncate the table (do nothing) on a task because of maintaining historical data in the target table that are getting purged in the source, we will never be able to choose the reload to fix a mismatch, or in some other cases or issues, the reload is an easier fix. Besides us, does anybody else follow a similar task design to prevent data from getting deleted from the source and not have an option to reload if needed? I like to hear from others.

Thanks,

Re: Prevent deleting archived data from the Source table

Dana_Baldwin — Mon, 16 May 2022 22:25:20 GMT

Hi @Attunity_user ,

I assume your task is set to filter out deletes so that the records are retained on the target.

There might be a more elegant solution, but one strategy that comes to mind is:

Rename the target table

Reload the target table

Insert all the rows from the renamed table to the new target table

Drop the renamed table

Thanks,

Dana

Re: Prevent deleting archived data from the Source table

Alan_Wang — Mon, 16 May 2022 22:28:29 GMT

Hello @Attunity_user

We do have Customers that wish to keep historical data on deleted records from the source tables. You're also correct that you would not be able to reload the target table without losing the historical data. You can choose to back up that table to another location should you really need to reload.

Most of our Customers are using the operation indicator function to act as soft deletes for keeping historical data. More information can be found in the link below.

https://help.qlik.com/en-US/replicate/November2021/Content/Global_Common/Content/SharedEMReplicate/Customize%20Tasks/tasks_expressOther.htm#Operation

Re: Prevent deleting archived data from the Source table

Attunity_user — Mon, 16 May 2022 22:35:19 GMT

Hi Dana,

Did you mean the following?

Rename the target table - from TBL A to TBL B

Reload the target table TBL B

Insert all the rows from the renamed table to the new target table – only the historical rows from TBL B to TBL A

Drop the renamed table – TBL B

My intention in asking this question was to find out the best way to use the product and gather use cases from others who use the Qlik Replicate product to move data from the source to the target and how they set that up.

Thanks

Re: Prevent deleting archived data from the Source table

Attunity_user — Mon, 16 May 2022 22:40:34 GMT

Hello Alan,

That’s good to know others are leveraging this tool to keep the historical data that the source will not have. We use an operation indicator to distinguish soft vs hard delete because we want to process the soft ones. We do not have a backup to another location but working now to set up a weekly export so we can move inadvertently purged history back to the main table.

Thanks

Re: Prevent deleting archived data from the Source table

Michael_Litz — Mon, 16 May 2022 22:48:15 GMT

Hi Attunity User,

I agree with Dana, I have seen a few customers implement this type of process when they need to do a reload of a replicate target table that has historical data.

I am pretty sure that you will do the reload into the "original table A" since the rename to table B has the historical data.

With typical task configuration set to recrate the target table if it doesn't exist then the "table " will get recreated. Then you run a job outside of replicate to merge the rows from table B that do not exist into table A.

Thanks,
Michael

Re: Prevent deleting archived data from the Source table

lyka — Mon, 16 May 2022 22:53:12 GMT

Hello,

If the table has date column, when you decide to reload, you can add a filter to load only the records with a date > than the current date (sample)

Thanks

Lyka

Re: Prevent deleting archived data from the Source table

shashi_holla — Mon, 16 May 2022 22:58:14 GMT

Hi,

This could be another approach:

Full Load Settings -> If target table already exists: -> Do Nothing
Change Processing -> When source table is truncated: -> Ignore Truncate

Based on the settings, during Full load, only the new records will be inserted as per the PK.

Also we can leverage the Error Handling settings (This works if we don't have both LOB columns and Table transformations but next Replicate patch should address this) -> Apply conflicts -> Duplicate key when applying INSERT: -> UPDATE the existing target record
and
No record found for applying an UPDATE: -> INSERT the missing target record

Outside of Replicate on Target DB, we can have triggers on the table to restrict Replicate user from deleting the historical or soft deleted/changed records (have to maintain unique flag to identify those) and also can restrict truncate permission on the table.

Re: Prevent deleting archived data from the Source table

Attunity_user — Tue, 17 May 2022 14:52:49 GMT

Hi Lyka,

On every task, we have already included the Error Handling settings for 'INSERTS' and UPDATES" when a record is missing or exists. My question is will it still fail or throw a constraint error in production if we reload with a filter and then can resume the CDC to pick up when it was stopped after the filtered reload?

Thanks

Re: Prevent deleting archived data from the Source table

lyka — Tue, 17 May 2022 15:54:11 GMT

Hello,

If you are already using the MERGE and UPSERT option then you should not get an constraint error for duplicates or missing rows.

Thanks

Lyka

Re: Prevent deleting archived data from the Source table

Attunity_user — Tue, 17 May 2022 15:59:42 GMT

Hi Sashi,

I think if we reload, all records will be transferred and not only new records. Because we are trying to resolve data mismatch or wanted to fix itwith an easy option of reload.

>>Based on the settings, during Full load, only the new records will be inserted as per the PK.

Below option will not be possible to go with.

>>Outside of Replicate on Target DB, we can have triggers on the table to restrict Replicate user from deleting the historical or soft deleted/changed records (have to maintain unique flag to identify those) and also can restrict truncate permission on the table.

Thanks

Re: Prevent deleting archived data from the Source table

shashi_holla — Tue, 17 May 2022 16:16:19 GMT

If we choose Do nothing in the Full Load settings, only the new data should be inserted and it's mentioned in the user guide as well:

Re: Prevent deleting archived data from the Source table

Attunity_user — Tue, 17 May 2022 18:18:23 GMT

Hi Lyka,

If we filter on a date to reload for some older dates, will it interrupt the CDC post reload because of choosing only the selected dates from older months to refill the missing records or can it resume from the last timestamp?

<< If you are already using the MERGE and UPSERT option then you should not get an constraint error for duplicates or missing rows

Shouldn’t the error handling apply only to CDC and if we do the full load, the merge/upsert will not apply?

Thanks

Re: Prevent deleting archived data from the Source table

Attunity_user — Tue, 17 May 2022 18:21:25 GMT

Hi,

Only the new meaning newly created record or an older record missing in the target? I will test it again because the last time this option was tried (Do nothing in the Full Load settings) and with that setting, the missing record did not insert.

Thanks

Re: Prevent deleting archived data from the Source table

Heinvandenheuvel — Mon, 23 May 2022 00:29:49 GMT

@shashi_holla - this is trick y wording.

"Only the new data" means ALL the currently available data in the source table.

It odes NOT mean 'all data since last full load' or similar.

That 'new' data may indeed be brand spanking new, never seen before on target, but it is more likely to be a mix of data previously loaded by full load or cdc as wel as actual new data.

Hein.

Re: Prevent deleting archived data from the Source table

lyka — Mon, 23 May 2022 17:09:24 GMT

Hi @Attunity_user

Please let us know if any of the suggested solution worked. If you need further assistance, you can also open a support case

Thanks

Lyka

Re: Prevent deleting archived data from the Source table

Attunity_user — Tue, 24 May 2022 20:38:51 GMT

Hi Lyka,

Could you answer my question for the following because you mentioned "If you are already using the MERGE and UPSERT option then you should not get a constraint error for duplicates or missing rows."?

QA: Shouldn’t the error handling apply only to CDC and if we do the full load, the merge/upsert will not apply?

Thanks,

Re: Prevent deleting archived data from the Source table

lyka — Tue, 24 May 2022 20:43:02 GMT

Hi AU,

You are correct. Apply error handling will only apply to CDC. So for full load, if you are expecting duplicates, then you have to disable the constraint

Thanks

Lyka

Re: Prevent deleting archived data from the Source table

Dana_Baldwin — Fri, 27 May 2022 15:30:39 GMT

Hi @Attunity_user

If any responses have solved the issue, please mark it as "accept as solution".

Thanks in advance,

Dana

Re: Prevent deleting archived data from the Source table

Attunity_user — Fri, 27 May 2022 20:18:11 GMT

Hi Lyka,

Removing constraints will cause duplicates. The only way to reload data when the target table has historical data is either to remove existing data from the target or rename the existing table.

Thanks,