I’m using snowflake’s row access policies to limit the rows that each user can query in some tables. In dbt right now the only way we found to do that is to add a post-hook with an ALTER TABLE command to add the policy.
The problem with this approach is that the security is applied after the table creation and there is a risk that it is not applied. It would be great to add this as a configuration option for snowflake models or to be able to customize the CREATE TABLE DDL.
Do you know a better way to do this ? Could it be implemented in dbt in the future ?
Hi Damien, just wanted to share that I’m testing a similar strategy in Snowflake using post-hooks to attach row access policies. I’m curious about the risk you mentioned about the policy not being applied. Have you been seeing issues with this?
As far as customizing the create table DDL, I think you could look at making a custom materialization to do this. I just noticed that dbt also has a guide/tutorial related to this topic which may be a good starting point: Create new materializations | dbt Developer Hub
Post hooks weren’t acceptable to us in snowflake due to the limitations around transactions. I overloaded the snowflake view materialization in our project so that the row access and column masking policies we add via model config properties are injected into the view DDL.