How to set DB to support multiple projects with an organisation?


This is a following question from monorepo or multiple projects .We decide to use one project per team/workflow, not monorepo.

How to setup DBs to support multiple projects?
The simplest way is to setup target schemas like analytics_dev, analytics_qa and analytics_prod . Each schema suport different environments(DEV, QA, PROD)

Within one schema, analytics_dev for example, models from multiple projects will exist in it together. Will it work?

I have seen other diags, such as

But I cannot work out how it works.

Are there any links or videos to discuss this topic?


Hi @redcenter, have you seen our docs on Using custom schemas? This should have all that you need to get this set up!

Hi @aescay Thank you for reply.
Correct me if I’m wrong, Using custom schemas supports multiple target schemas within ONE project.

My question is about best practice for target schemas for mutiple projects.

Question 1: Should we merge target schemas from multiple projects into one or should we split them base on projects?

Option 1: Merge target schemas from multiple projects into one .
Project 1: Target schema
Project 2: Target schema

Option 2: Should we split target schemas base on projects?
Project 1: Target schema
Project 2: Target schema

Question 2:
If we choose option 2, should we put all target schemas in one database or multiple databases?

I couldn’t find any links on the setup for multiple projects.

Hi @redcenter,
as described in the link provided by @aescay DBT generates actual schema names by using two components:

  • target schema, i.e. the schema name assigned to a profile (think person / environment) in the project setup
  • custom schema, i.e. the schema names assigned to a model through model configuration

=> the actual schema name will be targetSchema_customSchema.

I know the names could be better, but they are what they are.
My way of thinking is that “target” is the base target, i.e. the env, to build the model into and “custom” means customised to the specific model, so it is the specific schema inside the env.

As Schema can be only one level deep, this two layers are achieved by having the target being a PREFIX of the custom to generate the actual schema, so we use it to designate the environment.
Some examples of what TARGET schemata boil down to:

  • a user prefix, like “RZ” in my case, for development environments (every dev gets his one)
  • an environment prefix, like “QA”, “CI” or “PROD”, for deployment envs (shared envs, downloaded by a repo)

The custom schema is the second part of the schema name, it is more specific, then we use it to have the division inside the environment.
Some examples:

  • “models/stg” has “stage” as (custom) schema
  • “models/mktg” has “marketing” as (custom) schema

Then you would have this final effects…

In my dev environment I will have “RZ” as target schema so that

  • “models/stg” models will be created in schema RZ_stage
  • “models/mktg” models will be created in schema RZ_marketing

When released in QA environment that has “QA” as target schema you’ll get

  • “models/stg” models will be created in schema QA_stage
  • “models/mktg” models will be created in schema QA_marketing

So in the end you will have all the schemata that make up one of your environment nicely listed one close to the other, thanks to using the same prefix.
For each environment you will get one schema for each custom schema you use.
Note that different model path or macros or project can point to the same schema (custom => actual); the only limit is being sure to not have clashing names…

I guarantee you that it sounds more complicated than it is in practice.
My advice it to try to have a few models in different custom schema and using two profiles (with different target schema) to run them and see how things are generated.
It will be self explanatory after that :wink:

I hope it helps !

Ciao, Roberto

1 Like