SQL SERVER – Data Lineage Tracking in ETL Processes – Notes from the Field #124

Data lineage tracking in ETL processes helps answer a basic question: where did this value come from? In Notes from the Field #124, Tim Mitchell explained why that question deserves attention during design. Extracting, transforming and loading data is only part of understanding its path.

Guest contribution: Tim Mitchell. Tim was introduced in this 2016 article as a partner at Linchpin People. The original introduction mentioned a FOR LOOP Container by mistake; the subject here is data lineage.

Follow data through the ETL process

Tim’s original lesson starts with a familiar request: someone wants to know where a reported value originated. Data can pass through several sources, transformations and destinations. As those steps multiply, tracing the result becomes harder.

A useful lineage design records the source and the steps that produced the destination data. Row-level traceability can be valuable, but lineage also exists at dataset and column levels. The required detail depends on the questions the system must answer.

This work takes planning. A load can finish successfully without capturing enough information to explain its output. As Tim emphasized, lineage belongs alongside logging and auditing in a well-designed ETL process.

Why the extra information matters

• Trust: readers can investigate how a value was produced instead of relying on an unexplained total.
• Troubleshooting: source identifiers and transformation details help narrow the search for incorrect results.
• Completeness: tracing records helps investigate rejected, filtered or missing data.
• Visibility: the transformation path makes business rules easier to find and review.

These benefits do not prove the data is correct. Lineage describes its path; validation checks whether the values and rules are right. Both are needed when a report drives an important decision.

A small example of row-level traceability

Editorial example: consider two sales rows that become one customer total. A single source-row identifier cannot describe both contributions. This SQL Server example uses a table variable and leaves no permanent table behind.

DECLARE @SourceSales TABLE
(
    SaleId int PRIMARY KEY,
    CustomerId int,
    Amount decimal(10, 2)
);
INSERT INTO @SourceSales (SaleId, CustomerId, Amount)
VALUES (101, 7, 20.00), (102, 7, 30.00);
SELECT CustomerId, SUM(Amount) AS TotalAmount
FROM @SourceSales
GROUP BY CustomerId;
SELECT CustomerId, SaleId AS ContributingSourceSaleId
FROM @SourceSales
ORDER BY CustomerId, SaleId;

The first result is customer 7 with a total of 50.00. The second result identifies sales 101 and 102 as contributors. In a persisted load, a separate mapping can retain those relationships when row-level traceability is required.

Also record the source system, load run, target key and transformation version. For an aggregate, retain enough source detail to explain all contributions. Avoid copying sensitive payloads into lineage logs when identifiers and controlled references are sufficient.

Choose the right level of detail

Tim noted that a small, temporary or single-use process does not always need an elaborate lineage design. Start with the investigation and audit questions that matter. Then decide which identifiers and processing details must survive each load.

Test that decision by selecting a destination value and tracing it back. Can you identify the contributing sources, the applied rule and the relevant run? A diagram of connected tables alone does not establish that level of traceability.

Data lineage is not proof that a result is correct, it is the path you need to investigate that result.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.