Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Create a View in Snowflake: Syntax and Examples

To create a view in Snowflake, use the CREATE VIEW view_name AS SELECT ... statement. This defines a virtual table based on a query that you can reuse like a regular table.
📐

Syntax

The basic syntax to create a view in Snowflake is:

  • CREATE VIEW view_name AS SELECT ...: Creates a new view with the specified name.
  • view_name: The name you assign to the view.
  • SELECT ...: The query that defines the data the view will show.
sql
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;
💻

Example

This example creates a view named active_customers that shows customers with status 'active'.

sql
CREATE OR REPLACE VIEW active_customers AS
SELECT customer_id, customer_name, status
FROM customers
WHERE status = 'active';

-- Query the view
SELECT * FROM active_customers;
Output
customer_id | customer_name | status ------------|---------------|-------- 123 | Alice Smith | active 456 | Bob Johnson | active
⚠️

Common Pitfalls

Common mistakes when creating views in Snowflake include:

  • Not using CREATE OR REPLACE VIEW when updating a view, causing errors if the view exists.
  • Including non-deterministic functions like CURRENT_TIMESTAMP which can cause unexpected results.
  • Referencing tables or columns that do not exist or have insufficient privileges.
sql
/* Wrong: Trying to create a view that already exists without OR REPLACE */
CREATE VIEW active_customers AS
SELECT * FROM customers WHERE status = 'active';

/* Right: Use OR REPLACE to update or create */
CREATE OR REPLACE VIEW active_customers AS
SELECT * FROM customers WHERE status = 'active';
📊

Quick Reference

ClauseDescription
CREATE VIEW view_name AS SELECT ...Creates a new view with the given query
CREATE OR REPLACE VIEWCreates or updates an existing view
SELECT ...Defines the data the view will show
View nameMust be unique in the schema
PrivilegesUser must have SELECT rights on underlying tables
✅

Key Takeaways

Use CREATE OR REPLACE VIEW to safely create or update views.
Views are virtual tables defined by SELECT queries.
Avoid non-deterministic functions in view definitions.
Ensure you have proper permissions on underlying tables.
Query views like regular tables to reuse complex queries.