Skip to main content

🪟 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:

  1. Security – Expose only what each user needs
  2. Abstraction – Hide complexity
  3. Consistency – Centralize business logic
  4. Performance – Precomputed results via materialized views
  5. Flexibility – Users see stable interfaces even when tables change
  6. Compliance – Protect sensitive or regulated data
  7. 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 PropertyDescription
NameView identifier
OwnerUser who created the view
Created OnTimestamp when the view was created
CommentOptional descriptive text
Base ObjectsTables/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​

AspectStandard ViewMaterialized ViewSecure View
Stores DataNoYesNo
Query PerformanceDepends on base tablesFaster (precomputed data)Depends on base tables
Storage CostNoneUses storageNone
SecurityView SQL visibleView SQL visibleView SQL hidden from users
Auto RefreshN/AYesN/A