Search code examples
testingrolesdbt

DBT - Secondary Role as pre-hook is not working in tests

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

Solution

  • 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.