Data Lineage Examples: 6 Real Scenarios with SQL and Diagrams

UN exemple de lignée de données shows, for a concrete piece of SQL, which source tables and columns feed which output columns, and through which transformations the data passes along the way. The six worked examples below cover the situations data engineers actually hit: a plain INSERT-SELECT, a multi-CTE transform, a chain of views, a stored procedure with dynamic SQL, a dbt model chain, and a cross-database ETL flow. Each example gives the SQL, then describes exactly what the lineage graph looks like for it.

How to read these examples

Each lineage graph contains two kinds of edges. A direct edge means data physically flows from a source column into a target column, possibly through a function or aggregate. An indirect edge means a column shapes the result without landing in it: columns used in OÙ, REJOINDRE, et GROUPER PAR clauses. Missing the indirect edges is how impact analysis goes wrong, because dropping a filter column breaks a report just as surely as dropping a selected one. If these terms are new, start with what data lineage is et how column-level lineage works, then come back.

Example 1: a simple INSERT-SELECT data lineage example

The starting point for any lineage discussion: one statement writing aggregated data into a summary table.

INSERT INTO sales_summary (region, total_amount)
SELECT c.region, SUM(o.amount)
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
GROUP BY c.region;

The lineage graph has three tables (ordres, clients, sales_summary) and these column-level edges:

Colonne cible	Source column	Edge type	Via
sales_summary.region	clients.région	Direct	plain copy
sales_summary.total_amount	commandes.montant	Direct	SOMME()
sales_summary.*	statut des commandes	Indirect	OÙ filter
sales_summary.*	commandes.identifiant_client, clients.id	Indirect	REJOINDRE condition

Note what the graph already tells you that a table-level view would not: clients.email and every other untouched column play no role here, and statut des commandes matters even though it never appears in the output. Many lineage tools skip that last edge entirely; SQLFlow models direct and indirect lineage as separate, toggleable relationship types.

Example 2: multi-CTE transformation

CTEs are where hand-drawn lineage diagrams start to lie, because each CTE is a temporary relation the graph must pass through without losing track of the original sources.

WITH daily AS (
  SELECT order_date, customer_id, SUM(amount) AS day_total
  FROM orders
  GROUP BY order_date, customer_id
),
ranked AS (
  SELECT customer_id, day_total,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY day_total DESC) AS rn
  FROM daily
)
SELECT customer_id, day_total AS best_day_revenue
FROM ranked
WHERE rn = 1;

The lineage graph renders daily et ranked as intermediate nodes, so the full path of the interesting column reads: commandes.montant → SOMME() → daily.day_total → ranked.day_total → best_day_revenue. The window function creates a subtler chain: rn est calculé à partir de identifiant_client (partition) and day_total (ordering), and then WHERE rn = 1 makes rn an indirect input to every output column. So commandes.montant influences best_day_revenue twice: directly through the sum, and indirectly through the ranking that decides which row survives. A resolver that only pattern-matches column names cannot see that second path.

Example 3: a chain of views

Views stack. In a mature warehouse a report often sits three or four view definitions away from physical tables, and the question “where does this number really come from?” requires walking the whole chain.

CREATE VIEW v_active_customers AS
SELECT id, name, email
FROM customers
WHERE status = 'active';

CREATE VIEW v_customer_revenue AS
SELECT a.id, a.name, SUM(o.amount) AS revenue
FROM v_active_customers a
JOIN orders o ON o.customer_id = a.id
GROUP BY a.id, a.name;

The graph shows v_customer_revenue.revenue tracing through the view boundary back to commandes.montant, avec customers.status attached as an indirect edge inherited from the first view’s filter. It also exposes something useful in the other direction: v_active_customers.email is selected by the first view but consumed by nothing downstream. Dead columns like this are exactly what you want to find before a cleanup or migration, and they are invisible at table level. When view definitions use SÉLECTIONNER *, SQLFlow expands the star against the schema so every inherited column still resolves to its physical source.

Example 4: stored procedure with dynamic SQL

This is the example that separates lineage tools, because the target table name does not even exist as literal text in the code. Here is a SQL Server procedure that builds a monthly snapshot through a temp table and a dynamically assembled INSÉRER:

CREATE PROCEDURE dbo.load_monthly_snapshot @month VARCHAR(7) AS
BEGIN
  SELECT o.customer_id, SUM(o.amount) AS month_total
  INTO #monthly
  FROM dbo.orders o
  WHERE CONVERT(VARCHAR(7), o.order_date, 126) = @month
  GROUP BY o.customer_id;

  DECLARE @sql NVARCHAR(MAX) =
      N'INSERT INTO dbo.snapshot_monthly (customer_id, month_total) '
    + N'SELECT customer_id, month_total FROM #monthly';
  EXEC sp_executesql @sql;
END

The correct lineage graph hops through both obstacles: commandes.montant → SOMME() → #monthly.month_total → snapshot_monthly.month_total, avec commandes.date_de_commande as an indirect edge through the OÙ clause and the @month parameter tracked as an input to that filter. Getting there requires a real T-SQL procedural parser (not a statement splitter), temp-table tracking across statements, and resolution of the SQL string handed to sp_executesql. SQLFlow has dedicated procedural parsers for SQL Server T-SQL and Oracle PL/SQL, resolves dynamic SQL inside procedures, and additionally renders a call graph of which procedures invoke which, so procedure-to-procedure lineage is visible too. Dynamic SQL is the most common blind spot in lineage tooling; if you evaluate any tool, test it with this pattern first.

