Multi-project collaboration

[jtcohen6]

Or: how I learned to stop worrying and love the ✨ dbt mesh ✨. This discussion supersedes #5244.

The bigger a dbt project, the harder it gets: to develop with speed, to contribute with confidence, and to share with clarity. It's frustrating to see parse times get slower, wait for longer IDE loading times, or disentangle a mess of conflicting CI builds—but above all, it becomes harder to find the right model (or even know which model is the right one). We want the other kind of network effect: the more of an organization's knowledge graph is encoded in dbt models, the more value dbt can deliver in disseminating that knowledge through the organization.

Background

There are more large projects, and they are getting larger. We see it in our anonymous usage data, and we hear about it firsthand from mature organizations rolling out dbt deployments to more collaborators than ever before.

I define the ideal project size as <500 models. This is an arbitrary line in the sand, but for me it reflects the point at which dbt Labs' own internal analytics project went from feeling "manageable" to "there's too much going on." The goal of this initiative is to enable large dbt projects—even ones maintained by relatively small data teams!—to separate their concerns, and collaborate more effectively.

Over the past year, the number of "large" projects (>500 models) has tripled. A year ago, of all known dbt models, one out of every four was running in a "large" project. Today, it's one out of every three, and I expect the trend to continue. Unless!

The opportunity

Today, a large organization adopting dbt has two choices:

1. Force all dbt developers to work in a single monolithic project, which quickly encounters the scaling challenges described above: degraded performance, poor discoverability, lack of clear interfaces between teams.
2. Operate in silos, which is locally optimal but globally chaotic. Teams lack visibility into each others' work; with limited coordination, they risk redefining core business concepts (or worse, defining them differently).

The essential goal of this initiative is to break that dichotomy. We should enable teams to develop projects independently, with alacrity and assurance—while still providing them with the ability to share common datasets, and unified lineage as a given.

It should feel like this

When there are multiple teams developing dbt models, each team should have the ability to:

• Work in its own domain-specific project. Several projects could cohabitate in a monorepo, or live in separate repositories.
• Define which of its models are publicly usable, including a contract (set of guarantees) for data structure and quality. dbt should make it easy to provide access to a stable model, while also requiring the intentional commitment of a cross-team interface. dbt will also need to provide mechanisms around model versioning & deprecation.
• Access another team's public & contracted models, for use as starting datasets in their own domain-specific transformations.

If we do this right, a single developer on a single team can be working in a project of reasonable size (<500 models), building out a well-organized DAG producing their own final set of public & contracted models, without needing to know about the thousands of private models that exist elsewhere in the org. At the same moment, a colleague with requisite permissions could be viewing the full dbt DAG, in all its glory, seeing the dependencies across projects and between every model—because the full lineage is always there.

There are data teams who have made attempts in this direction: by tracking after-the-fact lineage outside of dbt; by pushing isolated metadata to central data catalogs; or by stitching together cross-project links (models or exposures in one project, recreated as sources in another), to run in separate orchestration tools. It's better than nothing, but I don't believe it's good enough. I believe dbt must solve this problem natively. When all the pieces are in place, it should feel like this:

-- models/marts/my_combined_model.sql

with model_from_my_project as (

    select * from {{ ref('model_from_my_project') }}

),

model_from_another_project as (

    select
        -- columns I can count on
        stable_column_a,
        stable_column_b
    
    from {{ ref('upstream_project', 'contracted_public_model') }}

),

joined as (
    ...
)

And it should just work.

The plan

Over the course of the day, I'll be opening another discussion for each of the three themes in "Phase 1" below. Each discussion will include narrative, motivations, and requirements for the user experience, supported by proposed specs and code snippets. The intent of those snippets will be to illustrate, rather than guarantee, the exact final syntax which you can expect to read about and beta-test over the coming months.

Over the next several days & weeks, @MichelleArk will also be opening narrower issues to track our intended implementation. We welcome comments here, there, everywhere. Bring us your thoughts, questions, doubts, challenges, enthusiasm.

Phase 1: Models as APIs

Goal: v1.5 (April)

Develop new constructs that enable dbt developers to create, contract, and communicate data models like software APIs. This work should enable more scalable monorepos, while also laying the foundation for Phase 2.

