How to Use Snowflake Create View?

Your data team runs the same 47-line SQL query across three dashboards, two notebooks, and a scheduled report. When finance changes how "active customer" is defined, someone has to hunt through all of them. One gets missed, numbers don't match, and now you're in a meeting explaining why the board deck shows different revenue than the investor update.

Snowflake views solve this by storing query logic in a single, reusable object. Instead of copying SQL everywhere, downstream consumers reference the view and get consistent results. This guide covers how to create standard views, secure views, and materialized views with practical examples for each scenario.

TL;DR: Snowflake Create View at a Glance

• Snowflake views centralize SQL logic so teams stop duplicating complex queries across dashboards, notebooks, and reports.
  
• Snowflake supports three view types:
  
  • Standard views for simplifying joins and standardizing business logic
  • Secure views for protecting sensitive logic or data when sharing across accounts
  • Materialized views for precomputing expensive aggregations and improving query performance
    
• CREATE VIEW and CREATE OR REPLACE VIEW let you safely deploy and update views without breaking downstream access when paired with COPY GRANTS.
  
• Materialized views improve performance but add storage costs and have functional limitations, so use them selectively.
  
• A clean Snowflake setup typically lands raw data first, then applies views for transformation and consumption. Tools like Airbyte handle ingestion, so views can focus on logic rather than data movement.
  

What Is a Snowflake View?

A view is a stored SELECT statement that acts as a virtual table. When you query a view, Snowflake executes the underlying SQL and returns results as if you were querying a table directly.

The key distinction: views store logic, not data. Every time you SELECT from a view, Snowflake runs the query fresh against the base tables. This means views always reflect current data without any refresh process.

Here's the basic syntax:

CREATE VIEW customer_orders AS
SELECT 
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS total_orders,
    SUM(o.order_amount) AS lifetime_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

Once created, anyone can query customer_orders like a table. The join logic stays in one place.

What Are the Different Types of Snowflake Views?

Snowflake offers three view types, each designed for different requirements around security, performance, and data freshness.

1. Standard Views

Standard views are the default. They store your SELECT statement and execute it at query time with full optimizer access. Use standard views when you need to simplify complex joins, restrict column access for specific roles, or standardize business logic across teams.

CREATE VIEW v_monthly_revenue AS
SELECT 
    DATE_TRUNC('month', order_date) AS revenue_month,
    product_category,
    SUM(order_amount) AS monthly_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE order_status = 'completed'
GROUP BY 1, 2;

Standard views work for most internal use cases where the underlying table structure isn't sensitive.

2. Secure Views

Secure views hide the view definition and prevent query optimization from leaking information about underlying data. The SECURE keyword tells Snowflake to restrict how much the optimizer can reveal.

CREATE SECURE VIEW v_customer_pii AS
SELECT 
    customer_id,
    CASE 
        WHEN CURRENT_ROLE() IN ('ADMIN', 'COMPLIANCE') THEN email
        ELSE SHA2(email)
    END AS email,
    CASE 
        WHEN CURRENT_ROLE() IN ('ADMIN', 'COMPLIANCE') THEN phone
        ELSE '***-***-' || RIGHT(phone, 4)
    END AS phone
FROM customers;

Use secure views when sharing data with external Snowflake accounts or when the view logic itself contains sensitive business rules. The trade-off is that Snowflake's optimizer has less flexibility, which can impact query performance.

3. Materialized Views

Materialized views precompute and store results. Unlike standard views, they don't run the query every time. Instead, they serve cached data that Snowflake refreshes automatically when base tables change.

CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT 
    order_date,
    region,
    SUM(order_amount) AS daily_revenue,
    COUNT(*) AS order_count
FROM orders
GROUP BY order_date, region;

Materialized views make sense for expensive aggregations that get queried frequently. However, they come with limitations: no joins across multiple tables, restricted function support, and additional storage costs. They also require the Enterprise edition.

How Do You Create a View in Snowflake?

The CREATE VIEW statement supports several optional clauses that control behavior when views already exist, preserve permissions, and add documentation.

1. Basic CREATE VIEW Syntax

Here's the full syntax with common options:

CREATE OR REPLACE VIEW schema_name.view_name (
    column_alias_1,
    column_alias_2,
    column_alias_3
)
COPY GRANTS
COMMENT = 'Description of what this view does'
AS
SELECT 
    column_a,
    column_b,
    column_c
FROM source_table
WHERE conditions;

The OR REPLACE clause overwrites any existing view with the same name. COPY GRANTS preserves permissions from the previous version, which prevents access disruptions during updates.

2. Specifying Column Names and Comments

Explicit column names make views self-documenting and protect downstream queries from breaking if source column names change:

CREATE VIEW v_order_summary (
    order_identifier,
    customer_identifier, 
    order_total,
    order_status
)
COMMENT = 'Simplified order data for dashboard consumption. Owner: Analytics team.'
AS
SELECT 
    order_id,
    customer_id,
    amount + tax + shipping AS total,
    status
FROM orders;

Column-level comments aren't supported in the CREATE VIEW statement, but you can add them afterward with ALTER VIEW.

3. Using CREATE OR REPLACE vs. IF NOT EXISTS

These clauses handle existing views differently. OR REPLACE overwrites the view definition entirely. IF NOT EXISTS skips creation if a view with that name already exists.

-- Overwrites existing view
CREATE OR REPLACE VIEW v_active_customers AS
SELECT * FROM customers WHERE status = 'active';

-- Does nothing if view exists
CREATE VIEW IF NOT EXISTS v_active_customers AS
SELECT * FROM customers WHERE status = 'active';

Use OR REPLACE for deployment scripts where you want the latest definition. Use IF NOT EXISTS for initialization scripts that shouldn't modify existing objects.

