PostgreSQL Data Lineage: Column-Level Lineage from Views and SQL

PostgreSQL data lineage is the column-level map of how data moves through your PostgreSQL views, CTEs, and DML: which base-table columns feed each view or report column, and which joins, filters, casts, and aggregates transform the data along the way. PostgreSQL’s own catalog tells you that objects depend on each other — pg_depend records that a view references a table — but it says nothing about how columns transform. Building real lineage means parsing the SQL itself, and that is exactly what Flujo de SQL de Gudu does, with a dedicated PostgreSQL dialect parser rather than a generic ANSI grammar.

Why pg_depend can’t answer lineage questions

PostgreSQL keeps meticulous dependency records. pg_depend y pg_rewrite know that v_region_revenue cannot be dropped while it references v_orders_enriched, y information_schema views expose which tables a view touches. That is object-level dependency tracking, built for DROP ... CASCADE safety — not lineage.

What the catalog cannot tell you: which specific columns feed revenue, whether amount was cast or aggregated on the way, whether status filters the result without ever appearing in it, and what the full path looks like when views stack three or four levels deep. Answering “where does this number come from?” or “what breaks if I drop this column?” requires reconstructing the transformation logic from the SQL text stored in the view definitions — a parsing problem, not a catalog query.

How SQLFlow builds PostgreSQL data lineage

1. Ingest SQL. Paste queries, upload script files, connect to PostgreSQL over JDBC to pull DDL and view definitions directly, or import a dbt manifest for dbt-on-Postgres projects.
2. Parse with a PostgreSQL-specific grammar. SQLFlow is built on the General SQL Parser (GSP), a commercial SQL compiler front-end developed since the mid-2000s and validated against roughly 13,600 per-dialect test fixtures. Its PostgreSQL parser handles the dialect’s own constructs — writable CTEs, INSERT ... ON CONFLICT, the :: cast operator, inheritance and partitioning DDL — and its semantic resolver expands SELECT *, resolves column references through CTEs, subqueries, and view definitions, then extracts source-to-target relationships at column granularity.
3. Visualize and export. The output is an interactive diagram you can trace upstream and downstream from any column, exportable as JSON, CSV, or PNG, or queryable through a REST API.

PostgreSQL is one of 39 dialects SQLFlow ships parsers for — including the Postgres-family engines Greenplum, Amazon Redshift, and EDB Postgres, each with its own parser because each has diverged from core PostgreSQL in ways that matter to a lineage engine. The full methodology is covered in the SQL data lineage tool overview.

Tracing a nested view chain back to base tables

View-on-view stacks are the default way logic accumulates in PostgreSQL, and they are where manual lineage tracing gives out. Take a two-level chain:

CREATE VIEW v_orders_enriched AS
SELECT o.order_id,
       o.customer_id,
       o.amount::numeric(12,2) AS amount,
       c.region
FROM   orders o
JOIN   customers c ON c.customer_id = o.customer_id
WHERE  o.status = 'shipped';

CREATE VIEW v_region_revenue AS
SELECT region,
       SUM(amount) AS revenue
FROM   v_orders_enriched
GROUP  BY region;

Ask SQLFlow where v_region_revenue.revenue comes from and it resolves the full chain: revenue is SUM() over v_orders_enriched.amount, which is a numeric(12,2) cast of orders.amount. The diagram shows each hop — base table, intermediate view, aggregate — as a drillable path, not a flattened guess. It also surfaces what a naive reading misses: orders.status never appears in the output, yet every revenue figure depends on the WHERE o.status = 'shipped' filter. SQLFlow records that as indirect lineage (more below).

The same resolution works at any depth. In a real schema where views stack five levels deep and half of them use SELECT *, SQLFlow expands the stars against the actual table definitions pulled over JDBC, so the lineage stays column-accurate even when the SQL text never names the columns.

PostgreSQL constructs that break generic parsers

Tools that treat PostgreSQL as “roughly ANSI SQL” fail precisely on the statements that carry the most dataflow. These are all handled by SQLFlow’s PostgreSQL dialect parser:

Construct	Why it matters for lineage
Writable CTEs (WITH ... INSERT/UPDATE/DELETE ... RETURNING)	A single statement both reads and writes: the CTE’s DML output feeds the outer query. Lineage must connect the modified table, the RETURNING columns, and the final target in one graph.
INSERT ... ON CONFLICT DO UPDATE	Upserts route data down two paths — the insert path and the update path via the EXCLUDED pseudo-table. Both are real column flows into the target table.
Table inheritance and declarative partitioning	Queries against a parent table implicitly read its children. SQLFlow parses the inheritance and partitioning DDL so lineage attaches correctly at the parent level instead of fragmenting per partition.
Chained CTEs and nested views	Column references must be resolved through every intermediate layer, including CTEs that reference earlier CTEs, back to physical base tables.
:: casts and expression columns	Every output column is mapped to its sources through the functions and casts applied, so you can see not just where a value came from but what happened to it.

