Dynamic Node Search and Layered Data Lineage Governance Based on Project SQL Evidence

Status: methodology note. This document summarizes the implementation path of the current SQLGlot project, its governance principles, and its correspondence with data provenance research and industry frameworks; it does not constitute production execution, runtime lineage, or confirmation of business definitions.

One-sentence definition

Dynamic node search and layered data lineage governance based on project SQL evidence means: in a frozen project SQL snapshot, starting from the target under analysis, automatically searching for linkable Dataset Nodes, writers, and upstream dependency paths; then running a field-level DFS inside the proven node paths and SQL scope. For relations that cannot be uniquely proven from project SQL evidence, the system preserves ambiguous evidence, and human review converts technical inference into auditable governance conclusions.

Its goal is not to complete lineage at any cost, but to guarantee that every published conclusion can be traced back to a specific SQL statement, expression, location, and review decision; what cannot be proven must state its boundary explicitly.

Main method chain

Freeze the project SQL snapshot and hash
  -> parse SQL AST / statement / scope
  -> dataset abstraction and node classification
  -> Dataset Node dependency graph inside the project scope and writer candidate search
  -> dynamic search for upstream nodes and paths from the target node
  -> field-level DFS inside the proven node paths
  -> separate delivery of VALUE lineage, control evidence, source classification, and business explanation
  -> human review, versioning, and reproducible generation

"Dynamic" here means target-driven search and traversal within the project: the analyzer automatically locates nodes in the frozen project SQL snapshot according to the target under analysis, searches reachable writers and upstream edges, and then expands field paths. It does not mean reading production run events, executing dynamic SQL, or treating the real data contribution of a particular run as proven fact.

Layer 1: project SQL parsing and dataset abstraction

The first thing processed is not fields, but datasets that can serve as inputs, intermediate results, or outputs in the frozen project SQL snapshot. Parsing is bounded by the statement and the SQLGlot scope, identifying the collections actually referenced by the current FROM, JOIN, CTE, subquery, or set operation.

Typical sources of datasets include:

• physical tables or known write targets;
• external tables in local SQL inputs that have no writer;
• CTEs;
• derived subqueries;
• query results of SELECT;
• results of set operations such as UNION.

A stable identity cannot rely on table names alone. Tables with the same name, logical tables spanning environments, and multiple statements or scopes inside one file may all differ, so node identity must be tied to traceable evidence such as the source SQL, the statement, and the scope.

Layer 2: Dataset Node classification, writer search, and the dependency graph

Each dataset is classified as a Dataset Node, and directed dependency edges are built from the project SQL structure: reads, CTE bindings, derivations, set-operation branches, write targets, and command targets. The analyzer searches for linkable nodes and writer candidates within the project scope according to the target; what forms at this point is a dataset-level lineage skeleton, which answers:

Which upstream datasets does a target table or logical dataset depend on, and which segment of SQL establishes each dependency?

The node graph expresses only dependencies that are actually referenced; a CTE that is "visible in scope but not used by the current statement" must not be recorded as an input. Binding the writer of a cross-file logical table must likewise remain an independently human-editable review decision: automatic discovery of candidates improves efficiency, but the program must not override a human REJECTED decision.

Layer 3: target-driven node search and DFS

With the Dataset Node graph in place, the analyzer searches dynamically upstream from a target node and performs DFS. This step handles overall structure rather than field semantics:

• expansion of CTEs, derived queries, and nested scopes;
• set-operation branches such as UNION;
• cross-file writer binding;
• external dataset endpoints;
• termination and diagnostics for cycles and unresolvable structures.

The output of node search and DFS is a set of upstream paths constrained by the project SQL structure. It first bounds "which datasets the value may have passed through", providing a legal context for field tracing; field DFS should not detach from this graph level and search all SQL by same-named fields.

Layer 4: field-level DFS

Field DFS starts from (root_node_id, field_name). It locates the actual projection expression of the field in the current node, extracts the input fields referenced by the expression, and then recurses upward only along input edges proven by the node-level DFS.

Every visit records a unique tree_id + visit_id, the parent visit, the expression, the input fields, the node edge, the source file, the statement, the scope, and the SQL location. Therefore, same-named fields entering through different edges or branches are never confused in an audit.

Field-level DFS must handle the following SQL structures conservatively:

• SELECT *: passed through only when field projection evidence can locate the source uniquely;
• unqualified columns: all candidates are kept when the binding is not unique in a multi-input scope;
• UNION: apply the outer projection expression first, then enter the set branches;
• dynamic environment relations: without a local writer they become external endpoints carrying a dynamic flag;
• cycles, missing fields, or non-enumerable projections: stop with an explicit terminal reason.

AMBIGUOUS_STAR_SOURCES and AMBIGUOUS_COLUMN_SOURCES are diagnostic evidence, not confirmed VALUE sources. Their role is to state completely "what static analysis cannot prove", rather than to fabricate a single path that looks complete but cannot be audited.

Governance controls: from technical lineage to usable conclusions

1. Evidence first, reproducibility always

The input SQL, the review table, the parser version, the generator version, and the artifact hashes together form the evidence chain of one analysis. Any result should be locatable to a source file, a SQL line range, and a generation rule; after a human edits the writer review table, the downstream field graph must be rebuilt starting from the node graph.

2. Separating VALUE, control conditions, and business conclusions

The origin of a field value and the conditions under which the data applies are not the same concept and must be stored and delivered separately:

