There are many different ways of configuring a dbt project to work with multiple teams and projects, here is a list of some features that can allow different configurations, and a proposal.

dbt by default shares all the models within a "project", although it allow for a lot of customization, like adding folders and subfolders, defining tags based on those folders, and even deciding destination schemas based on those folders. There is a major problem with a configuration like this, dbt doesn't allow to have multiple files with the same name, even if they live in different folders. This could be solved with a good naming convention. This is due to the ref() function that dbt uses to call other models, it doesn't expect paths, it only expects file names.

Some docs about model governance.

Interesting features:

Here there are some interesting features that allow organizing models and that we might want to consider:

• Groups: They are some sort of namespaces, they allow to define access to a group of models. This could be interesting to define some boundaries and private models within a team of project.
• Packages: Packages are like complete dbt projects, published and ready to be imported by other projects. This feature could be used to have each team with their own dbt project. If teams want to share models, they could publish a package and another team could import it. In my opinion, this would place an unnecessary burden on the repository, making things more complex than they need to be and hindering collaboration between teams. And I believe that "sharing a model" will become "sharing the code of a model", not the table created by that model.
• Project dependencies : This looks like a great feature to allow multiple projects interconnection, without the complexity of publishing packages, but it is only available in Enterprise, and we are using dbt Core.

Proposal

I think a good proposal could be to define some folders, subfolders and a proper naming convention for files, to avoid collisions between files in different folders. So:

• Folder by team, subfolders by projects.
• Each team/project can decide to have multiple subfolders (e.g.: organize code, intermediate models, etc) as they wish, having extra subfolders shouldn't be an issue.
• Some "shared" or "common" folder can be used to share models between teams and/or projects.
• Each folder is configured with a different destination dataset.
  • We need to modify the generate_schema_name macro to define schemas based on folders or teams. (Note that profiles.yml and custom schemas are related to this)
• Each folder can be configured with some groups and access visibility, to avoid other teams accidentally referencing models. (e.g: If a team considers a model to be "only internal", it can be configured as Private.
• Each "project" or subfolder can be configured to have specific tags. Each project can decide their tags.
• Tags could be added not only by teams and projects, but also related to how they need to be executed. For example, a project could have a tag daily, weekly, or monthly.
• (To be defined on another task): Airflow could have different DAGs, running dbt by team, by project or by schedule. In this way, a new "weekly" model doesn't require a new DAG, adding a simple tag will make it run weekly. (I believe this needs to be discussed in another task, I'll create one and link it here)
• We need a naming convention for files, like <team>_<project>_<modelname>.sql.

Folder structure

An example of folder structure could be:

├── models
│   ├── shared
│   │   └── shared_model1.sql
│   ├── team1
│   │   ├── project1
│   │   │   ├── team1_project1_model1.sql
│   │   │   └── team1_project1_model2.sql
│   │   ├── project2
│   │   │   ├── team1_project2_model2.sql
│   │   │   └── tema1_project2_model1.sql
│   │   └── shared
│   │       └── team1_shared_model1.sql
│   ├── team2
│   │   ├── project1
│   │   │   ├── team2_project1_model1.sql
│   │   │   └── team2_project1_model2.sql
│   │   ├── project2
│   │   │   ├── team2_project2_model1.sql
│   │   │   └── team2_project2_model2.sql
│   │   └── shared
│   │       └── team2_shared_model1.sql
│   └── team3

But please, feel free to suggest anything different, maybe some abbreviations for teams or projects, or anything else. The only constraint is that we cannot have models with the same name.

Sources

Sources are used to define tables created outside of dbt but needed to build pipelines. A main sources.yml could exist in the main folder. Most of the sources could be defined and shared in a main sources.yml, although each team could create more sources.yml files in their folders to define tables not being used by anyone else.

Running models

Then, to run dbt, we could run commands like:

• Run everything from a team: dbt run --select "models/team1"
• Run a single project: dbt run --select "models/team1/project1"
• Run everything tagged as "weekly" (it could include many projects): dbt run --select "tag:weekly"
• Run everything needed for a dashboard (or any goal): dbt run --selector "some-dashboard". Using specific selectors, models or projects can be grouped into single commands using selectors.yml.

