PostgreSQL - Lineage

This article outlines the lineage coverage, configuration settings, metadata handling, supported scenarios, component behaviors, process flow, and known limitations for lineage extraction in PostgreSQL.

The PostgreSQL connector provides automated, production-ready lineage extraction from SQL-based datasets and query logs. It enables end-to-end visibility into data flow by tracing relationships from source tables through SQL transformations to target objects. The connector supports both table-level and column-level lineage, helping users understand data movement, perform impact analysis, troubleshoot dependencies, and support governance initiatives.

Lineage Configuration Requirements

Accurate lineage extraction depends on specific configuration settings, access permissions, and metadata availability. Configure the following settings correctly to ensure successful lineage extraction and relationship mapping.

Configuration Requirements Table

Configuration Area	Required Detail
Lineage Build	Enable lineage generation through the Build Lineage job setting.
Authentication	Configure valid PostgreSQL database connection credentials.
Access Requirements	Ensure read access to tables, views, stored procedures, functions, and query logs. Access to dataset SQL or query text is required for lineage extraction.
Function Lineage Processing	Configure function and procedure lineage expansion using OEConfig.PROCESS_FUNCTION_RELATED_LINEAGE.
Relationship Extraction	Configure join and relationship extraction using OEConfig.LINEAGE_BUILD_RELEATIONSHIPS.
Optional Lineage Settings	Use OEConfig.LINEAGE_SHALLOW for fallback lineage generation and OEConfig.HANDLE_TEMP_TABLE_WITH_PREFIX for temporary table handling.

Stored procedure and function lineage require access to the corresponding source definitions. Query-log lineage requires access to PostgreSQL query logs.

Missing SQL definitions, insufficient permissions, or disabled lineage settings may prevent the connector from building complete lineage relationships.

Lineage Components

Component	Availability
Tables	✅
Columns	⚠️
Views	✅
SELECT Queries	✅
INSERT Queries	✅
UPDATE Queries	⚠️
DELETE Queries	⚠️
MERGE / UPSERT	⚠️
Stored Procedures	⚠️
Functions	⚠️
Query Logs	✅
Join Relationships	⚠️
Temp Tables	⚠️
Materialized Views	❌
Reports / BI Objects	❌
Expressions	⚠️
Aggregations	⚠️
Column Aliases	✅
Derived Columns	⚠️
Renamed Columns	✅
Temp Columns (fallback)	✅
Star (*) Expansion	⚠️
Function-based Columns	⚠️

The ⚠️ icon indicates partially supported functionality with limited lineage coverage in applicable scenarios.

Supported Use Cases

The PostgreSQL connector supports lineage extraction across SQL-based datasets, query execution patterns, views, and data movement operations. These scenarios represent the areas where lineage extraction functions as expected and produces reliable lineage relationships.

Supported Lineage Scenarios

Supported Scenario	Details
Table-to-Table Lineage	Supports lineage extraction from SQL statements that read data from source tables and write to target tables through SELECT and JOIN operations.
INSERT INTO > SELECT Lineage	Tracks data movement from source tables to target tables through INSERT INTO >SELECT statements.
View Lineage	Extracts lineage from CREATE VIEW statements and maps views to their underlying source tables and columns.
Column-Level Lineage	Supports direct column mappings, aliases, and source-to-target column relationships.
Join Relationship Extraction	Identifies and maps relationships between tables used in JOIN operations.
Query-Log Lineage	Extracts lineage from PostgreSQL query logs to trace database query activity and data flow.
Expression-Based Transformations	Supports lineage extraction for basic SQL expressions and straightforward column transformations.

Column-level lineage accuracy improves when SQL statements use explicit table aliases and fully qualified column references.

Lineage extraction requires access to dataset SQL definitions, query text, and related metadata. Missing source definitions may result in incomplete lineage relationships.

Partial or Limited Coverage

Certain lineage scenarios have partial coverage due to SQL complexity, metadata availability, or limitations in source object definitions. These scenarios may produce incomplete lineage relationships or reduced column-level mapping accuracy.

