I have various roles in snowflake
CI_CD_ROLE - to be used for deploying dbt projects to snowflake ( for snow dbt deploy …)
EXEC_ROLE - to be used for executing the dbt project from snowflake after deploy (for dbt run … will be executd by human from UI or programetic)
CI_CD_ROLE & EXEC_ROLE have distinct access levels & need to be used for its own action
my profiles.yml file looks like
<profile_name>:
target: dev
outputs:
dev:
type: snowflake
account: “account”
database: “MY_DB”
schema: “MY_SCHEMA”
warehouse: “MY_WH”
role: “CICD_ROLE” # this is where the problem is
threads: 4
client_session_keep_alive: false
This profile file deploys the dbt project to snowflake , but fails as the CI_CD_ROLE do not have permissions to dbt run
If i change the role in profile file to “EXEC_ROLE” - the deployment fails as CI_CD_ROLE cannot inherit EXEC_ROLE for deploy.
How can this be handled..
I tried keeping two differnt blocks /env one with CI_CD role and one with EXEC role, like
<profile_name>:
target: dev
outputs:
dev:
type: snowflake
account: “account”
database: “MY_DB”
schema: “MY_SCHEMA”
warehouse: “MY_WH”
role: “CICD_ROLE” # this is where the problem is
threads: 4
client_session_keep_alive: false
exec_role1:
type: snowflake
account: “account”
database: “MY_DB”
schema: “MY_SCHEMA”
warehouse: “MY_WH”
role: “EXEC_ROLE”
threads: 4
client_session_keep_alive: false
but this fails too as there is no env or target as exec_role1: in snowflake to “run”. and also the default target “dev” gets added to run automatically failing