ETL Agent Buffering for Robust Cloud Data Transfer
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing ETL systems lack efficiency and robustness in the data loading phase, leading to delays and potential halts due to network latency, interruptions, and hardware/software issues in the target system.
Innovation Solution
The proposed solution involves a computing system with an ETL agent that monitors the status of the target application and communication channels, storing residual data records in local buffers during disruptions. When connections are restored, the ETL agent resumes data loading from the buffers, avoiding the need to re-extract and re-transform data.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If data loading is performed directly to the target system without buffering, then data transfer speed is improved, but system reliability deteriorates due to network interruptions and target system unavailability
Solution Approach 1:
A buffer system is introduced as an intermediary between the data source and target system. The buffer temporarily stores extracted data when the target system is unavailable or when network interruptions occur, allowing continuous extraction while ensuring reliable data transfer when conditions permit.
Solution Approach 2:
Data is extracted and stored in the buffer in advance, before it is needed by the target system. This preliminary action allows the extraction process to continue uninterrupted, and the data is then transferred to the target system when it becomes available, improving both speed and reliability.
2Reliability
If the ETL process waits for target system availability before loading data, then data loading reliability is improved, but productivity deteriorates due to idle time during target system unavailability
Solution Approach 1:
The extraction process continues continuously without interruption, storing data in the buffer. This eliminates idle time during target system unavailability while ensuring data is transferred reliably when the target system is available, thus maintaining both productivity and reliability.
Solution Approach 2:
The buffer acts as a mediator that decouples the extraction process from the data loading process. This allows extraction to proceed continuously while loading occurs asynchronously when the target system is available, resolving the contradiction between reliability and productivity.
3Loss of information
If data is re-extracted and re-transformed after network interruptions, then data completeness is improved, but loss of time increases due to redundant processing
Solution Approach 1:
Data is extracted and stored in the buffer in advance, before network interruptions or target system unavailability occurs. When interruptions end, the pre-extracted data is simply transferred from the buffer to the target system, eliminating the need for redundant extraction and transformation while ensuring data completeness.
Solution Approach 2:
Instead of re-extracting and re-transforming data after interruptions, the system uses copies of the already-extracted data stored in the buffer. This copying approach maintains data completeness while avoiding the time loss associated with redundant processing.
Data Source
AI summary
A computer implemented method can obtain, in a data transfer system, a plurality of data records from data sources and monitor operating status of a target application running on a target machine. Responsive to finding that the target application stops operating, the method can send one or more first data records from the data transfer system to the target machine and store the first data records in a target buffer on the target machine. Responsive to finding that the target application resumes operating, the method can send one or more second data records from the data transfer system to the target machine and directly store the second data records in a data repository. While sending the one or more second data records, the method can transfer the one or more first data records from the target buffer to the one or more target databases.