Snowflake Session Variables, Context and Parameter Precedence

1.SQL variables live and die with the session

A Snowflake session keeps its own state. The simplest part of that state is a set of SQL variables that you define yourself. You create them with SET, remove them with UNSET, and list them with SHOW VARIABLES. You can also pass them in the connection string when you connect, which helps with tools where the connection string is the only thing you can customise. A variable's data type comes from whatever expression you assign to it. String and binary variables are limited to 16KB.

When you reference a variable, prefix it with $. That's how Snowflake tells it apart from bind values and column names. You can use a variable anywhere a literal constant is allowed.

SET (min, max)=(40, 70);

SELECT $min;

SELECT AVG(salary) FROM emp WHERE age BETWEEN $min AND $max;

A variable can also hold the name of an object, such as a table. In that case $my_table_name on its own won't work in DDL. You have to wrap it in IDENTIFIER(). In a FROM clause, TABLE($my_table_name) also works.

Variables are private to the session. Nobody can read a variable that was set in another session, and closing the session drops every variable created in it. Variable names are case-insensitive in SQL, but Snowflake stores them in uppercase. That matters for the convenience functions GETVARIABLE, SESSION_CONTEXT and SYS_CONTEXT, which take and return values as strings. GETVARIABLE('var_artist_name') returns NULL, while GETVARIABLE('VAR_ARTIST_NAME') returns the value.

CREATE TABLE  ? ($my_table_name) (i INTEGER);

Sources1

2.Session context: role, warehouse, database, schema

Besides your own variables, every session has a context: the active role, warehouse, database and schema. Snowflake groups the commands that set these with its account and session DDL:

- USE ROLE sets the primary role, and USE SECONDARY ROLES sets secondary roles; - USE WAREHOUSE sets the virtual warehouse for the session; - USE DATABASE and USE SCHEMA set where unqualified names resolve. The schema must be in the session's current database.

To read the context back, Snowflake's reference points you to context functions. You call them in a plain SELECT, with no need to query any view. The SQL variables page even shows context functions being captured into variables:

SET (current_user, current_warehouse) = ((SELECT CURRENT_USER()), (SELECT CURRENT_WAREHOUSE()));

The organization has a context function too: any role can run CURRENT_ORGANIZATION_NAME to find out which organization the current account belongs to. Context matters because it decides what a statement does. The active role sets which privileges apply. The current database and schema decide how a short name like orders resolves to a full database.schema.object name.

Sources23

3.Three parameter types and where each can be set

Variables are your own. Parameters are Snowflake's settings, and every parameter has a default value. There are three types, and each can be set at different levels.

- Account parameters apply to the whole account. You can set them *only* at the account level, with ALTER ACCOUNT, using a role that has the necessary privilege. - Session parameters make up most parameters. Their hierarchy is Account » User » Session. An account administrator sets an account-wide default with ALTER ACCOUNT. An administrator (typically with SECURITYADMIN) or the user themselves can override it for one user with ALTER USER, and that becomes the default for every session the user starts. Any user can then override it again for the current session with ALTER SESSION. - Object parameters default to objects such as warehouses, databases, schemas and tables. Setting one at the account level makes it the default for objects created in the account. A user with the right privileges can then override it on an individual object with CREATE <object> or ALTER <object>.

Sources45

4.Precedence: the most specific level wins, and how to prove it

Precedence follows the hierarchy: the most specific level that has a value wins. For session parameters, a session setting beats a user setting, and a user setting beats an account setting. If nothing is set at any level, the Snowflake default applies. Object parameters work the same way: a value set on the object with CREATE or ALTER overrides the account-level default. NETWORK_POLICY is a clear example: when it's set on both the account and a user, the user-level policy overrides the account-level one. STATEMENT_TIMEOUT_IN_SECONDS is worth remembering because it's both a warehouse object parameter and a session parameter.

To see which value is actually in effect, use SHOW PARAMETERS. With no clause it shows only session parameters. Add IN <object> to see one object's parameters, or IN ACCOUNT to see all of them. If you filter by name with LIKE, it must come before IN.

SHOW PARAMETERS IN DATABASE mydb;

SHOW PARAMETERS IN WAREHOUSE mywh;

The output has a level column that shows where the current value comes from, for example ACCOUNT. If level is empty, nobody has set the parameter and you're seeing the default. To return an account-level setting to its default, run ALTER ACCOUNT UNSET <param>.

Sources45

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A variable set with SET in one worksheet session can be read by other sessions or users, or survives after the session closes.Why is that wrong?

   SQL variables are private to the session that created them and are dropped when that session closes.

   Covered in SQL variables live and die with the session

2. 2.GETVARIABLE('my_var') finds a variable created with SET my_var = ..., because variable names are case-insensitive.Why is that wrong?

   $ references are case-insensitive, but Snowflake stores variable names in uppercase, so GETVARIABLE needs the uppercase name.

   Covered in SQL variables live and die with the session

3. 3.Any parameter can be overridden with ALTER SESSION.Why is that wrong?

   Only session parameters cascade down to the session. Account parameters can be set only at the account level.

   Covered in Three parameter types and where each can be set

Practise it for real

Create, use, inspect and drop session variables, then check where a parameter's value comes from

1. 1.Run SET (min, max)=(40, 70); and then SELECT $min;

   Why: This shows that variables are referenced with the $ prefix wherever a literal is allowed

   You should see: The SELECT returns 40

2. 2.Run SHOW VARIABLES;

   Why: This lists every variable defined in the current session

   You should see: Two rows, named MAX and MIN (stored in uppercase), with values 70 and 40

3. 3.Run SHOW PARAMETERS LIKE 'time%' IN ACCOUNT;

   Why: IN ACCOUNT includes account and object parameters, and LIKE must come before IN

   You should see: Parameters whose names start with TIME, with value, default and level columns

4. 4.Run UNSET min; and then SHOW VARIABLES; again

   Why: UNSET drops a variable explicitly, without waiting for the session to end

   You should see: Only MAX remains in the list