Graph operators can be used along with these commands for more specific needs.

Out of scope

Incremental models / partitions

There are also a lot of complexities related to how models are build, using incremental models, overriding partitions, which partition keys they should use, if a --full-refresh is available, if microbatching is needed, or if arguments passed from Airflow are needed to "backfill" data, and many others, but I believe those should be out of the scope of this task, and can be reviewed for each specific use case, although we could create some guidelines.

Data tests

We need to define how tests are defined and tested, probably each team can decide how to test their models, although testing is always recommended. We can work on testing on another task.

Monitoring

dbt has a freshness check that can be very useful for monitoring, but it should be discussed on another task, and probably each project can define their own goals.

Other dbt commands

Other commands like seeds, snapshots, tests, docs, etc could be discussed later as they are simpler and not used as often as dbt run.

Thank you @JMonton-WMF ! for all this info. this is a very nice and clear proposal. all of this makes sense to me. sharing a few thoughts -

• I like the proposed folder structure. happy to think through the naming convention.
  • <team>_<project>_<modelname>.sql would translate to something like movementinsights_contributors_retained_editors.sql or productanalystics_consumers_reader_retention_weekly.sql which could become unreasonably long or would need a lot of acronyms.
• My 2c on organizing models: while packages are complex, I feel they would facilitate re-orgs better than groups. if a team ceases to exist or changes, another team could inherit the projects of that group vs having to make copies of their existing projects and models and discarding the older version (this is how i understood group vs package concept, i may be wrong).
  • anyway the likelihood of this happening very often is low, so lets go with whats sustainable, and less complex.

This is great, thanks for the detailed proposal @JMonton-WMF !

I think this generally looks good as well. A single repository using groups/packages to organize model governance looks like the right idea.

Folder by team, subfolders by projects.

Let me make sure I understand: when we say "subfolders by projects", these are not distinct dbt projects right? The repository itself is the dbt project, and the subfolders are units of organization that the team defines?

Each folder can be configured with some groups and access visibility, to avoid other teams accidentally referencing models.

Yes, and I think we should specify a reasonable set of defaults and expectations here, like:

• Every model must belong to a group owned by their team, which DE creates when the team is onboarded to dbt
• Every group has access: private for its models, and teams can override this to expose individual models

dbt doesn't allow to have multiple files with the same name, even if they live in different folders

What happens there is a clash? Would dbt run fail? Can we detect this before orchestration with a linter/CI check or so?

I think having explicit model names, including owning team, is not that bad, but I understand it makes things a little harder if a team gets renamed/reshuffled. In this case, the mechanics of renaming things don't sound too hard: the most important part (knowing exactly what belongs to which team, and who depends on it) is there.

But if we can detect this early, when the duplicate is being introduced, and just have the author of the model rename it, maybe that's enough?

Tags could be added not only by teams and projects, but also related to how they need to be executed. For example, a project could have a tag daily, weekly, or monthly.

Agree this is out of scope for now, but yes - this is also what I'd love to see here :)

Really nice write up! Thank you so much for putting this in writing in phabricator!

My only comment: let's avoid using team names in code, for the reasons stated above. Functional names (that sometimes align with existent team names) are fine, but team names in code have always been and will be problematic. We should have team ownership of code, but that doesn't mean that code needs to be named after a team.

https://wikitech.wikimedia.org/wiki/User:Ottomata/Organized_Code#Avoid_using_team_names_in_code_or_systems

Peripherally relevant, in case you haven't seen:

• https://wikitech.wikimedia.org/wiki/Event_Platform/Schemas/Guidelines#Event_Data_Modeling_and_Schema_Naming
• https://wikitech.wikimedia.org/wiki/Data_Platform/Data_modeling_guidelines#Naming_and_data_type_conventions

Oh! Okay okay, I had more naming thoughts!

Project?

team1_project1_model1.sql

If dbt already has a codified 'project' concept, we should avoid using that concept to mean a different thing in our naming convention. Also, 'projects' change possibly more often than team names!

