Incomplete downstream dependencies in system.access.column_lineage

[antoine_pac]

Hi Community,

I'm currently facing an issue with the system.access.column_lineage system table.

My goal is to assess the downstream impact of removing a column from a source table. I tried to use the column_lineage table for this purpose, but it seems that it does not capture all downstream dependencies.

For example, suppose the definition of my table test is:

SELECT

    c.name,

    a.category

FROM contract c

LEFT JOIN account a

    ON c.entity = a.entity

In this case, I do not see the entity column from the account table reported as an upstream dependency for the test table. As a result, it is difficult to accurately assess the impact of dropping the entity column from account, since it is still required by the join condition.

I noticed the following statement in the documentation:

"Records are only emitted when lineage can be inferred."

Does this mean that the current behavior is expected?

If so, are there any plans to enhance the system.access.column_lineage table in the future to provide a more complete level of column-level dependency tracking, including columns used in join predicates and other non-selected expressions?

Thanks in advance for your help.

[Ashwin_DSA]

Hi @antoine_pac,

I think this is expected behaviour, and you have already identified the reason in your query.  The public docs for lineage system tables explicitly say that both lineage tables represent only a subset of read/write events, and that "records are only emitted when lineage can be inferred." The broader Unity Catalog lineage documentation also says that Databricks captures column lineage “as much as possible,” and notes that there are cases where column-level lineage cannot be captured.

In your example query, it makes sense that account.entity may not appear as an upstream dependency for the target table in system.access.column_lineage, because that column is being used in the join predicate rather than flowing into a projected target column. system.access.column_lineage is most reliable for source-to-target column derivation, not necessarily for every column referenced somewhere in the query logic.

If your intention is to do the impact analysis, you can probably use system.access.table_lineage to find downstream tables that depend on the source table at all first. You can then use system.access.column_lineage for direct column-to-column mappings. You can treat join/filter/predicate-only column usage as something that may require checking the defining SQL or query history separately.

I couldn't find any references that says this is under consideration for development but if this is really important for you, you should consider reaching out to your Databricks account team to request this as a feature or product enhancement. 

Hope this helps.

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.