View, secure view, materialized view - The Information Lab Nederland

In this blog post I will explain what a view is and how to create one, then I will discuss materialized views and secure views. While views and materialized views are common database concepts, the section concerning secure views is specific to Snowflake.

Views

In order to understand what a view is, it might be useful to underline similarities and differences between views and tables.

A database table is a collection of rows and columns storing related data. Tables are not physical objects, but a logical representation of underlying data. Tables are database objects, and they are included in database schemas.

A view, on the other hand, is a database object that contains the result of a query and is not included in database schemas. Views are often referred to as a “virtual tables”, in that they are structured in a tabular format and can be called and queried just like tables.

What are views useful for?

If you are looking to increase security and simplicity of your database, views can be a good option. As far as security is concerned, views can help you increase data security by while hiding sensitive information that are present in the original table.

Further, the query underlying a view takes care of joins and unions and allows users to query the table directly, increasing simplicity. This is especially handy for data that frequently needs to be queried together but lies in different tables.

How do I create a view?

In order to create a views, start out with a CREATE VIEW statement and then write the query.

Here is an example:

CREATE VIEW insert_view_name AS
SELECT id, date, user_name, company
FROM customers
WHERE id > 4000;

Materialized views

Materialized views can be defined as the pre-computed result of a query saved as an object in the database. Unlike regular views, until a materialized views is refreshed it will not reflect changes in the underlying tables.

What are materialized views useful for?

Do you want to increase performance? Then you might choose materialized views over regular views. Before creating a materialized view, however, make sure that all of the three conditions below are true:

• underlying data doesn’t change often, less often than the view is used;
• The view is used often;
• The underlying query is highly consuming.

How do I create a materialized view?

Use the statement “Create materialized view”:

CREATE MATERIALIZED VIEW insert_view_name AS
SELECT id, date, user_name, company
FROM customers
WHERE id > 4000;

Secure views

In Snowflake, some of the optimization processes applied to views might reveal data that was not included in the view. Secure views are views that are designed to work around any optimization process which might reveal hidden data.

What are secure views useful for?

If the purpose of a view is to make sure to hide certain data from the user, secure views are the best solution within Snowflake. If, on the other hand, a view was created in order to simplify queries, then secure views are not necessary.

How do I create a secure view?

Both views and materialized views can be secure views.

Here is how to create a secure view:

CREATE SECURE VIEW insert_view_name AS
SELECT id, date, user_name, company
FROM customers
WHERE id > 4000;

Here is how to create a secure materialized view:

CREATE SECURE MATERIALIZED VIEW insert_view_name AS
SELECT id, date, user_name, company
FROM customers
WHERE id > 4000;

Do you want to learn more about Tableau, Alteryx or Snowflake? Do you want to use these tools to their full potential? Check out our trainings and consultancy services!