Snowflake invalid materialized view definition

2
votes

When running in Snowflake the following command:

CREATE MATERIALIZED VIEW MV_CUSTOMER_PREFERENCE as select * from V_CUSTOMER_PREFERENCE;

I get the following error:

SQL compilation error: error line {0} at position {1} Invalid materialized view definition. More than one table referenced in the view definition

V_CUSTOMER_PREFERENCE is an existing and functioning view (it can be queried separately), that joins information from different tables. I get the same error when I put the original query instead of the view, it's just a long and complicated SQL query.

What can be the problem with the query in the view? I cannot understand it from the error description and I didn't find related restrictions in https://docs.snowflake.net/manuals/user-guide/views-materialized.html

2
materialized-viewssnowflake-cloud-data-platform

2 Answers

8
votes

A materialized view can query only a single table. You can see the list of limitations for working with materialized views here: https://docs.snowflake.net/manuals/user-guide/views-materialized.html#limitations-on-creating-materialized-views

0
votes

That is correct: Unlike other databases, MVIEWS in Snowflake are a very targeted and simplified feature. They have the following use cases:

  • Provide Alternative Clustering for tables with multiple access paths.
  • Provide Project/Restrict on high use columns/rows.
  • Provide Pre-aggregation for high frequency queries and sub-queries.