📘 An Article on Different Types of Views in Snowflake

In Snowflake Inc., views are virtual tables that help simplify queries, improve security, and enhance performance. Views do not store data themselves (except materialized views). Instead, they dynamically fetch data from underlying tables.

Understanding different types of views is essential for building scalable, secure, and efficient data systems.

🔹 What Is a View?

A view is a saved SQL query that behaves like a table.

Instead of writing complex queries repeatedly, you can:

• Save them as views
• Reuse them easily
• Apply business logic centrally

Example:

CREATE VIEW v_sales AS
SELECT id, product, amount
FROM sales
WHERE amount > 1000;
        

🔹 Types of Views in Snowflake

Snowflake mainly supports three types of views:

1. Standard View
2. Secure View
3. Materialized View

1️⃣ Standard View (Regular View)

Description

A Standard View is the most commonly used view. It stores only the SQL definition and executes the query every time it is accessed.

Characteristics

• No data is stored
• Always shows latest data
• Uses base table permissions
• Lightweight and flexible

Example

CREATE VIEW v_employee AS
SELECT emp_id, name, department
FROM employees;
        

Use Cases

• Reporting dashboards
• Simplifying joins
• Reusable queries
• Ad-hoc analysis

Limitations

• Performance depends on base tables
• Slower for large datasets

2️⃣ Secure View

Description

A Secure View hides the underlying SQL logic and base table structure. Users cannot see how the data is generated.

It is mainly used for data security and data sharing.

Characteristics

• Query definition is hidden
• Prevents data leakage
• Supports secure data sharing
• Protects business logic

Example

CREATE SECURE VIEW v_secure_salary AS
SELECT emp_id, salary
FROM payroll;
        

Use Cases

• Sharing data with partners
• Hiding business rules
• Protecting sensitive data
• External data sharing

Limitations

• Slight performance overhead
• Query plans are not visible

3️⃣ Materialized View

Description

A Materialized View stores precomputed query results physically. Snowflake automatically refreshes it when base data changes.

It improves performance for heavy and repetitive queries.

Characteristics

• Stores actual data
• Automatic refresh
• Faster execution
• Uses storage space

Example

CREATE MATERIALIZED VIEW mv_sales_summary AS
SELECT product, SUM(amount) AS total_sales
FROM sales
GROUP BY product;
        

Use Cases

• Aggregation queries
• BI dashboards
• Frequent reports
• Large fact tables

Limitations

• Consumes storage
• Maintenance overhead
• Limited query support

✅ 1️⃣ Card-Style Comparison (Modern Look)

📌 Standard View

• 📂 Stores Data: No
• ⚡ Performance: Normal
• 🔐 Security: Basic
• 💾 Storage Cost: No
• 🔄 Auto Refresh: No

🔒 Secure View

• 📂 Stores Data: No
• ⚡ Performance: Normal
• 🔐 Security: High
• 💾 Storage Cost: No
• 🔄 Auto Refresh: No

🚀 Materialized View

• 📂 Stores Data: Yes
• ⚡ Performance: High
• 🔐 Security: Medium
• 💾 Storage Cost: Yes
• 🔄 Auto Refresh: Yes

🔹 Which View Should You Use?

Use Standard View When:

• You need flexibility
• Data changes frequently
• Performance is acceptable

Use Secure View When:

• Sharing data externally
• Protecting business logic
• Handling sensitive information

Use Materialized View When:

• Queries are slow
• Heavy aggregations are needed
• Same queries run frequently

🔹 Best Practices

• Use Standard Views for most reporting
• Use Secure Views for external users
• Use Materialized Views for performance optimization
• Avoid too many nested views
• Monitor storage usage regularly

🔹 Real-World Example

In a sales system:

• Standard View → Used by internal analysts
• Secure View → Used by partners
• Materialized View → Used for BI dashboards

This layered approach improves security, performance, and maintainability.