Optimizing Extract, Transform, and Load Process Using Change Data Capture

The concept of applying Extract, Transform, and Load (ETL) in data warehouses continues to evolve from initially being carried out through scheduling to the point where the ETL process can be conducted in near-real-time. The development of this concept is driven by resource intensive scheduled ETL processes, which involve reading data sources, transforming them to the desired format, and loading the transformed data into a data warehouse. The high resource requirements enforce to execution of the ETL process by scheduling it at specific times and in separate environments from the production environment. Consequently, data in the data warehouse is not available in real-time. In addition, scheduling operates without considering changes in the source data, resulting in the ETL process being executed even if there are no changes in the source data. In optimizing the ETL process, this research proposes the methodology of CDC (Change Data Capture) log-based approaches to monitor log transactions of changes such as insert, update, and delete operations in the source data. When changes occur in the source data, CDC captures and publishes information about these changes using a message broker concept. This information is received by the System Target, an application responsible for executing the ETL process based on the received change information. These approaches make the data in the data warehouse always up-to-date, and the ETL process will only be executed when changes occur. The purpose of this study is to explore an alternative approach to executing the ETL process to make it more efficient by implementing the CDC.