{"id":6756,"date":"2026-07-12T02:06:52","date_gmt":"2026-07-12T10:06:52","guid":{"rendered":"https:\/\/www.gudusoft.com\/postgresql-data-lineage\/"},"modified":"2026-07-12T02:06:52","modified_gmt":"2026-07-12T10:06:52","slug":"postgresql-data-lineage","status":"publish","type":"page","link":"https:\/\/www.gudusoft.com\/pt\/postgresql-data-lineage\/","title":{"rendered":"PostgreSQL Data Lineage: Column-Level Lineage from Views and SQL"},"content":{"rendered":"

PostgreSQL data lineage<\/strong> 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 \u2014 pg_depend<\/code> records that a view references a table \u2014 but it says nothing about how columns transform. Building real lineage means parsing the SQL itself, and that is exactly what Gudu SQLFlow<\/strong> does, with a dedicated PostgreSQL dialect parser rather than a generic ANSI grammar.<\/p>\n\n\n\n

\n\n

Try it in 30 seconds:<\/strong> paste any PostgreSQL query or view definition into the free SQLFlow lineage visualizer<\/a> and get an interactive column-level lineage diagram. The cloud edition has a free tier.<\/p>\n\n<\/div><\/div>\n\n\n\n

Why pg_depend can’t answer lineage questions<\/h2>\n\n\n\n

PostgreSQL keeps meticulous dependency records. pg_depend<\/code> e pg_rewrite<\/code> know that v_region_revenue<\/code> cannot be dropped while it references v_orders_enriched<\/code>, e information_schema<\/code> views expose which tables a view touches. That is object-level dependency tracking, built for DROP ... CASCADE<\/code> safety \u2014 not lineage.<\/p>\n\n\n\n

What the catalog cannot tell you: which specific columns feed revenue<\/code>, whether amount<\/code> was cast or aggregated on the way, whether status<\/code> 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 \u2014 a parsing problem, not a catalog query.<\/p>\n\n\n\n

How SQLFlow builds PostgreSQL data lineage<\/h2>\n\n\n\n
  1. Ingest SQL.<\/strong> 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.<\/li>\n
  2. Parse with a PostgreSQL-specific grammar.<\/strong> SQLFlow is built on the General SQL Parser (GSP)<\/a>, 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 \u2014 writable CTEs, INSERT ... ON CONFLICT<\/code>, the ::<\/code> cast operator, inheritance and partitioning DDL \u2014 and its semantic resolver expands SELECT *<\/code>, resolves column references through CTEs, subqueries, and view definitions, then extracts source-to-target relationships at column granularity.<\/li>\n
  3. Visualize and export.<\/strong> 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.<\/li><\/ol>\n\n\n\n

    PostgreSQL is one of 39 dialects SQLFlow ships parsers for \u2014 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<\/a>.<\/p>\n\n\n\n

    Tracing a nested view chain back to base tables<\/h2>\n\n\n\n

    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:<\/p>\n\n\n\n

    CREATE VIEW v_orders_enriched AS\nSELECT o.order_id,\n       o.customer_id,\n       o.amount::numeric(12,2) AS amount,\n       c.region\nFROM   orders o\nJOIN   customers c ON c.customer_id = o.customer_id\nWHERE  o.status = 'shipped';\n\nCREATE VIEW v_region_revenue AS\nSELECT region,\n       SUM(amount) AS revenue\nFROM   v_orders_enriched\nGROUP  BY region;<\/code><\/pre>\n\n\n\n
    Ask SQLFlow where v_region_revenue.revenue<\/code> comes from and it resolves the full chain: revenue<\/code> is SUM()<\/code> over v_orders_enriched.amount<\/code>, which is a numeric(12,2)<\/code> cast of orders.amount<\/code>. The diagram shows each hop \u2014 base table, intermediate view, aggregate \u2014 as a drillable path, not a flattened guess. It also surfaces what a naive reading misses: orders.status<\/code> never appears in the output, yet every revenue figure depends on the WHERE o.status = 'shipped'<\/code> filter. SQLFlow records that as indirect lineage (more below).<\/p>\n\n\n\n
    The same resolution works at any depth. In a real schema where views stack five levels deep and half of them use SELECT *<\/code>, 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.<\/p>\n\n\n\n
    PostgreSQL constructs that break generic parsers<\/h2>\n\n\n\n
    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:<\/p>\n\n\n\n
    \n
    Construct<\/th>Why it matters for lineage<\/th><\/tr><\/thead>
    Writable CTEs (WITH ... INSERT\/UPDATE\/DELETE ... RETURNING<\/code>)<\/td>A single statement both reads and writes: the CTE’s DML output feeds the outer query. Lineage must connect the modified table, the RETURNING<\/code> columns, and the final target in one graph.<\/td><\/tr>\n
    INSERT ... ON CONFLICT DO UPDATE<\/code><\/td>Upserts route data down two paths \u2014 the insert path and the update path via the EXCLUDED<\/code> pseudo-table. Both are real column flows into the target table.<\/td><\/tr>\n
    Table inheritance and declarative partitioning<\/td>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.<\/td><\/tr>\n
    Chained CTEs and nested views<\/td>Column references must be resolved through every intermediate layer, including CTEs that reference earlier CTEs, back to physical base tables.<\/td><\/tr>\n
    ::<\/code> casts and expression columns<\/td>Every output column is mapped to its sources through the functions and casts applied, so you can see not just where<\/em> a value came from but what happened to it<\/em>.<\/td><\/tr>\n<\/tbody><\/table><\/figure>\n\n\n\n
    Direct vs indirect lineage: the filter columns you’d otherwise miss<\/h2>\n\n\n\n
    SQLFlow distinguishes direct lineage<\/strong> \u2014 column values that physically flow into an output \u2014 from indirect (impact) lineage<\/strong> \u2014 columns used in WHERE<\/code> clauses, JOIN<\/code> conditions, and GROUP BY<\/code> keys that shape the result without landing in it. In the example above, orders.amount<\/code> is direct lineage for revenue<\/code>; orders.status<\/code> and both customer_id<\/code> join keys are indirect.<\/p>\n\n\n\n
    The distinction is toggleable in the diagram, and it changes real decisions. If you plan to change the domain of orders.status<\/code>, 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.<\/p>\n\n\n\n
    From one query to a whole PostgreSQL estate<\/h2>\n\n\n\n
    For a single gnarly view chain, the SQLFlow Cloud free tier is enough: paste, analyze, drill. Beyond that:<\/p>\n\n\n\n