LineageX : A Column Lineage Extraction System for SQL

Shi Heng Zhang, Zhengjie Miao, Jiannan Wang · 2025

Read LineageX : A Column Lineage Extraction System for SQL by Shi Heng Zhang, Zhengjie Miao, Jiannan Wang (2025) in the Tower of Babel, a free online library of public-domain works preserved in clean, searchable text. No sign-up. The full text is served as a plain-text file at a predictable URL, where text is present: https://antarctic-labs.com/tower-of-babel/arxiv-2505-23133.txt.

Introduction

Shi Heng Zhang, Zhengjie Miao, and Jiannan Wang present LineageX, a tool that tracks how each column of a data warehouse is created and changed. Their 2025 paper notes that as company data grows, knowing the history of each column helps with quality checks, storage cleanup, and moving workflows. Older tools either had to join the running of queries or gave results that missed too much.

LineageX is a light Python library that reads SQL queries on its own and infers the lineage of each column. It walks the parse tree of a query and resolves unclear cases to reach high coverage and accuracy. An interactive display lets users view the lineage as a graph. The paper walks through use cases such as building those graphs and hunting down data quality problems. The code is open source.

Catalog record

CREATORShi Heng Zhang, Zhengjie Miao, Jiannan Wang
YEAR2025
COLLECTIONPAPERS
FORMATTXT
FILE SIZE20 KB
VERSION1.0
LICENSEhttp://creativecommons.org/licenses/by/4.0/
RIGHTSCC BY
SOURCEarXiv

Source: https://arxiv.org/abs/2505-23133

Browse the full library · About the Tower of Babel

Full text

The opening of the work, from the library's hosted text:

LineageX : A Column Lineage Extraction System for SQL Shi Heng Zhang, Zhengjie Miao, Jiannan Wang Abstract As enterprise data grows in size and complexity, column-level data lineage, which records the creation, transformation, and reference of each column in the warehouse, has been the key to effective data governance that assists tasks like data quality monitoring, storage refactoring, and workflow migration. Unfortunately, existing systems introduce overheads by integration with query execution or fail to achieve satisfying accuracy for column lineage. In this paper, we demonstrate LineageX , a lightweight Python library that infers column-level lineage from SQL queries and visualizes it through an interactive interface. LineageX achieves high coverage and accuracy for column lineage extraction by intelligently traversing query parse trees and handling ambiguities. The demonstration walks through use cases of building lineage graphs and troubleshooting data quality issues. LineageX is open sourced at https://github.com/sfu-db/lineagex and our video demonstration is at https://youtu.be/5LaBBDDitlw I Introduction Data governance has become increasingly crucial as data is becoming larger and more complex in enterprise data warehouses. For example, in an organization’s data pipeline, data flows from upstream artifacts to downstream services, which may be built by various teams that know little about other teams’ work and often introduce challenges when anyone wants to change their data. In this case, lineage [ 9 , 10 ] , especially finer-grained column-level lineage , is often needed for simplifying the impact analysis of such a change, i.e., how a change in the upstream would affect the downstream. In another real-world scenario, column-level lineage can help identify how sensitive data flows throughout the entire pipeline, thereby improving the overall data quality and validating data compliance with regulations, such as GDPR and HIPAA [ 7 ] . While capturing lineage information in DBMS has been studied extensively in the database community [ 2 , 1 , 11 ] , the need remains to curate the lineage information from static analysis of queries (without executing the queries). On the one hand, existing systems or tools would introduce large overheads by either modifying the database internals [ 2 , 1 ] or rewriting the queries to store the lineage information [ 11 , 12 ] . On the other hand, different data warehouse users may need to disaggregate the lineage extraction workflow from query execution to simplify their collaboration, as shown in the following example. Fig. 1: Lineage extraction from query logs without a database connection. Example 1 An online shop uses a data warehouse to store and analyze its customer and transaction data. There is a view, webinfo , which keeps track of user activities, and another view, info , connects the users’ website activities (stored in view webact ) to their orders, which may be used for recommendation purposes. However, the online shop owner decides to edit the page column of the web table and requests an impact analysis from the data warehouse provider. ⬇ 1 Q_{1}= CREATE VIEW info AS 2 SELECT c . name , c . age , o . oid , w .* 3 FROM customers c JOIN orders o ON c . cid = o . cid 4 JOIN webact w ON c . cid = w . wcid ; 5 Q_{2}= CREATE VIEW webact AS 6 SELECT w . wcid , w . wdate , w . wpage , w . wreg 7 FROM webinfo w 8 INTERSECT 9 SELECT w1 . cid , w1 . date , w1 . page , w1 . reg 10 FROM web w1 ; 11 Q_{3}= CREATE VIEW webinfo AS 12 SELECT c . cid AS wcid , w . date AS wdate , 13 w . page AS wpage , w . reg AS wreg 14 FROM customers c JOIN web w ON c . cid = w . cid 15 WHERE EXTRACT ( YEAR from w . date ) = 2022; Due to access control and privacy regulations, the engineer from the data warehouse provider can only access the log of database queries instead of the DBMS. The task is prone to being time-consuming and may involve tracing unnecessary columns without a comprehensive data flow overview. To address this, the engineer considers using tools like SQLLineage [ 6 ] to extract and visualize the lineage graph. Although it can generate a lineage graph as shown in Figure 2 , there are a few issues with the column lineage. One is that the node of webact erroneously includes four extra columns, highlighted in a solid red rectangle. Another error arises for view info due to the SELECT * operation, which makes it unable to match the output columns to columns in webact . Instead, it would return an erroneous entry of webact.* to info.* (in solid red rectangle) while omitting the four correct columns from webact . It would also return fewer columns for the view info (in dashed red rectangle) and completely ignore the edges connecting webact to it (the yellow dashed arrows). If the engineer used the information from this lineage graph, then not only an erroneous column ( webact.page ) is provided, but the results also miss actual impacted columns from the webact and info table. As we will demonstrate, our approach is able to handle statements like SELECT w.* and capture all columns and their dependencies missed by prior tools. Fig. 2 : The lineage graph for Example 1 . Existing tools like SQLLineage [ 6 ] would miss columns in the dashed red rectangle and return wrong entries in the solid red rectangle, while the yellow is the correct lineage Curating lineage information from query logs is also advantageous for debugging data quality issues, enhancing data governance, refactoring data, and providing impact analysis. However, existing tools [ 5 , 6 ] often fail to accurately infer column lineage due to the absence of metadata. To support developers and analysts in extracting lineage without the overhead of running queries in DBMS, we develop a lightweight Python library, LineageX , which constructs a column-level lineage graph from the set of query definitions and provides concise visualizations of how data flows in the DBMS. Challenges. LineageX achieves accurate column-level lineage extraction by addressing the following two challenges. First is the variety of SQL features , especially for features that involve intermediate results or introduce column ambiguity. For example, Common Table Expressions (CTEs) and subqueries generate intermediate results that the output columns depend on, while the desired lineage should only reveal the source tables and columns. Set operations may introduce column ambiguity, primarily due to the lack of table prefixes. Second, when there is an absence of metadata from the DBMS on each table’s columns, e.g., when the query uses SELECT * or refers to a column without its table prefix, it may introduce ambiguities. Thus, prior works fail to trace the output columns when the * symbol exists and cannot identify their original table without an explicit table prefix. Our contributions. For the first challenge, LineageX uses a SQL parser to obtain the queries’ abstract syntax trees (AST) and perform an intelligently designed traversal on the AST with a comprehensive set of rules to identify column dependencies . LineageX addresses the second challenge by

Read the complete text