Column level lineage

[sou-joshi]

Describe the feature

Currently have table/view level lineage captured, can this be extended to field/column based?

Additional context

Not specific to a database, applies to the product.

Who will this benefit?

There could be multiple renames to a field and with multiple joins the complexity to track field level changes and source of a particular column becomes difficult. From an Ops perspective it always good to know where a field is coming from to quickly solve data issues.

[jtcohen6]

@dataexpertz-blr Thanks for opening, I'm surprised there wasn't already an issue for this :) It's something we're hearing and talking about a lot these days.

Mechanisms

I view column-level lineage as existing in two orders of complexity:

• Extending existing constructs. Today, dbt developers have to duplicate a lot of resource properties (descriptions, tags, meta, tests) across models, even when model Y is just select * from model X. Over in Doc (and potentially, Test) Inheritance #2995, and dbt doc blocks #1158 before then, we've been discussing ways that YAML anchors make a version of this possible today, and how it could be better in the future (cross-file anchors, souped-up docs blocks, hierarchical properties-as-config).
• Massive new capabilities. To capture column-level lineage for real for real, we'd need a validating SQL grammar—same as would, incidentally, for a decent linter / auto-formatter (Automatic formatter for SQL #2356). In a world where we had this, and built it into dbt, we'd also have an AST representation of every column name, from relation, and SQL function. This is definitely on our minds; it's still a ways away.

Use cases

As with any compelling feature, column-level lineage feels both immensely valuable and a bit vague. If dbt could produce an EXPLAIN-style plan, of every single SQL function performed to produce a single column, that would be very cool, and also tricky to read and reason about as a human being.

So I do find it useful to think concretely about the kinds of things we'd hope to enable here:

• Property (tag/meta/etc) inheritance. E.g. If a source PII/PHI column is the indirect input to a column in a downstream model, being able to mark the latter model as sensitive.
• Saving code (+ time). Extending what I described above, if a column has not transformed from model X to model Y—no renames, no aggregations—dbt could natively inherit its properties, such as description + tests.
  • Or, better yet, dbt would understand that the column has been tested upstream, has not changed, and so does not need to run the same tests again.
• "dbt column advisor." If dbt has a full picture of how a column is produced—which input columns, which transformations—it could flag when there are potentially duplicative columns across models, and help avoid the repeating of business logic.
• Slim CI to the max: If you've only changed one column across a few models, rather than running the changed models and all their children, you'd only need to run + test downstream models that use the affected column.

I'm curious to hear what other things come to mind!

[bashyroger]

+4 for the use case you've mentioned @jtcohen6 , I'd like to add the following, IMO important use case:

• A variant of Slim CI to the max: being able to do an impact analysis at development time when you're changing a source / model's column name. After all, the fact that a column changes does not necessarily mean that the column also used in any dependent models. For that, you really need column level lineage.

Instead of building this yourself, you could also think about integrating DBT with the only data lineage focused SAAS product I have heard of: https://getmanta.com/integrations/

[tufanrakshit]

we are planning to use DBT Cloud for our project and this table by table lineage is really a killer feature which we would like to have as This would make data lineage and debugging much much easier

[devstein]

Hi 👋 , does anyone know how Datafold + DBT claims to provide this?

[jaypeedevlin]

