in my DBT project (in Snowflake) I'm using a secondary role, which is working fine if I run the models. But if I run the tests using dbt test I'm getting:
Database Error in test not_null_stg_mytable_myid (models\staging\schema_staging.yml)
14:43:19 002037 (42601): SQL compilation error:
14:43:19 Failure during expansion of view 'STG_MYTABLE': SQL compilation error:
14:43:19 Database 'MYDB' does not exist or not authorized.
14:43:19 compiled Code at
target\run\myproject\models\staging\schema_staging.yml\not_null_stg_mytable_myid.sql
It seems that my secondary role which I'm defining as pre-hook is not valid during runing the tests. Here the config in the dbt_project.yml:
models:
myproject:
+pre_hook:
- "USE SECONDARY ROLES MY_SECONDARY_ROLE"
Any idea, how I can set the secondary role for the tests? The following didn't work:
tests:
myproject:
+pre_hook:
- "USE SECONDARY ROLES MY_SECONDARY_ROLE"
Best regards Martin
You can't define pre- or post-hooks for tests. dbt simply ignores that config, which is why this works for your models but not your tests.
You can specify a role in your profiles.yml, and you can switch between targets at runtime, so if you only need privileges from a single role, to run your tests, you could dbt test -t test_target.
However, if you need privileges from both your primary and secondary roles to execute your tests, you'll have to refactor your roles and consolidate those privileges into a single role.
You could also open an issue for this in dbt-snowflake! Seems like a useful feature to support using different roles in different contexts.