add support for explicitly setting REFRESH_MODE = INCREMENTAL in dynamic tables

bpruss commented 2 weeks ago

Is this your first time submitting a feature request?

[X] I have read the expectations for open source contributors
[X] I have searched the existing issues, and I could not find an existing issue for this feature
[X] 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 want the option to set REFRESH_MODE when configuring a dynamic_table materialization. I need an INCREMENTAL dynamic table and because my SQL contains a lateral flatten, Snowflake is telling me I need to explicitly set the the REFRESH_MODE to INCREMENTAL.

Here is the message from the refresh_mode_reason column of a show dynamic tables; This dynamic table contains a lateral flatten which is now supported incrementally. To opt in, we recommend reading https://docs.snowflake.com/user-guide/dynamic-table-performance-guide and explicitly setting the refresh mode to INCREMENTAL.

Describe alternatives you've considered

Trying to take the generated sql, manually adding the "REFRESH_MODE = INCREMENTAL" and manage it separately.
Live with the full refresh which is taking approximately 7 min with a Large WH where the INCREMENTAL takes about 30 seconds. With an incremental we are inserting about 2,000 row every 10 min. With a FULL we are deleting 218,000 rows and then adding back 220,000, with those numbers increasing by about 2000 rows every 10 min, until we start over from 0 the next morning.

Who will this benefit?

Anyone using dynamic tables where they need to explicitly call out REFRESH_MODE = INCREMENTAL. At least people who use lateral flatten in their dynamic tables.

Are you interested in contributing this feature?

I would be interested in looking at the existing code for dynamic table materialization. If I can do this in a reasonable amount of time, I'd be happy to contribute.

Anything else?

https://docs.snowflake.com/en/sql-reference/sql/create-dynamic-table

amychen1776 commented 1 week ago

Hi! We have already resolved this in https://github.com/dbt-labs/dbt-snowflake/pull/1081. It will arrive in v1.9 when we cut the release next month. It's already available in dbt Cloud if you're on versionless.

bpruss commented 1 week ago

@amychen1776 - Thank you! I did try to do a search. And I'm new to participating on OSS.

amychen1776 commented 1 week ago

No worries! I still appreciate you opening it - shows people care :)

dbt-labs / dbt-snowflake