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)
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
