Snowflake offers three types of views: standard views, secure views, and materialized views. Each type of view has its own characteristics and is suited for different purposes.
Standard views are the most common type of view in Snowflake. They are simply a way to define a query and give it a name. When you query a standard view, Snowflake executes the underlying query and returns the results.
Secure views are similar to standard views, but they have an additional layer of security. The definition of a secure view is not visible to users who do not have the appropriate privileges. This can be useful for hiding sensitive data from unauthorised users.
Materialised views are a special type of view that is pre-computed. This means that the underlying query is executed once and the results are stored in a separate table. When you query a materialised view, Snowflake returns the results from the stored table, rather than executing the underlying query again. The materialised view is refreshed automatically whenever the underlying tables are updated.
Which type of view should you use?
The type of view you should use depends on your specific needs. If you need a simple way to define a query and give it a name, then you should use a standard view. If you need to hide sensitive data from unauthorized users, then you should use a secure view. If you need to improve performance by pre-computing the results of a query, then you should use a materialized view.
Here is a table that summarizes the key differences between standard, secure, and materialized views in Snowflake:
| Feature | Standard View | Secure View | Materialized View |
|---|---|---|---|
| Definition visible to all users | Yes | No | Yes |
| Results computed on demand | Yes | Yes | No |
| Results stored in a separate table | No | No | Yes |
| Can improve performance | No | No | Yes |
Examples of when to use each type of view:
- Standard views: You would use a standard view to define a query that is used by multiple users. For example, you could create a standard view that queries the customer table and returns the customer name, email address, and phone number.
- Secure views: You would use a secure view to hide sensitive data from unauthorized users. For example, you could create a secure view that queries the employee table and returns the employee name and salary.
- Materialized views: You would use a materialized view to improve performance by pre-computing the results of a query. For example, you could create a materialized view that queries the order history table and returns the total sales for each customer.
Creating these views is simple:
CREATE VIEW STANDARD_VIEW AS
SELECT * FROM KODEY.KODEY_CO_UK.ARTICLES
CREATE SECURE VIEW SECURE_VIEW AS
SELECT * FROM KODEY.KODEY_CO_UK.ARTICLES
CREATE MATERIALIZED VIEW MATERIALIZED_VIEW AS
SELECT * FROM KODEY.KODEY_CO_UK.ARTICLES
We can check when a materialised view was last updated using:
select *
from table(information_schema.materialized_view_refresh_history(
date_range_start=>'2023-05-22 19:00:00.000',
date_range_end=>'2023-06-22 20:00:00.000'));
And we can check how the view is defined by using:
SELECT get_ddl('view', 'STANDARD_VIEW');
I hope this blog post has helped you to understand the difference between standard, secure, and materialized views in Snowflake. If you have any questions, please feel free to leave a comment below.
