Improving Change Data Capture performance(CDC) for sources without CDC ©

I am outlining a process improvement to identify and process Change data in sources where there is no CDC available/not enabled it in a faster and better way. This will not only improve the processing time significantly but also give better auditing capabilities.

Note: I am assuming that you have already identified the NEW and UPDATED records from the source table as compared to the Dimension table.

Process Flow ©

After identifying the NEW and UPDATED records from source follow Flow chart steps to achieve the improved performance results.

Figure 1: Flowchart of the CDC process with CDC compare ©

Note: I am creating 3 audit tables to record daily changes to the Source system

1. CDC_New - Contains all new records from the source system for that day. History is maintained in this table, so that records are always appended to this table.
2. CDC_Updt - Contains all the updated records from the source system for that day. Again, history is maintained so that we can keep track of all updated records
3. CDC_Msng - Contains all missing records which were present yesterday as compared to today (they are soft deleted from the system due to "human error")

I am comparing the identified updated and new records with the audit tables above to gain maximum performance and processing gains.

COPYRIGHT

This work is copyright ©. Apart from any use permitted under the Australian Copyright Act 1968, no part may be reproduced by any process, nor may any other exclusive right be exercised, without the permission of the author and developer - Vishant Bhat, vishant.bhat@gmail.com