Parallel ETL Loading With Unified Transaction Verification
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing ETL processes for heterogeneous OLTP systems lack efficient transaction control and result in poor system performance due to serial execution when loading and post-processing data across multiple tables.
Innovation Solution
Implementing a single transaction with a single transaction identifier to coordinate parallel OS processes for ETL operations, allowing verification of success across multiple target tables within a unified transaction.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If separate processes are used for each table's ETL operation, then transaction control is maintained, but system performance deteriorates due to serial execution
Solution Approach 1:
The patent segments the ETL process into multiple independent worker processes, each handling a specific table or data flow. These segmented processes can execute in parallel while still being coordinated under a single transaction identifier, thus maintaining transaction control while improving performance through parallelization.
Solution Approach 2:
The patent merges multiple table-specific ETL operations into a single coordinated transaction by using a shared transaction identifier. This allows previously separate serial operations to be combined into a parallel execution model while maintaining the integrity of the overall transaction through the coordination layer.
2Productivity
If parallel execution is implemented across multiple tables, then system performance improves, but transaction control is lost
Solution Approach 1:
The patent introduces a coordinator process as an intermediary between the parallel worker processes and the transaction management system. This coordinator manages the transaction identifier distribution and collects results from parallel workers, enabling transaction control to be maintained across parallel operations without becoming a bottleneck.
Solution Approach 2:
The patent adds a new dimension to transaction control by introducing a hierarchical transaction model where a parent transaction identifier coordinates multiple child operations running in parallel. This dimensional expansion allows transaction integrity to be maintained at the orchestration level while permitting parallel execution at the operational level.
3Reliability
If multiple ETL operations are coordinated as a single transaction, then transaction control is improved, but process complexity increases
Solution Approach 1:
The patent implements self-service mechanisms where worker processes automatically register themselves with the coordinator, receive their assigned transaction identifiers, and report results back without requiring manual intervention. The coordinator automatically manages the coordination logic, reducing the complexity burden on operators while maintaining strong transaction control.
Solution Approach 2:
The patent performs preliminary actions by pre-establishing the coordination framework and transaction identifier management system before parallel operations begin. This upfront setup includes defining the coordinator-worker relationships and transaction protocols, which simplifies the actual parallel execution phase by having all coordination rules predetermined.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A computer implemented system and method of performing parallel load operations during an extract, transform, load (ETL) operation. The system generates a single transaction having multiple processes, where each process loads into one target table. The processes are executed in parallel, and the system performs verification on the single transaction.