• Model contracts #6726
• Model groups & access #6730
• Model versions #6736

Phase 2: Extend to many

Goal: v1.6 (July)
This is an ambitious timeline! If the dates need to change, we'll say when & why.

We will extend the constructs above to multiple projects. Cross-project ref is the tip of the iceberg. We must enable seamless experiences around development & deployment, enabled by dbt metadata. Developers in downstream projects do not need access to the full source code of upstream projects. Instead, they should get only & exactly the information they need, when they need it.

[lostmygithubaccount]

will this be limited to multiple projects using a single adapter? single adapter version?

[jtcohen6]

Good question. I expect we'll get more into the details of this when we talk about actually deploying multiple projects at once, in Phase 2. For now, my quicker answer would be:

• Each project is responsible for running its own models in development, CI, and maybe production — without requiring source code access to public models in upstream projects.
• Organizations could also opt for a "super" project, which install multiple projects (as source code) and run subsets of models from the unified DAG
• In the former case, different teams could be using different versions of dbt-core & their database adapter, so long as the contract for public models shared between them remains compatible (a good goal for us). In the latter case, teams would want to coordinate their dbt-core & adapter versions, to avoid discrepancies between development and deployment.
• It's out of this initiative's scope to provide mechanisms for sharing data "across adapters," except where data platforms already support this as built-in behavior (e.g. data shares between separate Snowflake accounts). It's something to think & talk more about in the future.

[ntnhaatj]

for the use case which maintains 2 projects with very different adapters (eg. dbt-spark and dbt-trino), will each catalog of them be generated by its own adapter? if so, can they be linked together to create a "super" catalog?

[jtcohen6]

@ntnhaatj Similar to my answer above — while we don't have immediate plans for supporting dbt deployments across multiple data platforms, it's exciting to think about for the future :) There will be lots of questions to sort out, including the one you've raised, around merging together catalogs produced by/for different platforms.

[guillesd]

Hi @jtcohen6! You know I'm excited about this one! I have a couple of questions:

• Is the idea that this feature works also for dbt-core? If so, how?
• What is the idea about permissions to the underlying objects? I'm guessing (hoping too) that in most companies a different dbt project means also a separation of concerns (different BQ project, different Snowflake database...). So cross-refing a project means you need the right permissions for that. Will this be a separate task outside of dbt before you can do something like this?

Thanks Jeremy! Super excited about this :)

[jtcohen6]

@guillesd Great questions! Thanks for asking them here :)

Is the idea that this feature works also for dbt-core? If so, how?

The big idea is: dbt-core can operate within the scope of a single project. You can --select +a_whole_bunch+ of models, and that will select models from the project in which you're developing/running. It would not select and run models from other projects, which are out-of-scope for the current command.

dbt-core will be able to receive stateful input, as an artifact, containing metadata about another project, in order to resolve cross-project references. Conceptually, it will feel similar to how project state works for Slim CI. It will be a feature of dbt Cloud to make that experience seamless, by handling state behind-the-scenes. With some legwork, it would also be possible to roll your own stateful service to enable the same development & deployment capabilities on top of dbt-core.

The "publication" artifact that will be produced by upstream projects and consumed by downstream projects is not the full manifest.json. It will be much smaller, containing just information about public models (access: public), and just the information needed to resolve a ref() to them.

Of course, it will remain possible to install other projects as packages. In that case, you need full access to the source code for those projects, and it's up to you to reconfigure & run those models. They're your models. Your dependency on the other project is as library code, with all its attendant flexibility & complexity — rather than an API that provides you with a pointer to the published dataset, no more & no less.

What is the idea about permissions to the underlying objects? I'm guessing (hoping too) that in most companies a different dbt project means also a separation of concerns (different BQ project, different Snowflake database...). So cross-refing a project means you need the right permissions for that. Will this be a separate task outside of dbt before you can do something like this?

dbt will not, behind-the-scenes, be coordinating permissions in the underlying data platform. Specifying access: public on a model does not trigger dbt to automagically grant select on that model to every user/role in your data platform when you materialize it. It will still be up to you & your organization to manage grants/permissions on every model/schema, as makes sense to you. Of course, dbt can facilitate this (make it easy) for each public model by means of the grants config. You could also implement rules in generate_schema_name, to the effect of putting private & public models in different schemas, with different default/future grants. In either case, it's still very much something you need to set explicitly.

