🪟 Views in Snowflake: Overview & Types
In Snowflake, a View is a virtual table based on a stored SQL query. Views do not store data themselves but provide a way to simplify, secure, and reuse query logic.
What is a View?​
- A saved SQL query treated like a table or They simply run a query every time you access the view.
- Shows live data from underlying tables or views.
- Simplifies complex queries and centralizes logic.
- Changes in underlying data reflect immediately in the view.
Why Do We Need Views When We Already Have Tables? (Professional Explanation)​
Even though tables store the actual data, views play a critical role in data management, governance, performance optimization, and user accessibility. They allow organizations to present the right data to the right audience in a structured and controlled manner, without duplicating information.
1. Data Security and Access Control​
A view allows you to expose only specific columns or rows of a table.
Example:
The employees table may contain:
- salary
- phone number
- address
- PAN number
- personal details
But a view for the analytics team can expose only:
emp_id, emp_name, department
This ensures data privacy, compliance, and safe access without altering the underlying table.
2. Simplified Data Consumption for End Users​
Tables often contain many columns, raw fields, technical attributes, or system metadata.
Views allow us to create business-friendly, simplified, and structured representations without modifying the base data model.
Example: A complex table with 50+ columns can be presented as a simple view with 8–10 relevant business fields.
3. Centralized Business Logic (Single Source of Truth)​
Without views, different teams may write different versions of the same logic:
- Calculations
- Filters
- Joins
- Derived fields
- Masks
A view provides one consistent version of the logic.
Benefit: Every dashboard and report uses the same trusted calculation, reducing errors and inconsistencies.
4. Hiding Internal Data Structures​
Views allow you to hide complexity from users:
- multiple joins
- transformations
- derived columns
- business rules
This protects the internal architecture and keeps the data model flexible.
If tables ever change, the view can be updated — users continue querying the same view without breaking anything.
5. Performance Optimization (Materialized Views)​
Materialized views store precomputed results and refresh automatically. This significantly improves performance for:
- dashboards
- aggregations
- frequently accessed metrics
- BI workloads
Users get instant results without querying large raw tables repeatedly.
6. Enables Controlled Data Sharing​
In Snowflake, Secure Views are essential for:
- Sharing data with external partners
- Cross-account data sharing
- Preventing exposure of underlying table structure or SQL logic
They provide controlled, contract-safe, audit-safe access.
7. No Data Duplication​
Instead of creating multiple tables for different teams, views allow:
- multiple perspectives
- multiple versions
- multiple filtered subsets
All without copying data.
This reduces:
- storage cost
- maintenance overhead
- data synchronization issues
Professional Summary
Views are needed because they enable:
- Security – Expose only what each user needs
- Abstraction – Hide complexity
- Consistency – Centralize business logic
- Performance – Precomputed results via materialized views
- Flexibility – Users see stable interfaces even when tables change
- Compliance – Protect sensitive or regulated data
- Efficiency – Avoid data duplication
Types of Views in Snowflake​
1. Standard View​
- Simple stored query.
- Always queries base tables live.
- Does not store data.
- Used for abstraction and simplifying query logic.
2. Materialized View​
- Stores precomputed query results physically.
- Improves performance by avoiding re-execution.
- Auto-refreshes when base tables change.
- Consumes storage.
- Best for frequently accessed aggregated/joined data.
3. Secure View​
- A type of Standard View with enhanced security.
- Hides the view’s underlying SQL definition from users without privileges.
- Prevents access to the view’s logic for unauthorized users.
- Useful for enforcing strict data access control.
- Supports row-level security by limiting exposed data.
Basic Metadata for Views​
- INFORMATION_SCHEMA.VIEWS
- SHOW VIEWS
| Metadata Property | Description |
|---|---|
| Name | View identifier |
| Owner | User who created the view |
| Created On | Timestamp when the view was created |
| Comment | Optional descriptive text |
| Base Objects | Tables/views the view depends on |
| Is Materialized? | Indicates if view is materialized |
| Is Secure? | Indicates if the view is a secure view |
Benefits of Using Views​
- Simplify complex queries for users.
- Provide data abstraction and centralized logic.
- Control data access with secure views.
- Improve query performance with materialized views.
- Reuse SQL logic easily.
Creating Views: Examples​
Standard View​
CREATE OR REPLACE VIEW sales_summary AS
SELECT
region,
product_category,
SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region, product_category;
Secure View​
CREATE OR REPLACE SECURE VIEW confidential_sales_summary AS
SELECT
region,
product_category,
SUM(sales_amount) AS total_sales
FROM sales
WHERE access_level = 'confidential'
GROUP BY region, product_category;
Materialized View​
CREATE OR REPLACE MATERIALIZED VIEW mat_sales_summary AS
SELECT
region,
product_category,
SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region, product_category;
Summary Comparison Table​
| Aspect | Standard View | Materialized View | Secure View |
|---|---|---|---|
| Stores Data | No | Yes | No |
| Query Performance | Depends on base tables | Faster (precomputed data) | Depends on base tables |
| Storage Cost | None | Uses storage | None |
| Security | View SQL visible | View SQL visible | View SQL hidden from users |
| Auto Refresh | N/A | Yes | N/A |