Dbt: Add COPY GRANTS to #Snowflake table materialisation

[bashyroger]

Describe the bug

Currently, doing a full table refresh on snowflake is done via create or replace table. Which works fine for the data side as the operation is atomic. However, existing grants are not copied when replacing the table, requiring a pre-hook to set them right. This can be corrected by adding COPY GRANTS to the CTAS command.
See https://docs.snowflake.com/en/sql-reference/sql/create-table.html

Steps To Reproduce

Running a job in Snowflake with the table materialisation

Expected behavior

Pre-existing grants being copied over when replacing a table

System information

Which database are you using dbt with?

• [ ] postgres
• [ ] redshift
• [ ] bigquery
• [X] snowflake
• [ ] other (specify: ____________)

[jtcohen6]

Right on, @bashyroger. dbt has supported copy grants in Snowflake materializations since 0.15.0 (changelog):

{{ config(
    materialized = 'table',
    copy_grants = true
}}

select 1 as fun

Can you give that a go and see if it does the trick? I'm going to close this issue, I encourage you to reopen if you run into any issues.

[drewbanin]

@jtcohen6 can you PR a change to the docs that adds this config to the list of Snowflake profile configs?

[bashyroger]

Right on, @bashyroger. dbt has supported copy grants in Snowflake materializations since 0.15.0 (changelog):

{{ config(
    materialized = 'table',
    copy_grants = true
}}

select 1 as fun

Can you give that a go and see if it does the trick? I'm going to close this issue, I encourage you to reopen if you run into any issues.

Ah, totally missed this @jtcohen6 ! I see it in the docs now . Also tested it and it indeed does the trick! my bad!