[ADAP-1078] [Feature] Add Materialized View as a Materialization to dbt-snowflake

[HRusby-Cheyne]

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

Snowflake has an object type called Materialized Views. I'm aware that dbt-snowflake previously supported these and it was archived into the dbt-labs-experimental-features repo, I'm unsure as to why this logic was deprecated.

The Dbt Docs currently suggest using dynamic tables instead of materialized views in Snowflake, but having recently had a call with a Senior Snowflake Solutions Architect it was made apparent that dynamic tables aren't a replacement for materialized views and in fact, they are often used in combination (i.e. a materialized view may sit on top of a dynamic table). I have considered manually creating a materialised view outside of dbt however it would be much preferable to contain all of the DDL within the dbt project.

Describe alternatives you've considered

I've considered the following alternatives

• Manually maintaining a materialised view outside of dbt
• Creating a custom materialization, though this seems to be infeasible given that Materialized View isn't a RelationType defined in relation.py

Who will this benefit?

All Snowflake Users should find a benefit in having access to Materialized Views, it is often recommended as a performance improvement by Snwoflake Support

Are you interested in contributing this feature?

Happy to contribute though am not familiar with the dbt codebase

Anything else?

No response

[jeremy-thomas-roc]

I'd like to second this feature request. Specifically, materialized views are the Snowflake recommended approach to query large external tables, a gap in which dynamic tables cannot bridge.

[amychen1776]

@jeremy-thomas-roc Thank you for your comment. We have confirmed with Snowflake that that is no longer the recommendation: Iceberg tables are the recommended way to query and manage large external tables.

[jeremy-thomas-roc]

@amychen1776 their official documentation seems to contradict that: https://docs.snowflake.com/en/user-guide/views-materialized#deciding-when-to-create-a-materialized-view

also, Iceberg tables are a larger technical lift than a materialized view, especially for smaller teams or single use. it seems prudent that dbt be able to support the "next best" option, if they do indeed recommend Iceberg going forward.

if it's a prioritization issue, I would be open to contributing, as I may end up creating this materialization for our team anyway.

[amychen1776]

@jeremy-thomas-roc Thank you! I just let the Engineering team know and see if they can update their documentation with the new recommendation. We always welcome external PRs from the community :)

[jeremy-thomas-roc]

@amychen1776 i opened a draft PR to get some feedback on my direction, hopefully this will help!

[BenoitLF]

Thanks @jeremy-thomas-roc !

[cp-rohitdesai]

Thanks @jeremy-thomas-roc !

@amychen1776 May we know when the PR#1101 will get merged and make this materialized view option available with dbt deployment in snowflake? Any estimated timeline or info on dbt version in which this will available will be helpful.

[CM000n]

@jeremy-thomas-roc Thank you for your comment. We have confirmed with Snowflake that that is no longer the recommendation: Iceberg tables are the recommended way to query and manage large external tables.

Iceberg tables in their current state cannot replace the simplicity of external tables in conjunction with materialized views!

Many companies, like us, still have a need for partition management to be done at the file system level, by tools outside of Snowflake. This is not possible with Iceberg Tables that use a metadata catalog managed by Snwoflake. And for Iceberg Tables with a 3rd party data catalog it is only possible with additional management overhead. We would therefore also welcome the support of Materialized Views.