πͺ 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 Practice | Why it Matters |
|---|---|
| Give views clear, purpose-driven names | Easy to understand (vw_high_salary_emps) |
| Avoid SELECT * | Prevents breakage if base table changes |
| Use views for security and abstraction | Hide sensitive columns or logic |
| Test views after schema changes | Ensure they still compile and return data |