Sefism is free for learners. We are here to help you grow, one step at a time.Join for free
Sefism.
Data & Analytics

Column-Level Data Lineage and Impact Analysis from SQL Code

A final year project that builds a column-level data lineage tool for analytics teams: it parses SQL and dbt models, works out which source columns feed every dashboard field, and warns in a pull request when a change will break or alter downstream reports. A data engineering project with a compiler-style core.

Topics
Difficulty

Advanced

Needs a team, real planning, and a supervisor who knows the area.
Effort

2 semesters · 2 to 3 students

Deliverables

6 to ship

3 optional extras

Suggested stack

Pythonsqlglotdbt CorePostgreSQLFastAPIReact FlowGitHub Actions
A suggestion, not a requirement. Swap anything for what you already know.

What you should ship

  • A SQL analyser (built on sqlglot's parser) that resolves column-level lineage through CTEs, subqueries, joins, aliases, SELECT *, unions and window functions for at least two SQL dialects.
  • A dbt integration that reads manifest and catalog files to know table schemas and builds a full project lineage graph.
  • A graph store and API that answer 'where does this column come from' and 'what depends on this column' with transformation type (direct copy, renamed, aggregated, filtered).
  • A web interface to browse the lineage graph with search, focus on one column, and upstream and downstream depth controls.
  • A GitHub Action that comments on a pull request listing downstream columns, models and dashboards affected by the changed SQL.
  • A benchmark suite of at least 150 hand-labelled SQL statements with expected lineage, and an accuracy report comparing your tool with an existing open-source parser.

If you have time left

  • Linking lineage to a BI tool's metadata (for example Metabase or Superset) so dashboard charts appear as graph nodes.
  • Propagating column tags such as 'personal data' downstream to show where sensitive fields end up.
  • Emitting OpenLineage events so the graph can be viewed in other catalogues.

The problem analytics teams have with changing SQL

Analytics teams keep hundreds of SQL models, often in dbt, that feed dashboards used by finance, sales and operations. Someone renames a column, changes a join or filters out test orders, and a revenue chart three layers downstream quietly changes. Nobody knows until a manager asks why last month's number moved. Table-level lineage (model A depends on model B) is common; what teams need is column-level: which exact fields depend on the column I am about to change.

Large companies built internal systems for this. Uber's Databook and Airbnb's Dataportal are well-known examples of metadata platforms that include lineage. Smaller teams do not have the people to build or run them.

What already exists and the gap

  • dbt shows model-level lineage in its docs site; column-level lineage is a feature of its paid cloud product.
  • DataHub and OpenMetadata are open-source catalogues that extract column lineage from SQL using parsers, but they are large platforms to deploy and operate.
  • sqllineage and openlineage-sql are open-source parsers that extract lineage from SQL statements.
  • sqlglot is a SQL parser and transpiler with utilities that make lineage extraction easier.

The gap: a light tool a small team can run in CI in minutes, that focuses on one job: accurate column lineage plus change impact in the pull request, where the decision is made. Reviewers see what their change affects before merging.

What you build

Roles: analytics engineer (writes SQL, reads PR comments), data consumer (browses lineage to understand a metric), admin (connects projects).

Flows:

  1. The tool loads a dbt project's compiled SQL, manifest and catalog.
  2. Each model's SQL is parsed, names are qualified using the schema, and column lineage is resolved.
  3. Edges are stored with transformation type; the graph is served by an API.
  4. In a pull request, the Action compares lineage before and after and posts affected downstream columns.
  5. Users explore the graph in the browser.

The technically interesting part

This is name resolution, the same problem a compiler solves. You need scopes for CTEs and subqueries, alias resolution, star expansion using the catalog, correlated subqueries, and UNION column alignment by position. Aggregations and window functions change the relationship type. Some statements are ambiguous without schema information; show how your tool degrades (marks an edge as uncertain) instead of guessing.

The impact analysis is a graph diff: build lineage for the base branch and the PR branch, find changed nodes and edges, and walk downstream. Keeping this fast enough for CI on a few hundred models is a real engineering constraint.

How to evaluate it

  • Accuracy: precision and recall of column edges on your 150+ labelled statements, grouped by feature (CTEs, stars, unions, windows).
  • Comparison: same benchmark against an existing open-source parser; discuss cases where each fails.
  • Real projects: run on at least two public dbt projects (for example the jaffle shop sample and a larger open-source project); report coverage and uncertain edges.
  • Performance: CI run time for a full project and for a one-model change.
  • Users: three or more analytics engineers review PR comments and rate usefulness and false alarms.

Suggested plan

  1. Weeks 1 to 4: study sqlglot's AST, read how DataHub describes its parser, build the benchmark set.
  2. Weeks 5 to 11: scope and name resolution, star expansion, core lineage for SELECT statements.
  3. Weeks 12 to 15: dbt integration, graph storage, API.
  4. Weeks 16 to 20: PR impact analysis and GitHub Action.
  5. Weeks 21 to 24: web graph explorer.
  6. Weeks 25 to 29: benchmark evaluation, real-project runs, user feedback, write-up.

Path to a product

Release the core as open source to build trust and adoption among dbt users; the first users are small analytics teams at startups and agencies. Sell a hosted version with BI tool connectors, history and team features per seat, or support contracts. Data consultancies are a good channel because they inherit messy projects and need to understand them fast.

Scope warning

Do not build a full data catalogue with search, glossaries and ownership workflows. Do not support every SQL dialect; pick two and do them well. Do not parse stored procedures or procedural SQL in the core deliverables.

Learn what you need on Sefism

Get help with this idea

The hardest scoping call is which SQL features to support fully and which to mark as uncertain. If you want a second opinion on scope, stack or how to evaluate it before you pitch it to your supervisor, a mentor can help you shape it. You still build it; the guidance is about making the plan realistic and the results defensible. New here? create a free account first.

Ideas and guidance, not finished projects

These are project ideas and scoping guidance, published free for students to use as a starting point. I do not build, write, or sell final-year projects, and I do not complete coursework for anyone. Take an idea, make it yours, and build it.

Not sure this one fits you?

I help students pick an idea that matches their skills and their deadline, then scope it down until it is finishable. Guidance only, never done for you.

Discuss this idea
Work with me

Stuck on something specific?

Writing only gets you so far. If you want an answer to your situation rather than the general case, book a session and we will work through it together. Sessions are free for approved Sefism members, and a few slots open each week.

Book a free sessionSee all services

Follow along

New writing, resources and project ideas land here first.