If you’ve been learning databases and modern data warehouses like Snowflake, you’ve probably heard the word "View".It sounds technical, but it’s actually one of the coolest and easiest tools.
Think of a View as a "Saved Search" or a "Virtual Window" into your data existed in database. It looks like a table, acts like a table, but it’s actually just a saved recipe for a query.
So what exactly is a View?
Imagine you have a giant, messy spreadsheet (a Table) with 100 columns and millions of rows.
In most day, you only need to see three columns: Customer Name, Item Purchased, and Price.
Instead of writing a long code to filter that data every single morning, you create a View.
The View is just a "shortcut" that shows you exactly what you want to see, instantly. And the Table is where the actual data lives.
Benefits of "Views"
- It’s a Time-Saver. You write the complex logic in SQL Worksheet once, name it (e.g., Daily_Sales_View), and then you just call that name whenever you need it.
- It’s compact. You can hide the "messy" parts of your data and only show the clean, useful bits.
- It’s Secure: You can let a co-worker or Business Analyst see a View of the data without giving them access to the other data in the main table (sensitive information like credit card numbers or addresses).
Let's talk about "View" in Snowflake
Snowflake has a few different types, but here are the three you'll hear about most.
Standard Views
The most common. They don't take up any extra space. They just run your query the moment you ask for it.
Secure Views
These are like standard views but with a "privacy shield." They stop people from seeing the secret code inside the view. You can learn more about Secure Views on Snowflake Documentation.
Materialized Views
These are the "pro" version of views. They actually save a copy of the results to make things super fast, but they cost a little extra money because they use storage and compute.
In summary.
Limitations
Snowflake views have limitations regarding performance, data manipulation operations, and structure. Standard views are not pre-computed (can be slow), while Materialized Views have strict limitations, including single-table restriction, no joins, no window functions, and no non-deterministic functions, often leading to higher storage and compute costs.
Key limitations of Snowflake views include-
Standard Views
- Cannot be updated via ALTER VIEW , the entire table must be replaced.
- They do not cache data, meaning they compute results on every query.
Materialized Views
- Cannot join multiple tables, cannot query other views, cannot use ORDER BY, LIMIT, HAVING, or window functions.
- Require background compute for maintenance, making them unsuitable for frequently updated data.
- You cannot run INSERT, UPDATE, or DELETE directly on materialized views.
- Non-secure views may not be directly shared; Secure Views are required for sharing, which can limit query performance optimization.
Therefore, for complex, high-latency queries, Dynamic Tables are often recommended over Materialized Views to overcome these constraints. In professional environments, to overcome these trade-offs, dbt is widely used along with Snowflake. In another words, dbt can manually managed Snowflake views by treating them as "models".
How to Create VIEW
It is simple and straightforward to create Standard "View". For this, you use CREATE VIEW or CREATE OR REPLACE VIEW.
CREATE OR REPLACE VIEW My_First_View AS
SELECT name, email
FROM customer_table
WHERE city = 'Manila';
To make it secure, you just add the word SECURE right before VIEW.
CREATE OR REPLACE SECURE VIEW My_First_Secure_View AS
SELECT name, email
FROM customer_table
WHERE city = 'Manila';
To make it materialized, you add the word MATERIALIZED.
CREATE OR REPLACE MATERIALIZED VIEW My_First_Materialized_View AS
SELECT name, email
FROM customer_table
WHERE city = 'Manila';
Ref: Snowflake Docs
Top comments (0)