dbt fails if destination schema contains a MATERIALIZED VIEW in Snowflake

[adrianisk]

Issue

Issue description

dbt fails to compile or run when the destination schema contains a MATERIALIZED VIEW

Results

Run dbt compile, get

Running with dbt=0.13.0
* Deprecation Warning: The adapter function `adapter.already_exists` is deprecated and will be removed in
 a future release of dbt. Please use `adapter.get_relation` instead.
 Documentation for get_relation can be found here:
 https://docs.getdbt.com/reference#adapter

Encountered an error:
Runtime Error
  Invalid arguments passed to "SnowflakeRelation" instance: type.'MATERIALIZED VIEW' is not one of ['table', 'view', 'cte', None] 

System information

The output of dbt --version: 0.13.0

The operating system you're running on: OS X

The python version you're using (probably the output of python --version): 3.7

Steps to reproduce

Run dbt when the destination schema contains a materialized view.

Cause

I believe the bug is caused by a combination of the snowflake__list_relations_without_caching macro https://github.com/fishtown-analytics/dbt/blob/027a0d2ee6d138b5c7ae05a0b20e511e6bfdf516/plugins/snowflake/dbt/include/snowflake/macros/adapters.sql#L53, which returns MATERIALIZED VIEW as the table_type:

and the list of acceptable relations in https://github.com/fishtown-analytics/dbt/blob/3f18b9398015e5c9e518ed43b344878e014c960c/core/dbt/adapters/base/relation.py.

[drewbanin]

Hey @adriank-convoy - thanks for the report! We have an open issue to support materialized views in Snowflake, but this is a smaller issue for sure.

I think you're right, a quick fix for this is to add a new class attribute called MaterializedView and add it to the RelationTypes enumeration here.

I experimented with Snowflake's materialized views a couple of months ago, and they had some pretty serious limitations if I remember correctly. Are you using these in production? If so, I'd love to get your thoughts on #1162!

[adrianisk]

Hey Drew,

We have a single materialized view used by one of our services that's preventing us from upgrading to 0.13.0 =/. Right now it's created by a post-hook macro that runs every time the base model is full-refreshed due to https://docs.snowflake.net/manuals/user-guide/views-materialized.html#changes-to-base-tables, which seems to be a reasonable pattern for now

I don't think we're in a rush to create more anytime soon, so I'll just try adding the MaterializedView type & send over a PR if it works

[drewbanin]

Got it, ok, that would be great!

[beckjake]

Fixed in #1432, Thank you @adriank-convoy !