How to Save Changes in Data Tables
Changes like inserts, updates, deletes are very common and businesses would naturally like to have a history of all the changes. The primary thing that needs to be done is to implement tools that ensure that all modifications are recorded and stored. This is where change data capture (CDC) has a very important role to play. It is the process of capturing changes made at the source of data and applying them throughout the organization. The goal of the CDC is to ensure data synchronicity by minimizing the resources required for ETL (extract, transform, and load) processes and is possible because CDC deals with data changes only.
Take any Data Warehouse (DWH) – it has to keep a track of all business measure changes. Hence, the ETL processes of DWH loading should be able to notice all data changes which have occurred in source operating systems during the business operations. This is ensured by change data capture which facilitates the insertion of new records, updating one or multiple fields of existing records, and deletion of records. When CDC records insertion, updating and deletion of activities applicable to a SQL Server table, all details of the changes made are available in an easily consumed relational format. Column information and the metadata that is required to apply the changes to a target environment is captured for the modified rows and stored in change tables that mirror the column structure of the tracked source tables. Hence, table-valued functions are provided to consumers that allow systematic access to changed data.
