How to Trace Column-Level Data Lineage Across Sources

Someone renamed total_amount to order_total in your Postgres table. The migration ran fine. The app works. And two days later your CFO asks why the revenue widget on the exec dashboard went to zero.

Table-level lineage wouldn't have helped. It would've told you the exec dashboard uses reporting.monthly_revenue, which uses raw.orders. Great. You already knew that. What you needed to know is that reporting.monthly_revenue.gross_revenue is computed from raw.orders.total_amount minus raw.refunds.amount, and that total_amount just got renamed. That's column-level lineage, and it's where impact analysis actually becomes useful.

I've traced these dependencies manually more times than I'd like to admit. It's tedious, it's error-prone, and it breaks every time someone adds a CTE or changes a join. But if you don't have automated lineage yet, you need to know how to do it by hand — because waiting until you do have it means waiting until the next incident.

Why table-level lineage falls short

Table-level lineage is a DAG of table dependencies. dashboard_7 reads from reporting.monthly_metrics, which is built from raw.stripe_charges and raw.orders. You can build this from INFORMATION_SCHEMA views or a dbt manifest in an afternoon.

But it can't answer the question that matters: what happens if I change this specific column?

A table might have 80 columns. Your dashboard uses 3 of them. Knowing the dashboard depends on the table doesn't tell you whether a given column change affects it. You either panic and treat every schema change as a potential incident, or you shrug and hope it doesn't matter. Neither is great.

Column-level lineage resolves this by tracing the actual flow of data from source column through transformations to output. reporting.monthly_metrics.revenue comes from SUM(raw.orders.total_amount) minus SUM(raw.refunds.amount). The revenue widget on dashboard_7 reads reporting.monthly_metrics.revenue. Now you have a chain. Rename total_amount, and you know exactly what breaks — and what doesn't.

Tracing column lineage manually with SQL

If you're doing this by hand, the process has three steps: find what references a column, resolve the transformations, and trace the outputs.

Step 1: Find column references in view definitions

Start with the column that changed and search for everything that references it. In Postgres, view definitions are stored in pg_views. You can grep them:

SELECT
  schemaname,
  viewname,
  definition
FROM pg_views
WHERE definition ILIKE '%total_amount%'
  AND schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, viewname;

This gives you every view that references total_amount by name. It's a text search, so it'll catch column references inside SELECT, WHERE, JOIN ON, GROUP BY — anywhere the column appears in the SQL.

The limitation is obvious: it only finds views in Postgres. If you have dbt models materializing tables, or queries saved in a BI tool, or Python scripts pulling from the same column — this misses all of them.

Step 2: Trace through dependent views

One layer isn't enough. total_amount might feed into reporting.order_summary, which feeds into reporting.monthly_revenue, which feeds into reporting.exec_metrics. You need the full chain. Here's a recursive CTE that walks the dependency tree in Postgres:

WITH RECURSIVE lineage AS (
  -- Seed: views directly referencing our column
  SELECT
    v.schemaname,
    v.viewname,
    v.definition,
    1 AS depth
  FROM pg_views v
  WHERE v.definition ILIKE '%total_amount%'
    AND v.schemaname NOT IN ('pg_catalog', 'information_schema')
 
  UNION ALL
 
  -- Recurse: views that reference a view from the previous level
  SELECT
    v2.schemaname,
    v2.viewname,
    v2.definition,
    l.depth + 1
  FROM pg_views v2
  JOIN lineage l
    ON v2.definition ILIKE '%' || l.viewname || '%'
  WHERE v2.schemaname NOT IN ('pg_catalog', 'information_schema')
    AND l.depth < 10
)
SELECT DISTINCT schemaname, viewname, depth
FROM lineage
ORDER BY depth, schemaname, viewname;

This walks outward from the source column through every layer of dependent views. At depth = 1 you get direct references. At depth = 2, views that reference those views. And so on.

It works, but it has real problems. The ILIKE '%viewname%' match is fragile — it'll false-positive on a view called order_summary if another view has a column called order_summary_id. It can't distinguish between a column reference and a table reference. And it can't tell you which output columns of each view are affected, because it's pattern-matching on raw SQL text, not parsing the query.

Step 3: Check which columns are actually affected

Once you've found the downstream views, you need to know which of their output columns carry the tainted data. This requires reading the view definition and tracing the column manually. For a simple view, you can inspect information_schema:

SELECT
  c.table_schema,
  c.table_name,
  c.column_name,
  c.data_type,
  v.definition
FROM information_schema.columns c
JOIN pg_views v
  ON v.viewname = c.table_name
  AND v.schemaname = c.table_schema
WHERE v.definition ILIKE '%total_amount%'
  AND c.table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY c.table_schema, c.table_name, c.ordinal_position;

