How We Built Data Lineage Automation Across a 400-Table Snowflake and dbt Warehouse in Six Weeks

Quick Summary

Introduction

A column rename shipped on a Tuesday. By Wednesday, eleven dashboards were wrong, and nobody could say which of them the finance team filed regulatory reports from. The lineage diagram lived in Confluence and was fourteen months old. Six weeks later, that question took under a minute to answer, because data lineage automation was doing the mapping. The stack and the wrong turns below come from our data engineering work across several warehouse engagements rather than one named client, on a representative environment: 400 tables in Snowflake, 310 of them built by dbt, Airflow handling orchestration, and a regulated reporting layer with auditors attached.

The pressure behind this is not subtle. In dbt Labs’ 2026 State of Analytics Engineering report, 71% of 363 respondents named incorrect outputs reaching stakeholders as a top concern and 41% cited ambiguous data ownership as an obstacle. Trust in data climbed from 66% to 83% as a stated priority in a single year.

Understanding the Existing Lineage Documentation Workflow

Before writing any code, we shadowed two schema changes from pull request to production to see what an engineer actually did to answer “what does this break.”

1. An engineer opened a pull request against a dbt model or a hand-built Snowflake view.

2. They searched Slack and the Confluence lineage page to guess what sat downstream.

3. They asked two or three analysts whether anything they owned read from that table.

4. They merged, because the answers were incomplete and the sprint was ending.

5. A stakeholder found the breakage in a dashboard, usually one to three days later.

Every step in that sequence depends on a person remembering something. The Confluence page was accurate on the day it was written and decayed from there. The other 90 objects predated dbt entirely: Fivetran raw landing tables, three stored procedures written in 2021, and a set of views built by hand in the Snowflake console. Those 90 were 22% of the warehouse and, predictably, where most of the breakage lived.

The Business Research Company sizes the data lineage automation market at USD 2.07 billion in 2026, up from 1.66 billion the year before at a 24.4% CAGR, and most of that spend flows to catalog and observability platforms. Our view going in was different: the useful part of it can run well before any catalog purchase, on metadata the team is already producing and discarding.

Our Approach to Data Lineage Automation Across the Warehouse

Six weeks against 400 tables ruled out mapping everything at once, so four sequencing decisions shaped the entire build.

Identifying the Highest-Risk Untracked Objects

We did not start by mapping everything. We started by ranking. Any table feeding a regulatory report, a customer-facing dashboard or a finance reconciliation went into tier one, which came to 60 objects. Tier one got column-level coverage. Everything else got table-level coverage first, with column-level added later. This is the single decision that made six weeks realistic instead of six months.

Mapping the dbt Layer Separately From the Legacy Layer

The two halves of the warehouse needed different techniques, so we treated them as separate workstreams from day one. dbt-managed models carried their own dependency metadata. Legacy objects carried nothing, and would need parsing. Splitting them early stopped us from building one parser that did both jobs badly.

Selecting the Parsing and Metadata Technologies

We chose sqlglot for SQL parsing because it handles Snowflake dialect specifics, OpenLineage as the event standard because it is vendor-neutral and Airflow ships a provider for it, and Marquez as the lineage store because it reads OpenLineage natively and we could stand it up in an afternoon. Nothing here was exotic, which was the point. These systems fail more often on maintenance than on the initial build.

Building Coverage Gaps Into the Graph Instead of Hiding Them

Every lineage system has edges it cannot resolve. We decided early that unresolved edges would be visible in the graph, flagged as unknown rather than silently dropped. A lineage graph that lies about its own coverage is worse than no graph, because people start trusting it.

The Data Lineage Automation Solution We Implemented

The finished data lineage automation stack runs on five layers, each closing a gap the one before it could not reach.

dbt Manifest Parsing for Model-Level Dependencies

Model-level lineage answers “what breaks.” Column-level lineage answers “what breaks and why,” which is the question an auditor asks.

We used sqlglot to parse the DDL for every object outside dbt, walking the abstract syntax tree to resolve each output column back to its source columns. Clean SELECT statements resolve well. SELECT * does not, so we expanded the star against the live Snowflake information schema at parse time and stored the expansion. Window functions, lateral flattens on JSON columns and one 600-line view containing nine CTEs needed manual review, and those went into the graph as known gaps.

Resolving dialect-specific SQL at this depth is specialist work. Teams without that skill in-house tend to hire Snowflake developers for the parsing layer specifically, rather than for the graph around it.

Column-Level Lineage Through SQL AST Parsing

We did not write a parser first. We read manifest.json.

Every dbt build emits a manifest containing a depends_on block for each model, which is a complete dependency graph the team was generating and throwing away on every single run. Ninety minutes of Python against that file gave us model-to-model lineage across all 310 dbt models with no new infrastructure at all. For any team running dbt, this is the cheapest data lineage automation available, and it is sitting in the target directory right now.

Runtime Lineage Events With OpenLineage and Airflow

Everything above is static. It describes what the code says, not what actually ran.