Setting access: public does mean that other teams are allowed to start taking a dependency on that model, assuming they've requested & you've granted access to select from the underlying dataset. The idea is also that, within dbt Cloud, folks on a different team will be able to see additional metadata about that model, versus a private model, as a way of starting that conversation.

[jtcohen6]

I've renamed this discussion from "Multi-project deployments" to "Multi-project collaboration." Why?

The primary goal of this initiative—the challenge it seeks to solve—is not in finding the most elegant way to deploy projects that are defined in separate repositories. That is an important outcome, and a lot of it is already possible with packages; we'll be making it easier as part of this effort, with improvements to how dbt handles namespacing.

Rather, our primary goal has been, and continues to be, enabling multiple teams to collaborate. To actually own their data models. To publish, share, and maintain those models with other teams in predictable ways. To do so with the recognition that those producing & consuming teams may have conflicting interests & incentives.

One team should not operate in a vacuum, siloed off from every other. They should be able to leverage their colleagues' work, while still having control over the scope of their project.

For folks who are using dbt at a smaller scale, and don't need to tackle organizational complexity with capabilities around model governance: that's okay! None of them is required for upgrading to v1.5 / v1.6, and there's a lot of other good stuff besides. I'd just say: Know that we're invested in making dbt scale as a framework, if/when you need it.

For everyone who does need these capabilities: Let's make the most of them, together.

[siljamardla]

Consider the following scenario:

• Project A has model A1
• Project B wants to use model A1 in model B1
• Project B has another model B2
• Project A wants to use model B2 in model A2

With packages this scenario was not possible: Project B could use Project A as a package, but adding Project B as a package into Project A would mean a "circular dependency".

Think domain A creating input for domain B and domain B creating input for domain A on something completely unrelated.

Will this scenario be supported in 1.5 or 1.6?

[jtcohen6]

@siljamardla Cool question! I'd like to support this pattern eventually, but in the first cut of this (coming in v1.6), we're not going to allow project-level cycles. I see the hypothetical utility of being able to do that cycle detection at the node level instead; I'm curious to hear how often it comes up in practice.

• Sooner: [CT-2494] Detect and restrict project-level cycles in dependencies.yml #7468
• Later: [CT-2528] [Spike] detect and restrict node-level cycles caused by cross-project models #7506

[siljamardla]

The practical use case we have:

• Domain teams have their own projects where they define domain metrics (on top of domain models)
• We have a global project that makes all those metrics available as documentation and uses many of them to materialize cross-domain metrics tables for reporting
• Domain teams would like to build on top of those materialized metrics tables

[maxpowis-bp]

I see both features were merged; does that mean that cross-project dependencies are now supported as long as model cycle dependency check is ok? The documentation at https://docs.getdbt.com/docs/collaborate/govern/project-dependencies#how-to-use-ref seems to indicate it is not the case yet but is the documentation accurate? Posted a question about this on slack at https://getdbt.slack.com/archives/C04FP5LQA15/p1702906085645819. Thanks in advance for the clarification.

[jaklan]

@jtcohen6 reading your previous comments, especially #6725 (reply in thread), I wonder if we can say that's gonna be more a dbt Cloud feature than dbt-core? Especially looking at that part:

dbt-core will be able to receive stateful input, as an artifact, containing metadata about another project, in order to resolve cross-project references. Conceptually, it will feel similar to how project state works for Slim CI. It will be a feature of dbt Cloud to make that experience seamless, by handling state behind-the-scenes. With some legwork, it would also be possible to roll your own stateful service to enable the same development & deployment capabilities on top of dbt-core.

and the mention of stateful service. Would be the server (either dbt Cloud or custom) mandatory to utilise multi-project capabilities, or would it be still possible to manage artifacts "manually", so e.g. generate them for one project, deploy somewhere (as static files), pull & consume by another project?

[jaklan]

@jtcohen6 thanks again! I don't have any more questions as for now, the approach you have taken is quite clear to me.

Having said that - of course I still have various concerns, and actually most of them is simply related to dbt Cloud itself. I don't want to go deep into that discussion here, but there's really a lot of job to be done in dbt Cloud to make it usable for more complex setups even in the current shape, not to mention all new challenges which will appear when the multi-project is introduced.

