Prevent deleting archived data from the Source table

[Attunity_user]

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,

AU

[Dana_Baldwin]

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

[Alan_Wang · Accepted solution]

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/C...

[Attunity_user]

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

[Attunity_user]

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

[Michael_Litz]

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

[lyka]

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

[shashi_holla]

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.

[Attunity_user]

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

[lyka]

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