• VALUE lineage: the upstream fields, constants, expressions, and processing path of a field value;
• semantic control evidence: CASE/IF conditions, provable inner-join conditions, and the row-qualification conditions of WHERE/HAVING/QUALIFY;
• source classification: S1--S4 derived only from the confirmed VALUE endpoint system mapping, without disguising control conditions, table-name patterns, or business common sense as source evidence;
• business explanation: field formation rules and boundaries explained on the basis of frozen technical evidence, with no template or model guessing business facts.

3. Human decision-making in the loop

Automation handles parsing, candidate discovery, evidence storage, and repeated verification; humans confirm cross-environment writers, source-system ownership, business meaning, and production facts. A review result should carry a status, notes, responsibility attribution, and an effective time, and be linked to the generated artifacts.

4. Layered delivery

The same evidence chain should provide artifacts of different granularity for different readers:

• nodes, edges, field trees, and JSONL details: for programs, audits, and technical troubleshooting;
• field origin and classification CSVs: for data owners, data governance, and change-impact analysis;
• business Markdown: formation rules, applicability conditions, association rules, and original SQL locations, written for business readers.

Research and industry positioning

This is not an isolated SQL parsing trick, but a combination of three mature lines of thought into a practical method suited to data governance:

1. Data Provenance / Data Lineage in the database field: explaining where data comes from and how it was formed through transformation and derivation relations;
2. SQL AST and column-level lineage engineering: extracting definite dependencies from SQL scope, projection expressions, and input bindings;
3. data governance frameworks: connecting technical evidence to responsibility, review, impact analysis, quality, and compliance controls.

The most direct academic counterpart is data provenance. Cui and Widom's Lineage Tracing for General Data Warehouse Transformations studies how to trace data back to original inputs in general data warehouse transformation graphs, and gives tracing algorithms for linear and general acyclic transformation graphs. It is highly isomorphic to the path of "abstract datasets first, build the node graph, then DFS"; the difference is that the research is more tuple-level, runtime, or general-purpose, while this project focuses on static field-level analysis of SQL source code. Paper

Another theoretical foundation is why / where / how provenance: where explains where a result field or value came from, why explains which inputs jointly caused the result, and how explains what processing combination it went through. Field DFS takes static SQL as evidence and covers field origins and expression processing; CASE, UNION, functions, constants, and definite input-field branches all constitute an auditable how explanation. Survey: Provenance in Databases: Why, How, and Where; Early theory: Why and Where: A Characterization of Data Provenance.

On the engineering side, the recent LINEAGEX also extracts column-level lineage by walking SQL parse trees, handles ambiguity, and is used for data quality investigation. The additional governance design of this project is to first form a stable Dataset Node graph from the project SQL snapshot, then automatically search reachable nodes, writers, and upstream paths from the target, and run the field DFS inside existing node relations and scope constraints, rather than extracting column dependencies directly from isolated SQL statements.

W3C PROV is the cross-system semantic model most worth borrowing: a Dataset Node can map to an Entity, a SQL statement, scope, or ETL step to an Activity, and a writer reviewer, data owner, or generator to an Agent. Adding these three kinds of objects and the used, generated, derived, and associated relations to the existing model lets technical lineage further support cross-system exchange, accountability, and audit expression. PROV-DM / PROV-O

OpenLineage is similar to this method but not the same: it centres on runtime Job, Run, and Dataset events, recording the inputs, outputs, versions, and statuses actually executed; this method takes the design-time project SQL snapshot as evidence, dynamically searches nodes and paths inside the project, and explains CTEs, expressions, field mappings, and potential ambiguity. The two should be used as complements: the project node graph and field graph explain the design, run events verify actual execution. OpenLineage Specification

DAMA-DMBOK and EDM Council DCAM do not specify DFS algorithms, yet they define why and how this capability enters a data governance system, including metadata, data architecture, quality, the control environment, accountability, change impact, and audit. So the SQLGlot method is the technical lineage capability foundation, and DAMA/DCAM is the framework for organizing, using, and measuring that capability. DAMA-DMBOK / EDM Council DCAM

The governance character of this method lies not in simply using DFS, but in the following four principles:

1. datasets before fields, to control complexity;
2. evidence before conclusions, never completing lineage by guesswork;
3. automatic discovery separated from human approval, preserving the accountability boundary;
4. layered delivery of technical detail, classification results, and business explanation.

Relationship to papers, standards, and industry frameworks

Applicability boundary

What this method can prove is the dependency and processing evidence supported by the frozen project SQL snapshot, not every business fact. Node search inside the project is dynamic, but the evidence range is still bounded by that snapshot; the following require separate governance or runtime verification:

• rows actually read and the actual contribution ratio under parameters, partitions, and run conditions;
• logic inside stored procedures, UDFs, dynamic SQL, external tasks, or uncollected scripts;
• source systems, data owners, metric definitions, and the business meaning of fields;
• whether multi-system origins such as S4 actually contributed together in some production run;
• data quality, permissions, sensitive-data classification, and access-compliance conclusions.

Therefore, lineage by dynamic node search based on project SQL evidence should be treated as an auditable technical foundation for data governance, not as a single source of truth that replaces production observation, data accountability, or human business confirmation.

Recommended formal statement

We recommend the following definition for external use:

This project adopts the dynamic node search and layered data lineage governance method based on project SQL evidence: first freeze the project SQL snapshot, abstract the physical tables, external tables, CTEs, derived queries, and set-operation results inside it into traceable dataset nodes, and build a set-level dependency graph; then automatically search the reachable writers, nodes, and upstream paths inside the project from the target node, and run a field-level DFS under the constraints of those paths and the SQL scope. For relations that cannot be uniquely proven from project SQL evidence, the system preserves ambiguous and termination evidence, and keeps a human review gate for critical logical-table bindings and business attribution, thereby converting project SQL analysis results into reproducible, traceable, and auditable data governance deliverables.