[Feature] transient dynamic tables

### Is this your first time submitting a feature request?

- [X] I have read the [expectations for open source contributors](https://docs.getdbt.com/docs/contributing/oss-expectations)
- [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

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_