sqlglot.lineage doesn't preserve the struct-field path on leaf nodes

[treff7es]

Summary

sqlglot.lineage doesn't preserve the struct-field path on leaf nodes. For a query like SELECT MIN(widget.metric.a, widget.metric.b) FROM tbl, both leaves come back as tbl.widget — the metric.a and metric.b parts are gone after to_node returns. v30.7.0 added on_node and Node.payload for consumer-side enrichment, which is the right pattern, but on_node(leaf) doesn't have access to the source column where that information lives.

Filing as an issue first to agree on the shape before sending a PR.

Repro

import sqlglot
import sqlglot.lineage as l
import sqlglot.optimizer.qualify as q
import sqlglot.optimizer.scope as scp

sql = (
    "SELECT MIN(widget.metric.metric_a, widget.metric.metric_b) AS x "
    "FROM tbl"
)
parsed = sqlglot.parse_one(sql, dialect="bigquery")
qualified = q.qualify(
    parsed,
    dialect="bigquery",
    schema=None,
    validate_qualify_columns=False,
    identify=False,
)
node = l.lineage(
    "x",
    qualified,
    dialect="bigquery",
    scope=scp.build_scope(qualified),
    trim_selects=False,
    copy=False,
)

for leaf in node.walk():
    if not leaf.downstream:
        print(leaf.name)

Output:

tbl.widget

One leaf. The metric_a and metric_b paths are both gone, and they're also collapsed to a single leaf by the set(find_all_in_scope(...)) on line 355. We see this on Snowflake VARIANT, BigQuery STRUCT, Iceberg nested types, and Spark complex-type queries — anywhere users access struct fields.

Where the information goes missing

In to_node, when a leaf is built:

# v30.7.0 sqlglot/lineage.py around line 468
col_expr = col_source or exp.Placeholder()
leaf = Node(name=c.sql(comments=False), source=col_expr, expression=col_expr)
node.downstream.append(leaf)
if on_node:
    on_node(leaf)

The source column c carries the Dot ancestors (the .metric.metric_a chain) on c.parent, but only c.sql(comments=False) makes it into the leaf — and that renders just the column reference, not the parent Dots. After the function returns there's no way to tell from the leaf which struct field it referred to.

What we tried externally before opening this

We tried two approaches that don't require any sqlglot change:

1. Pre-walk the qualified SQL and build a column_ref → [subfields] map.

from sqlglot import exp
from sqlglot.optimizer.scope import find_all_in_scope

subfield_index = {}
for c in find_all_in_scope(qualified, exp.Column):
    chain, f = [], c
    while isinstance(f.parent, exp.Dot):
        f = f.parent
        chain.append(f.name)
    if chain:
        subfield_index.setdefault(c.sql(comments=False), []).append(".".join(chain))

For the repro query: {'tbl.widget': ['metric.metric_a', 'metric.metric_b']}. Useful, but the lineage tree has only one leaf named tbl.widget and there's no way to tell which entry in the list it should be paired with.

2. Override Column.__hash__ to identity-based hashing so the set() on line 355 stops collapsing duplicates.

This restores both leaves, but both are still named tbl.widget. From outside to_node, there's no way to know which leaf came from which source column.

Where this breaks down:

• Same column ref appearing with different subfields (the repro above).
• Joined queries where each struct subfield resolves to a different upstream table — the pre-walk knows the subfields but can't attribute them to the right upstream branch.
• UDF / aggregate calls over multiple struct fields (COALESCE(t.s.a, t.s.b), STRUCT(t.s.x, t.s.y)).

For simple queries with one struct access per column ref, the external workaround is good enough. For the typical analytical workload — joins, aggregates, conditional logic over struct fields — it's lossy.

Why a hook fits

v30.7.0 added:

on_node: Callable[[Node], None] | None = None
payload: dict[str, t.Any] = field(default_factory=dict)  # on Node

The shape is right for consumer-side enrichment. The gap is that on_node(leaf) doesn't have the source column. Adding a second callback that does:

def to_node(
    ...
    on_node: Callable[[Node], None] | None = None,
    on_leaf: Callable[[Node, exp.Column], None] | None = None,  # new
) -> Node:
    ...
    col_expr = col_source or exp.Placeholder()
    leaf = Node(name=c.sql(comments=False), source=col_expr, expression=col_expr)
    node.downstream.append(leaf)
    if on_node:
        on_node(leaf)
    if on_leaf:
        on_leaf(leaf, c)

