[CT-2044] Secondary role in tests

[mdbiolik]

Is this your first time submitting a feature request?

• [X] I have read the expectations for open source contributors
• [X] I have searched the existing issues, and I could not find an existing issue for this feature
• [X] I am requesting a straightforward extension of existing dbt-snowflake functionality, rather than a Big Idea better suited to a discussion

Describe the feature

In my project I'm using a secondary role as a pre-hook, which is working fine. But the problem is, that the pre-hook is not working for tests. I have experieced a similar problem in some macros (from dbt_utils), which need to access tables related with the secondary role. Unfortunately I need to merge the previleges of both roles now, which is not always that easy in big companies. I hope, I haven't missed any solution, that already exists.

Describe alternatives you've considered

No response

Who will this benefit?

Anybody, who is forced to use a secondary role.

Are you interested in contributing this feature?

I think, I'm not able.

Anything else?

No response

[erenelci]

Indeed highly needed here as well, as I rely in my models on a pre_hook that enables "USE SECONDARY ROLES ALL". The views created in this way are not available for tests, as they require tables only available through secondary roles. So tests only work on tables created not on views.

[jtcohen6]

This seems like a reasonable request. Rather than extending support for arbitrary pre-hook to tests, we could look to implement this in a manner similar to how we implemented support for node-configurable Snowflake warehouses, by running a use role ... statement right before, and reverting to the previous role right after:

https://github.com/dbt-labs/dbt-snowflake/blob/ad9cae8ca6dae42687b0edb22058ecb51a86143d/dbt/adapters/snowflake/impl.py#L78-L89

To extend that support to tests (in addition to models/seeds/snapshots), it would also require resolving this issue:

• https://github.com/dbt-labs/dbt-adapters/issues/212

[github-actions[bot]]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions[bot]]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.

[bpederson-coh]

Would very much like to encourage dbt development to consider this enhancement. Some (but admittedly not all) Snowflake shops have adopted the concept of secondary roles while setting up the RBAC architecture. The use of secondary roles can significantly improve and simplify the "topology" of roles within a large, complex Snowflake instance/account. In effect it allows a multi hierarchical structure instead of a hairball graph with many loops. When we have dbt users we are forced to implement the latter at least for that subset of users.

As a dbt outsider (I'm a Snowflake admin) I really hope this upgrade can be acted on. As previously noted it seems like this problem only applies to the 'test component' of the framework. We just need the test part of the product to be able to fire off "use secondary roles all" as an option. It doesn't seem like it would be very hard to implement...