Change Data Capture Activities to a SQL Server Table

 Change Data Capture is a set of software design patterns to track and determine data that has changed to enable taking necessary action on it. The CDC approach is data integration based on the principle of identification, capture, and delivery of the changes made to a source enterprise database.

Change Data Capture most often occurs in data warehouse environments. This is because capturing and preserving the state of the data is the basic function of a data warehouse though CDC can be used in any data repository or database system.

Change Data Capture records insert, update, and delete activity that applies to a SQL Server table and puts the details of the changes available in a relational user-friendly format. Any column information and the metadata required for recording the changes to a target database are captured for the modified rows. These are stored in change tables that mirror the column structure of the tracked source tables.

The most simplified change data capture form is where one computer system has data that is believed to have changed over time and another computer system needs to take some action based on that changed data. The first is called the source and the second, the target. Hypothetically, the source and the target can be the same system physically but that would not have any effect on the design pattern because several CDC solutions can exist in a single system.

One of the most optimized CDC applications is ETL (Extract, Transform, Load) that loads change data incrementally from SQL Server tables to a data warehouse or a data mart. There are several ways that CDC mechanisms can be set up by system developers.



Comments

Post a Comment

Popular posts from this blog

Oracle CDC Software and Technology

Image
 The Change Data Capture technology was first launched as a built-in feature of the Oracle database in the 9i version. It was very effective in tracking and recording all changes made in user tables in a database. These changes were stored in change tables for use in ETL applications. The data could then be processed and loaded into other data warehouses and databases. The first release of the Oracle Change Data Capture functioned through triggers placed in the source. However, this method did not find favor with the DBAs as it was too invasive.   Subsequently, a new and improved version of the Oracle Change Data Capture, named Oracle Streams,was introduced with the 10g version of the Oracle database. It leveraged the redo logs of the source database and the built-in replication tool of Oracle. Streams was a very optimized process to identify and move change data to a target database without adversely affecting the performance of the source database. Unfortunately, despite its efficie
Read more

Exploring the SAP Data Lake – Architecture and Features

Image
 SAP launched the HANA Data Lake in April 2020 in a bid to further strengthen its cloud-based ecosystem and provide customers with a cost-effective and highly optimized storage system. The package of benefits included a native storage extension and a relational SAP data lake. Soon the data lake was considered to be in the same league with the leaders in the niche, namely Amazon S3 and Microsoft Azure because of its powerful data processing capabilities and functionalities.  The architecture of the SAP data lake is like a pyramid. At the top is stored data that is critical for businesses and used constantly. The cost of storing this data is, therefore, the highest on SAP data lake. The middle of the pyramid stores data that is not regularly used but important enough not to be deleted. This data is not as high performing as the top tier and access requirements are also quite low. The storage cost of this tier is significantly less. The bottom of the pyramid holds data that is hardly e
Read more