[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

```markdown
- 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

[github-actions[bot]]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.