Example 5: dbt model chain

dbt gives you model-level lineage out of the box via ref(), but the interesting question is column-level: which source field feeds which mart column, through which staging renames?

-- models/staging/stg_orders.sql
SELECT id AS order_id, customer_id, amount, status
FROM {{ source('raw', 'orders') }}

-- models/marts/fct_customer_revenue.sql
SELECT customer_id, SUM(amount) AS lifetime_revenue
FROM {{ ref('stg_orders') }}
WHERE status = 'completed'
GROUP BY customer_id

Import the project’s dbt manifest into SQLFlow and the graph resolves the templated references into a concrete column chain: raw.orders.amount → stg_orders.amount → SOMME() → fct_customer_revenue.lifetime_revenue, with the rename id → id_de_commande preserved in the staging hop and statut carried as an indirect edge from the mart’s filter. Because the manifest ties models to the warehouse objects they build, the same graph reconciles dbt-managed lineage with everything else that touches those tables outside dbt.

Example 6: cross-database ETL

Real estates span engines. A typical flow: an operational SQL Server database is extracted to cloud storage, loaded into Snowflake staging, then transformed into an analytics schema. The load-and-transform side looks like this:

-- Snowflake
COPY INTO staging.orders_raw
FROM @s3_extract/orders/;

INSERT INTO analytics.fct_orders (order_id, customer_id, amount_usd)
SELECT r.order_id, r.customer_id, r.amount * fx.rate
FROM staging.orders_raw r
JOIN staging.fx_rates fx ON fx.currency = r.currency;

Within the Snowflake side, the graph shows amount_usd as a computed column with two direct parents, orders_raw.amount et fx_rates.rate, joined through the arithmetic expression, plus currency as an indirect edge from the join condition. To cover the whole pipeline, you feed SQLFlow both sides in their own dialects: the T-SQL extract logic parsed with the SQL Server parser and the Snowflake scripts parsed with the Snowflake parser, out of 39 dialect-specific parsers total. In enterprise deployments the results land in a persistent lineage repository that batch-scans estates of 100+ databases and over a million columns, so the end-to-end SQL Server-to-Snowflake picture lives in one graph and can be exported to DataHub, Microsoft Purview, or OpenMetadata.

What all six examples have in common

Every graph above was derived purely from SQL text and schema metadata. That is the core method behind SQL-based data lineage: since the transformation logic already lives in SQL, static analysis of that SQL reconstructs the lineage without agents, without query-log access, and without ever reading a row of your data. The differences between the examples are only in how hard the parsing gets, from a single INSERT-SELECT to dynamic SQL inside a procedure.

Example	What it exercises	Hard part
1. INSERT-SELECT	Direct vs indirect edges	Modeling OÙ/REJOINDRE columns as lineage
2. Multi-CTE	Intermediate relations	Window function inputs; filter-on-rank path
3. View chain	Nested definitions	Star expansion; finding dead columns
4. Stored procedure	Procedural code	Temp tables and dynamic SQL resolution
5. dbt chain	Templated SQL	Resolving ref()/source() to real objects
6. Cross-database ETL	Multiple dialects	Stitching per-engine graphs into one repository

Foire aux questions

What is a data lineage diagram?

A data lineage diagram is a directed graph in which nodes are tables, views, or columns and edges show how data flows between them through SQL transformations. Column-level diagrams, like the ones described on this page, draw one edge per source-column-to-target-column relationship, annotated with the function or clause that created it.

Can I reproduce these examples myself?

Yes. Paste any of the SQL snippets above into the free SQLFlow visualizer, pick the matching dialect, and the interactive diagram appears in seconds. The cloud edition has a free tier.

What is the difference between direct and indirect lineage?

Direct lineage means data flows from a source column into a target column, for example SOMME(commandes.montant) feeding total_amount. Indirect lineage means a column influences the result without appearing in it, such as a OÙ filter or REJOINDRE key. SQLFlow tracks both as separate, toggleable edge types; most tools capture only direct flow.

Can lineage be extracted from stored procedures and dynamic SQL?

Yes, but it needs a procedural parser, not just a query parser. SQLFlow has dedicated parsers for Oracle PL/SQL and SQL Server T-SQL, traces lineage through parameters and temp tables, resolves SQL strings built inside procedures, and draws the procedure-to-procedure call graph.

Do open-source tools handle these examples?

Partially. Libraries like SQL Lineage et sqlglot are genuinely good at parsing individual queries, and they handle examples 1 and 2 well. The examples worth testing before you commit are 4 through 6: procedural code with dynamic SQL, indirect lineage as a separate edge type, and stitching a multi-dialect estate into one interactive graph. Run your hardest stored procedure through any tool you evaluate and compare the output.

Which SQL dialects do these examples work with?

SQLFlow ships 39 dialect-specific parsers, including Snowflake, BigQuery, Redshift, Databricks, Oracle, SQL Server, PostgreSQL, MySQL, Teradata, Hive, Spark SQL, and Trino. The examples above use generic SQL, T-SQL, dbt-templated SQL, and Snowflake syntax, and each is parsed by its own grammar rather than a generic ANSI one.