Databricks Community

prathamesh1982 · yesterday

I have bronze layer data which is directly referred in silver. there are one or two columns from bronze layer which are used to build silver column.

is there any way I can have column-level lineage shown in databricks like table lineage

@DATA @lineage

balajij8 · yesterday

Hi Prathamesh,

Column level lineage is tracked automatically in Databricks if the tables are governed by Unity Catalog. You do not need to configure explicit column tracking or alter the pipeline code, once your bronze-to-silver transformations run on Unity Catalog enabled compute, the lineage graph captures column-level dependencies out of the box.

You can open Catalog Explorer, navigate to the Silver table, and switch to the Lineage tab. Selecting an individual column from the table schema will filter the lineage graph to show the exact upstream Bronze column's feeding into it, the execution context and any downstream Gold layer dependencies consuming that column.

You can first confirm whether the setup meets the prerequisites for lineage capture

Unity Catalog Governance: Both your Bronze and Silver tables must exist in a Unity Catalog as lineage is not captured for tables in the legacy hive_metastore.
Compute Access Mode: Your transformations must run on UC-compliant compute (eg Serverless/Pro SQL Warehouse, a cluster configured with Shared access mode or a Single User cluster with Unity Catalog enabled).
Ingestion: Lineage capture is handled natively across interactive notebooks, automated workflows, SQL queries without manual setup.

You can also use system tables to find it via SQL

-- Find which bronze columns feed into silver columns
SELECT
    source_table_full_name,
    source_column_name,
    target_table_full_name,
    target_column_name,
    event_time
FROM system.access.column_lineage
WHERE source_table_full_name = 'workspace.default.table'
ORDER BY target_table_full_name, target_column_name, event_time DESC;

-- See what columns in silver use a specific bronze column
SELECT DISTINCT
    target_table_full_name,
    target_column_name
FROM system.access.column_lineage
WHERE source_table_full_name = 'workspace.default.table'
  AND source_column_name = 'r_id'  -- Change column name
ORDER BY target_table_full_name, target_column_name;

Louis_Frolio · 11 hours ago

Hi @prathamesh1982 ,

Good news: there's nothing to enable. @balajij8 has it right, Unity Catalog captures column-level lineage automatically. I'll add some prerequisites and gotchas to check if the lineage doesn't appear.

If your bronze and silver tables are registered in Unity Catalog and the bronze-to-silver transformation runs on UC-compliant compute, the lineage graph picks up column-level dependencies on its own. No pipeline changes, no configuration. Once the job or notebook runs, open Catalog Explorer, go to your silver table, click the Lineage tab, and select the target silver column. The graph filters down to show exactly which bronze columns feed it, plus anything downstream that consumes it. Since you mentioned only one or two bronze columns build the silver column, that's precisely the view you want.

If lineage isn't showing up, take a gander at these prerequisites:

Both tables must live in Unity Catalog. Lineage isn't captured for tables in the legacy hive_metastore.
Your compute must be UC-compliant: a SQL warehouse, or a cluster in standard (shared) or dedicated (single user) access mode with Unity Catalog enabled.
The transformation must use a supported interface, such as Spark DataFrame operations or SQL. RDD-based processing, some user-defined functions, and checkpointing can prevent lineage from being captured.
Column lineage isn't captured if you reference the source or target by path, for example SELECT * FROM delta.s3://bucket/path``. Read and write through the registered table names instead.
Columns populated from explicit literal values (an INSERT with hardcoded values) won't show column lineage, since there's no source column to trace.
If you're building these tables with Lakeflow Spark Declarative Pipelines, column lineage requires DBR 13.3 LTS or above.
To view lineage, you need at least BROWSE on the parent catalog.

You can also pull this programmatically from the lineage system tables, as @balajij8 showed. Filtering on the target table is handy for your case:

sql

SELECT
  source_table_full_name,
  source_column_name,
  target_table_full_name,
  target_column_name,
  event_time
FROM system.access.column_lineage
WHERE target_table_full_name = 'catalog.schema.silver_table'
ORDER BY event_time DESC;

Two caveats there. The system tables only contain events where lineage could be inferred, so not every expression or workload is guaranteed to show up. And they keep a rolling one-year window. For history beyond that, use Catalog Explorer or the lineage API, which retain lineage captured after September 1, 2024 indefinitely.

One last option: if your bronze data lives outside Unity Catalog or the transformation runs outside Databricks, you can use external lineage (currently in Public Preview) to add those relationships to the graph. Just know that external lineage isn't written to system.access.column_lineage.

References: