ETL Validation Optimization via Exposure Thresholds
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The increasing volume and velocity of input data in data warehouses lead to significant resource consumption and increased costs for enterprises, resulting in prolonged validation times that negatively impact user experience.
Innovation Solution
A system and method that optimize data validations by selecting critical fields using a data dictionary, analyzing historical validation results to determine probability of failure, calculating exposure values, and comparing them to a threshold to decide which validations to skip, thereby reducing unnecessary validation processes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If all validations are executed for input data, then data quality and reliability are improved, but resource consumption and validation time increase significantly
Solution Approach 1:
The patent applies partial action by selectively executing only a subset of validations based on calculated exposure values. Instead of running all validations uniformly, the system identifies and executes only those validations with exposure values above a threshold, thereby reducing validation time while maintaining data quality for critical fields.
Solution Approach 2:
The patent changes the parameter of validation execution from a static all-or-nothing approach to a dynamic selective approach. By calculating exposure values that combine failure probability and business impact, the system adapts which validations to execute based on data characteristics, transforming the validation process from rigid to flexible and efficient.
2Reliability
If all validations are executed for input data, then data quality and reliability are improved, but resource consumption increases
Solution Approach 1:
The system performs only the necessary subset of validations by filtering based on exposure values. This partial action principle reduces CPU cycles, memory usage, and other computational resources consumed during the validation process, while still ensuring data quality for high-exposure fields.
Solution Approach 2:
The validation execution parameter is changed from fixed to dynamic based on exposure calculation. The system transforms resource allocation by executing validations selectively according to their exposure values, thereby optimizing resource consumption while maintaining reliability for critical data elements.
3Loss of energy
If validations are skipped to reduce resource consumption, then operational costs decrease, but data quality may be compromised
Solution Approach 1:
The system introduces exposure value as a new parameter that combines failure probability and business impact. By changing from uniform validation to exposure-based selective validation, the system reduces operational costs for low-exposure fields while maintaining data quality for high-exposure fields, achieving cost optimization without compromising overall reliability.
Solution Approach 2:
The patent applies local quality by treating different data fields differently based on their exposure values. Critical fields with high exposure values receive full validation to ensure data quality, while non-critical fields with low exposure values undergo reduced or no validation, thereby reducing operational costs without compromising the quality of important data.
4Measurement precision
If comprehensive validations are performed, then data accuracy is improved, but processing speed decreases
Solution Approach 1:
The system applies partial action by executing only the necessary validations based on exposure values. This selective approach maintains data accuracy for high-exposure fields while significantly improving processing speed by skipping validations for low-exposure fields, thereby resolving the contradiction between accuracy and speed.
Solution Approach 2:
The validation execution parameter is transformed from static to dynamic based on exposure calculation. By changing which validations are executed according to their exposure values, the system achieves both high data accuracy for critical fields and high processing speed overall, eliminating the trade-off between these two parameters.
Data Source
AI summary
This disclosure relates generally to a system and method to optimize a plurality of validations in an ETL workflow. The ETL workflow implements all types of validations expecting that there is erroneous data and that the validation would fail. Therefore, the actual execution of validation is made conditional based on a switch that can be turned on (validation is performed) or off (validation is skipped). The output of each instance of execution is recorded continuously at a repository. The means of identifying a specific validation that is eligible for being turned off requires a thorough analysis of all validations performed so far in the history. The thorough analysis includes how many times a validation has been performed on a particular field and to calculate an exposure value which is compared with a threshold value of exposure and determine specific validation that is eligible to be turned off.