search

Montreal-Analytics / dbt-snowflake-utils

Snowflake-specific utility macros for dbt projects.
Apache License 2.0
107 stars 37 forks source link

Add a macro to clone databases #11

Closed gnilrets closed 3 years ago

gnilrets commented 4 years ago

In our dbt implementation, we have two production databases: PROD_RAW and PROD_WH. PROD_RAW houses all of the raw source data and PROD_WH is where dbt builds models. These production database are owned by a PROD_TRANSFORMER role. dbt developers use the DEVELOPER role. When we try using the simple create database dev_wh clone prod_wh as a DEVELOPER, all of the tables are still owned by the PROD_TRANSFORMER role and thus the developer cannot run dbt in the DEV_WH database.

In order to overcome this limitation, I've modified the clone_schema macro to clone each table/view individually, which ends up creating tables owned by the DEVELOPER role.

MartinGuindon commented 3 years ago

Haven't heard back from PR comments, closing PR.

gnilrets commented 3 years ago

@MartinGuindon - What PR comments? Did I fail to respond to something?

MartinGuindon commented 3 years ago

It was a very long time ago, but yes. :) https://github.com/Montreal-Analytics/dbt-snowflake-utils/pull/11/files/ddecfa11a7fc4d2ee202c46ba66325d0cb78b9a8#diff-0332e624a3a646edad4d2669797f8224a2c50c6516c5719ab063f6fb1c6f5d91R17

gnilrets commented 3 years ago

Hmmm.... I'm not seeing any comments when I click that link:

image

MartinGuindon commented 3 years ago

Mmm that's really odd, but might explain why you never answered.. lol.

here's what I had written:

@gnilrets Thank you for your contribution!

Is there a reason why you went with generating the SQL queries in warehouse using a case when and concats, rather than simply retrieving the list of tables/views as a dict and creating those queries directly in your for loop in Jinja?

gnilrets commented 3 years ago

Not particularly, that's just how this started before it was all wrapped up into a macro, and my SQL is better than my jinja