Column level lineage #3226

Activity

1. sou-joshi

   added

   type:featureA new feature or improvement request.A new feature or improvement request.

   status:triageAwaiting initial triage / categorization.Awaiting initial triage / categorization.

   on Apr 5, 2021
2. jtcohen6

   added

   discussion

   and removed

   status:triageAwaiting initial triage / categorization.Awaiting initial triage / categorization.

   on Apr 8, 2021
3. jtcohen6 commented on Apr 8, 2021

   

   jtcohen6

   on Apr 8, 2021

   Contributor

   More actions

   @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!

   👍React with 👍10Reacted by Rogier Werschkull, Georvic Tur, Dan, Dani Solà, Mark MacArdle, tufanrakshit, emily-hawkins, farmboycycles, Juliette Dz and Antonio Curado
4. bashyroger commented on Apr 26, 2021

   

   bashyroger

   on Apr 26, 2021

   More actions

   +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/
5. tufanrakshit commented on Jul 22, 2021

   

   tufanrakshit

   on Jul 22, 2021

   More actions

   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
6. devstein commented on Sep 23, 2021

   

   devstein

   on Sep 23, 2021

   More actions

   Hi 👋 , does anyone know how Datafold + DBT claims to provide this?
7. jaypeedevlin commented on Sep 23, 2021

   

   jaypeedevlin

   on Sep 23, 2021

   Contributor

   More actions

   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!)
8. dbt-labs

   locked and limited conversation to collaborators on Dec 8, 2021
9. jtcohen6

   converted this issue into a discussion #4458 on Dec 8, 2021