# Data Lineage Explorer

> Paste SQL or dbt models: table lineage graph, column lineage and upstream/downstream of any field. Paste SQL (CREATE VIEW/TABLE AS SELECT, INSERT … SELECT, MERGE, UPDATE, CTEs, JOINs, UNION) or dbt models with {{ ref() }} and {{ source() }}. A built-in tokenizer and parser derives table-level lineage and column-level lineage for direct references and aliases, marks expressions as derived or aggregated, and traces everything upstream and downstream of a chosen table or column.

Part of usefulweb.site (https://usefulweb.site/). Results are deterministic calculations from the inputs and the listed assumptions; the API returns exactly what the web page shows.

## Use it
- Human interface: https://datalineageexplorer.usefulweb.site/ (interactive tool at https://datalineageexplorer.usefulweb.site/tool)
- JSON API: POST https://datalineageexplorer.usefulweb.site/api/v1/calculate with a JSON object of inputs (schema: https://datalineageexplorer.usefulweb.site/api/v1/tool)
- OpenAPI: https://datalineageexplorer.usefulweb.site/openapi.json
- MCP (Streamable HTTP): https://datalineageexplorer.usefulweb.site/mcp - tool `data_lineage_explorer`

## Inputs
- sql (textarea): SQL / dbt models - Separate statements with ";". Mark dbt models with a line "-- model: name" (or "-- file: models/name.sql"); the model’s SELECT becomes that table.
- file (file): Or upload a .sql file
- focus (text): Focus table or column - table or table.column. Defaults to the last target in the input.
- direction (select): Trace

## Method
- dbt Jinja is resolved first: {{ ref("m") }} → m, {{ ref("pkg","m") }} → m, {{ source("s","t") }} → s.t, {{ this }} → the current model, {{ config(materialized=…) }} is read; other Jinja is stripped with a note.
- A hand-written tokenizer handles quoted identifiers ("x", `x`, [x]), strings, comments, dollar-quoting and operators; statements are split at top-level semicolons.
- A recursive-descent parser understands WITH/CTEs (column lists, RECURSIVE), SELECT lists with aliases, FROM/JOIN … ON/USING, comma joins, derived tables, scalar/EXISTS/IN subqueries, UNION/INTERSECT/EXCEPT, SELECT */t.* (incl. EXCEPT/EXCLUDE), CREATE … AS, INSERT … SELECT (positional mapping to the column list or known DDL), MERGE (UPDATE SET / INSERT VALUES) and UPDATE … SET … FROM.
- Each output column is resolved through CTEs and subqueries to base-table columns. A bare column reference is "direct" (or "renamed" when aliased); anything else is "derived", "aggregate" if it uses an aggregate function, or "constant" without column inputs. Columns used only in JOIN/WHERE/GROUP BY are listed as filter columns of the statement.
- Unqualified columns are resolved against the tables in scope using known columns (from CREATE TABLE DDL or earlier statements); if several tables could supply a column it is attributed to all and flagged as ambiguous.
- Upstream/downstream tracing is a breadth-first walk over column edges (or table edges for a table focus) across all statements; the reported transform is the strongest transformation along the path.
- Not supported: dynamic SQL, stored procedures, PIVOT/UNPIVOT column lineage, column lineage of table functions (UNNEST/FLATTEN/LATERAL VIEW), Jinja loops and macros, and SELECT * from tables whose columns are unknown (kept as a "*" star edge).