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.

1 Like

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.

Hi, I’m running into the same problem as you. Would you mind sharing what you have explored? @Tafkas