What Are Common Use Cases for Snowflake Views?

Views serve several practical purposes beyond just simplifying queries.

1. Simplifying Complex Queries

Views let analysts query a single object instead of writing multi-table joins repeatedly:

CREATE VIEW v_customer_360 AS
SELECT 
    c.customer_id,
    c.customer_name,
    c.signup_date,
    COALESCE(o.total_orders, 0) AS total_orders,
    COALESCE(o.lifetime_value, 0) AS lifetime_value,
    s.subscription_tier,
    s.renewal_date
FROM customers c
LEFT JOIN (
    SELECT customer_id, COUNT(*) AS total_orders, SUM(amount) AS lifetime_value
    FROM orders GROUP BY customer_id
) o ON c.customer_id = o.customer_id
LEFT JOIN subscriptions s ON c.customer_id = s.customer_id;

Downstream users just query v_customer_360 without understanding the underlying schema.

2. Implementing Row-Level Security

Secure views can filter data dynamically based on who's querying:

CREATE SECURE VIEW v_regional_sales AS
SELECT *
FROM sales
WHERE region = CASE 
    WHEN CURRENT_ROLE() = 'GLOBAL_ANALYST' THEN region
    WHEN CURRENT_ROLE() = 'NA_ANALYST' THEN 'North America'
    WHEN CURRENT_ROLE() = 'EU_ANALYST' THEN 'Europe'
    ELSE NULL
END;

Each role sees only their permitted data through the same view.

3. Standardizing Business Logic

When "active customer" or "qualified lead" has a specific definition, encode it once in a view:

CREATE VIEW v_active_customers AS
SELECT *
FROM customers
WHERE last_order_date >= DATEADD('day', -90, CURRENT_DATE())
  AND account_status = 'active'
  AND NOT is_test_account;

Every team now uses the same definition. When criteria change, update one view instead of fifty queries.

How Do You Modify and Manage Existing Views?

Snowflake provides commands for renaming, documenting, and inspecting views after creation.

1. Altering Views

ALTER VIEW handles metadata changes without recreating the view:

-- Rename a view
ALTER VIEW v_old_name RENAME TO v_new_name;

-- Add or update comment
ALTER VIEW v_customer_360 SET COMMENT = 'Updated Q1 2025 with subscription data';

-- Convert to secure view
ALTER VIEW v_customer_360 SET SECURE;

To change the underlying SELECT statement, use CREATE OR REPLACE. ALTER VIEW doesn't support modifying the query definition.

2. Viewing View Definitions

Retrieve the SQL behind any view using GET_DDL:

SELECT GET_DDL('VIEW', 'v_customer_360');

For a list of all views in a schema with metadata:

SHOW VIEWS IN SCHEMA my_database.my_schema;

What Are Best Practices for Snowflake Views?

Follow these guidelines to keep your view architecture maintainable:

• Use consistent naming conventions: Prefix views with v_ or vw_ to distinguish them from tables at a glance
• Document purpose with comments: Future you (or your replacement) needs context on why this view exists and what business logic it encodes
• Avoid deeply nested views: A view querying a view querying a view creates debugging nightmares and unpredictable performance
• Choose materialized views carefully: They speed up reads but add storage costs and refresh overhead; only use them for genuinely expensive, frequently-run aggregations
• Default to secure for shared data: When exposing data outside your Snowflake account, secure views prevent information leakage through query optimization
• Test performance before production: Check QUERY_PROFILE for unexpected full table scans or exploding row counts from bad join logic

How Does Airbyte Help with Snowflake Data Pipelines?

Views organize and standardize data once it's in Snowflake. Getting data there reliably is the prerequisite challenge.

Airbyte provides the data movement layer that feeds your Snowflake views. With 600+ pre-built connectors, you can pull data from source systems like CRMs, databases, SaaS applications, and APIs into Snowflake without building custom integrations. The native Snowflake destination handles optimized loading, schema mapping, and incremental syncs that minimize compute costs.

Teams typically land raw data through Airbyte, then build view layers on top for consumption. When source schemas change, Airbyte's automatic schema propagation keeps data flowing while your views handle the transformation logic.

Ready to build reliable data pipelines into Snowflake? Try Airbyte free and connect your first data source today. For teams managing multiple data sources feeding Snowflake views, talk to sales to learn how Airbyte's capacity-based pricing keeps ingestion costs predictable as your data volumes grow.

Frequently Asked Questions

What is the difference between a view and a table in Snowflake?

A table stores data physically on disk. A view stores a query definition and executes it against base tables each time you query the view. Tables consume storage; standard views don't. Tables need explicit updates to reflect changes; views always show current data from underlying tables.

How do materialized views refresh in Snowflake?

Snowflake refreshes materialized views automatically when base table data changes. You don't schedule refreshes manually. The refresh happens in the background, and queries against the materialized view use the precomputed results. Refresh operations consume compute credits, which adds to the total cost of ownership.

When should you use a secure view instead of a standard view?

Use secure views when sharing data with external Snowflake accounts, when the view definition contains sensitive business logic you want to hide, or when you need to prevent query optimization from revealing information about underlying data distribution. For internal use without these concerns, standard views perform better.

Can you create a view from another view in Snowflake?

Yes, views can reference other views. However, deeply nested view chains create performance unpredictability and debugging complexity. Each layer adds overhead, and troubleshooting slow queries requires tracing through multiple definitions. Keep nesting to two levels maximum when possible.

How do you drop a view in Snowflake?

Use the DROP VIEW command: DROP VIEW IF EXISTS view_name;. The IF EXISTS clause prevents errors when the view doesn't exist. Dropping a view doesn't affect base tables or data. It only removes the view definition. Any queries or other views referencing the dropped view will fail until you recreate it or update the references.