Same idea at the pivot leaf (around line 454) and star leaf (around line 349) — pass whatever source expression makes sense for each.

Consumer side, recovering the struct path:

def _capture_subfield(leaf: Node, c: exp.Column) -> None:
    chain, f = [], c
    while isinstance(f.parent, exp.Dot):
        f = f.parent
        chain.append(f.name)
    if chain:
        leaf.payload["subfield"] = ".".join(chain)

lineage(..., on_leaf=_capture_subfield)

Subfield lands in the existing Node.payload dict. No new fields on the dataclass.

Question

Does on_leaf(leaf, source_column) look right, or would you prefer a different shape — extending on_node with an optional second arg, populating payload automatically with the source, or something else?

Once the shape's settled, I can open a PR if you want.

[georgesittas]

Hey, thanks for the report. We'll take a look soon.

[georgesittas]

I don't think on_leaf makes sense, because you can distinguish an intermediate node from a leaf simply by checking its downstream within the on_node callback; if it's empty, it's a leaf node. So API-wise, it feels a bit counter-intuitive why you'd need another callback there.

I'm also not sure about changing the API for on_node to allow passing in a Column (or whatever else, for that matter). I'm thinking whether we should make the struct/json/whatever field access first class in the lineage module itself.

Can you share some more details on how you were planning to use the payload with the additional struct field information?

[georgesittas]

Closing this for now as it's not high priority for the core team. Happy to continue discussing.

[aviraj-gour]

Hi @georgesittas — re-opening the discussion on this. We tried some fix, I'd like your steer on the direction, since there are a few viable shapes

Problem

For field-wise nested access like MIN(t.s.a, t.s.b), lineage returns a single t.s leaf — the accessed fields are lost. Three code points are involved in sqlglot/lineage.py:

Point	Line	Current	Issue
A. Source-column collection	355	set(find_all_in_scope(select, exp.Column))	The two t.s occurrences are equal by value → the set collapses them to one.
B. Leaf name	468	name=c.sql(comments=False)	Renders t.s; drops the field info on c.parent (Dot / Bracket / JSONExtract).
C. Scope recursion	408	to_node(c.name, scope=col_source, ...)	Recurses with the bare column name → the field path is lost when resolving through CTEs/derived tables (this is #6258's "over-broad lineage" case).

The field path itself is already structurally in the AST for every access form (struct → Dot, array/map → Bracket, json/variant → JSONExtract + a structured JSONPath), so recovering it is not the hard part. The design questions are how to represent it and how far to resolve it.

Following your "first-class in the lineage module" steer

We want to avoid the hook/payload route you pushed back on, and instead make the accessed field a native part of the lineage tree. There are two largely independent axes to decide:

Axis 1 — surface representation

How a node carries the field it accessed:

1. Typed attribute on Node — e.g. field_path: str | None (or list[str]); name stays the column. Native and structured; small, additive API.
2. Set the leaf's expression/source to the access AST (the Dot/Bracket/JSONExtract), so the node is the field access — most AST-native, but needs a small to_html adjustment and changes leaf expression semantics.
3. Full path in Node.name (t.s.a) — no new field, but overloads name and changes existing leaf identifiers (consumers that parse name would be affected).

Axis 2 — scope / depth

How far the field is resolved:

• (I) Leaf-level — recover the field at the leaf/pivot-leaf branches only; the leaf still points at the base column. Fixes A + B. We can do this without changing the recursion (keep the set, collect field paths from all occurrences before dedup, attach/emit at the leaf), so existing lineage output is unaffected. Covers MIN(t.s.a, t.s.b) and joins where struct columns come straight from tables.
• (II) Through scopes (fully first-class). Follow the field down into subqueries/CTEs so it resolves to its real source column. For SELECT s.a FROM (SELECT STRUCT(t1.x AS a, ...) AS s ...), lineage would correctly link s.a → t1.x instead of stopping at the struct s. This is what STRUCT lineage - More granular support #6258's second example needs.

[aviraj-gour]

Stepping back from the specific fix — Do you think this is a feature-direction question: should sqlglot support field-level lineage, not just column-level?

Today lineage is column-granular, so for nested types (struct/array/json) it resolves the base column but drops which field was accessed.

Wanted to get your opinion on problem

You can also check the PR linked to this issue, where we tried a few fixes—let me know if that approach makes sense for SQLGlot to have for now.

[aviraj-gour]

Hi @georgesittas — just following up on this. Do you have a preferred direction for the fix? Happy to hear your thoughts on this problem.