How to Create Parameterized Views in Snowflake?

A view allows the result of a query to be accessed as if it were a table. It will encapsulate the business logic by combining two or more tables. It can also refer other views. Views serve a many different purposes, including combining tables or views, segregating, and protecting the underlying data. For example, you can create separate views that meet the needs of different types of employees, such as doctors and nurses at a hospital. In this article, we will check how to create parameterized views in Snowflake. Though, there are no direct methods to create parameterized views, but we will check an alternate method.

Why do we need Parameterized Views?

Before going into method to create parameterized views in Snowflake, first, let us understand why do we need Parameterized Views?

View serves variety of purpose, that includes combining tables or views, segregating, and protecting the underlying data. Sometimes, you may get requirement to change the condition in the WHERE clause in your view to pull specific records. Instead of creating a separate view for each condition, you can create single view and pass the condition value as a parameter. This also reduces maintenance overhead.

How to Create Parameterized Views in Snowflake?

Snowflake does not support parameterized views. But, you can use the session variables in conjunction with a regular view. You can refer a session variable in the view DDL, and will need to be set in any sessions before querying the view.

How to use session variable?

You can use the SET command to set session variable. A SET command initializes the value of a session variable to the result of a SQL expression or a constant value.

Following example demonstrate the use of session variable inside view definition.

-- Session variable for WHERE condition
SET WHERE_C = '10';

-- Create view with session variable
CREATE OR REPLACE VIEW sample_view
AS
  SELECT source_id,
         source_name
  FROM   rec_test
  WHERE  source_id = $where_c; -- Session Variable

Following is the output with different WHERE conditions.

-- Result when SOURCE_ID is set to 10
SET WHERE_C = '10';
SELECT * FROM SAMPLE_VIEW;
+-----------+-------------+
| SOURCE_ID | SOURCE_NAME |
|-----------+-------------|
|        10 | Chris Speed |
+-----------+-------------+

-- Result when SOURCE_ID is set to 1
SET WHERE_C = '1'
SELECT * FROM SAMPLE_VIEW;
+-----------+-----------------+
| SOURCE_ID | SOURCE_NAME     |
|-----------+-----------------|
|         1 | Robert C. Allen |
|         1 | Robert C. Allen |
+-----------+-----------------+

Snowflake Identifier Function

You have to use to identifier function when referring any session variable in SELECT or FROM clause. For example, when you are passing table name using session variable.

For example,

set temp_v = 't1';

CREATE OR replace VIEW v1 AS SELECT col1,
       col2
FROM   identifier($temp_v); -- session variable

Without identifier function, you may end up with a compilation error.

For example,

CREATE OR replace VIEW v1 ASSELECT col1,
       col2
FROM   $temp_v;
001003 (42000): SQL compilation error:
syntax error line 1 at position 52 unexpected '$temp_v'.