Extract - Load (ETL/ELT)
vs Change Data Capture (CDC)
CDC is a method to increase the efficiency of data movements by capturing changes of data instead of periodic snapshots. The benefits: greater speed, less processing, less system resources.
Extract - Load
Extract - Load is a data integration pattern for transferring raw data from source systems to a target data storage system, typically a data warehouse or data lake. The process involves extracting a dataset from its source, loading it directly into the target storage, and then transforming the data for querying and analysis purposes.
How does it works?
ELT processes will periodically read a source system (database, CRM, ERP...) and copy datasets or portions of datasets its contains - this is called an Extract. They will then go on to write the extracted datasets to a destination system (generally a data warehouse or a data lake) as raw data - the Load part. Since the data is loaded raw, it often needs further processing - for instance to identify which records within the new datasets are in fact updates to existing records, or to apply logics to identify records that might have been deleted. This is the Transform part.
Benefits
- Scalability: the scalability approach for ETL involves handling burst workloads by waiting for the right moment and then moving large amounts of data at once. This method allows for significant data operations to be executed efficiently, accommodating large-scale data processing needs by leveraging periods of low activity to perform heavy data transfers.
- Well Understood: ETL pattern has been around since the 1970s, making it a well-established practice within the data management field. Its longevity has allowed data practitioners to gain a deep understanding of its mechanisms and best practices, ensuring a broad base of knowledge and expertise that can be tapped into for effective data handling.
- Integration Capabilities: ETL excels at integrating disparate data sources, allowing for the consolidation of varied data types and formats into a unified format in the target system. This is why ELT/ETL vendors will often boast several hundred connectors.
Limitations
- Cost: implementing and maintaining ETL processes can be expensive, especially as data volume and complexity grow. The need for significant computational resources to transform data before loading it into the target system can lead to increased operational costs.
- Orchestration: ETL processes are heavily dependent on triggers, and consistency between systems can only be achieved at specific points in time. This dependency can lead to challenges in maintaining continuous data integrity and can require complex orchestration to ensure data is synchronized across systems effectively.
- Source System Impact: ETL processes can generate a substantial load on source systems, especially during the extraction phase. This impact can affect the performance of the source systems, potentially disrupting operational activities and affecting system availability.
Change Data Capture
CDC is a pattern used to replicate the changes made to data from a source systems - rather than the data itself. CDC identifies changes such as new records, updates to existing records or records deleted. These changes are automatically pushed to a target system, (be it data stores or applications) ensuring it is consistantly up-to-date.
How does it works?
CDC leverages the internals of source systems to capture changes as they happen. This could mean consuming database logs, service events or webhooks. The captured changes are then transferred to the target system to be applied. By transporting only changes and directly applying then in the destination, CDC makes it possible to achieve data consistency accross multiple systems while moving less data in real-time.
Benefits
- Data Consistency: CDC ensures a high level of data consistency across systems by capturing and replicating data changes in near-real-time. This continuous synchronization supports accurate and up-to-date data across the enterprise, enhancing decision-making and operational processes.
- Scalability: CDC's approach to scalibility is based on continually and incrementally moving data as changes occur. This allows for scalable data integration and efficient handling of both large and small data volumes by transferring only the modified data, and scaling resource usage over time.
- Efficiency: by transferring only the changes made to the data, CDC minimizes the volume of data that needs to be moved and processed. This efficiency reduces network and storage requirements, leading to lower costs and faster data availability. It also significantly reduces the load on source systems compared to full-scale data extraction methods.
- Real-time Data Replication: CDC facilitates real-time data replication, enabling immediate data availability for analysis and decision-making. This capability supports dynamic business environments where timely information is crucial for competitiveness and operational efficiency.
Limitations
- Newer: while CDC is based on long established patterns, as a generally available technology, it is relatively newer. This novelty means that some data practitioners may not be as familiar with CDC, potentially requiring additional training and adaptation efforts.
- Source Features: the effectiveness of CDC is dependent on specific features of the source system, such as the availability of logs or webhooks. This dependency means that not all source systems are compatible with CDC, limiting its applicability in certain environments.
- Configuration: implementing CDC may require preparation and configuration of the source system to ensure changes are available for capture. This preparation can involve additional complexity with systems that are not originally designed with CDC in mind.
Free up to 1 million rows
Try for yourself! Beyond the 14-day trial, Popsink is free up to 1 million rows per month.
Connect with us on Slack
We know live data is hard and how complex it is to ensure self-service and reliability. Fortunately all these problems have solutions we have deployed in the past. Today we implement them for your benefit, making it easy for you to understand and react to operational events as they happen.
%201.svg)