And that's why I'm the fan of the hybrid approach, as advanced users wouldn't be affected by issues and limitations related to dbt Cloud as a DataOps platform, but they would still use it (and pay for it) for the capabilities mentioned above. I also believe that would be beneficial for dbt Labs, as it would be easier to define and follow the roadmap for dbt Cloud with less external pressure if you can offer any alternative for your tech-savvy customers.

We have a meeting yet this month, so let's discuss it in details then, and as for now - I'm just waiting for the release of multi-project 😄

[gzoritchak]

@jtcohen6

I am well aware of the difficulty in reconciling open-source and business. Despite this, IMHO, your path is not the right one.

The mechanism we are talking about is the definition of dependencies between projects. It is a matter of differentiating between a dependency on an API (multi-project) and a dependency on an implementation (package). This is a well-known subject for many platforms and development tools. This topic is at the heart of building relatively large solutions.

There are few technical reasons to make this dependency only possible in DBT-cloud. In any case, it is pretty easy to define an API in the form of files. Limiting this functionality to DBT-cloud is a business choice that goes against those who have chosen to use DBT-core. Forcing current users to switch to a cloud version is risky. Not everyone can necessarily accept the other constraints brought by DBT-Cloud (delegation of monitoring, development environment, integration into the company, etc.).

In my case, the company I work for would be more willing to find another financial form to access this functionality without switching to DBT-cloud.

I hope you will reconsider your position to ensure better user trust.

[jtcohen6]

@gzoritchak I appreciate the honest feedback.

There is going to be some dbt functionality that we choose to reserve for our commercial offering (dbt Cloud). In each case, the primary motivation behind making this choice is supporting & maintaining a sustainable business around dbt, which ensures that we can continue to support & maintain dbt-core as truly open source software long into the future. Some of that functionality will be gated to "Enterprise" plans, when we believe the capabilities exist to support complex deployments at scale, but a lot of it will also be available on the Teams and Developer (forever free) plans.

There can be technical considerations that inform these choices. In the case of cross-project ref, had we continued pursuing the file-system approach, we realized that we would: (a) be adding a lot of stateful functionality into dbt-core that is outside its intended scope, and has proven tricky for us to maintain elsewhere; (b) be exposing an implementation detail (files) as a stable & documented interface, and locking ourselves into it — thereby reducing our future flexibility and adding to our maintenance burden — all before we even know exactly how people want to be using this!

But ultimately, the technical motivations are secondary. The primary motivation — the business motivation — is to offer a compelling, differentiated commercial product: a platform for developing, deploying, and collaborating on dbt at scale. If dbt Cloud isn't yet as good as it needs to be — if that statement doesn't yet resonate with you, or with other members of the community — consider this choice to be one step on the way there.

The "other constraints" in dbt Cloud that you speak to — better monitoring/observability, more flexibility around where you can develop, adoption across the company, integration into existing toolchain — these are real! Each limitation or point of friction is something that we need and want to improve (or are already in the process of improving). We should be making it so that more people can use dbt Cloud, at/beyond parity with their current custom deployments of dbt-core.

There has always been functionality available in our proprietary commercial offering (dbt Cloud) that we haven't made available in OSS. Historically, the two sets of functionality have been non-overlapping: dbt Core is a language/compiler that interoperates with data warehouses; whereas dbt Cloud is an IDE, a scheduler, a metadata store, an access-management system, ... Cross-project ref is the first "language feature" that is Cloud-only. I don't believe it will be the last.

I take user trust very seriously. I know this discussion/thread have been disappointing for several community members who were excited to be getting multi-project capabilities this year. I don't foresee us reconsidering our position here in the short term. I do think, over the next few months, we need to clearly (re)state our philosophy of what functionality goes where — to set some clear expectations, and stick to them. I want to make sure you know what you can continue to expect as a user of dbt Core OSS, and I also want you to understand what additional capabilities you would be getting as a customer of dbt Cloud. We cannot avoid disappointment, but we can do our utmost to avoid unwelcome surprises.

[nicholasyager]

I know this discussion/thread have been disappointing for several community members who were excited to be getting multi-project capabilities this year.

