I’d like to use model hooks to use row access policies in snowflake. Via post hook, this seems to work. However, the hook is not executed in the same transaction as table creation. Thus, 1) for a very brief time between creation and adding the RAP, the table is not protected by RAP, and 2) if creation of the RAP fails, the table is still replaced but now without RAP and data is leaked.
As per documentation, dbt does not support transactions for snowflake:
Are transactions in snowflake still completely unsupported?
What is the suggested way of creating tables with RAPs in snowflake using dbt?
Since dbt itself doesn’t support transactions in Snowflake, a safer approach would be to wrap the entire table creation and RAP application within a stored procedure. You can then call this stored procedure within dbt using an on-run-end macro or an operation model. This ensures that if any part of the process fails, the table won’t be left unprotected.
Another workaround is to use a transient staging table, create the table there first, apply RAP, and only then swap it into production.
Interesting, what would be the benefit of using a stored procedure vs executing SQL directly? In particular, how would a stored procedure ensure that if any part of the process fails, the table won’t be left unprotected?
I’m now using a custom materialization that uses a staging table, applies the RAP, and then swaps it into production (using atomic SWAP).