Sessions & Parameter Hierarchy

Overview

Understanding sessions and parameters is essential for managing how users interact with Snowflake and how their queries execute. For the SnowPro Core (COF-C03) certification, you must understand the session context, the parameter hierarchy, precedence rules, and how to query or modify these settings.

Key Concepts

Session Context

When a user connects to Snowflake (via web interface, SnowSQL, or a connector), they establish a session. Each session maintains its own context, which defines the environment for executing statements.

Key context variables include:

• Current User: The user authenticated in the session.
• Current Role: The primary active role (CURRENT_ROLE()).
• Current Secondary Roles: Active secondary roles (CURRENT_SECONDARY_ROLES()).
• Current Database: The default database for the session.
• Current Schema: The default schema for the session.
• Current Warehouse: The active virtual warehouse used to execute queries.

Parameter Hierarchy and Precedence

Snowflake parameters can be set at multiple levels. The hierarchy, from highest (most general) to lowest (most specific), is:

1. Account Level: Applies to all users, sessions, and objects in the account. Requires the ACCOUNTADMIN role to modify (for most parameters).

2. User Level: Applies to all sessions established by a specific user.

3. Session Level: Applies only to the current active session.

4. Object Level: Applies to specific objects (e.g., Warehouse, Database, Schema, Task).

Precedence Rule: The lowest level (most specific) parameter setting always overrides a higher-level setting.

*Example: If STATEMENT_TIMEOUT_IN_SECONDS is set to 3600 (1 hour) at the Account level, but 600 (10 minutes) at the Session level, the Session setting (600) takes precedence for that session.*

How It Works

Managing Parameters

You can view, set, and unset parameters using specific SQL commands:

• SHOW PARAMETERS: View parameter values at a specific level.
• SET / ALTER SESSION SET: Modify a parameter for the current session.
• UNSET: Remove a session parameter setting, reverting to the next highest level (e.g., User or Account).
• ALTER ACCOUNT / USER / WAREHOUSE / DATABASE: Set parameters at specific levels.

Key Parameters to Know for the Exam

| Parameter | Level | Description |

| :--- | :--- | :--- |

| STATEMENT_TIMEOUT_IN_SECONDS | Account, Session, User, Warehouse | Maximum time (in seconds) a SQL statement can execute before being canceled. Default is 172800 (48 hours). |

| STATEMENT_QUEUED_TIMEOUT_IN_SECONDS | Account, Session, User, Warehouse | Maximum time a statement can remain queued in a warehouse before being canceled. Default is 0 (no timeout). |

| USE_CACHED_RESULT | Account, Session, User | Determines if Snowflake should use the Result Cache for identical queries. Default is TRUE. |

| QUERY_TAG | Account, Session, User | An optional string that can be attached to queries for tracking and accounting purposes within the QUERY_HISTORY view. |

| TIMEZONE | Account, Session, User | Specifies the time zone for the session. Default is America/Los_Angeles. |

| BINARY_INPUT_FORMAT | Account, Session, User | Defines the format for binary input data (e.g., HEX, BASE64). |

| ROWS_PER_RESULTSET | Account, Session, User | Limits the number of rows returned by a query. |

| AUTOCOMMIT | Account, Session, User | Determines if DML statements are automatically committed. Default is TRUE. |

SQL Examples

-- View session context variables
SELECT CURRENT_USER(), CURRENT_ROLE(), CURRENT_WAREHOUSE(), CURRENT_DATABASE(), CURRENT_SCHEMA();

-- Set session context variables
USE WAREHOUSE compute_wh;
USE DATABASE sales_db;
USE SCHEMA public;
USE ROLE analyst_role;

-- Show all parameters at the session level
SHOW PARAMETERS IN SESSION;

-- Show parameters at the account level (Requires ACCOUNTADMIN)
SHOW PARAMETERS IN ACCOUNT;

-- Show parameters for a specific warehouse
SHOW PARAMETERS IN WAREHOUSE compute_wh;

-- Set a parameter at the session level
ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS = 300; -- 5 minutes

-- Set a parameter at the account level (Requires ACCOUNTADMIN)
ALTER ACCOUNT SET STATEMENT_TIMEOUT_IN_SECONDS = 3600; -- 1 hour

-- Set a query tag for tracking
ALTER SESSION SET QUERY_TAG = 'Monthly Financial Report';
-- Run your query...
SELECT * FROM sales_data WHERE month = 'January';
-- Unset the query tag
ALTER SESSION UNSET QUERY_TAG;

-- Set a parameter at the user level
ALTER USER john_doe SET TIMEZONE = 'UTC';

-- Revert a session parameter to its default/higher-level value
ALTER SESSION UNSET STATEMENT_TIMEOUT_IN_SECONDS;

Exam Tips

⚠️ Hierarchy is crucial: Memorize the hierarchy: Account -> User -> Session -> Object. The most specific (lowest) level wins.

⚠️ Result Cache: The USE_CACHED_RESULT parameter controls the Result Cache. If a question asks how to bypass the result cache for performance testing, the answer is ALTER SESSION SET USE_CACHED_RESULT = FALSE;.

⚠️ Timeouts: Know the difference between STATEMENT_TIMEOUT_IN_SECONDS (execution time) and STATEMENT_QUEUED_TIMEOUT_IN_SECONDS (wait time in warehouse queue).

⚠️ Accountadmin requirement: Remember that modifying Account-level parameters generally requires the ACCOUNTADMIN role.

Key Takeaways

• A session encapsulates the context (user, role, warehouse, db) for executing SQL.
• Parameters control system behavior (timeouts, caching, formats).
• Parameter hierarchy allows for flexible overrides (Account -> User -> Session -> Object).
• The lowest-level setting always takes precedence.
• Use SHOW PARAMETERS to view current settings and ALTER SESSION SET / UNSET to manage session behavior.