[Feature] support configuring change_tracking for table materializations

[dataders]

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

Context

User request (internal Slack thread):

Currently, we’re trying to figure out the best way to make sure the change_tracking setting persists with our DBT model. Do you (or anyone from Snowflake in this thread) have any documentation regarding how to set change_tracking in a DBT model’s config?

This is not currently possible

Reference

Snowflake SQL Reference: CREATE TABLE

CHANGE_TRACKING = { TRUE | FALSE }

[When TRUE it] adds a pair of hidden columns to the source table and begins storing change tracking metadata in the columns. These columns consume a small amount of storage.

The change tracking metadata can be queried using the CHANGES clause for SELECT statements, or by creating and querying one or more streams on the table.

Additional Info

Describe alternatives you've considered

• user makes a custom materialization to do so. not ideal
• Snowflake makes CHANGE_TRACKING=TRUE the new default so we need not make it explicit

Who will this benefit?

Any user who relies on change_tracking, Dynamic Tables (dependencies of Dynamic Tables must have change_tracking=true), and streaming scenarios in Snowflake

Are you interested in contributing this feature?

No response

Anything else?

No response

[jaypeedevlin]

@dataders is this something the adapters team is considering working on? Otherwise I may pick it up as it would be useful for us.

[dataders]

@dataders is this something the adapters team is considering working on? Otherwise I may pick it up as it would be useful for us.

it's not on the roadmap yet, any contribution would be much appreciated!

I'd also be curious to know if you've got a use case.

[jaypeedevlin]

My understanding of the use case is that we have a downstream project that uses DT, and we have an upstream model that we have added change tracking via DDL, but if it gets full refreshed it loses the config and the downstream DT breaks.

[bpruss]

Have you considered a post hook?

{{ config( 
 post_hook="alter table {{ this }} set change_tracking = true",  ) 
 }}

For my use case it's not very helpful, since I need it mainly for RAW tables created by Matillion or Fivetran, and we don't give the dbt user permission to alter raw tables. Once I have a staging layer, DT Snowflake will set change tracking for us if downstream DTs need it.