How to use parameterized Views in Snowflake?

Can we use Parameterized Views in Snowflake. Such as pass the table name or database name as parameters instead of hard-coding it in the view DDL ?

The best bet is to use session variables in conjunction with a regular view

A session variable can be referenced in the view DDL, and will need to be set in any sessions querying the view.

To do this, you can make use of the IDENTIFIER function in Snowflake, which lets you use text as an object identifier.

create table t1 (col1 number, col2 number);

create table t2 (col1 number, col2 number);

set temp_var = 't1';

create view v1 as select col1, col2 from identifier($temp_var);

Before you query the view, you will need to set the session variable (ti in this case) to the table name (fully qualified if need be).

set temp_var = 't1';

select * from v1; -- returns data from t1

set temp_var = 't2';

select * from v1; -- returns data from t2

dan-irving

posted on 30 Oct 19