Direct vs indirect lineage: the filter columns you’d otherwise miss

SQLFlow distinguishes direct lineage — column values that physically flow into an output — from indirect (impact) lineage — columns used in WHERE clauses, JOIN conditions, and GROUP BY keys that shape the result without landing in it. In the example above, orders.amount is direct lineage for revenue; orders.status and both customer_id join keys are indirect.

The distinction is toggleable in the diagram, and it changes real decisions. If you plan to change the domain of orders.status, direct-only lineage says nothing depends on it; indirect lineage shows every revenue report it silently filters. Most lineage tools do not model this relationship type at all.

From one query to a whole PostgreSQL estate

For a single gnarly view chain, the SQLFlow Cloud free tier is enough: paste, analyze, drill. Beyond that:

• Whole-database scans: connect over JDBC (or use the Grabit metadata extractor) to pull every view and object definition and build a persistent lineage repository. Enterprise deployments batch-scan estates of 100+ databases and over a million columns, with incremental re-scans.
• Catalog integration: export adapters push column-level lineage into DataHub, Microsoft Purview, and OpenMetadata, so SQLFlow can serve as the lineage engine behind the catalog you already run.
• Air-gapped environments: SQLFlow local runs on Docker or Kubernetes inside your network at $500/month or $4,800 one-time per database type, installable on two servers. SQL text never leaves your infrastructure.
• Privacy by design: SQLFlow performs static analysis of SQL code and schema metadata only. It never reads the rows in your tables.

Since version 8.2.3 you can also query the resulting graph in plain English — “which views depend on orders.amount?” — with every table and column in the answer validated against the analyzed lineage before it is shown.

What about open-source PostgreSQL lineage tools?

Open-source parsers like sqllineage y sqlglot are genuinely good at extracting table-level and basic column-level lineage from individual queries, and for a handful of clean SELECT statements they may be all you need. The gaps show up on production PostgreSQL: resolving SELECT * through view chains requires live schema metadata; writable CTEs and ON CONFLICT upserts need dialect-exact grammars; indirect lineage through filters and join keys is usually not modeled; and extracting lineage is not the same as visualizing, storing, and diffing it across thousands of objects. If you are evaluating, run your deepest view stack and your ugliest upsert through both and compare the graphs.

If your estate mixes engines, the same parser family covers them with the same output format — see the companion pages for MySQL data lineage, Redshift data lineage, y Greenplum data lineage, or the data lineage knowledge base for concepts.

Frequently asked questions

Does PostgreSQL have built-in data lineage?

No. PostgreSQL tracks object-level dependencies in pg_depend — which views reference which tables — for integrity purposes like DROP ... CASCADE. It does not record how columns are transformed. Column-level lineage has to be derived by parsing the SQL in view definitions and scripts, which is what SQLFlow does.

Does SQLFlow need access to my table data?

No. SQLFlow performs static analysis of SQL code and optionally reads schema metadata (table and column definitions) to resolve references. It never reads rows. With the On-Premise edition, even the SQL text stays inside your network.

Can SQLFlow trace lineage through writable CTEs and ON CONFLICT upserts?

Yes. The PostgreSQL dialect parser handles WITH ... INSERT/UPDATE/DELETE ... RETURNING statements and INSERT ... ON CONFLICT DO UPDATE, mapping both the insert and update column flows into the target table.

How are partitioned and inherited tables handled?

SQLFlow parses PostgreSQL inheritance and declarative partitioning DDL, so lineage for queries against a parent table is attached at the parent level rather than splintering into per-partition edges.

How do I get lineage for an entire PostgreSQL database, not just one query?

Connect SQLFlow to the database over JDBC or extract metadata with the Grabit ingester. SQLFlow pulls all DDL and view definitions, analyzes them together, and maintains a persistent, incrementally updated lineage repository — scaling to estates of 100+ databases.

How much does SQLFlow cost?

SQLFlow Cloud starts free; premium accounts are $49.99/month. SQLFlow On-Premise is $500/month or $4,800 one-time per selected database type, plus $100/month or $1,000 one-time per additional database type.