[Feature] transient dynamic tables

[homerunner-mqp]

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

Could we implement the "transient" table config to dynamic tables? Follows same pattern as for tables.
https://docs.snowflake.com/en/sql-reference/sql/create-dynamic-table

Describe alternatives you've considered

None.

Who will this benefit?

For cases of dynamic tables as output from a data warehouse, it could decrease cost.

Are you interested in contributing this feature?

Possibly

Anything else?

No response

[bpruss]

I just realized that transient dynamic tables will make a huge difference in our failsafe storage.

[andreasbjorshol]

I just realized that transient dynamic tables will make a huge difference in our failsafe storage.

I have noticed the same. This would make a huge difference and is in line with Snowflake's recommendation for dynamic tables with high refresh throughput.

[jhsb25]

working on this #1210

[jhsb25]

waiting for approval but #1210 is good to merge

[jhsb25]

@dataders can I get an approval on #1210

[jhsb25]

@colin-rogers-dbt please can you approve #1210

[amychen1776]

@jhsb25 We are looking at this PR and will update accordingly if we are able to merge this in. Please do not tag adapter maintainers 🙏