How to Periodically delete records from Change capture tables

[matheb04]

What is the option available to periodically truncate / (keep n number of records) the change capture table created by Qlik replicate?

[PGN]

It all depends on your specific needs and how you're using them.  We use an Informatica workflow to process the entries several time a day and it deletes the rows as it processes them.  I believe there are date/time stamps in the rows that facilitate the filtering for selecting which rows to process.  In some cases, we have workflows that process entire sets of CDC tables and then do a truncate table on them once the workflow completes. 

[KellyHobson]

Hey @matheb04 ,

Depending on your target endpoint type, another option to consider is Change Data Partitioning.

Task Settings-> Change Processing-> Store Changes Settings -> Change Data Partitioning

In a standard replication task, changes are replicated to the target in no particular order. Change Data Partitioning enables processing of Change Data from many tables in a consistent fashion. You can define the duration of partitions as well as the partitioning base time, thereby ensuring overall consistency of the partitioned data

Additionally, you can set a partition retention time which can be triggered periodically via API call.

Ref: https://help.qlik.com/en-US/replicate/November2021/Content/Global_Common/Content/SharedEMReplicate/C...

scroll to "Change Data Partitioning" section for more info like the supported endpoints

Best,

Kelly

[Michael_Litz · Accepted solution]

There is no built in method from replicate to manage the records in the change capture tables.

You will need to develop a process from outside of replicate.

Michael

[rendyajisoko]

Agreed, maybe you can make Store Procedure to monthly housekeeping your records at some tables. 

[lyka]

Hello,

Please let us know if any of the suggested solution works for you

Thanks

Lyka