Views In Snowflake – Protect the data

Understanding different types of views in Snowflake

What is a View

Views in snowflake are basically virtual tables and are result of a query acting just like a table.

Views provides an abstraction over a query and are able to hide the actual data providing limited or only required information access, hiding complex logic etc.

Types of Views

Views in snowflake are majorly categorized in two types along with recursive and secure views:

1. Non-materialized Views: These are often simply referred to as “views”. They are virtual representations of data based on a defined query. Non-materialized views do not store data themselves; instead, they execute the underlying query each time they are accessed.
2. Materialized Views: Unlike non-materialized views, materialized views store the results of the underlying query, essentially pre-computing and persisting the data. This enables faster query performance since the results are readily available without the need to recompute them each time the view is queried. Materialized views support clustering and caching.
3. Recursive Non-Materialized View: A view can be defined within a view creating a recursive view which is eventually non materialized view.
4. Secure views, whether non-materialized or materialized, offer enhanced data privacy and sharing capabilities compared to standard views. However, it’s important to note that they may also introduce certain performance considerations. Secure view definition is not visible to others who are not granted access privileges on that object.
   • The advantages of secure views include:
     • Improved Data Privacy: Secure views provide a layer of abstraction over the underlying data, enabling finer control over which columns or rows are accessible to different users or roles. This helps enforce data security and privacy policies.
     • Enhanced Data Sharing: Secure views facilitate controlled data sharing by restricting access to sensitive information and presenting a tailored view of the data to different users or groups.

When to Create a Materialized View

1. Limited Row or Column Count: When the query results constitute a small subset of rows or columns relative to the base table, materialized views can significantly improve performance by pre-computing and storing these results.
2. Complex Processing: Queries involving significant processing tasks, such as analyzing semi-structured data or calculating lengthy aggregates, can benefit from materialized views. By pre-computing and persisting the results, materialized views streamline query execution.
3. Queries on External Tables: Materialized views can enhance performance when querying external tables, which may exhibit slower performance compared to native database tables. Storing pre-computed results in materialized views mitigates the latency associated with accessing external data sources.
4. Infrequent Changes to Base Table: If the base table of the view experiences infrequent changes, materialized views offer a performance advantage. Since the results are pre-computed and stored, there’s no need for frequent recalculations, resulting in faster query response times.
5. Reporting layer Views: Aggregated views with large volume data aggregation that are required for reporting in data warehouses are best examples of materialized views.

Below image highlights syntax for creating views in snowflake.

Syntax for Views in Snowflake