Scenarios

Scenario	Limitation Description
Complex Expressions	Column-level lineage for complex expressions may degrade to partial lineage or fixed-value mappings.
Functions in SQL	Function-based lineage depends on OEConfig.PROCESS_FUNCTION_RELATED_LINEAGE. Without this configuration, function lineage may be incomplete.
UPDATE Statements	Lineage extraction depends on the ability to resolve source and target column mappings within the SET clause.
DELETE Statements	Supports limited lineage refinement and dependency tracking.
MERGE / UPSERT Statements	Lineage coverage is partial and depends on the complexity of the SQL statement.
Common Table Expressions (CTEs) and Subqueries	Supports lineage extraction, but column-level mappings may be incomplete in complex scenarios.
Stored Procedures and Functions	Lineage extraction depends on the availability of procedure and function definitions. Missing source text may result in incomplete lineage.
Wildcard (*) Columns	Wildcard column references may not fully resolve to individual source columns.
Temporary Tables	Temporary tables may result in partial lineage when source-to-target relationships cannot be fully resolved.

Using explicit table aliases, fully qualified column names, and accessible source definitions can improve lineage accuracy and column-level mapping.

Complex SQL logic, unavailable procedure definitions, and dynamically generated SQL statements may prevent the connector from building complete lineage relationships.

Unsupported Scenarios

The PostgreSQL connector does not support lineage extraction for certain SQL constructs, runtime-generated queries, external execution mechanisms, and non-database assets due to limitations in SQL parsing and metadata availability.

Unsupported Lineage

Not Supported	Description
OPENQUERY and External Query Execution	Queries executed through external query mechanisms are not supported for lineage extraction.
Dynamic SQL	Runtime-generated SQL statements cannot be resolved and are not included in lineage.
EXEC Procedure Calls	Single-word EXEC procedure calls without accessible SQL definitions are not supported.
BEGIN...END Procedural Blocks	Simple procedural blocks and procedural execution flows are not fully processed for lineage extraction.
REFRESH MATERIALIZED VIEW	Materialized view refresh operations are not included in lineage generation.
MDX and Non-SQL Constructs	MDX statements and non-SQL query languages are outside the scope of lineage extraction.
OPTIMIZE and DESC Statements	Administrative and metadata inspection commands are not processed for lineage.
SSIS Package Lineage	Lineage extraction from SSIS (.dtsx) packages is not supported by the PostgreSQL connector.
Report and BI Lineage	Lineage for reporting, dashboard, and business intelligence assets is outside the scope of the database connector.

Unsupported scenarios do not generate lineage relationships and may appear disconnected or absent in lineage visualizations.

For lineage involving reporting tools, ETL platforms, or external processing frameworks, use the corresponding connector that supports those assets.

Current Functional Status

This section describes the current lineage coverage and functional behavior supported by the PostgreSQL connector.

Status Area	Details
Overall Coverage	Partial coverage across PostgreSQL datasets, query logs, views, stored procedures, and functions.
Lineage Depth	Supports both table-level and column-level lineage through SQL parsing and relationship mapping.
Supported Inputs	Tables, views, query logs, SQL datasets, and supported SQL transformation statements.
Functional Scope	Lineage extraction works reliably for standard SQL operations, including SELECT, INSERT, JOIN, and VIEW definitions.
Limitation Areas	Complex expressions, function-based transformations, stored procedures, dynamic SQL, temporary tables, and certain advanced SQL patterns.
Resulting Output	Produces reliable lineage for most standard SQL-based scenarios, with partial mapping in complex or unsupported cases.

The PostgreSQL connector is production-ready for standard SQL-based lineage extraction and provides strong table-level and column-level lineage capabilities. However, complex expressions, functions, stored procedures, and unsupported SQL patterns may result in incomplete lineage and could require manual validation or lineage supplementation.

---

Copyright © 2026, OvalEdge LLC, Peachtree Corners GA USA