Back to articles list
- 4 minutes read

How to Model Snowflake Materialized Views in Vertabelo

Cloud technologies are becoming more and more popular. Recently, Vertabelo added support for the Snowflake database. An additional feature, much awaited by our users, was support for materialized views in Snowflake. We are happy to announce that you can now model materialized views in a Snowflake database using Vertabelo.

What Is a Materialized View?

Materialized views are different from simple views. While simple views allow us to save complicated queries for future use, materialized views store a copy of the query results. They are not always perfectly up-to-date, but can be very useful if the results of complicated queries must be obtained very quickly.

In this article, we will show you how to model Snowflake materialized views in Vertabelo.

Modeling Snowflake Materialized Views in Vertabelo

1. Add a Materialized View

To add a view, click the Add materialized view icon.

How to Model Snowflake Materialized Views in Vertabelo

Alternatively, in Model Structure (in the left panel), right-click on Materialized views and choose Add materialized view.

How to Model Snowflake Materialized Views in Vertabelo

2. Change the View Name

If you don’t change the default view name, the following warning will pop up:

How to Model Snowflake Materialized Views in Vertabelo

To fix that, click on the materialized view. In the right panel, under General, type the name of your choice.

How to Model Snowflake Materialized Views in Vertabelo

You can also add a comment to the view if you’d like.

3. Add the Query

The next step is to add a query. Use the SQL query field in the Materialized View Properties panel.

How to Model Snowflake Materialized Views in Vertabelo

Next, scroll down and click Update columns.

How to Model Snowflake Materialized Views in Vertabelo

A new window will appear. Verify the columns that will be generated based on the SQL query you provided. Then, click Update columns.

How to Model Snowflake Materialized Views in Vertabelo

The view’s columns should be updated:

How to Model Snowflake Materialized Views in Vertabelo

Note that the materialized view must have at least one column. Otherwise, the following error will appear:

How to Model Snowflake Materialized Views in Vertabelo

How to Edit Columns

If the automatically generated columns are incorrect or if they need to be changed, you can always modify them manually in the Columns section of the right panel. Here you can change column names and types, add new columns, or delete existing columns.

How to Model Snowflake Materialized Views in Vertabelo

Materialized View Options

Additional SQL Scripts

To configure additional SQL scripts, select the materialized view. In the right panel, scroll down to the Additional SQL scripts section. You can add scripts that will be run before and after the materialized view is created. They can perform actions that cannot be modelled directly in Vertabelo, such as defining functions or stored procedures, adding users, or setting permissions for objects like views or tables.

How to Model Snowflake Materialized Views in Vertabelo

Additional Properties

To see additional view properties, select the view. In the right panel, scroll down to the Additional properties section.

How to Model Snowflake Materialized Views in Vertabelo

Click Set to add property.

How to Model Snowflake Materialized Views in Vertabelo

Click Unset to remove it.

How to Model Snowflake Materialized Views in Vertabelo

Let’s discuss the role of each materialized view property in Snowflake:

Schema – This is the name of the schema in which the table will be placed.

Secure – If this is set to yes, the view is marked as secure. This means that the underlying tables, internal structural details, and the data in the view’s base tables are hidden and can only be accessed by authorized users.

Cluster by – This property is a comma-separated list of columns or expressions that determine how to cluster the materialized view. When the materialized view is clustered, the data inserted is sorted based on the clustering columns. This can improve performance when the data is queried, as not all the rows will be scanned.

Format

To change the view’s appearance, select the right panel and scroll down to the Format section. To change the table background color, click on the color field under Fill color. Select the desired color in the color picker. You can also choose the table’s line color and set its size to fixed.

How to Model Snowflake Materialized Views in Vertabelo

Learn More About Materialized Views in Vertabelo

In this article, we discussed what materialized views are and how to model them in Vertabelo. You can learn more about materialized views here. To see what new features were introduced in Vertabelo this year, check out  2020 Was Vertabelo Database Modeler’s Year. It’ll Get Even Better in 2021.

go to top