Ooh, I'm in this picture 😆

As a dissapointed community member, I have created dbt-loom, an Apache 2-licensed python package that enables cross-project references in dbt-core and hybrid Core/Cloud environments. While it may not be as refined as dbt Labs' official approach in dbt Cloud, it effectively meets the needs discussed in this thread.

@jtcohen6 As for the disappointment, no hard feelings! You all are navigating a tricky balance between value creation and commercial success. Looking ahead, I believe that the community could benefit from clearer labeling of dbt Cloud-specific functionality and perhaps a separate forum for non-open-source dbt Labs' products. In any case, I remain confident in the team's good intentions and eagerly anticipate the innovative tools and products to come.

[Spince]

Not all heroes wear capes but they do write open source Python!

[seub]

Hi @jtcohen6 , many thanks for sharing all this information, it's very helpful.

After being excited by the release of dbt-core 1.6, I was a bit disappointed to find out that cross-project refs would be available for dbt Cloud only, which led me to find and read this thread. Thanks again for all the explanations.

There's a basic technical point that's still not completely clear to me: why does cross-project ref needs the state of the ref'ed project? After all, it doesn't need it when including the project as a package, so what's the difference? I think I have a guess but I'd love for someone who actually knows to clarify that. Apologies if this has already been explained somewhere!

[jtcohen6]

@seub Sorry for the delay getting back to you! I'm glad you were able to find this thread, and that it answered some of your questions.

To repeat some of what I said in the threads above, there are technical motivations here, but also a business motivation. The goal of this initiative is to provide mechanisms for tackling complexity at scale. This specific mechanism exists for the primary benefit of large distributed organizations. We believe many of those organizations are good potential customers of dbt Cloud Enterprise, and we need to be able to offer differentiated functionality in Cloud that is not available in Core. Their business is important for sustaining the ongoing development of dbt Core + Cloud, which benefits everyone.

As to the narrower question you're asking: When you import a project as a package, you are providing the full source code of that other project to dbt-core—as if they were files living in your own project/repository. When you write a cross-project ref, in the way supported by dbt Cloud, dbt-core is not getting access to the full source code of those other projects. Instead, it is getting access to a collection of metadata, which needs to be supplied (and kept up-to-date) by a metadata service running behind the scenes. That metadata includes the location of the referenced public models, as defined & built by upstream projects—projects for which you (as a developer in the downstream project) do not need and may not have access to the source code, configurations, etc. Does that distinction make sense?

[seub]

Thanks a lot @jtcohen6! That answer does make a lot of sense.

Business motivation aside, my guess for the technical difference was along the lines of:

