Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
3 tasks done
HRusby-Cheyne opened this issue Dec 20, 2023 · 8 comments · May be fixed by #1101
Open
3 tasks done

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

HRusby-Cheyne opened this issue Dec 20, 2023 · 8 comments · May be fixed by #1101
Labels
enhancement New feature or request materialized_views

Comments

@HRusby-Cheyne
Copy link

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

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
@HRusby-Cheyne HRusby-Cheyne added enhancement New feature or request triage labels Dec 20, 2023
@github-actions github-actions bot changed the title [Feature] Add Materialized View as a Materialization to dbt-snowflake [ADAP-1078] [Feature] Add Materialized View as a Materialization to dbt-snowflake Dec 20, 2023
@HRusby HRusby mentioned this issue Dec 26, 2023
Closed
2 tasks
@jeremy-thomas-roc
Copy link

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
Copy link

amychen1776 commented Jun 28, 2024 •
edited
Loading

@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
Copy link

@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
Copy link

@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 jeremy-thomas-roc linked a pull request Jul 1, 2024 that will close this issue
Open
4 tasks
@jeremy-thomas-roc
Copy link

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

@BenoitLF
Copy link

Thanks @jeremy-thomas-roc !

@cp-rohitdesai
Copy link

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
Copy link

CM000n commented Aug 14, 2024

@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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
enhancement New feature or request materialized_views
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

[FEATURE] materialized views jeremy-thomas-roc/dbt-snowflake
7 participants
@CM000n @dbeatty10 @cp-rohitdesai @amychen1776 @HRusby-Cheyne @jeremy-thomas-roc @BenoitLF