How do you limit the query results to records where the value in the credits field is greater than 120?
A materialized view is a pre-computed data set derived from a query specification (the SELECT in the view definition) and stored for later use. Because the data is pre-computed, querying a materialized view is faster than executing a query against the base table of the view. This performance difference can be significant when a query is run frequently or is sufficiently complex. As a result, materialized views can speed up expensive aggregation, projection, and selection operations, especially those that run frequently and that run on large data sets. Show
Note Materialized views are designed to improve query performance for workloads composed of common, repeated query patterns. However, materializing intermediate results incurs additional costs. As such, before creating any materialized views, you should consider whether the costs are offset by the savings from re-using these results frequently enough. In this Topic: Deciding When to Create a Materialized View¶Materialized views are particularly useful when:
Advantages of Materialized Views¶Snowflake’s implementation of materialized views provides a number of unique characteristics:
Deciding When to Create a Materialized View or a Regular View¶In general, when deciding whether to create a materialized view or a regular view, use the following criteria:
These criteria are just guidelines. A materialized view might provide benefits even if it is not used often — especially if the results change less frequently than the usage of the view. Also, there are other factors to consider when deciding whether to use a regular view or a materialized view. For example, the cost of storing the materialized view is a factor; if the results are not used very often (even if they are used more often than they change), then the additional storage costs might not be worth the performance gain.
Comparison with Tables, Regular Views, and Cached Results¶Materialized views are similar to tables in some ways and similar to regular (i.e. non-materialized) views in other ways. In addition, materialized views have some similarities with cached results, particularly because both enable storing query results for future re-use. This section describes some of the similarities and differences between these objects in specific areas, including:
Snowflake caches query results for a short period of time after a query has been run. In some situations, if the same query is re-run and if nothing has changed in the table(s) that the query accesses, then Snowflake can simply return the same results without re-running the query. This is the fastest and most efficient form of re-use, but also the least flexible. For more details, see Using Persisted Query Results. Both materialized views and cached query results provide query performance benefits:
Regular views do not cache data, and therefore cannot improve performance by caching. However, in some cases, views help Snowflake generate a more efficient query plan. Also, both materialized views and regular views enhance data security by allowing data to be exposed or hidden at the row level or column level. The following table shows key similarities and differences between tables, regular views, cached query results, and materialized views:
Examples of Use Cases For Materialized Views¶This section describes some general usage scenarios that also provide a conceptual overview of materialized views:
How the Query Optimizer Uses Materialized Views¶You don’t need to specify a materialized view in a SQL statement in order for the view to be used. The query optimizer can automatically rewrite queries against the base table or regular views to use the materialized view instead. For example, suppose that a materialized view contains all of the rows and columns that are needed by a query against a base table. The optimizer can decide to rewrite the query to use the materialized view, rather than the base table. This can dramatically speed up a query, especially if the base table contains a large amount of historical data. As another example, in a multi-table join, the optimizer might decide to use a materialized view instead of a table for one of the tables in the join. Note Even if a materialized view can replace the base table in a particular query, the optimizer might not use the materialized view. For example, if the base table is clustered by a field, the optimizer might choose to scan the base table (rather than the materialized view) because the optimizer can effectively prune out partitions and provide equivalent performance using the base table. A materialized view can also be used as the data source for a subquery. When the optimizer chooses to use a materialized view implicitly, the materialized view is listed in the EXPLAIN plan or the Query Profile instead of the base table. You can use this information to experiment and understand which queries can benefit from existing materialized views. About Materialized Views in Snowflake¶The next sections explain how materialized views are represented in Snowflake. DML Operations on Materialized Views¶Snowflake does not allow standard DML (e.g. INSERT, UPDATE, DELETE) on materialized views. Snowflake does not allow users to truncate materialized views. See Limitations on Working With Materialized Views (in this topic) for details. Access Control Privileges¶There are three types of privileges that are related to materialized views:
You can use the standard commands for granting and revoking privileges on materialized views:
Privileges on a Materialized View’s Schema¶Materialized views consume storage space. To create a materialized view, you need the CREATE MATERIALIZED VIEW privilege on the schema that will contain the materialized view. You need to execute a statement similar to: GRANT CREATE MATERIALIZED VIEW ON SCHEMA <schema_name> TO ROLE <role_name>; For more details about the GRANT statement, see GRANT Privileges on a Materialized View¶Materialized Views, like other database objects (tables, views, UDFs, etc.), are owned by a role and have privileges that can be granted to other roles. You can grant the following privileges on a materialized view:
As with non-materialized views, a materialized view does not automatically inherit the privileges of its base table. You should explicitly grant privileges on the materialized view to the roles that should use that view. Note The exception to this rule is when the query optimizer rewrites a query against the base table to use the materialized view (as explained in How the Query Optimizer Uses Materialized Views). In this case, the user does not need privileges to use the materialized view in order to access the results of the query. Privileges on the Database Objects Accessed by the Materialized View¶As with non-materialized views, a user who wishes to access a materialized view needs privileges only on the view, not on the underlying object(s) that the view references. Secure Materialized Views¶Materialized views can be secure views. Most information about secure views applies to secure materialized views. There are a few cases where secure materialized views are different from secure non-materialized views. The differences include:
For more information about secure views, see Working with Secure Views. The syntax to create secure materialized views is documented at CREATE MATERIALIZED VIEW. Creating and Working With Materialized Views¶This section provides information about creating and working with materialized views. Planning to Create a Materialized View¶When deciding to create a materialized view, consider doing some analysis to determine the need for the view:
Creating a Materialized View¶Use the CREATE MATERIALIZED VIEW command to create a materialized view. For an example, see Basic Example: Creating a Materialized View (in this topic). Note the following:
Limitations on Creating Materialized Views¶Note These are current limitations; some of them might be removed or changed in future versions. The following limitations apply to creating materialized views:
Basic Example: Creating a Materialized View¶This section contains a basic example of creating and using a materialized view:
More detailed examples are provided in Examples (in this topic). Working With a Materialized View¶After you create a materialized view, a background process automatically maintains the data in the materialized view. Note the following:
The following command and view provide information about materialized views:
Limitations on Working With Materialized Views¶Note These are current limitations; some of them might be removed or changed in future versions. The following limitations apply to using materialized views:
Effects of Changes to Base Tables on Materialized Views¶In Snowflake, changes to a base table are not automatically propagated to materialized views based on that table.
Materialized Views in Cloned Schemas and Databases¶If you clone a schema or a database that contains a materialized view, then the materialized view is cloned. If you clone the materialized view and the corresponding base table at the same time (as part of the same CREATE SCHEMA ... CLONE or CREATE DATABASE ... CLONE operation), then the cloned materialized view refers to the cloned base table. If you clone the materialized view without cloning the base table (e.g. if the table is in Database1.Schema1 and the view is in Database1.Schema2, and you clone only Schema2 rather than all of Database1), then the cloned view will refer to the original base table. Maintenance Costs for Materialized Views¶Materialized views impact your costs for both storage and compute resources:
Estimating and Controlling Costs¶There are no tools to estimate the costs of maintaining materialized views. In general, the costs are proportional to:
You can control the cost of maintaining materialized views by carefully choosing how many views to create, which tables to create them on, and each view’s definition (including the number of rows and columns in that view). You can also control costs by suspending or resuming the materialized view; however, suspending maintenance typically only defers costs, rather than reducing them. The longer that maintenance has been deferred, the more maintenance there is to do. See also Best Practices for Maintaining Materialized Views. Tip If you are concerned about the cost of maintaining materialized views, Snowflake recommends starting slowly with this feature (i.e. create only a few materialized views on selected tables) and monitor the costs over time. Viewing Costs¶You can view the billing costs for maintaining materialized views using Snowsight, the classic web interface, or SQL: SnowsightAs an account administrator, click on Admin » Usage. Classic Web InterfaceAs an account administrator, click on Account » Billing & Usage.The credit costs are tracked in a Snowflake-provided virtual warehouse named MATERIALIZED_VIEW_MAINTENANCE.SQLQuery either of the following:
For example: SELECT * FROM TABLE(INFORMATION_SCHEMA.MATERIALIZED_VIEW_REFRESH_HISTORY()); Note Resource monitors provide control over virtual warehouse credit usage; however, you cannot use them to control credit usage for the Snowflake-provided warehouses, including the MATERIALIZED_VIEW_MAINTENANCE warehouse.Materialized Views and Clustering¶Defining a clustering key on a materialized view is supported and can increase performance in many situations. However, it also adds costs. If you cluster both the materialized view(s) and the base table on which the materialized view(s) are defined, you can cluster the materialized view(s) on different columns from the columns used to cluster the base table. In most cases, clustering a subset of the materialized views on a table tends to be more cost-effective than clustering the table itself. If the data in the base table is accessed (almost) exclusively through the materialized views, and (almost) never directly through the base table, then clustering the base table adds costs without adding benefit. If you are considering clustering both the base table and the materialized views, Snowflake recommends that you start by clustering only the materialized views, and that you monitor performance and cost before and after adding clustering to the base table. If you plan to create a table, load it, and create a clustered materialized view(s) on the table, then Snowflake recommends that you create the materialized views last (after loading as much data as possible). This can save money on the initial data load, because it avoids some extra effort to maintain the clustering of the materialized view the first time that the materialized view is loaded. For more details about clustering, see Understanding Snowflake Table Structures and Automatic Clustering. For more information about the costs of clustering materialized views, see Maintenance Costs for Materialized Views and Best Practices for Materialized Views. Materialized Views and Time Travel¶Currently, you cannot use Time Travel to query historical data for materialized views. However, note the following:
Best Practices for Materialized Views¶Best Practices for Creating Materialized Views¶
Best Practices for Maintaining Materialized Views¶
Best Practices for Clustering Materialized Views and their Base Tables¶
Examples¶This section contains additional examples of creating and using materialized views. For a simple, introductory example, see Basic Example: Creating a Materialized View (in this topic). Simple Materialized View¶This first example illustrates a simple materialized view and a simple query on the view.
Joining a Materialized View¶You can join a materialized view with a table or another view. This example builds on the previous example by creating an additional table, and then a non-materialized view that shows profits by joining the materialized view to a table:
Suspending Updates to a Materialized View¶The following example temporarily suspends the use (and maintenance) of the mv1 materialized view, and shows that queries on that view generate an error message while the materialized view is suspended:
Clustering a Materialized View¶This example creates a materialized view and then later clusters it:
Sharing a Materialized View¶You can use Snowflake’s data sharing feature to share a materialized view. For more information about data sharing, see Sharing Data Securely in Snowflake. Note Remember that maintaining materialized views will consume credits. When someone else creates a materialized view on your shared data, any changes to your shared data can cause charges to the people who have materialized views on your shared data. The larger the number of materialized views on a shared base table, the more important it is to update that base table efficiently to minimize the costs of maintaining materialized views. Troubleshooting¶Compilation Error:
Failure during expansion of view 'Possible Causes |