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:
- Name of the view. This needs to be unique for the schema you are operating in.
- 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 bystatement 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
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 existerror 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:
- 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
- You save time by storing frequently run queries
- 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