Snowflake Session, Context Variables & Parameter Precedence
Snowflake exposes hundreds of configurable parameters: query timeout, Time Travel retention, time zone, autocommit, log level. Most can be set at more than one level, and the exam tests one question on every scenario: which level wins?
| Term | Plain meaning |
|---|---|
| Parameter | A configurable setting that changes Snowflake behaviour. Query timeout, retention days, and time zone are common examples. |
| Session | One login. Connecting through Snowsight, the CLI, or a driver starts a session. Anything you change with ALTER SESSION dies when you log out. |
| Context | The state of your session. Active role, current warehouse, current database, current schema. Snowflake exposes context through the CURRENT_*() functions. |
| Scope | The level at which a parameter is set. Account, User, Session, or Object. The rule of thumb: most-specific scope wins. |
| Effective value | The value Snowflake actually uses after walking the hierarchy. SHOW PARAMETERS reports this in the value column. |
Today’s Concept
Micro-Concept 1: Three Types of Parameters
Snowflake parameters split into three types. The type decides where you are allowed to set them.
| Type | Where can it be set? | Examples |
|---|---|---|
| Account parameters | Account level only. Set with ALTER ACCOUNT by ACCOUNTADMIN. Cannot be overridden lower. | NETWORK_POLICY, PERIODIC_DATA_REKEYING, SSO_LOGIN_PAGE |
| Session parameters | Account → User → Session | TIMEZONE, STATEMENT_TIMEOUT_IN_SECONDS*, QUERY_TAG, AUTOCOMMIT, DATE_OUTPUT_FORMAT |
| Object parameters | Account → Database → Schema → Object | DATA_RETENTION_TIME_IN_DAYS, STATEMENT_TIMEOUT_IN_SECONDS* (also settable on warehouse), LOG_LEVEL |
* A handful of parameters belong to both groups. STATEMENT_TIMEOUT_IN_SECONDS is the main example. It works as a session parameter (Account → User → Session) and as an object parameter on a warehouse. Each hierarchy resolves independently, which is why the session-vs-object split matters on the exam.
Micro-Concept 2: The Two Hierarchies
The rule is one sentence:
Most-specific scope wins.
What “specific” means depends on the parameter type. The two hierarchies look alike but apply to different parameters. Keep them separate.
| Parameter type | Hierarchy (broadest → narrowest) | Most-specific wins example |
|---|---|---|
| Session parameters | Account → User → Session | Account sets TIMEZONE=’UTC’. User sets it to ‘America/New_York’. Session sets it to ‘Asia/Kolkata’. Effective value is Asia/Kolkata. |
| Object parameters | Account → Database → Schema → Object | Account sets DATA_RETENTION_TIME_IN_DAYS=1. Database sets it to 7. Schema sets it to 14. Table sets it to 30. Effective value on that table is 30. |
Account parameters live at only one scope. They cannot be overridden lower.
Micro-Concept 3: How to Read a Three-Layer Question
The exam phrases these scenarios in a predictable shape:
“An admin sets STATEMENT_TIMEOUT_IN_SECONDS to 3600 at the account level. The user is set to 1800. The current session is set to 600. What is the effective timeout for queries in this session?”
Walk it from the most-specific scope outward. Stop at the first level that has a value:
→ Most-specific set = Session = 600.
→ Effective value = 600.
If Session were unset, you fall to User = 1800. If User were also unset, you fall to Account = 3600. If nothing is set anywhere, you get the Snowflake default for that parameter.
Micro-Concept 4: The Object-Parameter Variant (DATA_RETENTION)
The object hierarchy adds a Schema level between Database and Object. Same rule, one extra step:
“Account: DATA_RETENTION_TIME_IN_DAYS = 1. Database SALES = 7. Schema SALES.RAW = 30. Table SALES.RAW.ORDERS: not set. What is the effective retention for ORDERS?”
→ Object (table) is unset. Fall back.
→ Schema is set to 30. That wins.
→ Effective value = 30 days.
Setting DATA_RETENTION_TIME_IN_DAYS = 5 on the table afterwards makes 5 the new effective value. Object beats Schema beats Database beats Account.
Micro-Concept 5: Setting and Unsetting
Setting at any level uses ALTER <scope> SET <param> = <value>. Removing the override at that level uses UNSET. After an unset, the next-most-specific value above takes over.
| Scope | Set | Unset |
|---|---|---|
| Account | ALTER ACCOUNT SET STATEMENT_TIMEOUT_IN_SECONDS = 3600; | ALTER ACCOUNT UNSET STATEMENT_TIMEOUT_IN_SECONDS; |
| User | ALTER USER alice SET STATEMENT_TIMEOUT_IN_SECONDS = 1800; | ALTER USER alice UNSET STATEMENT_TIMEOUT_IN_SECONDS; |
| Session | ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS = 600; | ALTER SESSION UNSET STATEMENT_TIMEOUT_IN_SECONDS; |
| Warehouse (object) | ALTER WAREHOUSE lab_xs SET STATEMENT_TIMEOUT_IN_SECONDS = 900; | ALTER WAREHOUSE lab_xs UNSET STATEMENT_TIMEOUT_IN_SECONDS; |
Inspect the resolution. Run SHOW PARAMETERS LIKE 'STATEMENT_TIMEOUT_IN_SECONDS'; to see two columns that matter. The value column is the effective value. The level column is the scope it came from. That second column is the fastest way to debug a “why isn’t my override taking effect” ticket in production.
Micro-Concept 6: Context Functions
Context functions are separate from parameters. They report the state of the current session rather than configurable settings. The common ones appear directly on the exam and turn up in every production debugging session.
| Function | Returns |
|---|---|
CURRENT_USER() | The user logged into this session |
CURRENT_ROLE() | The currently-active primary role |
CURRENT_AVAILABLE_ROLES() | JSON array of all roles you could USE |
CURRENT_WAREHOUSE() | The warehouse used by the next query |
CURRENT_DATABASE() | The default database for unqualified names |
CURRENT_SCHEMA() | The default schema for unqualified names |
CURRENT_ACCOUNT() | The Snowflake account name |
CURRENT_REGION() | Cloud + region (e.g., AWS_US_WEST_2) |
CURRENT_VERSION() | The Snowflake version your account is on |
CURRENT_SESSION() | Numeric ID of the current session |
Two situations where context functions matter. The first is debugging a wrong-warehouse or wrong-role question. Running SELECT CURRENT_ROLE(), CURRENT_WAREHOUSE(), CURRENT_DATABASE(); tells you instantly where the session is pointing. The second is embedding them into audit and log tables so every row carries the role and user that produced it.
Cheat Sheet
| Concept | What to remember |
|---|---|
| The rule | Most-specific scope wins. One sentence covers every scenario. |
| Session-parameter hierarchy | Account → User → Session |
| Object-parameter hierarchy | Account → Database → Schema → Object (e.g., Table) |
| Account-only parameters | Set with ALTER ACCOUNT; cannot be overridden lower |
| UNSET behaviour | Removes the override at that level. Next most-specific value applies |
| Inspect effective value | Run SHOW PARAMETERS LIKE '…';. The level column shows where it was set |
| Common session params | TIMEZONE, AUTOCOMMIT, QUERY_TAG, DATE_OUTPUT_FORMAT |
| Common object params | DATA_RETENTION_TIME_IN_DAYS, LOG_LEVEL, STATEMENT_TIMEOUT_IN_SECONDS |
| Context functions | CURRENT_USER, CURRENT_ROLE, CURRENT_WAREHOUSE, CURRENT_DATABASE, CURRENT_SCHEMA, CURRENT_REGION, CURRENT_VERSION |
Any option that says “Account always overrides Session” is wrong. Most-specific wins, full stop. Session beats User, User beats Account. For object parameters, Table beats Schema, Schema beats Database, Database beats Account. Three traps I have seen catch candidates on COF-C03 practice tests. (1) UNSET does not reset to the Snowflake default. It exposes the next-most-specific value still set above. (2) Account-only parameters like NETWORK_POLICY cannot be overridden at a lower scope. That is the one exception to the hierarchy rule. (3) DATA_RETENTION_TIME_IN_DAYS set at the table level wins over schema and database. This is the standard Time Travel scenario question, and the table-level override is almost always the answer.
Hands-On Lab
Check the current effective value. Switch to ACCOUNTADMIN so you can set the parameter at every level. Capture your username now because steps 3 and 7 need it.
USE ROLE ACCOUNTADMIN;
SELECT CURRENT_USER(); -- note this — you'll use it as
SHOW PARAMETERS LIKE 'STATEMENT_TIMEOUT_IN_SECONDS' IN SESSION;
-- Look at the value and the level columns level column is empty when nothing has been overridden anywhere.Set at the account level. A 1-hour cap for every session in the account:
ALTER ACCOUNT SET STATEMENT_TIMEOUT_IN_SECONDS = 3600;
SHOW PARAMETERS LIKE 'STATEMENT_TIMEOUT_IN_SECONDS' IN SESSION;
-- value = 3600, level = ACCOUNTlevel column reports ACCOUNT, which tells you the value is inherited from the account scope.Override at the user level. Replace <yourself> with the username from step 1:
ALTER USER <yourself> SET STATEMENT_TIMEOUT_IN_SECONDS = 1800;
-- User-level overrides take effect on the NEXT new session.
-- For now, watch what happens after we layer a session override.Override at the session level. Most-specific scope wins.
ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS = 600;
SHOW PARAMETERS LIKE 'STATEMENT_TIMEOUT_IN_SECONDS' IN SESSION;
-- value = 600, level = SESSIONWatch UNSET cascade upward. Remove the session override. The user-level value would take over on a new login. In the current session, the fallback goes to the account value because user-level changes activate only at the start of a new session:
ALTER SESSION UNSET STATEMENT_TIMEOUT_IN_SECONDS;
SHOW PARAMETERS LIKE 'STATEMENT_TIMEOUT_IN_SECONDS' IN SESSION;
-- value falls back; level shifts to ACCOUNT (in this session)Run the object-parameter scenario. DATA_RETENTION_TIME_IN_DAYS at three levels of an object hierarchy:
CREATE OR REPLACE DATABASE day6_retention_lab DATA_RETENTION_TIME_IN_DAYS = 7;
CREATE OR REPLACE SCHEMA day6_retention_lab.long_term DATA_RETENTION_TIME_IN_DAYS = 30;
CREATE OR REPLACE TABLE day6_retention_lab.long_term.archive (id INT);
SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN TABLE day6_retention_lab.long_term.archive;
-- value = 30 (inherited from schema)
ALTER TABLE day6_retention_lab.long_term.archive
SET DATA_RETENTION_TIME_IN_DAYS = 5;
SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN TABLE day6_retention_lab.long_term.archive;
-- value = 5, level = TABLE — most specific winsCleanup. Reset everything this lab touched:
ALTER USER <yourself> UNSET STATEMENT_TIMEOUT_IN_SECONDS;
ALTER ACCOUNT UNSET STATEMENT_TIMEOUT_IN_SECONDS;
DROP DATABASE IF EXISTS day6_retention_lab;Snowflake Documentation
External References
Context-function and parameter resources.
Practice Questions
Options:
A. 3600 (account always wins)
B. 1800 (user always wins)
C. 600 (most-specific scope wins)
D. The values are averaged
Why C: Session is the narrowest scope in the Account → User → Session hierarchy. The most-specific scope that has a value set is the effective value, so Session = 600 wins.
Why not A: Account is the broadest scope, not the most-specific. The “account always wins” framing is the classic trap on this question type.
Why not B: User outranks Account, but Session outranks User. Picking the middle scope means knowing the hierarchy but missing the “most-specific wins” tiebreaker.
Why not D: Snowflake never averages parameter values across scopes. It walks the hierarchy and stops at the first scope with a value.
Options:
A. 1 day
B. 7 days
C. 14 days
D. The minimum of all set values
Why C: The object-parameter hierarchy is Account → Database → Schema → Object. The table has no explicit setting, so resolution falls back to the next-most-specific scope above. Schema is set to 14, so 14 is the effective value.
Why not A: Account is the broadest scope. Any more-specific scope with a value set overrides it. Picking it means reading the hierarchy backwards.
Why not B: Database = 7 is overridden by Schema = 14. The schema is more specific.
Why not D: Snowflake does not pick the min or max across scopes for object parameters. It walks the hierarchy and stops at the most-specific value.
Options:
A. Account-level parameters always override session-level parameters
B. The most-specific scope wins; UNSET at one level falls back to the next-most-specific value above
C. Object-level parameters cannot be overridden (they are immutable)
D. Parameter precedence follows alphabetical order of scope names
Why B: This is the rule stated exactly. Most-specific scope wins. UNSET removes the override at one level so the next-most-specific value takes effect.
Why not A: Account is the broadest scope. Every more-specific scope overrides it, which is the inverse of the trap option.
Why not C: Object parameters can be set, altered, and unset like any other parameter. “Immutable” is a fabricated restriction.
Why not D: Distractor. There is no alphabetical precedence rule anywhere in Snowflake.
Options:
A. Resets the parameter to the Snowflake default (172800)
B. Removes the session override; the User-level value (the next most specific) takes effect
C. Errors because the parameter is set at higher levels
D. Removes the parameter from all three scopes
Why B: UNSET removes the override at one scope only. Resolution then falls to the next-most-specific value still set above, which is the User-level value in this scenario.
Why not A: The Snowflake default applies only when no override exists at any scope. UNSET exposes whatever was already underneath, not the default.
Why not C: UNSET at one scope is independent of values set at other scopes. There is no error.
Why not D: Each scope is independent. ALTER SESSION UNSET touches the session value only.
Options:
A. CURRENT_USER()
B. CURRENT_ROLE()
C. CURRENT_AVAILABLE_ROLES()
D. SYSTEM_ROLE()
Why B: CURRENT_ROLE() returns the active primary role being used for privilege checks in the current session.
Why not A: CURRENT_USER() returns the logged-in user, not the role. Easy to confuse if you read the question quickly.
Why not C: CURRENT_AVAILABLE_ROLES() returns a JSON array of all roles the user could activate, not the one currently active.
Why not D: SYSTEM_ROLE() is not a valid Snowflake function. Pure distractor.
Today you learned: One rule governs every Snowflake parameter. Most-specific scope wins. Session parameters cascade Account → User → Session. Object parameters cascade Account → Database → Schema → Object. UNSET removes the override at one level and exposes the next-most-specific value still set above. Account-only parameters are the lone exception because they cannot be overridden at a lower scope. Context functions like CURRENT_USER, CURRENT_ROLE, and CURRENT_DATABASE report the state of the current session and are separate from parameters.
Key takeaway: On any multi-level parameter scenario, walk from the most-specific scope outward. Stop at the first level that has a value set. That value is the effective value.
Tomorrow (Day 7), Week 1 Recap: No new concepts. The day combines a 30-minute hands-on drill with a 10-question mixed quiz across Days 1–6. Topics covered: cloud-native, three-layer architecture, editions, interfaces, the 12 database objects, INFORMATION_SCHEMA vs ACCOUNT_USAGE, and parameter precedence. We’ll also flag any topic you should re-read before Week 2 covers virtual warehouses.
