Column Lineage Graphs for Stored Procedure Verification
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In data centers, modifying stored procedures can disrupt column lineage relationships due to the complexity of logical relationships and the use of wildcards, leading to difficulties in accurately identifying column information and verifying the correctness of changes.
Innovation Solution
A data processing method and device that obtain stored procedures from development, quality assurance, and production environments, analyze for wildcards, convert and restore procedures with complete column information, generate column lineage graphs, and compare them to identify differences and notify administrators.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of manufacture
If conventional sampling verification method is used to verify stored procedure modifications, then the verification process is simple to implement, but it is time-consuming and cannot prove correctness
Solution Approach 1:
The patent replaces manual sampling verification with an automated system that uses column lineage graphs to track and verify data flow. The system automatically generates and compares lineage graphs before and after stored procedure modifications, eliminating the need for manual sampling while providing comprehensive verification coverage.
Solution Approach 2:
The patent creates virtual copies of the data flow paths through column lineage graphs. Instead of actually executing verification queries on real data, the system copies the logical data flow relationships into graph structures that can be efficiently compared, reducing verification time while maintaining accuracy.
2Ease of operation
If wildcards are used in stored procedures to match any column, then the programming is simplified, but the column lineage relationship becomes difficult to track and verify
Solution Approach 1:
The patent introduces column lineage graphs as an intermediary representation that bridges the gap between wildcard usage and precise column tracking. The graphs resolve wildcard ambiguities by mapping them to specific column relationships in the data flow, allowing engineers to maintain simple wildcard syntax while achieving precise lineage tracking through the intermediate graph structure.
Solution Approach 2:
The patent adds a dimensional layer of abstraction by representing column relationships as graph structures. This new dimension allows the system to handle the complexity of wildcard matching in one layer while providing clear, detailed column lineage information in another layer, making both programming simplicity and verification precision achievable simultaneously.
3Adaptability or versatility
If multiple tables have columns with the same name, then the data structure becomes more flexible, but the difficulty of accurately modifying stored procedures increases
Solution Approach 1:
The patent segments the column identification process by incorporating table names into the column lineage graph nodes. Instead of relying solely on column names (which can be duplicated across tables), the system creates unique identifiers that combine table and column information, enabling precise tracking of which specific column is being modified even when multiple tables have columns with the same name.
4Ease of operation
If manual verification and user feedback are relied upon, then the system is simpler to operate, but wrong data may cause potential and invaluable losses
Solution Approach 1:
The patent implements an automated feedback mechanism that continuously monitors column lineage relationships and provides immediate verification results. The system automatically compares column lineage graphs before and after modifications, providing feedback on potential issues without requiring manual user inspection, thereby both maintaining ease of operation and significantly improving data correctness and reliability.
Data Source
AI summary
A data processing method applied in a data center is provided. The data processing method includes obtaining stored procedures of a development environment, a data quality assurance system environment and a production environment and column information of all tables, for each stored procedure, analyzing the stored procedure and determining whether the stored procedure contains a wildcard, converting and restoring the stored procedure into a restored stored procedure with complete column information based on the wildcard, generating column lineage graphs of the development environment, the data quality assurance system environment and the production environment respectively according to the restored stored procedures of the development environment, the data quality assurance system environment and the production environment, comparing the column lineage graphs of the development environment, the data quality assurance system environment and the production environment to generate a comparison result and performing a notification function according to the comparison result.