Understanding Snowflake Sessions

Snowflake, a cloud-based data warehousing platform, facilitates handling and analyzing large datasets with ease. One of its features is the concept of Snowflake Sessions, which are crucial for managing user interactions and ensuring the seamless operation of various tasks.

This blog post delves into the intricacies of Snowflake sessions, elaborating on how to initiate and maintain them, and under what circumstances they are suspended.

When does a session begin and end?

Sessions in Snowflake commence once a user successfully authenticates using any client like Snowsight, the Classic Console, or programmatic interfaces.

These sessions, which are distinct from your identity provider (IdP) sessions, are maintained indefinitely with user activity but do have a default idle session timeout of four hours.

It's important to note that passive actions like scrolling through query results don't reset the session's idle timer.

The CLIENT_SESSION_KEEP_ALIVE option helps maintain the session indefinitely, though its excessive use can lead to resource strain. Snowflake suggests keeping sessions alive as long as they're needed and responsibly closing them afterward to optimize resource utilization.

Snowflake creates a new session for each worksheet in Snowsight. A worksheet session enforces the session policy that applies to the user that creates the worksheet.

There are two types of sessions that are initiated when creating a new worksheet:

• Snowsight Sessions Each worksheet in Snowsight initiates a new session, with the session policies of the creating user being applied.

• Classic Console Sessions: Similar to Snowsight, new worksheets in the Classic Console also start fresh sessions, each with a separate 4-hour idle limit.

As long as you interact with one of the sheets, user session is constantly refreshed.

Snowflake Session Policies

Available from the Enterprise Edition onwards, session policies let you define and manage idle timeout periods, enhancing control over user session behavior and helping meet compliance requirements.

Key properties of these policies include:

• SESSION_IDLE_TIMEOUT_MINS: For programmatic and Snowflake clients.

• SESSION_UI_IDLE_TIMEOUT_MINS: Specifically for the Classic Console and Snowsight.

If a session policy isn’t explicitly set, Snowflake defaults to a 240-minute timeout. However, the minimum configurable timeout is 5 minutes.

You can use the SESSION_POLICIES view and POLICY_REFERENCES function to monitor and apply policies.

Steps to Implement a Session Policy

1. Create a Custom Role: Start by creating a role (e.g., policy_admin) responsible for managing session policies.

1. Grant Necessary Privileges: This role should have privileges like USAGE, CREATE SESSION POLICY, and APPLY SESSION POLICY on the required database, schema, account, or users.

1. Create the Session Policy: Define your session policy with appropriate timeout settings and comments for clarity.

1. Apply the Policy: Use the ALTER ACCOUNT or ALTER USER command to apply this policy at the account or user level.

1. Manage and Replicate Policies: session policies can be replicated across accounts using Snowflake's replication features.

To keep in mind

• The default idle time limit is 240 minutes (4 hours). It's possible to change this limit using a session policy.

• Session policies are available from the Enterprise Edition onwards.

• Active queries continue even after the session ends and a user logs out.

• Scrolling through query results or sorting data doesn't reset the idle session timeout.

• Session policies can be replicated across accounts.

Sources

• Snowflake Sessions & Sessions Policies