ETL Pipeline Data Flow Optimization via Column Segmentation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In traditional ETL pipelines, database columns that are not undergoing transformations are copied across different stages, leading to significant input/output operations and resource utilization, resulting in slower performance.
Innovation Solution
Database columns are grouped into a non-processing schema, and a large object data type is created to reference this schema, with an identifier inserted in the data processing stages to avoid copying, allowing these columns to pass-through from the source to the target database, reducing processing resources used.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If database columns are copied across all ETL stages, then data completeness is maintained, but processing resources and I/O operations increase significantly
Solution Approach 1:
The patent segments database columns into two distinct groups: transformed columns and non-transformed columns. This segmentation allows the ETL system to apply different handling strategies to each group, copying only the transformed columns through the pipeline while using identifiers for non-transformed columns, thereby reducing unnecessary I/O operations while maintaining data completeness.
Solution Approach 2:
The patent extracts non-transformed columns from the main data flow and replaces them with lightweight identifiers. This extraction removes the burden of copying large volumes of unchanged data through multiple ETL stages, significantly reducing processing resources and I/O operations while preserving the ability to reconstruct complete data when needed.
2Reliability
If all database columns are copied through data processing stages, then data availability is ensured, but processing time increases
Solution Approach 1:
By segmenting columns into transformed and non-transformed categories, the system processes only necessary columns through time-consuming transformation stages, reducing overall processing time while ensuring transformed columns remain fully available for downstream operations.
Solution Approach 2:
The patent uses lightweight identifiers as substitutes for copying actual non-transformed column data. These identifiers serve as references that preserve data availability without the time cost of physical data copying, allowing rapid reconstruction of complete datasets when needed.
3Manufacturing precision
If database columns undergo transformation processing, then data quality improves, but resource utilization increases
Solution Approach 1:
The patent segments the data processing workload by identifying which columns require transformation and which do not. Only transformed columns undergo resource-intensive processing operations, while non-transformed columns are handled via identifier references, significantly reducing overall resource utilization while maintaining data quality for columns that need it.
Solution Approach 2:
The patent applies transformation processing partially—only to the extent necessary for data quality improvement. By transforming only the columns that require it and using identifiers for the rest, the system avoids excessive resource consumption while still achieving the necessary data quality standards for analytical workloads.
Data Source
AI summary
A computer-implemented method, system and computer program product for optimizing a flow of data within transform, load (ETL) data processing pipelines. Database columns from a source database that are to be transformed as well as not transformed in the data processing stages of a processing segment of an ETL data processing pipeline are identified. Those database columns that are to be transformed are grouped into a processing schema. Transformations are then performed on the database columns of the processing schema. Those database columns that are not to be transformed are grouped into a non-processing schema. A large object data type (LOB) is then created to reference the non-processing schema. An identifier is created and inserted in the data processing stages to identify the LOB in replace of the database columns that are not to be transformed thereby avoiding the copying of the database columns that are not to be transformed.