I think a model should be named after what it represents, including a good namespacing. If it is pageviews to mediawiki pages, then maybe something like mediawiki.traffic.pageview.sql ? Or perhaps something that is very wikimedia foundation scoped (not a generic mediawiki model) should have 'wikimedia' or 'wmf' in the namespace. Something modeling interactions of a newcomer tool feature, maybe wikimedia.mediawiki.contributors.tools.interaction.<feature_name>.sql?

A convention will help us a lot, but anytime there is a new (long lived/official/reusable) model, a bit of name bikeshedding will go a long way to keep us from regrets and tech debt in the future.

Versioning?

I know next to nothing about dbt. How does dbt handle model versioning? Do we need to consider versioning in model names? Do models explicitly map to a e.g. a data lake table, or are they just schemas? If we read Event_Platform/Stream_Configuration#Stream_versioning and swapped out the word 'stream' for 'dbt model', would it make sense?

Let me make sure I understand: when we say "subfolders by projects", these are not distinct dbt projects right? The repository itself is the dbt project, and the subfolders are units of organization that the team defines?

That's right, there is a single dbt project, folders are just ways of organizing models.

Yes, and I think we should specify a reasonable set of defaults and expectations here, like

That looks great! We could come up with a list and configure it automatically by default.

What happens there is a clash? Would dbt run fail? Can we detect this before orchestration with a linter/CI check or so?

Yes, dbt run will fail. dbt compile will fails too I believe, so we could run this in the CI as a check. We can look at it, maybe even the linter will fail, as I believe it compiles dbt.

I also have concerns about the naming convention, the only constraint is that names can not be duplicated. Good point on avoiding <team> names @Ottomata!

I think a model should be named after what it represents, including a good namespacing. If it is pageviews to mediawiki pages, then maybe something like mediawiki.traffic.pageview.sql ? Or perhaps something that is very wikimedia foundation scoped (not a generic mediawiki model) should have 'wikimedia' or 'wmf' in the namespace. Something modeling interactions of a newcomer tool feature, maybe wikimedia.mediawiki.contributors.tools.interaction.<feature_name>.sql

I like this, but I think we need to define very well how these names are produced. If other teams are going to work on new dbt models, they should understand how to decide names, and names could not collide between teams, even if they are building similar things. One pain point might be that multiple teams might want to create similar "intermediate" tables, and they end up giving them the same name.

About Versioning, I'd say dbt doesn't handle versioning in any way, and the way we handle it might change a lot depending on the way we are materializing models. In dbt, Files map to Tables. Schemas can be defined by us, e.g.: Each folder goes a different schema (folder1/table1.sql will be materialized in folder1.table1).

dbt has a general idea of using incremental models, different from what we would do from Airflow. Rather than running chunks of data (daily, hourly, etc), dbt proposes to create an incremental strategy that will always pick the missing data. As an example, you would do something like:

SELECT * FROM source('schema', 'table')
{% if is_incremental() %}
WHERE ds > select max(ds) from { this }
{% endif % }

Where normally only new data is appended to the table. If you change a schema, ideally, it should be backward compatible, but if it isn't, you could run dbt run --full-refresh and it will ignore the filter, drop the destination table, and build it completely again.

That should work for most cases in Data Warehouses, but we might have cases where a --full-refresh is not doable, and we need to use a "backfilling" strategy like Airflow does. But I think this could be big enough to handle it on another talk

Great writeup @JMonton-WMF ! A monorepo shared by all teams definitely sounds like the way to go about this. I’ll try to offer some concrete suggestions on how to organize the monorepo, and address some questions posted above.

The general structure of dbt projects

While we as an organization do not currently adhere to a strict organizational structure of our datasets, I believe that functionally our datasets do follow the staging/intermediate/marts structure as described here: https://docs.getdbt.com/best-practices/how-we-structure/1-guide-overview

In short, dbt recommend dividing all models into three separate layers which are about separating concerns:

• Staging: Normalize raw source data into a clean, predictable shape.
• Intermediate: Apply business logic, transformations, and relationships.
• Marts: Deliver data in a form that’s ready to be queried.

This kind of layering makes perfect sense to me, as it makes it easy to reason about the place a given dataset has in the data processing pipeline.

Now, if we wanted to combine this kind of model layering with our monorepo team folder structure, we basically have two choices:

1. Team-first approach. Structure would have team folders first under models, and then inside each team folder we would have the three model layers.

