Skip to main content

πŸͺŸ Understanding Standard Views in Snowflake

🧾 What is a Standard View?​

A Standard View is a logical (virtual) table based on the result of a SELECT statement. It doesn’t store data physically but always fetches live data from the underlying tables when queried.

It's like saving a SQL query under a name and calling it when needed.


βœ… Key Characteristics​

  • Data is not stored – always fetched at runtime.
  • Views can join multiple tables, apply filters, aggregations, etc.
  • Schema binding: The view references table columns; if column names change or drop, the view may break.
  • Supports role-based access control.

πŸ“Š Sample Table Setup​

Let’s create a sample employee table with 10 records:

CREATE OR REPLACE TABLE emp_data (
emp_id INT,
emp_name STRING,
department STRING,
salary NUMBER(10,2),
joining_date DATE
);

INSERT INTO emp_data VALUES
(101, 'Anil Kumar', 'HR', 55000.00, '2021-01-15'),
(102, 'Divya Rani', 'IT', 75000.00, '2020-08-22'),
(103, 'Rohit Das', 'Finance', 67000.00, '2019-11-01'),
(104, 'Meera Menon', 'IT', 80000.00, '2022-02-20'),
(105, 'Suresh Babu', 'Sales', 60000.00, '2023-04-10'),
(106, 'Nisha Jain', 'Marketing', 58000.00, '2020-12-05'),
(107, 'Ravi Teja', 'Finance', 69000.00, '2021-09-17'),
(108, 'Priya Iyer', 'HR', 62000.00, '2022-07-11'),
(109, 'Kiran Rao', 'Sales', 61000.00, '2021-03-03'),
(110, 'Aarti Patel', 'IT', 82000.00, '2023-01-29');

πŸͺŸ Create a Standard View​

Create a view to show employees with salary greater than β‚Ή60,000:

CREATE OR REPLACE VIEW vw_high_salary_emps AS
SELECT emp_id, emp_name, department, salary
FROM emp_data
WHERE salary > 60000;

Now, querying the view:

SELECT * FROM vw_high_salary_emps;

This returns live data from emp_data whenever it’s called.


πŸ” Metadata Analysis​

Check all views in current schema:​

SHOW VIEWS;

Describe the view structure:​

DESC VIEW vw_high_salary_emps;

You’ll see the column names, data types, and base table references.

View definition:​

SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'VW_HIGH_SALARY_EMPS';

This shows the actual SQL used to create the view.


🚧 Things to Keep in Mind​

Best PracticeWhy it Matters
Give views clear, purpose-driven namesEasy to understand (vw_high_salary_emps)
Avoid SELECT *Prevents breakage if base table changes
Use views for security and abstractionHide sensitive columns or logic
Test views after schema changesEnsure they still compile and return data