2

I have a dbt job running once a day, and the dataset is shared out to a reader account in Snowflake. The dbt job is recreating the dataset as a table daily using create or replace transient table as ..., which causes the reader account to lose permissions on the table everytime the job runs. I tried grant select on future tables in schema my_db.my_schema to share my_share; but got the following error:

Future grant on objects of type TABLE to SHARE is restricted.

What is the best practice for dealing with this scenario for reader accounts? I've seen this which is a nice solution, but I have to imagine there is a better way than adding a post-hook to every marts model in dbt.

Marty_C137
  • 330
  • 1
  • 10
  • Assuming the structure of the table is not changing, can you truncate/insert the data each time, rather than re-creating the table? If you re-create the table you will need to grant permissions each time as the table object is changing i.e. though the table might have the same name each time you create it, it is a different object – NickW May 24 '23 at 13:57

2 Answers2

2

Add the following to dbt_project.yml:

models:
  +copy_grants: true

copy_grants is set to false by default in dbt for Snowflake configuration.

Marty_C137
  • 330
  • 1
  • 10
  • 2
    Good answer - link to docs with other Snowflake configuration options in dbt https://docs.getdbt.com/reference/resource-configs/snowflake-configs#copying-grants – Felipe Hoffa May 24 '23 at 22:47
1

You can specify 'copy grants' clause in the 'create or replace table' command. This would retain the grants to the share.

If the existing table was shared with another account, the replacement table is also shared.

create or replace TABLE <table_name> (<column definitions>) copy grants;
Uma Deivasigamani
  • 142
  • 5