Multi tenant setup

I have a database where for each customer there is one specific schema. All the schemas share the same layout.

customer_1.orders
customer_2.orders
.
.
.
customer_n.orders

I am trying to have a small etl process using dbt to create the following. I want a transformed orders_fact table in each of these schemas.

customer_1.orders_fact
customer_2.orders_fact
.
.
.
customer_n.orders_fact

On top each schema has a unique user/password combination to access the data (e.g. schema customer_1 has username: user_1, password: random_password).

How would I set this up in dbt. I am currently struggling with the unique name constraint of a model and how to set up the config in a way that for each schema there are specific credentials.

Hi there,
You may want to consider creating “dynamic” models which, in a nutshell, means this:

  • Have code for generic model customer.orders
  • Schema, table and database are all variables in the config block
  • When you execute dbt run, provide values for variables as per normal
  • You could also have a macro called from the generic model with a FOR loop to go through the list of variable values
  • Have a macro to give access can be given based on variable values

Hope this makes sense.