CompTIA Data+ (DA0-002)Data MiningMedium
A data engineer is designing an ETL pipeline for a new customer relationship management (CRM) system. The source system is an older, on-premise database, and the target is a cloud-based data warehouse. The business requires that all customer records, including new ones, updates to existing records, and deletions, are synchronized daily. Which data acquisition strategy is most efficient for capturing only the changes from the source system without transferring the entire dataset each day?
- ASnapshot replication
- BFull database dump and reload
- CManual data entry and validation
- DChange Data Capture (CDC)
Show answer & explanationAnswer & explanation
Correct answer: D. Change Data Capture (CDC)
Change Data Capture (CDC) is specifically designed to identify and capture only the data that has changed in the source system since the last extraction. This is highly efficient for synchronizing data daily without the overhead of transferring the entire dataset, which is crucial for large databases.
Why the other options are wrong
- A. Snapshot replication copies the entire state at a point in time, which is less efficient than CDC for incremental changes.
- B. A full dump and reload is inefficient for large datasets as it transfers all data daily, even unchanged records.
- C. Manual data entry is impractical and error-prone for daily synchronization of a CRM system.
Change Data Capture (CDC)
A set of software design patterns used to determine and track the data that has changed within a database since the last time data was extracted.
- Enables incremental loading, significantly reducing data transfer volume and processing time.
- Typically works by monitoring database transaction logs, using timestamps, or trigger-based mechanisms.
- Essential for real-time or near real-time data synchronization in ETL/ELT pipelines.
Memory trick: Acquiring data for ETL: CDC is like a smart tracker, only picking up new footprints.