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.