Change Tables vs Audit Tables

[guilherme-matte]

Hello team

I'm having a hard time understanding the difference between the usage of Change Tables and Audit Tables whenever selecting Store Changes in the Replicate Task

Both seem to have a similar implementation process, creating a table in the target database which stores the changes made during the CDC phase. The audit table being for audit purpose later on (right?). But what is main purpose of using the Change table mode? Does it provide advantages during the CDC phase? if so, why wouldn't I always use this option?

Could you provide some examples on this topic?

Thank you very much!

Matte

[john_wang · Accepted solution]

Hello @guilherme-matte ,

Thanks for reaching out!

There are 3 different apply changes modes in Replicate CDC:

1- Applying Changes

2- Using Change Tables

3- Using Audit Table

the major different between them are:

1- it's a 1:1 mapping between source tables and target tables. All changes of Source side table "A"  will be applied to target side corresponding table , by default it's "A" as well ( you can rename it freely). For example an UPDATE in source "A" will be repeated in target side "A".

2- it's a 1:1 mapping as well. source side table "A" changes will be stored in target side "A__ct", and you determine whether to replicate the changes for the final target table. "A__ct" can be regard as an interim table which store a given source table DML changes. For example an UPDATE in source "A" will be recorded likes an "UPDATE" SQL.

3- it's a M:1 mapping, that means all source tables changes will be stored in a single target side table, certainly in the audit table every row contains the metadata information eg task name, source table name and the corresponding DML changes.

Hope this helps.

Regards,

John.

[guilherme-matte]

Hello @john_wang !

Thank you for the insights. Still  regarding the Store Changes, is there any recommendations regarding when it should be enabled? does it provide a better CDC performance/utilisation or doest it only provide benefits when I have an specific usage for it? like for example troubleshooting and error finding.

Kind Regards!

[john_wang]

Hello @guilherme-matte ,

You are right! Store changes only provide benefits with specific usage for example you just want to get statistics of how many changes (insert/update/delete) were done but need not know the changes details etc.

In general if the replication is used to keep the source side and target side data synchronized then apply changes is good enough.

Feel free to let us know if you need any additional information,

John.

[mk_istts]

Hi, I am a new user on Qlik Replicate, I just want to know, what is the use case or example where I need to use Audit table?

Thank you very much!

[sureshkumar]

Hello @mk_istts 

If you need to verify that specific changes were replicated correctly or investigate any discrepancies between the source and target systems, the audit table provides a detailed history of these changes.

Regards,

Suresh

[mk_istts]

Thanks for the help!

How can I ensure that 100% of changes from the source data are replicated to the target? If replication is incomplete (e.g., only 90% of changes are replicated), how can I determine the cause and identify the non-replicated data? Should I use a Change Table or Audit Table, and why?

Additionally, in an Audit Table, change records (old and new data) are stored in JSON format (Using an Audit table #Using an Audit table | Qlik Replicate Help). Does this significantly increase storage compared to a Change Table?

Thank you very much!

[sureshkumar]

Hello @mk_istts 

Both Aduit table and CT table are used to track the data which missed during replication.

You can set up 2 test tasks, one for CT table and another for Audit table.

Do the DML operations from the source, observe the behavior in both tasks i.e what time the data has been transferred to the target/any data discrepancies.

If it's an update DML operations, you can observe before and after image of the data.

I am not sure the storage usage, CLOB column will be storage as JSON, kindly do test it in your test environment.

Regards,

Suresh

[mk_istts]

Thanks for the help! 

I have another question regarding Change Data Capture (CDC) in Qlik Replicate. I've read about the attrep_changes table. Is this simply the table name used when using a Changes Table, or is it a distinct feature? If it's a separate feature, how does it differ from Changes Tables?

Thanks!

[sureshkumar]

Hello @mk_istts 

The attrep_changes table reside in the target database. it is used a staging table for CDC processing when using batch apply:

the attrep_changes table is used as a temporary table to hold operations that are being done against the target tables. It makes the operations faster as we can do a single statement to execute many DML statements as once. Like doing an 'insert into table select * from attrep_changes' or 'delete from table where pk in select pk from attrep_changes'. This table is only used in CDC. Depending on the target endpoint type there is either one attrep_changes table for the entire task (attrep_changes<hash(task_uuid)>) or one table per target table for each task (attrep_changes<hash(task_uuid)>_<hash(table_id)>). This is due to performance and limitations of the various databases that Replicate support

The attrep_changes tables are created while the task is running. When a task is stopped, the attrep_changes tables are dropped.

The attrep_changes are erased by the task when it is stopped normally, if a task crashes those tables are not erased and you will have to perform a manual cleanup.

Regards,

Suresh