Snowflake materialized view fails on 2nd run

[sandopolus]

DBT Version: 0.20.0

If i create a simple materialized view in snowflake and do dbt run it creates the Materialized View fine. But on the 2nd run of dbt run it fails because it thinks there is a view rather than a materialized view present. This causes it to try and run the drop view command

I am new to DBT and have been trying to look into it more It seems like the following query is used to lookup the information about the object show terse objects in <DB>.<SCHEMA> When i run this in the snowflake console it is returning info about the objects, but it lists both views and materialized views as just view. Which i assume is causing the problem.

I have been trying to modify the macros and adaptors to work around the problem. But still in the learning phase of this project. So i am bumping into quite a few unknowns at the moment.

[matt-winkler]

@jtcohen6 I noticed this same thing, running dbt v0.19.2 in dbt Cloud, and I think it's due to the existing_relation.is_view check for refreshes. This check makes sense on a first run, and works there. But, on subsequent runs, the existing_relation is of type view incorrectly. It feels like this issue is caused by upstream logic in Core that determines the relation type, most likely because materialized views aren't handled explicitly in Core at present.

[jtcohen6]

Thanks for raising the issue @sandopolus, and for confirmation @matt-winkler.

I don't love this behavior from Snowflake:

Basically:

• show terse objects includes materialized views with TYPE: VIEW
• select * from information_schema.tables includes materialized views with TABLE_TYPE: MATERIALIZED VIEW

Back in v0.17, dbt-snowflake switched from using information_schema.tables to using show terse objects for performance/cost reasons (https://github.com/dbt-labs/dbt/issues/2174, https://github.com/dbt-labs/dbt/pull/2322).

As I see it, we have two options for changes to the materialized_view materialization on Snowflake:

• Query information_schema.tables every time we find an existing_relation, to determine whether it's a view-view or a materialized view. I don't love that!
• Remove the existing_relation.is_view check, and thereby fail to replace an existing view with a materialized view (or vice versa) in the case when a user is switching a model's materialization between the two. We can document that as a limitation, and suggest that they either dbt run --full-refresh (if replacing a standard view with a materialized view), or first manually drop the old object (in either case).

What do you two think?

[matt-winkler]

@jtcohen6 oof. My instinct was to remove the is_view check and document the limitation + provide a pattern if users want to implement queries against information_schema.tables themselves. This use case certainly affects more than 0 users, but my sense is the majority of projects don't implement materialized views and they are best deployed for certain use cases.

[sandopolus]

The other potential option to use show views call rather than information_schema.tables The show views call provides more information back about the view including whether it is materialized or not and it doesn't require the query to be executed in a warehouse like the information_schema.tables query does.

I tried to override some of the implementation to try and use show views to get more info back. But i was unsuccessful in my attempts.

[jtcohen6]

@sandopolus That's a fair point! show views is definitely preferable to information_schema.tables. In either case, though, I hesitate to require another metadata query every time the materialized view runs, to handle the <1% case in which a model has switched between a standard and materialized view. I'm leaning toward documenting the limitation.

[ingolevin]

@jtcohen6 This not just happens when a model is switched, but every second run -- even if you change nothing.

{{ config(
    materialized='materialized_view'
    ) 
}}

SELECT
    CLIENT_ID,
    SUM(QUANTITY)
from {{ ref('base_model')}} 
GROUP BY 1

First run, all fine - MV gets created. Second run, 002203 (02000): SQL compilation error: Object found is of type 'MATERIALIZED_VIEW', not specified type 'VIEW'.

This is not a "<1% case". It renders the solution unusable on Snowflake, imho..

[datanrd]

can confirm the behavior as seen by @ingolevin, every subsequent run after the first returns 002203 (02000): SQL compilation error: Object found is of type 'MATERIALIZED_VIEW', not specified type 'VIEW'.

I can currently work around it as described by removing the is_view check.

[dataders]

Materialized Views have been supported in dbt since dbt-core 1.7 (November 2023). See dbt docs: materialized views for more information.

The version of materialized views in this experimental repo is is long-overdue for official deprecation -- it has not been meaningfully maintained or improved for over three years (see https://github.com/dbt-labs/dbt-labs-experimental-features/pull/11).

If you haven't already, you should try out the official implementation! In the case of Snowflake, we implemented Dynamic Tables instead of Materialized Views which works in much the same way but better.

Should you encounter an issue, you can then open bug or enhancement ticket on either dbt-core or the adapter repo for your current data platform.

For now, I'm going to close this ticket as "wont_do"

[GTLangseth]

@dataders I think that it is worth considering the list of known limitations on Dynamic Tables. To say that Dynamic Tables are simply "better" than Materialized Views neglects the non-trivial number of cases for which a Materialized View may be the best or only option.

In my case, I'd like to use a Materialized View, not a Dynamic Table, to maintain a flattened view on top of an external table containing semi-structured data. Unfortunately, it looks like I will have to write my own adapter as Dynamic Tables cannot reference external tables. I am hopeful that Snowflake will some day combined these two concepts into a single construct as they are so similar, but until then it would be nice if your team just supported both inside of core instead of simply ignoring the Materialized View concept because Dynamic Tables are seen as "better"?