{"id":6748,"date":"2026-07-12T02:05:45","date_gmt":"2026-07-12T10:05:45","guid":{"rendered":"https:\/\/www.gudusoft.com\/data-lineage-examples\/"},"modified":"2026-07-12T02:05:45","modified_gmt":"2026-07-12T10:05:45","slug":"data-lineage-examples","status":"publish","type":"page","link":"https:\/\/www.gudusoft.com\/pt\/data-lineage-examples\/","title":{"rendered":"Data Lineage Examples: 6 Real Scenarios with SQL and Diagrams"},"content":{"rendered":"

UM exemplo de linhagem de dados<\/strong> 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<\/code>, 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.<\/p>\n\n\n\n

\n\n

Every example on this page is reproducible:<\/strong> paste the SQL into the free SQLFlow lineage visualizer<\/a> and you get the interactive diagram described below.<\/p>\n\n<\/div><\/div>\n\n\n\n

How to read these examples<\/h2>\n\n\n\n

Each lineage graph contains two kinds of edges. A direct<\/strong> edge means data physically flows from a source column into a target column, possibly through a function or aggregate. An indirect<\/strong> edge means a column shapes the result without landing in it: columns used in WHERE<\/code>, JOIN<\/code>, e GROUP BY<\/code> 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<\/a> e how column-level lineage works<\/a>, then come back.<\/p>\n\n\n\n

Example 1: a simple INSERT-SELECT data lineage example<\/h2>\n\n\n\n

The starting point for any lineage discussion: one statement writing aggregated data into a summary table.<\/p>\n\n\n\n

INSERT INTO sales_summary (region, total_amount)\nSELECT c.region, SUM(o.amount)\nFROM orders o\nJOIN customers c ON o.customer_id = c.id\nWHERE o.status = 'completed'\nGROUP BY c.region;<\/code><\/pre>\n\n\n\n
The lineage graph has three tables (orders<\/code>, customers<\/code>, sales_summary<\/code>) and these column-level edges:<\/p>\n\n\n\n
\n
Target column<\/th>Source column<\/th>Edge type<\/th>Via<\/th><\/tr><\/thead>
sales_summary.region<\/code><\/td>customers.region<\/code><\/td>Direct<\/td>plain copy<\/td><\/tr>\n
sales_summary.total_amount<\/code><\/td>orders.amount<\/code><\/td>Direct<\/td>SUM()<\/code><\/td><\/tr>\n
sales_summary.*<\/code><\/td>orders.status<\/code><\/td>Indirect<\/td>WHERE<\/code> filter<\/td><\/tr>\n
sales_summary.*<\/code><\/td>orders.customer_id<\/code>, customers.id<\/code><\/td>Indirect<\/td>JOIN<\/code> condition<\/td><\/tr>\n<\/tbody><\/table><\/figure>\n\n\n\n
Note what the graph already tells you that a table-level view would not: customers.email<\/code> and every other untouched column play no role here, and orders.status<\/code> 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.<\/p>\n\n\n\n
Example 2: multi-CTE transformation<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
WITH daily AS (\n  SELECT order_date, customer_id, SUM(amount) AS day_total\n  FROM orders\n  GROUP BY order_date, customer_id\n),\nranked AS (\n  SELECT customer_id, day_total,\n         ROW_NUMBER() OVER (PARTITION BY customer_id\n                            ORDER BY day_total DESC) AS rn\n  FROM daily\n)\nSELECT customer_id, day_total AS best_day_revenue\nFROM ranked\nWHERE rn = 1;<\/code><\/pre>\n\n\n\n
The lineage graph renders daily<\/code> e ranked<\/code> as intermediate nodes, so the full path of the interesting column reads: orders.amount<\/code> → SUM()<\/code> → daily.day_total<\/code> → ranked.day_total<\/code> → best_day_revenue<\/code>. The window function creates a subtler chain: rn<\/code> is computed from customer_id<\/code> (partition) and day_total<\/code> (ordering), and then WHERE rn = 1<\/code> makes rn<\/code> an indirect input to every output column. So orders.amount<\/code> influences best_day_revenue<\/code> 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.<\/p>\n\n\n\n
Example 3: a chain of views<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
CREATE VIEW v_active_customers AS\nSELECT id, name, email\nFROM customers\nWHERE status = 'active';\n\nCREATE VIEW v_customer_revenue AS\nSELECT a.id, a.name, SUM(o.amount) AS revenue\nFROM v_active_customers a\nJOIN orders o ON o.customer_id = a.id\nGROUP BY a.id, a.name;<\/code><\/pre>\n\n\n\n
The graph shows v_customer_revenue.revenue<\/code> tracing through the view boundary back to orders.amount<\/code>, with customers.status<\/code> 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<\/code> 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 SELECT *<\/code>, SQLFlow expands the star against the schema so every inherited column still resolves to its physical source.<\/p>\n\n\n\n
Example 4: stored procedure with dynamic SQL<\/h2>\n\n\n\n
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 INSERT<\/code>:<\/p>\n\n\n\n
CREATE PROCEDURE dbo.load_monthly_snapshot @month VARCHAR(7) AS\nBEGIN\n  SELECT o.customer_id, SUM(o.amount) AS month_total\n  INTO #monthly\n  FROM dbo.orders o\n  WHERE CONVERT(VARCHAR(7), o.order_date, 126) = @month\n  GROUP BY o.customer_id;\n\n  DECLARE @sql NVARCHAR(MAX) =\n      N'INSERT INTO dbo.snapshot_monthly (customer_id, month_total) '\n    + N'SELECT customer_id, month_total FROM #monthly';\n  EXEC sp_executesql @sql;\nEND<\/code><\/pre>\n\n\n\n
The correct lineage graph hops through both obstacles: orders.amount<\/code> → SUM()<\/code> → #monthly.month_total<\/code> → snapshot_monthly.month_total<\/code>, with orders.order_date<\/code> as an indirect edge through the WHERE<\/code> clause and the @month<\/code> 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<\/code>. 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.<\/p>\n\n\n\n
Example 5: dbt model chain<\/h2>\n\n\n\n
dbt gives you model-level lineage out of the box via ref()<\/code>, but the interesting question is column-level: which source field feeds which mart column, through which staging renames?<\/p>\n\n\n\n
-- models\/staging\/stg_orders.sql\nSELECT id AS order_id, customer_id, amount, status\nFROM {{ source('raw', 'orders') }}\n\n-- models\/marts\/fct_customer_revenue.sql\nSELECT customer_id, SUM(amount) AS lifetime_revenue\nFROM {{ ref('stg_orders') }}\nWHERE status = 'completed'\nGROUP BY customer_id<\/code><\/pre>\n\n\n\n
Import the project’s dbt manifest into SQLFlow and the graph resolves the templated references into a concrete column chain: raw.orders.amount<\/code> → stg_orders.amount<\/code> → SUM()<\/code> → fct_customer_revenue.lifetime_revenue<\/code>, with the rename id<\/code> → order_id<\/code> preserved in the staging hop and status<\/code> 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.<\/p>\n\n\n\n
Example 6: cross-database ETL<\/h2>\n\n\n\n
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:<\/p>\n\n\n\n
-- Snowflake\nCOPY INTO staging.orders_raw\nFROM @s3_extract\/orders\/;\n\nINSERT INTO analytics.fct_orders (order_id, customer_id, amount_usd)\nSELECT r.order_id, r.customer_id, r.amount * fx.rate\nFROM staging.orders_raw r\nJOIN staging.fx_rates fx ON fx.currency = r.currency;<\/code><\/pre>\n\n\n\n
Within the Snowflake side, the graph shows amount_usd<\/code> as a computed column with two direct parents, orders_raw.amount<\/code> e fx_rates.rate<\/code>, joined through the arithmetic expression, plus currency<\/code> 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.<\/p>\n\n\n\n
What all six examples have in common<\/h2>\n\n\n\n
Every graph above was derived purely from SQL text and schema metadata. That is the core method behind SQL-based data lineage<\/a>: 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<\/code> to dynamic SQL inside a procedure.<\/p>\n\n\n\n
\n
Example<\/th>What it exercises<\/th>Hard part<\/th><\/tr><\/thead>
1. INSERT-SELECT<\/td>Direct vs indirect edges<\/td>Modeling WHERE<\/code>\/JOIN<\/code> columns as lineage<\/td><\/tr>\n
2. Multi-CTE<\/td>Intermediate relations<\/td>Window function inputs; filter-on-rank path<\/td><\/tr>\n
3. View chain<\/td>Nested definitions<\/td>Star expansion; finding dead columns<\/td><\/tr>\n
4. Stored procedure<\/td>Procedural code<\/td>Temp tables and dynamic SQL resolution<\/td><\/tr>\n
5. dbt chain<\/td>Templated SQL<\/td>Resolving ref()<\/code>\/source()<\/code> to real objects<\/td><\/tr>\n
6. Cross-database ETL<\/td>Multiple dialects<\/td>Stitching per-engine graphs into one repository<\/td><\/tr>\n<\/tbody><\/table><\/figure>\n\n\n\n
Frequently asked questions<\/h2>\n\n\n\n
What is a data lineage diagram?<\/h3>\n\n\n
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.<\/p>\n\n\n\n
Can I reproduce these examples myself?<\/h3>\n\n\n
Yes. Paste any of the SQL snippets above into the free SQLFlow visualizer<\/a>, pick the matching dialect, and the interactive diagram appears in seconds. The cloud edition has a free tier.<\/p>\n\n\n\n
What is the difference between direct and indirect lineage?<\/h3>\n\n\n
Direct lineage means data flows from a source column into a target column, for example SUM(orders.amount)<\/code> feeding total_amount<\/code>. Indirect lineage means a column influences the result without appearing in it, such as a WHERE<\/code> filter or JOIN<\/code> key. SQLFlow tracks both as separate, toggleable edge types; most tools capture only direct flow.<\/p>\n\n\n\n
Can lineage be extracted from stored procedures and dynamic SQL?<\/h3>\n\n\n
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.<\/p>\n\n\n\n
Do open-source tools handle these examples?<\/h3>\n\n\n
Partially. Libraries like sqllineage<\/code> e sqlglot<\/code> 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.<\/p>\n\n\n\n
Which SQL dialects do these examples work with?<\/h3>\n\n\n
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.<\/p>\n\n\n\n
\n\n
Turn your own SQL into a lineage diagram<\/h2>\n\n\n
Paste a query, a procedure, or a whole script into the free visualizer and get the column-level graph in seconds. For estates of many databases, talk to us about the enterprise repository.<\/p>\n\n\n
\n
Try SQLFlow free<\/a><\/div>\n\n
Request an enterprise demo<\/a><\/div>\n<\/div>\n\n<\/div><\/div>\n\n\n\n