Datafold use their own lineage capabilities to do this — while they do read in your dbt project, the column level lineage is part of their platform (it's an awesome feature though!)

[MarkMacArdle]

Monzo (a UK startup bank) have written about how they created column lineage using BigQuery audit logs and parsing the compiled dbt queries with ZetaSQL. Hadn't heard about ZetaSQL before but it's an open source project from Google for analysing SQL.

That solution is BigQuery specific but still interesting to see an implemented approach to this problem.

[after-ephemera]

Here is GCP's post describing a similar approach.

[christopherridley]

Is there a huge benefit here where you could define your docs/descriptions once at the source table level and then have that trickle down to all columns that reference the original source column? Still wrestling how to populate descriptions for all columns effectively and efficiently.

[KayakinKoder]

This, in my opinion, is a huge unmet need. Documenting data has become a huge time suck, and the lack of description propagation is a reason why.

[jtalmi]

one new application i thought of: dbt build could only block downstream nodes that are directly affected by a test failure. let's say a random column column1 fails in model A, and model A -> model B. B uses column2 from A but not column1. if a test on column1 fails, let's say a not null test, it shouldn't block model B since model B doesn't use column1.

[bashyroger]

It looks like the following company is working on this functionality: https://www.hivedive.io/
Did anyone testdrive this already?

[stephanclaus]

I think there are countless vendors out there specializing on a certain database vendor (i.e. for Snowflake column level lineage is easier as they provide great meta data around accessed objects on column level already).

Another very concrete use case I wanted to add: Optimize your tracking plan (and thus, saving compute)

We are using the open source version of Snowplow and are lacking a unified UI for frontend + backend + analytics to align for. That lead to situations where very similar events where added but nothing was deprecated as "it could break things downstream". Being able to analyse which events from the raw source tables are used in downstream models would make that way more convenient.

[mertbakir]

I was expecting dbt to generate (inherit) column descriptions for downstream tables if it's already defined on source table. Then I faced this discussion and realised I also need column level lineage because it takes so much time to understand the affect of changing a column.

[vergenzt]

I address some of these limitations (specifically the maintenance ones, i.e. the ability to "forward" unchanged column descriptions and tests from upstream to downstream models -- this doesn't expose that lineage anywhere) with some dbt macros on top of the scripts I defined for #5093 (comment). It's a bit convoluted so not going to go into full detail right now unless folks ask.

Summary: After I had a solution for #5093 via SQL comments & a regex extraction script, I started using a modified version of dbt_utils.star (I called my macro ref_columns_from) to pull from graph.nodes instead of the database, so I can get dbt's metadata about that model and its columns instead of just what the database knows about it from get_columns_in_relation. Then, since a single dbt compile pass can only propagate columns one level, I iterate through "dbt compile" → extract column definitions from compiled SQL → "dbt compile" → ..., until the generated "schema.yml" file (column doc & test definition) stops changing.

[z3z1ma]

https://github.com/z3z1ma/dbt-osmosis

This tool does it already with a very easy CLI @vergenzt

[YohanGrember]

I'm currently ramping up on an existing DBT stack, and I just wanted to stress how column-level lineage would help me do so more efficiently.

Without it, figuring out the upstream flow of a transformed table field takes me forever, and I'd dream to be able to see it in one click! This would be a game-changer for my productivity and I hope we'll move forward on this topic 🙏

[ijoseph]

Amundsen (second link) is open-source, perhaps someone can try it out in a real-world environment and report back (so I don't have to be the guinea pig) :)

We have added native lineage support in Amundsen so you can ingest lineage metadata (both at table and column level) straight into the graph backend.

[after-ephemera]

I've played with Amundsen and it's great for bringing lineage in, but I think this issue was specifically focused on capturing column-level lineage within dbt, which is a bit of a different beast.

[axelborja]

@jtcohen6, any news about doing it in DBT directly?

[JoePollo]

Enterprise compliance for columnar lineage is causing heartache for my corp as we adopt DBT; this is a well desired feature that would provide exponential value to us. Native DBT functionality in the capacity of DBTerd would be fantastic. We are working on creating a templated repo that would be used by hundreds of engineers across the corporation, all of which would need to support columnar lineage.

[nicoteiza]

Hi! I'd love to read if there has been any update in this front. As I understand sqlmesh can do column lineage. Does anyone know how they achieved it? Is it something that could be translated somehow to dbt?

[indy-jonesy]

The creator of sqlmesh, also created sqlglot.
You can find an independent dev team were able to build a proprietary dbt lineage solution here using sqlglot as a backbone. It would be a matter of leveraging sqlglot as a compiler and incorporating it into dbt core.

[ismailsimsek]

@indy-jonesy @nicoteiza it seems like dbt proprietary feature is also based on sqlglot link1 link2

[VDFaller]

Would ColumnInfo be the right place to try to put this in the manifest? Or am I barking up the wrong tree? Would it be helpful if we could get something like

{
    "nodes": {
        "model.my_dbt.child_model": {
            "database": "dev_vince",
            "schema": "my_schema",
            "name": "child_model",
            "resource_type": "model",
            "package_name": "mydbt",
            "columns": {
                "id": {
                    "name": "id",
                    "description": "my_pk",
                    "depends_on": []                    
                },
                "source_column": {
                    "name": "source_column",
                    "description": "some column passed through from a source table",
                    "depends_on": [
                        {"node": "model.my_dbt.parent_source", "column": "source_column"}
                    ]
                },
                "model_column": {
                    "name": "model_column",
                    "description": "some column passed through from a model",
                    "depends_on": [
                        {"node": "model.my_dbt.parent_model", "column": "model_column"}
                    ]
                }, 
                "surrogate_key": {
                    "name": "surrogate_key",
                    "description": "this is a derived surrogate key from source_column/model_column",
                    "depends_on": [
                        {"node": "source.my_dbt.source_schema.parent_source", "column": "source_column"}, 
                        {"node": "model.my_dbt.parent_model", "column": "model_column"}
                    ]
                }
            }
        }
    }
}

[shishircc]

Is this on roadmap ? SQLMesh is able to use dbt project as source and generate column level lineage. Since that is open source, could that be used to bring the column level lineage to DBT ?

[shishircc]

The underlying library, sqlglot makes this very easy.
E.g. See the simple 3 lines of python code creating column level lineage graph here https://twitter.com/Captaintobs/status/1619185992111624197

[robomill]

It's definitely more complicated than this depending on the complexity of the sql. But this is essentially how datahub generates lineage:

https://github.com/datahub-project/datahub/blob/8415fc214b1b9d0f99eafbffc01f3e46043f54dc/metadata-ingestion/src/datahub/utilities/sqlglot_lineage.py#L178

[ismailsimsek]

@robomill it seems like dbt proprietary feature is also based on sqlglot link1 link2

[robomill]

@ismailsimsek It's kinda weird they didn't implement it themselves for enterprise.

They already have all the ingredients to write it themselves built into DBT. They already have the sqlparse library that generates an AST that they could use to generate the inputs and outputs of a model. They already have code that can introspect the schema of tables as well. It's easy enough with a good engineer to reverse engineer what SQLglot have done.

Seems kinda lazy on their part and less of a reason to exclude from dbt-core if they are using a third party open source library

[shishircc]

After the great announcements at coalesce, is this on roadmap ?

[matfior-finn]

While we wait, I suggest checking https://www.turntable.so/, it's quite nifty and really fast.

[shishircc]

Turntable is great. My main concern is that it requires developer level access to get the lineage in VS Code. All people who benefit from lineage may not have developer level access.

[ianrtracey]

@shishircc Ian from Turntable here. What do you mean by developer level access? We're currently building a cloud version if you'd like to learn more

[matteoannotell]

Hello friends, is there any updates on this one?

[matfior-finn]

I would like to know as well. I tried asking during coalesce in London but I got some evasive answers.

[AlexThomas90210]

Is progress still being made on this?

[rrivera-ut]

I'm going to take a stab and say probably not

[matfior-finn]

would be cool to get some sort of official answer about it.

[ViniciusRaphael]

That's true. Some official answer about status would be great.

[borjavb]

If anyone is interested into building something for BigQuery, this parser will give you full column lineage for your queries, it covers pretty much the whole BigQuery syntax! https://github.com/borjavb/bq-lineage-tool

[mertbakir]

It looks like dbt decided to make column level lineage for the cloud only. That's sad.

[matfior-finn]

Enterprise only, that's sad

[ViniciusRaphael]

Column level lineage on dbt cloud? Just accessed there and couldn't find anything like that. Are you guys sure?

[matfior-finn]

see here: https://www.getdbt.com/blog/proactively-improve-your-dbt-projects-with-new-dbt-explorer-features

Column-level lineage is available in public beta to Enterprise plan customers only.

[ViniciusRaphael]

Thanks @matfior-finn

[indy-jonesy]

Sad indeed.

[rrivera-ut]

Is anyone surprised?

…

On Wed, Feb 14, 2024 at 10:57 AM indy-jonesy ***@***.***> wrote: Sad indeed. — Reply to this email directly, view it on GitHub <#4458 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AS7LO2FSVVAFALMJGAKQ4DTYTTNFLAVCNFSM5JWKHCA2U5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TQNBWG43TGMQ> . You are receiving this because you commented.Message ID: ***@***.***>

[calleo]

I don't understand the disappointment.

Most of us are working for companies that needs to earn money to keep on living. DBT is no exception. Why on earth should they be giving away the product for free?

Of course we can go build something new and free, but it will take a long time, and eventuelly we will also have to pay our bills.

[KayakinKoder]

@calleo I'm a bit confused. The Team plan is $100/month per developer, and as of right now it doesn't include column level lineage. You consider that pricing to be free?

Let's all just hope that the folks at dbt realize that making column-level lineage available to SMBs on the Team plan would add a lot of value/use cases that could justify teams purchasing plans.

[rrivera-ut]

DBT is just too expensive - it's competitors who are building better features are cheaper - see Paradime.io

…

On Wed, Feb 14, 2024 at 3:07 PM Carl Vander ***@***.***> wrote: I don't understand the disappointment. Most of us are working for companies that needs to earn money to keep on living. DBT is no exception. Why on earth should they be giving away the product for free? Of course we can go build something new and free, but it will take a long time, and eventuelly we will also have to pay our bills. — Reply to this email directly, view it on GitHub <#4458 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AS7LO2ECHRVVSQ2UNJLUCHDYTUKRXAVCNFSM5JWKHCA2U5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TQNBXGE3DGOI> . You are receiving this because you commented.Message ID: ***@***.***>

[jyotidhiman0610]

Is there any plan to release this for dbt core?

[1cadumagalhaes]

Pretty sure there isn't

[ismailsimsek]

Would it be accepted if community adds this feature to dbt core? im happy to help.

[1cadumagalhaes]

I was thinking about that this week, maybe creating a dbt package that adds column level lineage would work.
I'm not sure if this could be added to the core itself.
It should be possible since both dbt (on cloud) and sqlmesh have column level lineage

[ismailsimsek]

+1
also it looks like both (dbt on cloud and sqlmesh) are using opensource sqlglot for column level lineage link1 link2

side note: AFAIU using sqlglot, its also possible to get rid of the {{ref('model_a')}} syntax. and use pure sql(table name) instead. that's how sqlmesh find dependencies.

[z3z1ma]

A few thoughts. At this point just use sqlmesh. Really. Why jump through these hoops. Dbt implemented CLL (and the backing UI) as a paid feature which isn't inherently wrong. They are a business at the end of the day. It would not benefit them to pursue this in the oss product after releasing it as a differentiator in the paid product. Either it's worst and people are unhappy and they must maintain it or they devalue their cloud products value proposition. So probably just make another pypi package if you want to attempt it.

And doing away with refs is shortsighted only because people override the ref macro constantly and it's used in so many places. Jinja and dbt are quite married at this point if you dig under the hood. Furthermore people depend on dbt behaving consistently and not discover new "refs" because of some new parsing.

Sqlglot is not just 'some oss package' with a one way relationship to sqlmesh but is in fact built and maintained by the creators of sqlmesh. So their usage of it is in the blood of the software and not bolted on.

[wenwu35]

We just released an open-source utility for dbt column-level lineage.

This was primarily developed to provide a programmatic interface to dbt column level lineage, enabling further development and use, such as creating automated tools for tagging sensitive column data. But you can also visualized the output json using tools such as jsoncrack.com

Existing column-level lineage tools, like Atlan, dbt Cloud, SQLMesh, and Turntable, lack this programmatic capability and also face challenges such as subscription fees, indexing delays, complexity, or concerns about transmitting organizational code/data to vendor servers, which hinder their wider adoption.

Hope it helps.

[ismailsimsek]

@wenwu35 Would it be possible to add this feature to opendbt. opendbt already adds some new features to dbt-core and this will be great addition. opendbt is dbt-core+additional features which are not got in dbt-core. i believe this will be good addition. whoever is interested contributions are welcome.

i am in progress of adding sql formatting to it

[1cadumagalhaes]

Hey, this looks like an interesting project. Some of the features that are there and work with airflow are in the astronomer cosmos project, maybe you could take a look