Apply the same transformation to multiple DBs in Snowflake

Help
1

I am trying to build a service to provide analytics to multiple customers. I am working in Snowflake using a multi-tenancy architecture in which the warehouse is shared. All of the customers have the exact same DB structure with the same Schemas and tables. The problem I’m having is finding out how to apply the exact same set of transformations to every customer DB.

The Snowflake warehouse is structured like this:

- Customer1
  - Shopify
    - Orders
    - Products
    - etc...
  - MetaAds
  - Production
    - tables
    - views
- Customer2
- ...
- CustomerN

I want to apply the exact transformations to all customer to product the desired fact tables/views in the “Production” schema.

2

I’m guessing this isn’t even possible by the lack of replies.

3

You should be able to do this using a variable that would hold the database name and then supply it on the dbt command line. That would require one job per customer if you were going to create jobs via their UI, but it would use the same code.

However, you should be able to then call the dbt run/build using python in a loop that would set the database variable for each customer. Note - this is something I have been going to look into exactly how to do myself but have not done it yet so I can’t give you more specifics than the idea.