[Feature] dynamic table cluster_by support

[mstelter52]

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

It appears the cluster_by is only functionality for transient tables, Snowflake now supports it with dynamic tables. Can this be added to the dynamic table macros.

Describe alternatives you've considered

No response

Who will this benefit?

No response

Are you interested in contributing this feature?

No response

Anything else?

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

[dtiesling]

Hi @amychen1776 👋 . Sorry to @ you out of blue but saw you were the last to touch this ticket. I also need this functionality. Do you know if this feature is already on the roadmap and/or under active development? If not I may have time to contribute a PR for this one.

cc: @j-choe

[dtiesling]

On a related note I would not want to implement the feature using the legacy method of clustering that is used for tables now. CREATE DYNAMIC TABLE supports a CLUSTER BY parameter I would prefer to use. Looks like there is already discussion about migrating table creation to use this as well in this ticket.

[amychen1776]

Hello @dtiesling - this is not in our current roadmap, but it is being considered. We would always welcome a PR

[axiomoixa]

I would appreciate the availability of this feature as well.
Our dynamic tables are badly clustered possibly due to the group by that we have, they urgently need to be reclustered.

[loic-fontaine-msr]

+1 cluster_by is currently not doing anything, it should at least fail since it is not implemented.

As a workaround, I'm doing the clustering in a post hook:

{{
  config(
    materialized='dynamic_table',
    cluster_by=['my_cluster_key'],
    post_hook="ALTER table {{ this }} CLUSTER BY (my_cluster_key);",
  )
}}