[Feature] Add refresh_mode to macro snowflake__get_create_dynamic_table_as_sql

[kylienhu]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt-snowflake functionality, rather than a Big Idea better suited to a discussion

Describe the feature

I'm looking to set the parameter refresh_mode for dynamic tables on Snowflake. Can we add that parameter to the macro snowflake__get_create_dynamic_table_as_sql so it can be set through dbt_project.yml or config block?

Describe alternatives you've considered

No response

Who will this benefit?

The incremental refresh feature of dynamic tables are still being developed and on some of my complicated models they took even longer than a full refresh. I would like to choose the refresh mode as full in these cases.

Are you interested in contributing this feature?

No response

Anything else?

No response

[dataders]

OK! Last I checked that wasn't a parameter, but now I see it's in the below doc

Snowflake SQL Reference: CREATE DYNAMIC TABLE: Optional Parameters

REFRESH_MODE = INCREMENTAL: Enforces an incremental refresh of the dynamic table. If the query that underlies the dynamic table can’t perform an incremental refresh, dynamic table creation fails and displays an error message.

I agree that REFRESH_MODE = INCREMENTAL would be very valuable, especially during initial development!

You're right to identify that snowflake__get_create_dynamic_table_as_sql() (below) is the macro that needs to change.

dbt-snowflake/dbt/include/snowflake/macros/relations/dynamic_table/create.sql

Lines 1 to 10 in c22e13e

	{% macro snowflake__get_create_dynamic_table_as_sql(relation, sql) -%}
	create dynamic table {{ relation }}
	target_lag = '{{ config.get("target_lag") }}'
	warehouse = {{ config.get("snowflake_warehouse") }}
	as (
	{{ sql }}
	)
	{%- endmacro %}

The lowest-effort fix would be to modify to pepper in the two lines below to the macro.

-- before DDL
{%- set refresh_mode = config.get('refresh_mode') -%}

-- before the line starting with "as (sql)"
{% if refresh_mode is not none -%}
   REFRESH_MODE = {{ refresh_mode }}
{%- endif -%}

@kylienhu you are unblocked to to try this today by putting the modified macro into your project's macros/ directory. If it works, let us know, we'd even accept a PR to this repo with that change (and happy to assist you in doing so)

However, I see other parameters that are potentially useful we should also consider adding these.

Other parameters not yet supported:
INITIALIZE, CLUSTER BY, DATA_RETENTION_TIME_IN_DAYS, MAX_DATA_EXTENSION_TIME_IN_DAYS, COMMENT

[kylienhu]

Thank you for your response! I saw there might already be a PR that might tackle this issue?
#893

[dataders]

@kylienhu you are very right! thanks for finding it. Looks like its a few months old, we'll have to resolve some merge conflicts in it, after which we can get it reviewed and merged

[dataders]

closing in favor of #868