Skip to content

Column-Level Data Lineage

General SQL Parser (GSP) does not stop at "which tables feed which tables." Its data-flow analyzer (dlineage) answers the harder question:

For every output column, which source columns feed it — through which functions, casts, subqueries, joins, set operators, and stored-procedure calls?

This guide shows how to get column-level lineage from GSP, and then walks through a capability most parsers simply give up on: tracing lineage through an opaque in-database script such as SQL Server's sp_execute_external_script (the Python/R machine-learning entry point).

The one call you need

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.dlineage.DataFlowAnalyzer;
import gudusoft.gsqlparser.dlineage.dataflow.model.Option;
import gudusoft.gsqlparser.dlineage.dataflow.model.xml.dataflow;

Option option = new Option();
option.setVendor(EDbVendor.dbvmssql);   // pick your dialect
option.setSimpleOutput(false);          // keep intermediate hops

DataFlowAnalyzer analyzer = new DataFlowAnalyzer(sqlText, option);
analyzer.generateDataFlow();
dataflow model = analyzer.getDataFlow();   // walk model.getRelationships()

Each relationship is a column-level edge: a target column, its sources, an edge type (fdd = value flows into the target; fdr = the column only filters or joins), and an effectType (select, insert, function, …).

A first look: lineage that follows the expression tree

Take an ordinary aggregate load:

1
2
3
4
5
6
7
8
INSERT INTO mart.customer_summary (cust_id, full_name, order_total)
SELECT c.id,
       c.first_name + ' ' + c.last_name,
       SUM(o.amount)
FROM   sales.customers c
JOIN   sales.orders o ON c.id = o.cust_id
WHERE  o.status = 'PAID'
GROUP BY c.id, c.first_name, c.last_name;

GSP resolves each target column to its real source columns, including the functions on the path:

1
2
3
4
5
mart.customer_summary.cust_id      <= sales.customers.id
mart.customer_summary.full_name    <= sales.customers.first_name, sales.customers.last_name   (via  + )
mart.customer_summary.order_total  <= SUM  <= sales.orders.amount
-- filter / join columns are reported separately (fdr):
INSERT-SELECT-1.RelationRows       <= sales.customers.id, sales.orders.cust_id, sales.orders.status

Notice what you get for free: the concatenation feeding full_name is split into its two real source columns, the aggregate order_total is traced through SUM back to sales.orders.amount, and the WHERE/JOIN columns are kept apart from the projection lineage as fdr edges. That separation of "value flow" from "row filter" is what makes GSP lineage usable for impact analysis and migration, not just a pretty graph.

Table-level lineage, too

The same model answers the coarser "which source tables feed each target table" question — just deduplicate the fdd edges by parent table.

The hard case: lineage through an opaque script

SQL Server 2016+ runs Python/R/Java inside the database with sp_execute_external_script. A typical machine-learning scoring procedure looks like this:

1
2
3
4
5
6
7
8
9
CREATE PROCEDURE dbo.score_loan_term
AS
BEGIN
    EXEC sp_execute_external_script
        @language = N'Python',
        @script   = N'OutputDataSet = InputDataSet',   -- the model runs here
        @input_data_1 = N'SELECT loan_id, orig_term FROM dbo.stg_loan WHERE flag = 1'
    WITH RESULT SETS ((loan_id INT, orig_term INT));
END;

Most lineage tools see the procedure as a black box: the @input_data_1 query is just a string parameter, so dbo.stg_loan never appears as a source, and the scored result set has no upstream. The real feed of the ML pipeline silently vanishes.

GSP parses that embedded query and recovers its source lineage:

1
2
3
dbo.stg_loan.loan_id    -> <result set>.loan_id
dbo.stg_loan.orig_term  -> <result set>.orig_term
dbo.stg_loan.flag       -> <result set>.RelationRows   (WHERE filter)

Following lineage through the script

The input query is only half the flow. The other half — how the script's input maps to the declared WITH RESULT SETS output — lives inside opaque Python. GSP models it with an explicit, opt-in-and-labeled assumption so lineage can flow end to end, for example across an INSERT ... EXEC:

1
2
3
4
5
6
CREATE PROCEDURE dbo.load_scores
AS
BEGIN
    INSERT INTO dbo.scored_results (loan_id, orig_term)
    EXEC dbo.score_loan_term;      -- consumes the scored result set
END;

Running the analyzer over both procedures yields a fully connected chain from the base table all the way to the loaded table:

1
2
3
[fdd/select]                      RS-1.loan_id             <= dbo.stg_loan.loan_id
[fdd/external_script_passthrough] RS-2.loan_id             <= RS-1.loan_id
[fdd/insert]                      dbo.scored_results.loan_id <= RS-2.loan_id

The column-level graph GSP produces for these two procedures makes the flow obvious end to end — every column of the source table reaches the loaded table, and the hop across the opaque script is called out on its own:

Column-level lineage traced through an sp_execute_external_script call: dbo.stg_loan flows into the input-query result set, across an external_script_passthrough hop into the WITH RESULT SETS output, and on into dbo.scored_results

The hop across the Python script carries a dedicated effect type, external_script_passthrough, so it is never confused with proven lineage. In simpleOutput mode the whole chain flattens to one edge — and it keeps the marker, so a flattened dbo.stg_loan → dbo.scored_results edge is still reported as external_script_passthrough, never as a plain insert:

1
[fdd/external_script_passthrough] dbo.scored_results.loan_id <= dbo.stg_loan.loan_id

Both call shapes are supported: a wrapping stored procedure (as above) and the direct INSERT INTO t EXEC sp_execute_external_script … WITH RESULT SETS (…).

Turning the assumption on or off

The passthrough model is controlled by one option and is on by default:

1
2
3
4
5
6
7
8
Option option = new Option();
option.setVendor(EDbVendor.dbvmssql);

// on by default; set false to suppress the assumption and keep only proven lineage
option.setAssumeExternalScriptPassthrough(true);

DataFlowAnalyzer analyzer = new DataFlowAnalyzer(sqlText, option);
analyzer.generateDataFlow();

With it off, GSP still recovers the proven half (the @input_data_1 query's source lineage) but stops at the script boundary — the scored result set has no modeled input, and an INSERT ... EXEC of it produces no stg_loan → scored_results edge.

Assumed vs. proven — why it is a separate edge type

The script body can reorder, drop, or synthesize columns, so a positional input→output mapping is an assumption, not a fact. GSP keeps it honest:

  • every assumed edge is tagged external_script_passthrough, in full output and after simplification — it can always be filtered out or shown differently;
  • when the input query is SELECT * (unknown width), the assumption is declined rather than guessed;
  • a variable-valued @input_data_1 (whose text is not statically known) is left unanalyzed instead of fabricated.

This is the same principle that runs through all of GSP lineage: never publish an edge you cannot justify, and when you must assume, label the assumption.

Where to go next