Snowflake invalid materialized view definition

Question

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

Swirly

Answer 1

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

Periodontal

So there's no effective way to materialize complex views that include more than few tables joins. – Swirly

Answer 2

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.

Madelenemadelin