Accounts and Security · Topic 2 of 17

Snowflake Parameters - Account, Object, Session and the Hierarchy

Learn the three Snowflake parameter types, how account, object and session parameters override each other, and how to set them centrally across accounts.

  • 5 min
  • Includes a diagram
In a nutshell
  • Three types: account, object, session; the lower level overrides the higher one.
  • Session parameters: account, then user, then session. Object parameters follow object containment.
  • For timeout parameters set in more than one place, the lower value wins.

What it is

Parameters control how Snowflake behaves. Some are set once for the whole account, some belong to a user, a warehouse, a database or a table, and some change only the current session. The exam tests which type a parameter is, who can set it, and which value wins when the same parameter is set at several levels.

There are three types: account parameters, object parameters and session parameters. Every parameter has a default, and a value set lower in the hierarchy overrides the one above it.

Snowflake defaultoverridden byAccount valueALTER ACCOUNT SEToverridden byObject or user valuedatabase, schema, table, warehouse, useroverridden bySession valueALTER SESSION SET

How it works

The three parameter types

TypeScopeSet withExamples
AccountThe whole account onlyALTER ACCOUNT SETPERIODIC_DATA_REKEYING, REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_CREATION, PREVENT_UNLOAD_TO_INLINE_URL, MIN_DATA_RETENTION_TIME_IN_DAYS
ObjectA specific object (and its children)ALTER <object> SET or at creationDATA_RETENTION_TIME_IN_DAYS, MAX_CONCURRENCY_LEVEL, STATEMENT_TIMEOUT_IN_SECONDS, STATEMENT_QUEUED_TIMEOUT_IN_SECONDS, DEFAULT_DDL_COLLATION
SessionOne session, a user or the account defaultALTER SESSION SET, ALTER USER SET, ALTER ACCOUNT SETTIMEZONE, QUERY_TAG, USE_CACHED_RESULT, AUTOCOMMIT, DATE_OUTPUT_FORMAT

Account parameters apply to the account as a whole and cannot be set on a user or session. Many are security controls, so they are usually changed by ACCOUNTADMIN.

Object parameters are set on an object such as a warehouse, database, schema, table or user. They can also be set on the account, where they act as the default for every object of that kind.

Session parameters change query behavior. They can be set for the whole account, for a user, or inside a running session.

The hierarchy

Account parameters are the defaults; object parameters flow from account to warehouse and to database, schema and table, and session parameters flow from account to user to session, with the most specific level winning. For session parameters the order is account, then user, then session. A value on the user overrides the account value, and a value in the session overrides both.

For object parameters the order follows containment. For example, DATA_RETENTION_TIME_IN_DAYS can be set on the account, a database, a schema and a table, and the closest one to the object wins.

AccountDATA_RETENTION 1default forDatabaseset to 7inherited bySchemanot setinherited byTableset to 30 wins

Some parameters are both object and session parameters. STATEMENT_TIMEOUT_IN_SECONDS can be set on a warehouse and in a session; when both exist, the lower value is used. The same rule applies to STATEMENT_QUEUED_TIMEOUT_IN_SECONDS.

A few object parameters have a floor set at account level. MIN_DATA_RETENTION_TIME_IN_DAYS is an account parameter: the effective retention for an object is the larger of its own DATA_RETENTION_TIME_IN_DAYS and this minimum.

Working with parameters

SHOW PARAMETERS LIKE 'DATA_RETENTION%' IN DATABASE sales;
ALTER ACCOUNT SET STATEMENT_TIMEOUT_IN_SECONDS = 3600;
ALTER WAREHOUSE etl_wh SET STATEMENT_TIMEOUT_IN_SECONDS = 7200;
ALTER USER analyst1 SET TIMEZONE = 'Europe/London';
ALTER SESSION SET QUERY_TAG = 'nightly_load';
ALTER ACCOUNT UNSET STATEMENT_TIMEOUT_IN_SECONDS;

SHOW PARAMETERS lists the value, the default and the level at which each value was set, so it shows where an override comes from. UNSET returns the parameter to the next level up.

Central account and additional accounts

Parameters are per account. In a multi account design, set the same security and cost controls in every account, for example a network policy, a statement timeout, storage integration requirements and retention minimums. The central (admin) account is where the standards are decided; each additional account must still be configured, because nothing is inherited across accounts.

Central standardparameter baselineapplied by script toAccount 1ALTER ACCOUNT SETsameAccount 2ALTER ACCOUNT SETsameAccount 3ALTER ACCOUNT SET
Exam tip

Defaults worth knowing. DATA_RETENTION_TIME_IN_DAYS is 1 by default and up to 90 on Enterprise Edition or higher. STATEMENT_TIMEOUT_IN_SECONDS defaults to 172800 (two days). STATEMENT_QUEUED_TIMEOUT_IN_SECONDS defaults to 0, meaning no timeout.

Watch out

A warehouse level STATEMENT_TIMEOUT_IN_SECONDS does not replace the session value; Snowflake takes the lower of the two. A long running job can still be cut off by a lower session setting.

Common mistakes

Setting an account parameter on a lower level. Account parameters exist only at account level. Session and object parameters are the ones you can place lower.

Assuming account values are inherited by other accounts. Each account holds its own parameters.

Forgetting where an override came from. When a query behaves oddly, run SHOW PARAMETERS and read the level column instead of guessing.

Setting retention too low at the object and expecting it to win. A higher MIN_DATA_RETENTION_TIME_IN_DAYS at account level still raises the effective value.

What to remember for the exam

  • Three types: account, object, session; the lower level overrides the higher one.
  • Session parameters: account, then user, then session. Object parameters follow object containment.
  • For timeout parameters set in more than one place, the lower value wins.
  • SHOW PARAMETERS ... IN <scope> shows value, default and level; UNSET falls back to the parent.
  • MIN_DATA_RETENTION_TIME_IN_DAYS raises the effective retention above a lower object setting.
  • In multi account designs every account must be configured; nothing carries across.

Check yourself

  • Retention is 1 day on the account and 14 on one table. Which applies to the table?

    14 days, because the setting closest to the object wins.

  • A warehouse timeout is 7200 seconds and the session timeout is 3600. What is used?

    3600 seconds, because the lower of the two values is used.

  • Which parameter type applies to the whole account and cannot be set on a user or in a session?

    Account parameters, such as PERIODIC_DATA_REKEYING, which apply to the whole account only.

Topic 2 of 17 in Accounts and Security

Test yourself on Accounts and Security

The full Snowflake ARA-C01 question bank has 687 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.