[ADAP-947] [Feature] Support copy grant for dynamic tables

[ZhiZinTan-KSO]

Is this a new bug in dbt-snowflake?

• [X] I believe this is a new bug in dbt-snowflake
• [X] I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

with copy grant set in dbt_project.yml, when running dbt build --select some_dynamic_table_model --full-refresh, the grants that was given manually have been removed.

Expected Behavior

with copy grant set in dbt_project.yml, the existing grants on the dynamic table should be remain as it is.

Steps To Reproduce

1. create snowflake dynamic table model and run it

{{
    config(
        materialized='dynamic_table',
        snowflake_warehouse = target.warehouse,
        target_lag = '5 minutes',
    )
}}

select * from {{ref('some_dynamic_table_model')}}

2. manually grant to snoflake dynamic table grant select on some_database.some_schema.some_dynamic_table_model to role some_role;
3. set copy grant config # dbt_project.yml models: +copy_grants: true
4. dbt build --select some_dynamic_table_model --full-refresh
5. check snowflake dynamic table grants show grants on table some_database.some_schema.some_dynamic_table_model;

Relevant log output

No response

Environment

- OS:Ventura 13.6
- Python:3.11.5
- dbt-core:1.6.5
- dbt-snowflake:0.21.3

Additional Context

No response

[dataders]

@zztan-ipg thanks for opening the issue! My first thought was "oh shoot, we totally missed that!", until I did some digging.

I'm going to confirm directly with some Dynamic Table folks at Snowflake, but my understanding based on the docs is that the COPY_GRANTS parameter is not supported for a CREATE DYNAMIC TABLE statement, where it is supported for other table types (e.g. CREATE TABLE)

[ZhiZinTan-KSO]

Hey @dataders just wondering if you got the reply from Snowflake dynamic table folks yet?

[dataders]

thanks for the bump @zztan-ipg! I reached out to @sortalongo and team and the response was

COPY GRANTS is on the release train, but its delayed and going through a behavior change process. Opt-in period should start mid Nov

my advice would be to reach out to your Snowflake reps and tell them you're interested!

[ZhiZinTan-KSO]

Noted with thanks!

[dbeatty10]

Relabeling this as a feature request since dbt is not behaving incorrectly. We will need to confirm that it works once its available within Snowflake