We installed the OpenLineage Airflow provider and pointed it at Marquez. Every DAG run then emitted lineage events carrying real job names, run IDs, timestamps and row counts. That turned a diagram into an operational record: which job last wrote this table, when, and whether it succeeded. Static parsing plus runtime events is the combination that makes data lineage automation useful during an incident rather than during a planning meeting.

Snowflake ACCESS_HISTORY as a Gap-Filling Signal

We kept query history in the architecture, but demoted it. Instead of driving the graph, ACCESS_HISTORY now runs weekly to surface objects the parser never saw, which catches ad hoc views and anything created outside the deployment process. It is a coverage audit, not a source of truth.

That distinction is important when designing lineage for a Snowflake environment. Our Snowflake consulting expertise can help teams determine which metadata sources should drive lineage, which should only be used for validation, and how those decisions fit into the wider warehouse architecture.

CI Impact Analysis on Every Pull Request

The final piece was the one the team actually felt. A pull request touching a dbt model or a tracked view now posts its downstream impact set as a comment before anyone reviews it. Tier one objects in that set get flagged. The Wednesday morning discovery moved to Monday afternoon, before the merge button.

This last step is ordinary workflow automation services work rather than data engineering, and it is the point where a lineage graph stops being a reference document and starts changing what people do.

This is also where data warehouse consulting work tends to pay off twice, since the same metadata layer supports cost attribution and deprecation cleanup once lineage exists.

The Results: What Data Lineage Automation Changed in Incident Response

Six weeks of work did not change what the warehouse contains, only how fast anyone could answer a question about it.

The figures below are representative of this class of engagement rather than measured from a single named client. Treat them as the shape of the improvement, not as a benchmark.

The graph did not stop schema changes from breaking things. Data lineage automation moved the discovery earlier, which is the entire point.

Our Key Learnings From Building Data Lineage Automation

We started in the wrong place and it cost two weeks. The first attempt parsed Snowflake’s ACCESS_HISTORY view, on the theory that observed queries beat declared code. It was expensive to scan, noisy with ad hoc analyst queries, and it only showed what had already run, so any table refreshing monthly was invisible. Worse, it produced edges that looked authoritative and were not. Start with the manifest. Use query history to find what the manifest missed.

• Coverage percentage matters less than coverage honesty. A graph at 88% column-level coverage that flags its own gaps gets used. A graph claiming 100% that quietly guesses gets abandoned after the first wrong answer.
• Tiering the warehouse is what makes the timeline work. Full column-level lineage across 400 tables in six weeks is not realistic. Full column-level lineage across the 60 tables that carry regulatory and financial weight is very realistic.
• Lineage is an ownership tool before it is a debugging tool. The most valuable output was not the dependency graph. It was the list of 63 objects nobody would claim.
• Runtime events change the conversation with auditors. Static lineage says what should have happened. OpenLineage events say what did, with timestamps. Only one of those survives an audit question.

Build Your Data Lineage Automation Pipeline With Bacancy Technology

If your warehouse has outgrown its documentation, the fix is rarely a bigger diagram. We help data teams stand up data lineage automation on the metadata their stack already emits, then extend it into column-level coverage and runtime events where the risk actually sits. Our engineers work across Snowflake, dbt, Airflow, Databricks and BigQuery, and we start by ranking your warehouse by blast radius rather than by table count.

Tell us which tables you would not want to break, and we will show you what currently depends on them.

Frequently Asked Questions (FAQs)

How long does data lineage automation take to implement?

For a dbt-managed warehouse, model-level lineage is achievable in days rather than weeks, because the dependency metadata already exists in the manifest. Column-level coverage on legacy objects is what takes time. A tiered six-week scope covering a few hundred tables is realistic. Full column-level coverage across a large warehouse takes longer.

Do we need to buy a data catalog first?

No. A catalog is a useful destination once lineage exists, but it is not a prerequisite. Marquez, an OpenLineage-compatible store, or even a well-structured Postgres schema will hold a lineage graph. We generally recommend proving the graph’s value before committing to catalog licensing.

What is the difference between table-level and column-level data lineage?

Table-level lineage tells you that a downstream table reads from an upstream one. Column-level lineage tells you which specific fields flow where. Table-level is enough for impact analysis on most changes. Column-level is what you need for audit provenance and for narrowing the blast radius of a single field change.

Does data lineage automation work outside Snowflake and dbt?

Yes, with adjustments. The manifest-first technique applies anywhere a transformation tool emits dependency metadata. The SQL parsing layer is dialect-specific but portable. OpenLineage has integrations for Spark, Flink, Airflow and dbt, so the runtime layer moves across stacks more easily than the parsing layer does.

How accurate is column-level data lineage automation?

Accurate on straightforward SQL, imperfect on dynamic SQL, deeply nested CTEs and heavy JSON flattening. The right response is to measure and publish the gaps rather than to claim full coverage. In our experience, 85% to 90% column-level accuracy with flagged exceptions is far more useful than an unflagged claim of 100%.

How does data lineage automation help with compliance and audits?

It replaces reconstruction with retrieval. When an auditor asks where a reported figure originated, a lineage store answers with a traced path and a timestamped run record. Without it, an engineer rebuilds that answer from memory and code review, which is slow and difficult to evidence.

Conclusion