[CT-1826] [Feature] Use dbt to manage Dynamic Tables

[boxysean]

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

I would like to use dbt to create and manage Snowflake Dynamic Tables. Dynamic Tables are interesting to me because I have a realtime use-case at my company, when I normally deal with use-cases that scheduling batch processing can handle. I would like to use dbt to manage these new resources because my team and I are familiar with dbt and use it extensively for batch processing. We would like to leverage the same tooling to implement this new use-case.

Describe alternatives you've considered

I am considering creating a script that deploys these tables for me. I also considered creating a dbt package with custom materializations, but I don't have enough bandwidth to implement it myself.

Who will this benefit?

Any team interested in using the new realtime features of Snowflake Dynamic Tables, but don’t want to create a new custom materialization (or other implementations) themselves, either due to lack of expertise or lack of time. I expect dbt to deliver this functionality out-of-the-box.

Are you interested in contributing this feature?

No response

Anything else?

No response

[dbeatty10]

Thanks for opening @boxysean !

As you noted, a new materialization would be the way to support Snowflake's Dynamic Tables in dbt in the way that the team is familiar with.

I don't see Core engineering team at dbt Labs implementing this in the near term, but this would be great for Snowflake or someone in the community to implement and share with others.

There would be a few main places that such a materialization could live:

1. dbt-core
2. dbt-snowflake
3. A dbt package on the dbt Package Hub
4. Somewhere else

I'd think that Option 3 would be a good option. We wouldn't want to put it in dbt-core because we only put materializations there that we'd expect to be supported across multiple adapters. I'd lean away from dbt-snowflake primarily because I'm not sure how much precedent there is within dbt adapters of completely unique materializations.

In terms of which dbt package to put the new materialization within, there are multiple options:

1. A new package created by Snowflake Labs
2. snowflake_utils
3. A new package created by some other organization

"Somewhere else" it could theoretically go:

• https://github.com/dbt-labs/dbt-labs-experimental-features

[jtcohen6]

I've just assigned @dataders @Fleid @amychen1776 to swing back here with some updated thinking, when they're ready to share :)

[nbkrd1]

Hi,
After being in touch with DBT Labs on this, we are describing our use case here.

Describe the feature
We have a number of DBT table models each based on a handful of Snowflake source tables. We create logic to run these tables when the source data change, but this is sometimes tricky, it would make our setup simpler if we could materialize these models as dynamic tables instead. We would need to have way of configuring the lag and warehouse for the model.

Describe alternatives you've considered
We have a primitive DBT macro setup for our own materialized view materialization, and could create a similar setup for dynamic tables we had to. But this would only be a temporary workaround, we want to be based on standard functionality in DBT.

Who will this benefit?
As mentioned above there are many Snowflake users that could benefit from this feature. A standard table model could be changed to use this materialization, and no pipeline automation work would be required for this model going forward.

[Fleid]

Closing as duplicate, work will start from #603