|── models/
│   ├── data_platform/
│   │   ├── sources.yml
│   │   ├── staging/
│   │   ├── intermediate/
│   │   └── marts/
│   ├── movement_insights/
│   │   ├── sources.yml
│   │   ├── staging/
│   │   ├── intermediate/
│   │   └── marts/
│   └── core/
│       ├── dimensions/
│       └── facts/


2. Layer-First, Team-Second approach.
Structure would have “global” layer folders under models, and then inside each layer each team would have a folder.


|── models/
│ ├── staging/
│   ├── data_platform/
│   └── movement_insights/
│ ├── intermediate/
│   ├── data_platform/
│   └── movement_insights/
│
├── marts/
│   ├── data_platform/
│   ├── movement_insights/
│   └── shared/

I personally think the team-first approach would work much better for our organization.

Pros of the team-first approach:
• Clear ownership
• Teams can move fast without merge conflicts
• Easy to set up folder-based permissions
• Natural place to apply team-specific configs
Cons:
• Shared logic starts to creep into team folders
• Cross-team dependencies get messy if not disciplined

Upgrade
In order to mitigate the problem of shared logic getting duplicated within team folders, we could introduce a non-team _shared folder at the same level:

|── models/
│ ├── _shared/
│   ├── staging/
│   └── dimensions/
│
├── data_platform/
│
  ├── sources.yml
│
  ├── staging/
│
  ├── intermediate/
│
  └── marts/

I think this hybrid kind of model would work because:
• Teams still own everything in their folder
• _shared requires stricter review
• Shared models are consumed, not modified casually
• Shared logic is visible and protected

Similar _shared + team_names folder logic could be applied to the macros folder as well.

What do you think? If we reach a consensus I could create an MR of dbt-jobs that applies one of these structures, so we can see what it looks like in practice.

Some thoughts on model naming guidelines

First, some basic facts and rules of working with models in dbt:

1. Model file names equal model names. A file named wikimedia_history_derivative.sql represents a model named wikimedia_history_derivative which will materialize as a table named wikimedia_history_derivative in a Hive schema/namespace defined in profiles.yml or dbt_project.yml. NOTE: It is possible to define a default Hive schema per team/team folder.
2. There can be no duplicate-named models inside a single dbt project, even if the files of these two models are in different folders. NOTE: Our monorepo is a single dbt project.
3. The folders containing model SQL files play no role in determining the model names. In other words, the folder path in models/team_name/staging/stg_my_model.sql plays no role in determining the model name stg_my_model.

With that in mind, we can talk about the model naming conventions. One typical approach to this would be to apply same or at least similar principles to the three model layers.

1. Staging layer models:

stg_<source>__<entity>
Example: stg_wmf_raw__mw_history

2. Intermediate layer models:

int_<entity>__<transformation>
Example: int_mw_history__enriched

3. Mart layer models:

Dimension tables: dim_<entity>
Fact tables: fct_<event>
Aggregate tables: agg_<entity>__<grain>

dbt doesn't allow to have multiple files with the same name, even if they live in different folders

What happens there is a clash? Would dbt run fail? Can we detect this before orchestration with a linter/CI check or so?

This is the output of dbt compile when there's a model name clash:

(base) ➜  dbt-jobs git:(main) ✗ poetry run dbt compile
03:47:44  Running with dbt=1.10.13
WARNING:thrift.transport.sslcompat:using legacy validation callback
03:47:44  Registered adapter: spark=1.9.3
03:47:45  Encountered an error:
Compilation Error
  dbt found two models with the name "demo_regional_editors_test_dbt".

  Since these resources have the same name, dbt will be unable to find the correct resource
  when looking for ref("demo_regional_editors_test_dbt").

I think it would be fairly easy constructing some CI scripting around detecting these error messages.

I also think the Team-first approach would be better, and I like the proposal of sticking to dbt's proposal of staging, intermediate and mart folders.

For the CI, I believe that adding a simple dbt build step would be enough to find those issues. It'd be very nice to add dbt test at some point too, but that would require to work on a way of running dbt with access to the data, from the CI.

A good practice would be to ensure that every model (or at least every Mart?) have a .yml with the description of the table, fields, and ideally, some data tests.