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 VIEWwhen updating a view, causing errors if the view exists. - Including non-deterministic functions like
CURRENT_TIMESTAMPwhich 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
| Clause | Description |
|---|---|
| CREATE VIEW view_name AS SELECT ... | Creates a new view with the given query |
| CREATE OR REPLACE VIEW | Creates or updates an existing view |
| SELECT ... | Defines the data the view will show |
| View name | Must be unique in the schema |
| Privileges | User 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.