• When you ref a model in a package, the ref is resolved using the current build environment (the jinja in the config of the ref'ed model is resolved using the current environment variables etc)
• When you ref a model in a project, the config of the ref'ed model was already resolved by the build environment of the ref'ed project, and the ref merely looks it up in its state.

That difference in treatment would make sense to me (since in the first case, the current project takes ownership of the model, not in the second). It sounds like the metadata service is a more sophisticated version of "looking up the state". And I hear you about not getting access to the full source code of the ref'ed project.

[SoumayaMauthoorMOJ]

Hello @jtcohen6 can you clarify the reasoning between splitting up into multiple projects in more detail and the number of ~500 models that you quoted? We're wondering if it's better for us to stick with a single dbt project instead of having to deal with cross-project references. Our dbt project currently contains 500 models and could grow to 1000 models over the next year but shouldn't grow much further than that. We don't use dbt cloud. Unfortunately the github repo is internal so I can't share it but the user docs are public: https://user-guidance.analytical-platform.service.justice.gov.uk/tools/create-a-derived-table/

Parsing

I (full-)parsed a project containing about 500 models. This took ~20 secs:

(venv)  % dbt parse --profiles-dir ../.dbt/
08:44:08  Running with dbt=1.6.2
08:44:08  Registered adapter: athena=1.6.0
08:44:09  Unable to do partial parsing because of a version mismatch
08:44:28  Performance info: [...]target/perf_info.json

I partial-parsed a project containing about 500 models. This took <2 secs:

(venv) % dbt parse --profiles-dir ../.dbt/
08:44:36  Running with dbt=1.6.2
08:44:37  Registered adapter: athena=1.6.0
08:44:37  Performance info: [...]target/perf_info.json

Hence as long as I don't make any changes that trigger a full parse, parsing becomes a non-issue?

Finding the right model

We split our models into business-facing domains, assigning a directory to each domain. There is also a data engineer responsible for each domain to ensure consistency inter and intra-domain.

Analysts can search across the repo for the right model, or create the model in the right location.

How would splitting the dbt project into multiple dbt project by domain, whether in the same repo or in multiple repos, would make it easier to find/add a model?

Thanks !!

[jtcohen6]

@SoumayaMauthoorMOJ Thank you for the comment! It's really neat that you've made the docs for this public. (It looks like you're working for the Ministry of Justice of the United Kingdom?)

I think there are two primary motivations for splitting up one monolithic project into multiple:

1. There are a lot of people (>20) developing dbt models. It's becoming difficult to facilitate their collaboration—to keep track of who owns what, who should be editing what, who should be reviewing which PRs, etc. Assuming that specific people/teams are working in specific domains of data modeling, it would be appropriate to split out those domains as separate standalone projects and repositories.
2. There are a lot of dbt models (>1k). There are technical and workflow challenges—not just slowness, but also the risk of accidentally typing dbt run and including >1k models, or the difficulty of visualizing a DAG with so many nodes. Many of those models are purpose-built, or may have even outlived their purpose (!). As a matter of code organization, it could make sense to split out some models into separate projects.

Since originally opening this discussion back in January, and after talking to a bunch of organizations who have adopted dbt at scale, I believe that (1) is the primary motivation, and (2) is secondary. There may be smaller teams with many models, who benefit from the patterns discussed here and enabled by our feature work this year — but I expect the primary beneficiaries to be large distributed orgs, with many dozens (or hundreds) of contributors, who are looking to facilitate cross-team collaboration.

To that end, if you're finding that the scaling challenges of your project are feeling manageable, you expect it to grow to only ~1k models, and you have clear lines of ownership (1:1 between domain : data engineer/owner/maintainer) — that's great! Keep on keeping on. I might even recommend that you start using model groups & private access as a way to draw even-clearer boundary lines around domains within one project.

[SoumayaMauthoorMOJ]

Thanks @jtcohen6 this is really helpful (and reassuring!). We were planning to GitHub codeowners to allow specific teams to make changes to specific domains, but I'll definitely look into model groups & private access

[mrcool4]

hi @SoumayaMauthoorMOJ , I thought GitHub codeowners is only for adding default reviewers, Can we use it to restrict the access to folders?

[Spince]

Not all heroes wear capes but they do write open source Python!

…

On Thu, Sep 14, 2023 at 08:58 Nicholas A. Yager ***@***.***> wrote: I know this discussion/thread have been disappointing for several community members who were excited to be getting multi-project capabilities this year. Ooh, I'm in this picture 😆 As a dissapointed community member, I've have created dbt-loom <https://github.com/nicholasyager/dbt-loom>, an Apache 2-licensed python package that enables cross-project references in dbt-core and hybrid Core/Cloud environments. While it may not be as refined as dbt Labs' official approach in dbt Cloud, it effectively meets the needs discussed in this thread. @jtcohen6 <https://github.com/jtcohen6> As for the disappointment, no hard feelings! You all are navigating an tricky balance between value creation and commercial success. Looking ahead, I believe that the community could benefit from clearer labeling of dbt Cloud-specific functionality and perhaps separate a forum for non-open-source dbt Labs' products. In any case, I remain confident in the team's good intentions and eagerly anticipate the innovative tools and products to come. — Reply to this email directly, view it on GitHub <#6725 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACELLMHQSIVE3DAV2CYDKB3X2MLQXANCNFSM6AAAAAAUGLF3ZA> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[jtcohen6]

I'm going to close this discussion as resolved, though we're only just beginning our mesh-y journey together. Thank you to everyone for your full & honest participation since January. It's meant a lot to me personally.

Check out the docs & guides, if you haven't already!

• https://docs.getdbt.com/docs/collaborate/govern/about-model-governance
• https://docs.getdbt.com/docs/collaborate/govern/project-dependencies
• https://docs.getdbt.com/guides/best-practices/how-we-mesh/mesh-1-intro