5

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

Simon D
  • 5,730
  • 2
  • 17
  • 31
user2129817
  • 85
  • 1
  • 7

2 Answers2

11

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

Ryan Templeton
  • 179
  • 1
  • 4
1

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.
Kevin Meade
  • 11
  • 1