Transformation-Based Data Lineage in Data Warehouses

Problem 1: Path-Sharing Problem

Data warehouses commonly consolidate multiple source tables into shared harmonizing tables before producing downstream datasets. While this architecture simplifies data integration, it introduces an important limitation for attribute-level data lineage.

When multiple input tables are merged into a common harmonizing table, traditional lineage tools often lose the ability to distinguish which source actually contributed to a particular downstream attribute. As a result, they report every possible upstream path rather than the true transformation path.

Example

Consider the following architecture.

When tracing an attribute in Consumption Table X, most lineage tools report both possible paths:

However, if the attribute actually originates only from Input Table A, the correct lineage is:

The additional path through Input Table B is a false dependency introduced solely because both inputs share the same harmonizing table. Consequently, the reported lineage becomes broader than the actual data flow.

I refer to this as the Path-Sharing Problem in Data Lineage.

Problem 2: Mapping Tables

In enterprise data warehouses, source systems often use different values to represent the same business concept. During the ETL process, these source-specific values are translated into standardized values using mapping tables before being loaded into the harmonized data model.

For example, different source systems may represent the customer type of an individual customer as Personal, Household, or Private. Although these values differ, they all represent the same business concept and are mapped to the harmonized value Individual.

This mapping ensures consistent reporting and analytics across source systems.

The Problem

Off-the-shelf attribute-level lineage tools often stop tracing when they encounter a mapping table. As a result, the reported lineage ends at:

However, the mapping table only translates source-specific values into harmonized values. It is not the true origin of the data.

The correct lineage should bypass the mapping table as a lineage endpoint and continue to the original source attribute:

The mapping step should still be recognized as a transformation rule, while the source table remains the true origin of the attribute.