The problem: tribal knowledge instead of traceable metadata
A finance question about an unexpected revenue figure usually turns into an hour of archaeology: grep through ETL scripts, follow foreign keys, chase a view that references another view that references a table someone may have renamed. That is the data lineage problem.
Lineage is a paper trail for data, answering several distinct questions:
- Provenance — where did this come from?
- Dependency — what else depends on this?
- Transformation history — what happened to it along the way?
- Impact analysis — if I change this upstream, what breaks?
Most teams answer with one of two approximations. Either a wiki page or spreadsheet that was accurate for about a week, or the dependency graph held by an orchestrator such as Airflow, dbt or Dagster — which only covers what the orchestrator knows about, leaving ad-hoc queries and legacy pipelines invisible. In both cases the lineage lives somewhere other than the data.
Compliance adds pressure. GDPR Article 5(2) requires that people can understand "the logic involved" in processing their data; SOX requires auditable quarterly reports; BCBS 239 requires banks to know where risk numbers originate.
The alternative is to store lineage as data: in tables, queryable, version-controlled, colocated with the rest of the system.
A worked e-commerce pipeline
Say you have raw clickstream data, a staging table that cleans and casts it, a fact table aggregating sales by product and day, and a monthly report that rolls up revenue by product category:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
-- Raw clickstream: what users did on the website CREATE TABLE raw_clickstream ( event_id bigint PRIMARY KEY, user_id int NOT NULL, product_id int NOT NULL, action text NOT NULL, -- 'view', 'cart', 'purchase' amount numeric(10,2), ts timestamptz NOT NULL ); -- Raw CRM: customer records from the sales system CREATE TABLE raw_crm ( customer_id int PRIMARY KEY, name text NOT NULL, segment text NOT NULL, -- 'enterprise', 'smb', 'consumer' acquired_at date NOT NULL ); -- Raw inventory: product stock levels CREATE TABLE raw_inventory ( product_id int PRIMARY KEY, name text NOT NULL, category text NOT NULL, stock_level int NOT NULL ); -- Staging: raw events with type coercion and basic cleaning CREATE TABLE stg_events_raw ( event_id bigint PRIMARY KEY, user_id int NOT NULL, product_id int NOT NULL, action text NOT NULL, amount numeric(10,2), ts timestamptz NOT NULL, loaded_at timestamptz NOT NULL DEFAULT now() ); -- Fact table: sales aggregated by product and day CREATE TABLE fact_sales ( product_id int NOT NULL, sale_date date NOT NULL, units_sold int NOT NULL, revenue numeric(12,2) NOT NULL, PRIMARY KEY (product_id, sale_date) ); -- Report: monthly summary by product category CREATE TABLE report_monthly ( report_month date NOT NULL, category text NOT NULL, total_revenue numeric(14,2) NOT NULL, total_units int NOT NULL, PRIMARY KEY (report_month, category) ); |
The relationships between those tables are themselves data, and the ETL you already wrote encodes them:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
-- These tables explicitly record "which row came from which" CREATE TABLE edge_loads_into ( src_event_id bigint NOT NULL REFERENCES raw_clickstream(event_id), dst_event_id bigint NOT NULL REFERENCES stg_events_raw(event_id), PRIMARY KEY (src_event_id, dst_event_id) ); CREATE TABLE edge_aggregates_into ( src_event_id bigint NOT NULL REFERENCES stg_events_raw(event_id), dst_product int NOT NULL, dst_date date NOT NULL, FOREIGN KEY (dst_product, dst_date) REFERENCES fact_sales(product_id, sale_date), PRIMARY KEY (src_event_id, dst_product, dst_date) ); CREATE TABLE edge_rollup_into ( src_product int NOT NULL, src_date date NOT NULL, dst_month date NOT NULL, dst_category text NOT NULL, FOREIGN KEY (src_product, src_date) REFERENCES fact_sales(product_id, sale_date), FOREIGN KEY (dst_month, dst_category) REFERENCES report_monthly(report_month, category), PRIMARY KEY (src_product, src_date, dst_month, dst_category) ); |
For the example, the dataset is four clickstream events (three purchases, one view), three CRM companies, three inventory products, two days of fact sales, and two rows in the monthly report (electronics and accessories). The flow is raw_clickstream → stg_events_raw → fact_sales → report_monthly.
Declaring the graph with SQL/PGQ
PostgreSQL 19's SQL/PGQ support lets you declare a PROPERTY GRAPH — a metadata layer stating that these tables form a graph with these relationships. No rows are copied; the graph only says, for example, that rows in stg_events_raw come from rows in raw_clickstream sharing the same event_id.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
CREATE PROPERTY GRAPH lineage VERTEX TABLES ( raw_clickstream KEY (event_id) LABEL clickstream_source PROPERTIES (event_id, amount, ts), raw_crm KEY (customer_id) LABEL crm_source PROPERTIES (customer_id, name), raw_inventory KEY (product_id) LABEL inventory_source PROPERTIES (product_id, name), stg_events_raw KEY (event_id) LABEL staging PROPERTIES (event_id, action, amount, loaded_at), fact_sales KEY (product_id, sale_date) LABEL fact PROPERTIES (product_id, sale_date, revenue), report_monthly KEY (report_month, category) LABEL report PROPERTIES (report_month, category, total_revenue) ) EDGE TABLES ( edge_loads_into SOURCE KEY (src_event_id) REFERENCES raw_clickstream (event_id) DESTINATION KEY (dst_event_id) REFERENCES stg_events_raw (event_id) LABEL loads_into, edge_aggregates_into SOURCE KEY (src_event_id) REFERENCES stg_events_raw (event_id) DESTINATION KEY (dst_product, dst_date) REFERENCES fact_sales (product_id, sale_date) LABEL aggregates_into, edge_rollup_into SOURCE KEY (src_product, src_date) REFERENCES fact_sales (product_id, sale_date) DESTINATION KEY (dst_month, dst_category) REFERENCES report_monthly (report_month, category) LABEL rollup_into ); |
Vertex labels (clickstream_source, staging, fact, report) let you talk about pipeline layers, which matters because auditors care about sources and reports while engineers care about everything. Edge labels (loads_into, aggregates_into, rollup_into) describe what kind of transformation occurred — a load is a straight copy, an aggregation groups, a rollup summarizes further — which is far more legible than "there is a foreign key here."
In practice the CREATE PROPERTY GRAPH statement would be generated from orchestration metadata; dbt, Airflow and Fivetran all track upstream and downstream relationships, and the graph is the compiled, queryable form of that metadata.
Answering the questions that come up
Origin of a report value
Asked why electronics shows $99.98 for January, you trace it back to source:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT * FROM GRAPH_TABLE (lineage MATCH (r IS report) <-[IS rollup_into]-(f IS fact) <-[IS aggregates_into]-(s IS staging) <-[IS loads_into]-(c IS clickstream_source) WHERE r.report_month = '2024-01-01' AND r.category = 'electronics' COLUMNS ( r.report_month, r.category, r.total_revenue, f.product_id, f.sale_date, f.revenue, s.event_id, s.action, s.amount, c.event_id AS source_event_id, c.ts AS source_ts ) ) ORDER BY source_ts; |
|
1 2 3 4 |
report_month | category | total_revenue | product_id | sale_date | revenue | event_id | action | amount | source_event_id | source_ts --------------+-------------+---------------+------------+-----------+---------+----------+-----------+--------+-----------------+--------------------- 2024-01-01 | electronics | 99.98 | 10 | 2024-01-15| 49.99 | 1 | purchase | 49.99 | 1 | 2024-01-15 10:30:00+00 2024-01-01 | electronics | 99.98 | 10 | 2024-01-16| 49.99 | 3 | purchase | 49.99 | 3 | 2024-01-16 14:00:00+00 |
The $99.98 comes from two purchase events in raw_clickstream, with a complete chain: report_monthly → fact_sales → stg_events_raw → raw_clickstream. That output can be handed straight to finance.
Blast radius of an upstream change
Before touching the raw_clickstream loader, check what depends on it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT DISTINCT downstream_label, downstream_table FROM GRAPH_TABLE (lineage MATCH (s IS clickstream_source)-[IS loads_into]->(st IS staging) -[IS aggregates_into]->(f IS fact) -[IS rollup_into]->(r IS report) WHERE s.event_id IN (1, 2, 3) COLUMNS ( s.event_id AS src_event, st.event_id AS stg_event, f.product_id AS fact_product, r.report_month AS rpt_month, r.category AS downstream_label, 'report_monthly' AS downstream_table ) ) ORDER BY downstream_table, downstream_label; |
|
1 2 3 |
downstream_label | downstream_table -------------------+------------------ electronics | report_monthly |
Events 1, 2 and 3 feed the electronics row in report_monthly, so you know to test that report first.
The full audit trail for one number
A single query can return the entire chain for a specific value:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT * FROM GRAPH_TABLE (lineage MATCH (r IS report) <-[IS rollup_into]-(f IS fact) <-[IS aggregates_into]-(s IS staging) <-[IS loads_into]-(c IS clickstream_source) WHERE r.report_month = '2024-01-01' AND r.category = 'accessories' COLUMNS ( 'report'::text AS layer, 'report_monthly'::text AS table_name, r.report_month::text || ',' || r.category AS key_value, 'total_revenue'::text AS value_column, r.total_revenue AS value, f.product_id, f.sale_date, f.revenue, s.event_id AS staging_event, s.action, s.amount AS staging_amount, c.event_id AS source_event, c.amount AS source_amount ) ); |
|
1 2 3 |
layer | table_name | key_value | value_column | value | product_id | sale_date | revenue | staging_event | action | staging_amount | source_event | source_amount --------+----------------+------------------------+---------------+-------+------------+------------+---------+---------------+----------+----------------+--------------+--------------- report | report_monthly | 2024-01-01,accessories | total_revenue | 99.00 | 12 | 2024-01-20 | 99.00 | 4 | purchase | 99.00 | 4 | 99.00 |
One row carries the whole story: $99.00 in the report, traced through the fact table (product 12, Jan 20), the staging event (event 4, purchase), and back to source (event 4, amount $99.00). This is the artifact regulators ask for when they want proof a calculation is correct.
Data with no downstream consumer
The reverse direction is just as useful — finding rows that never reach the next layer:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT s.event_id, s.ts, s.action FROM GRAPH_TABLE (lineage MATCH (c IS clickstream_source) WHERE c.event_id NOT IN ( SELECT s2.event_id FROM GRAPH_TABLE (lineage MATCH (s2 IS staging) COLUMNS (s2.event_id) ) ) COLUMNS (c.event_id, c.ts, c.action) ) ORDER BY ts; |
|
1 2 3 |
event_id | ts | action ----------+---------------------+--------- 2 | 2024-01-15 10:31:00+00 | view |
Event 2 is a view that never entered fact_sales, since only purchase events are aggregated. That may be intended, or it may be an aggregation bug; either way it is now visible.
Why not foreign keys and recursive CTEs?
You could model the same relationships that way, but the property graph has four practical advantages:
- The pattern reads like the question. Tracing "from this report back to its sources" is literally
(report) <-[...]-(fact) <-[...]-(staging) <-[...]-(source). - Edge labels carry semantics that auditors and data stewards can read.
GRAPH_TABLEreturns an ordinary row set, so its output can beJOINed to a data quality log, a change data capture table, or dbt run results.- The graph is a catalog object. It migrates with
pg_dumpand is updated in the same change as the table it describes, so documentation lives beside the schema.
Rolling it out, and one limitation
A staged adoption path: start by emitting CREATE PROPERTY GRAPH from your orchestrator's existing dependency metadata; then treat the graph like a migration, updating the declaration in the same PR as any pipeline change and making that part of the definition of done; then hook lineage queries into dbt tests, Great Expectations checks or custom monitoring, so "does this table have lineage defined?" becomes a data contract check; then show the tabular output to auditors, who need to read a table, not understand graph theory.
The caveat is depth. GRAPH_TABLE in PostgreSQL 19 does not yet support variable-length paths (->{1,3}) or path aggregation, so chains of five or more hops fall back to WITH RECURSIVE over the underlying edge tables. Less elegant, but it works.
