Views in Snowflake Explained
A view in Snowflake is defined as a virtual table that enables the result of a query to be accessed dynamically as if it were a table to tables. Unlike ordinary database tables in a relational database, a view does not form part of the physical schema.The query is specified in the CREATE VIEW statement
Views can provide the following benefits over tables:
- Views can join and simplify multiple tables into a single virtual table.
- Views can be used as aggregated tables, where the columns can provide calculated results such as (sum, average, etc.)
- Views can improve performance
- Views do not use too much memory; the database stores only the view definitions, not the data.
- Views can provide extra security.
- Views can be used for security purposes in order to give user access to only a particular subset of data, while denied access to the rest of the base table(s)
Snowflake supports the following types of views:
- Non-materialized views (usually simply referred to as “views”)
- Materialized views.
Non-materialized views: the term “view” generically refers to all types of views; however, the term is used here to refer specifically to non-materialized views. In non-materialized views results are not stored for future use. Performance is slower than with materialized views.
Any query expression that returns a valid result can be used to create a non-materialized view, such as:
- Selecting some (or all) columns in a table.
- Selecting a specific range of data in table columns.
- Joining data from two or more tables.
Materialized views: these views act more like a table. Materialized views give a static snapshot of the data. They provide a faster access than non-materialized views. However, they require memory allocation and maintenance. In addition, materialized views have some restrictions that non-materialized views do not have.
Both non-materialized and materialized views can be defined as secure in Snowflake. Secure views have advantages over standard views, including improved data privacy and data sharing; however, they also have some performance impacts to take into consideration.
A non-materialized view can be recursive (i.e. the view can refer to itself).
ALTER VIEW in Snowflake: the use of ALTER Script for views in Snowflake is very limited – you cannot change the view definitions and can only use it for the following purposes:
- Renaming a view.
- Converting to (or reverting from) a secure view.
- Adding, overwriting, removing a comment for a view.
Support for Snowflake Views in SqlDBM
SqlDBM enables users to create, export and import Snowflake views. You can also use Compare Revisions feature to identify differences between your views along with the Alter Script feature to incorporate changes.
Snowflake Views support in SqlDBM:
- Create views objects for your data model
- Import views through reverse engineering
- Compare revisions and generate alter script including views
Creating a view in SqlDBM: SqlDBM provides Snowflake customers with a SQL editor that allows them to create and modify views. In oder to access the editor please follow the following steps in SqlDBM:
(1) Click on the “Database Explorer” menu icon in the left menu bar
(2) Select “Views” in the list of objects
(3) Click “+ Create new” or select one of the existing views in the list.
Importing views through Reverse Engineering: You can also bring your existing views from Snowflake to SqlDBM. SqlDBM Reverse Engineering feature allows to bring views with two different ways:
(1) Directly connect to Snowflake environment in order to bring the objects
(2) Copy and paste the view definitions from Snowflake to SqlDBM
Compare revisions and Alter Script: You can identify differences between your views in SqlDBM revisions and use the Alter script to incorporate the changes. Follow the steps below to access these features:
(1) Click on the “Compare revisions” menu icon in the left menu bar
(2) Select two revisions between which you want to identify the changes in your view definition
(1) Click on the “Reverse Engineer – Generate SQL” menu icon in the left menu bar
(2) Click “Generate Alter SQL”