This lists every output column of every view that mentions total_amount. But it doesn't tell you which output columns depend on total_amount and which just happen to coexist in the same view. For that, you'd need to actually parse the SQL — understand which SELECT expressions reference total_amount, what they get aliased to, and how those aliases propagate through downstream views.

That's the wall. Text search can find references, recursive CTEs can walk the dependency graph, but neither can tell you which specific output columns are affected by a specific input column change. You need a SQL parser for that.

Where manual tracing breaks down

I've built versions of the queries above at three different companies. They work for about six months, then they start lying to you. A few patterns that break them:

CTEs with renames. A CTE aliases total_amount to amount, and now every downstream reference uses amount. Your ILIKE '%total_amount%' search stops at the CTE boundary. The dependency still exists, but you can't see it.

Cross-database joins. Your dbt model in BigQuery reads from a Postgres source via Fivetran. The column name might change in transit. The view definitions live in different systems. No single pg_views query can trace across that boundary.

BI tool queries. Looker LookML definitions, Tableau calculated fields, Metabase questions — all of these reference columns, and none of them show up in pg_views. A column rename that doesn't break any database view can still break half your dashboards.

Aggregation and expressions. SUM(total_amount) AS revenue means revenue depends on total_amount. But total_amount * quantity AS line_total means line_total depends on both total_amount and quantity. Text search can't parse arithmetic expressions to extract dependency sets.

dbt model dependencies. If you're using dbt, you can query the manifest to find column references, but it requires parsing the compiled SQL, not the Jinja templates. Something like this against the information_schema of a warehouse where dbt materializes:

SELECT
  t.table_schema,
  t.table_name,
  c.column_name,
  c.data_type
FROM information_schema.tables t
JOIN information_schema.columns c
  ON c.table_schema = t.table_schema
  AND c.table_name = t.table_name
WHERE t.table_schema = 'analytics'
  AND EXISTS (
    SELECT 1 FROM pg_views v
    WHERE v.viewname = t.table_name
      AND v.definition ILIKE '%total_amount%'
  )
ORDER BY t.table_name, c.ordinal_position;

This finds materialized dbt models (in the analytics schema, or wherever yours land) that reference the column. But it still has the same alias-tracking blind spot — if dbt renames the column in a staging model, the reference disappears from the compiled SQL of downstream models.

This is why schema drift detection catches the event (something changed), but without column-level lineage you can't assess the impact (what downstream things are affected).

Automated column-level lineage

Fastero's lineage engine does what the manual approach can't: it parses SQL at the column level, across sources, and maintains the graph continuously.

Every query surface feeds the lineage graph — dashboard widgets, saved queries, metric definitions, notebook cells, workflow steps, scheduled reports. The engine runs dialect-specific parsers for BigQuery, Redshift, Snowflake, and Postgres, so it handles CTEs, subqueries, window functions, MERGE statements, and warehouse-specific syntax like Snowflake's FLATTEN or BigQuery's STRUCT access.

The result is a column-level dependency graph. Not "this dashboard uses that table" — "this widget's revenue metric depends on orders.total_amount and refunds.amount, through a view that aggregates them, through a CTE that renames one of them." When a schema drift event fires, you get impact analysis: here are the specific dashboards, widgets, and metrics that reference the changed column. Not "something might break" — "these three things will break."

Cross-source lineage works through the DuckDB query layer. When a query joins Postgres data with BigQuery data, the lineage engine traces columns from both sources through the join into the output. A column rename in Postgres that affects a cross-source metric shows up in the impact analysis, even though the downstream query runs against DuckDB.

Fastero also supports OpenLineage integration, so lineage from external tools — dbt, Airflow, Spark — flows into the same graph. If your dbt model transforms total_amount into revenue before it hits the warehouse, that transformation is part of the lineage chain, and impact analysis traces through it.

The impact analysis question

The scenario I opened with — someone renamed a column and the exec dashboard broke two days later — is the exact scenario column-level lineage prevents. Not by stopping the rename, but by telling you what the rename affects before you merge the migration.

If you're not ready for automated lineage yet, the SQL queries above will get you through the next incident. Build the recursive view dependency search, keep it in a saved query, and run it before any schema migration. It's imperfect — it'll miss cross-source dependencies and BI tool references — but it's better than nothing.

If you're tired of maintaining those queries, and you want impact analysis that works across sources and traces through transformations — that's what Fastero's lineage engine does. It also pairs with data freshness monitoring and data quality checks so the same system that tells you what changed also tells you what it broke.

Try Fastero free — column-level lineage across every source, with impact analysis that tells you what breaks before you ship the migration. No credit card required.