Snowflake CREATE VIEW, what you need to know

A view is a SQL query that is stored for repeat usage, and is simple to implement in Snowflake. In this post I will walk through the code required to create a view, the benefits, and common errors that users experience.

Snowflake create view

To run create view in Snowflake you only need two required parameters:

1. Name of the view. This needs to be unique for the schema you are operating in.
2. Select statement. This needs to select data from one or more tables or views.

And then use the following syntax.

SQL syntax for creating a view

The basic syntax for creating a view is:

create view <view_name> as <select_statement>

create view cheap_products as 
select * from products where price < 10

This is using the required fields that we discussed previously, but we can add more complexity to this if we want. We can add the commonly used clauses or replace and if not exists as well:

create or replace view <view_name> as <select_statement>

create view if not exists <view_name> as <select_statement>

One more common addition to a create view statement is to add a comment. This is helpful knowledge for users and saves you time explaining the key aspects of your view:

create or replace view <view_name>
comment = '<comment_string>' 
as <select_statement>

Helpful tips when creating a view in Snowflake

• It is recommended to not add an order by statement to your view as this incurs unnecessary compute cost
• To see the definition of your view after it has been created, you need to run select get_ddl('view',<view_name>)

Common errors when creating views

• View definitions are not updated when the underlying tables are changed, so if you rename a column which is referenced in your view then an error will be returned
• If you reference a table in your view which is subsequently dropped then your view will return the object does not exist error when queried

Benefits of using views

We have seen that views are incredibly helpful tools to have at your disposal when working with a database. So to conclude this post I will summarise the key benefits you should remember:

1. They are simple to implement and allow to serve data to an end user faster than it would take to create a new tabular data model
2. You save time by storing frequently run queries
3. They promote good data storage practices by making sure that the source data is only held in a few tables with views built on top, instead of copying the data to a new table

Related articles

Snowflake materialized view
View vs materialized view
View vs table
Create table in Snowflake
Create or replace table in Snowflake
Create table as select in Snowflake

References

Snowflake create view documentation