Imagine that your company has completed the migration of its data warehouse to the Snowflake Data Cloud and all your data is stored in Snowflake tables. The data analysts, data scientists, and business intelligence employees want to begin taking advantage of your new data platform to find insights and build reports.Â
However, employees have said that some of the tables are a bit challenging to work with. Examples include tables having:
- Ambiguous column names
- Rows and columns that are unnecessary for downstream workflows
- Long query time
Are there Snowflake database objects that can be used to make your employees’ lives easier?Â
The answer is yes!Â
Snowflake views and materialized views have functionalities that can alleviate these workflow challenges.Â
In this post, we’ll explain the core differences between Snowflake views and materialized views and situations that present an opportunity for Snowflake views and materialized views to be used instead of tables.Â
What is the Difference Between View and Materialized View in Snowflake?
Both views and materialized views are Snowflake database objects which return the results of a defined query, and both objects can be used like tables in most cases. A main advantage to using views and materialized views is that workflows can be greatly simplified for downstream users as query logic can be hidden in the view definition.
A materialized view differs from a regular view by its ability to pre-compute the dataset derived from the query specified in its definition. Every time the base table referenced in the materialized view’s query definition is updated, the output dataset will be re-computed.
Because the query output for a materialized view is pre-computed, querying is much faster for a materialized view than it is for a regular view.
One main restriction to be aware of with Snowflake materialized views is that the materialized view’s definition can only query from one table and self joins with the same table are not allowed. Other materialized view restrictions can be found in Snowflake’s documentation.
If view definition privacy or data security are a concern, both views and materialized views in Snowflake can be created or altered into secure views and secure materialized views. When a view is secure, the view definition will only be visible to the object’s owner and information of data in the view’s underlying tables will not be exposed.
While data privacy and security are gained with secure views, be aware that query performance will worsen. This is because Snowflake’s internal view optimizer will bypass certain optimizations to avoid exposing the base table’s data. A worsened query performance may only be observed with secure views as secure materialized views are already pre-computed.
Cost Considerations for Views and Materialized Views in Snowflake
There are also cost considerations that organizations need to be aware of when using materialized views. While views are only charged a compute cost when queried, materialized views are charged a compute cost each time the base table in the view definition is updated as well as a storage cost for storing the pre-computed output dataset.Â
A materialized view that uses a table that is updated in batch daily will incur far less compute costs than a materialized view that uses a table that is constantly updated with streaming inserts.
Example Use Cases for Views
Views are great for lightweight transformations of your underlying tables that are not resource-intensive to run.
Let’s say your customer’s table was loaded based on a source file with ambiguous column names (ex:// column_1, column_2, etc.) and your data analysts have a difficult time figuring out which columns they need to reference in their queries.
In this situation, it would be useful to create a view that quickly renames the columns with meaningful names. Your data analysts can reference the newly created view instead and any confusion derived from ambiguous column names.Â
Perhaps you have an employees’ table that contains information on all your employees that your operations team uses as a reference to send a weekly newsletter announcing new hires.Â
For this situation, your end-users in operations only need the name, role, and start date of employees that have been onboarded in the past week. Instead of having the operations team query from the employee table and manually including the desired columns and start date filter, you can create a view with the desired query definition.Â
With the view created, your operations team only needs to SELECT from the view to retrieve the new employee information they need for their weekly newsletter.
Example Use Case for Materialized Views
While views are great for lightweight transformations, materialized views are great for results that are often queried and resource-intensive. Bonus points if the underlying table is not updated frequently.
Imagine your organization has a large, 15 million row customer orders table that business intelligence developers use to build a report that processing and outstanding customer orders.Â
Managers run this customer orders report multiple times a day. However, the report takes minutes to run due to the size of table and aggregation complexity of the underlying queries. The minutes your managers spend waiting for the report to run quickly add up whereas you would prefer your managers to spend more time understanding order fulfillment progress.Â
In this situation, creating a materialized view for your preferred business intelligence report builder would be a great solution.
The main syntax difference between creating a view and a materialized view is the key word ‘MATERIALIZED’.
The same report will run much faster when referencing the materialized view instead of querying from the raw table and your managers get time back in their day that they can spend on more valuable areas.Â
Example Use Case for Secure Views
The same principles used when deciding between a view and materialized view also apply when deciding between a secure view or a secure materialized view.Â
Imagine your company’s executive leadership has completed end-of-year bonus reviews. The leadership team would like to keep the bonus calculation confidential while at the same time sharing the bonus payout information with the payroll team for them to update records in the appropriate areas.
In this instance, the leadership team, or a trusted data engineer, can create a secure view that contains the employee bonus calculation. We are choosing a secure view in this example, instead of a secure materialized view, because the bonus calculation is a lightweight transformation.
By creating a secure view, the payroll team will be able to use the view to see each employee and their bonus amount.Â
But because the payroll team are not owners of the secure view, they will not be able to use commands such as SHOW VIEWS to expose the view definition, and in turn, the bonus calculation. The view definition would appear in the [text] column with the correct perm. This is unlike regular views in Snowflake where if the user’s role has usages grants on the database and schema and select grants on the view, they will have access to the view definition.
Closing
Snowflake views, materialized views, and secure views are a viable and easy solution to implement in order to speed up or make downstream workflows less complex in many use cases, beyond just the four I described in this blog.
Are you interested in using Snowflake views, materialized views, and secure views or learning more about Snowflake? As the Snowflake 2022 Partner of the Year, our Snowflake experts are passionate about the platform and happy to provide Snowflake guidance around costs, timeframe, logistics, and anything else under the sun.
FAQs
Using secure views or secure materialized views is one of the options in securing confidential information in Snowflake. For more examples of using secure views or to explore other options to secure information, consider reading our other blog: How Do You Secure PII Data in Snowflake.
Views are available in any Snowflake version. Materialized views are only available in Snowflake Enterprise versions and above.Â
Snowflake materialized views are considered zero maintenance and use compute costs to re-compute its output dataset anytime the base table is updated. This process runs in the background and cannot be controlled by the user. To understand the costs associated with your materialized views, use can use either the Snowflake web UI under the Admin >> Usage tab or the CREDITS_USED column in the table function MATERIALIZED_VIEW_REFRESH_HISTORY found in the Snowflake Information schema or MATERIALIZED_VIEW_REFRESH_HISTORY View view in Account Usage.
