• Read
  • Discuss

A view allows the result of a query to be accessed as if it were a table. The query is specified in the CREATE VIEW statement.

Views serve a variety of purposes, including combining, segregating, and protecting data. For example, you can create separate views that meet the needs of different types of employees, such as doctors and accountants at a hospital:

CREATE VIEW doctor_view AS
    SELECT patient_ID, patient_name, diagnosis, treatment FROM hospital_table;

CREATE VIEW accountant_view AS
    SELECT patient_ID, patient_name, billing_address, cost FROM hospital_table;

A view can be used almost anywhere that a table can be used (joins, subqueries, etc.)

Types of Views in Snowflake

Snowflake supports two types of views:

  • Non-materialized views (usually simply referred to as “views”)
  • Materialized views.

Non-materialized Views in Snowflake

The term “view” generically refers to all types of views; however, the term is used here to refer specifically to non-materialized views.

A view is basically a named definition of a query. A non-materialized view’s results are created by executing the query at the time that the view is referenced in a query. The results are not stored for future use. Performance is slower than with materialized views. Non-materialized views are the most common type of view.

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

Although a materialized view is named as though it were a type of view, in many ways it behaves more like a table. A materialized view’s results are stored, almost as though the results were a table. This allows faster access, but requires storage space and active maintenance, both of which incur additional costs.

In addition, materialized views have some restrictions that non-materialized views do not have.

Secure Views

Both non-materialized and materialized views can be defined as secure. 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.

Leave a Reply

Leave a Reply

Scroll to Top