[Feature] Snowflake dynamic table adaptor missing support for IMMUTABLE WHERE

[igorbelianski-cyber]

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 functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Support IMMUTABLE WHERE clause https://docs.snowflake.com/en/user-guide/dynamic-tables-immutability-constraints on dynamic tables. This clause controls part of dynamic table that remains static and part that needs to be refreshed.

Describe alternatives you've considered

With the dynamic tables materialization there is no alternative to limit scope of the refresh outside of IMMUTABLE WHERE clause

Who will this benefit?

All DBT pipelines that use dynamic table may benefit from this feature. Core scenarios are:

• Prevent propagation of updates or deletions to existing data.
• Restrict inserts, updates, and deletes for rows that meet a condition.
• Limit future modifications while still enabling incremental updates to other parts of the table.
• Backfill dynamic tables with immutability constraints. For more information, see Create dynamic tables by using backfill.

Are you interested in contributing this feature?

yes PR is coming

Anything else?

No response