Data Governance Tool: Concept and Design of SQL-based Graphical Data Lineage System
Background introduction:
The importance of data to the enterprise does not need to be repeated;
At present, the storage format of text data is roughly divided into two types: structured and unstructured;
Due to the widespread use of relational databases, a large amount of data is stored and processed in a structured way. The most widely used processing method is the SQL language.
Pain points:
As far as the code itself is concerned, SQL is already concise enough. However, as the complexity of business requirements grows, the data processing process becomes longer and longer and criss-crosses. It is becoming more and more difficult to understand the relationship between the starting point and the ending point of the data.
At this time, if there is a problem with the data of a node, and you need to find the cause upward or analyze the impact downward, it is often only possible to analyze the script or schedule the job. Both methods have shortcomings, the former is time-consuming and error-prone, while the latter is not detailed enough. Data problems are only a pain point in data governance.
The solutions:
Establish a data lineage system. This allows users to easily and intuitively query the relationship between data nodes without analyzing the SQL code.
Scheme design requirements：
The data lineage system should meet the following functions:
Users can use it without special knowledge of SQL, and the operation is simple;
Users can quickly trace the data processing path up or down according to any data node;
The traceability granularity of data nodes should be as fine as possible.
System design:
For requirement 1:
In order to facilitate users to use, an operation interface is required.
In order to make the interface express the data lineage relationship clearly and intuitively, the system adopts the method of graphical SQL code to form a code vector diagram.
According to this vector diagram, users can clearly see each node of data processing.
For requirement 2:
The user can select any node in the vector diagram, and then click the button to realize this node as the starting point (end point), and trace the path of other nodes affected by it downward (upward).
The affected node paths should be marked with different colors in the vector diagram for easy identification.
For requirement 3:
The smallest granularity of data processed by database SQL is fields.
Therefore, the minimum granularity of data nodes traceable by the data lineage system should also be at the field level.
In order to achieve this goal, it is necessary to analyze the SQL code in detail to form the data lineage relationship information at the table level and the field level.
Conclusion
Thank you for reading my article and I hope you’ve enjoyed it. If you want to learn more about the data lineage, I would like to advise you to visit Gudu SQLFlow for more information.
Gudu SQLFlow, as a data lineage analysis tool, can not only analyze SQL script files, obtain data lineage, and perform visual display, but also allow users to provide data lineage in CSV format and perform visual display.