Non-materialized, materialized, and secure views - Snowflake Tutorial

- [Instructor] If you analyze data using the same complex query over and over again, it might be useful for you to define a view. Views in Snowflake are defined by a query, and views allow users to access the result of that query as though the result were in a table. Views can thus be thought of as a named reference to a query, and other queries can reference views exactly like they do tables. Just like you use a table name within a query, you can use a view name instead. Views are very useful because they give you access to different cuts of your data. Views allow you to combine, segregate, or protect data. For example, instead of giving users access to all of the records in a table, you might want to give them access to only a subset of records that you define using a view. Snowflake supports two types of views. We have non-materialized views, or just regular views, and we have materialized views. Let's understand how these are different. We look at the non-materialized view first. A non-materialized view is defined using a query and is just a named definition of a query. In a non-materialized view, the result of running the query associated with the view is not stored anywhere. Each time the view is referenced in another query, the actual query associated with the view is run and the results generated. The performance of a non-materialized view will often be slower than materialized views. A materialized view is defined exactly like a regular view, but it behaves like a table. A materialized view is also defined using a query. The results of the materialized view are stored in a similar fashion to how our table's contents are stored. So it's in assistance storage somewhere. The implication of this is that you get faster access to the results of the view. So you get faster access to the underlying data, but you have to pay for additional storage costs because the materialized view is actually taking up extra storage, and there are some additional restrictions on materialized views, querying them, working with them, which are beyond the scope of this particular course. Now, if you want Snowflake to provide additional security to your views, you can define both materialized and non-materialized views as secure views. Secure views allow for additional data privacy for the records associated with that view. And because of this, secure views may be less performant than other views because they do not allow Snowflake to perform certain kinds of query optimizations. Views are useful because they make your code more modular and easier to read and maintain. Rather than repeating the same subquery over and over again, you can just define a view for that subquery. Views also allow you to grant access to a subset of a table to your users. Rather than giving them access to all records in a table, you can just give them access to the records that they really need. And if you choose to use materialized views, these will significantly improve the performance of your queries because they store the subset of data that you're interested in, similar to how table contents are stored. Views in Snowflake have certain limitations. The definition of a view cannot be updated. You have to replace or recreate the view from scratch. If you make changes to the underlying table from which a view is generated, changes to the table structure are not propagated to the views. And finally, views in Snowflake are read-only. You cannot use a view to update the underlying data.