💦What are CDC system and why you need it
To reduce the amount of data read from a corporate warehouse or lake, but always keep abreast of the latest changes, a CDC approach is used: Capture Changed Data or Change Data Capture. There are ready-made CDC tools: Oracle Golden Gate, Qlik Replicate and HVR, which are best suited for receiving data from frequently updated relational DBMSs. Also, data engineers create their own solutions: • CDC calculations using timestamps marking creation, update, and expiration in the source tables. Any process that inserts, updates, or deletes a row must also update the corresponding timestamp column. Hard deletion is not allowed. The drawbacks to this method are that you need to redesign the database structure to add a timestamp column, and the need for tight coupling between the original table and the ETL process code. • CDC computation using a negative query, when a link is created between the source and the target sink and a minus SQL query is executed to calculate the change log. This method is more of an anti-pattern, since only works if the source and destination databases are of the same type, and it also increases the amount of data moved. Both methods of handwritten CDC impose significant overhead on the original database. And special CDC tools reduce the load on the network and the source by analyzing the logs to calculate changes. However, the main disadvantage of off-the-shelf CDC solutions is their high cost. In addition, the source DBMS administrator must grant the CDC tool privileged access to the database log, which is perceived as not very loyal for security reasons.
https://towardsdatascience.com/change-data-capture-cdc-for-data-ingestion-ca81ff5934d2