Parameters

Snowflake provides parameters that let you control the behavior of your account, individual user sessions, and objects. All the parameters have default values, which can be set and then overridden at different levels depending on the parameter type (Account, Session, or Object).

In this Topic:

Parameter Hierarchy and Types

This section describes the different types of parameters and the levels at which each type can be set.

Hierarchy of account, session, and object parameters

Account Parameters

Account parameters can be set only at the account level by users with the appropriate administrator role. Account parameters are set using the ALTER ACCOUNT command.

Snowflake provides the following account parameters:

Parameter Notes
CLIENT_ENCRYPTION_KEY_SIZE Used for encryption of files staged for data loading or unloading; may require additional installation and configuration (see description for details).
NETWORK_POLICY This is the only account parameter that can be set by either account or security administrators.
PERIODIC_DATA_REKEYING  
SAML_IDENTITY_PROVIDER Takes a JSON object as an input value; specifies the information necessary to enable Okta, ADFS, or a custom IdP for federated authentication and SSO.
SSO_LOGIN_PAGE  

Note

Account parameters are not displayed in the default output of SHOW PARAMETERS. For more information about viewing account parameters, see Viewing the Parameters (in this topic).

Session Parameters

Most parameters are session parameters, which can be set at the following levels:

Account:Account administrators can use the ALTER ACCOUNT command to set session parameters for the account. The values set for the account default to individual users and their sessions.
User:Administrators with the appropriate privileges can use the ALTER USER command to override session parameters for individual users. The values set for a user default to any sessions started by the user. In addition, users can override sessions parameters for themselves using ALTER USER.
Session:Users can use the ALTER SESSION to override session parameters within their sessions.

Object Parameters

Object parameters can be set at the following levels:

Account:Account administrators can use the ALTER ACCOUNT command to set object parameters for the account. The values set for the account default to the objects created in the account.
Object:Users with the appropriate privileges can use the corresponding CREATE or ALTER commands to override object parameters for an individual object.

Snowflake provides the following object parameters:

Parameter Object Type Notes
DATA_RETENTION_TIME_IN_DAYS Database, Schema, Table  
MAX_CONCURRENCY_LEVEL Warehouse  
STATEMENT_QUEUED_TIMEOUT_IN_SECONDS Warehouse Also a session parameter, i.e. can be set at both the object and session levels. For inheritance and override details, see the parameter description.
STATEMENT_TIMEOUT_IN_SECONDS Warehouse Also a session parameter, i.e. can be set at both the object and session levels. For inheritance and override details, see the parameter description.

Note

DATA_RETENTION_TIME_IN_DAYS and MAX_CONCURRENCY_LEVEL are not displayed in the SHOW PARAMETERS output by default. For more information about viewing these and other object parameters, see Viewing the Parameters (in this topic).

Viewing the Parameters

Snowflake provides a command, SHOW PARAMETERS, that displays a list of the parameters, along with the current and default values for each parameter:

  • By default, the command displays only session parameters:

    SHOW PARAMETERS;
    
  • To display all parameters, including account and object parameters, use an IN ACCOUNT clause:

    SHOW PARAMETERS IN ACCOUNT;
    
  • To display the object parameters for a specific object, use an IN clause with the object type and name. For example:

    SHOW PARAMETERS IN DATABASE mydb;
    

The command also supports using a LIKE clause to limit the list of parameters by name. For example:

  • To display the session parameters whose names contain “time”:

    SHOW PARAMETERS LIKE '%time%';
    
  • To display all the parameters whose names start with “time”:

    SHOW PARAMETERS LIKE 'time%' in account;
    

Note that the LIKE clause must come before the IN clause.

List of Parameters

This section provides descriptions and details for all parameter types (Account, Object, and Session), listed in alphabetical order by parameter name.

ABORT_DETACHED_QUERY

Type:

Session — Can be set for Account ▶ User ▶ Session

Data Type:

Boolean

Description:

Specifies the action that Snowflake performs for in-progress queries if connectivity is lost due to abrupt termination of a session (e.g. network outage, browser termination, service interruption). Possible values are:

  • TRUE: In-progress queries are aborted.
  • FALSE: In-progress queries are completed. Note that queries that utilize compute resources require a warehouse, which consumes credits while it is running. Setting this parameter to FALSE may result in the warehouse to consume additional credits to complete any in-progress queries after the session terminates.
Default:

FALSE

AUTOCOMMIT

Type:

Session — Can be set for Account ▶ User ▶ Session

Data Type:

Boolean

Description:

Specifies whether autocommit is enabled for the session. Autocommit determines whether a DML statement, when executed without an active transaction, is automatically committed after the statement successfully completes:

  • TRUE: Autocommit is enabled.
  • FALSE: Autocommit is disabled. DML statements must be explicitly committed or rolled back.
Default:

TRUE

AUTOCOMMIT_API_SUPPORTED (View-only)

Type:

N/A

Data Type:

Boolean

Description:

View-only parameter that indicates whether API support for autocommit is enabled for your account. If the value is TRUE, you can enable or disable autocommit using the APIs for the following drivers/connectors:

Default:

TRUE

BINARY_INPUT_FORMAT

Type:Session — Can be set for Account ▶ User ▶ Session
Data Type:String
Description:Input format for binary values. Supported values: HEX, BASE64, or UTF-8 (or UTF8).
Default:HEX

BINARY_OUTPUT_FORMAT

Type:Session — Can be set for Account ▶ User ▶ Session
Data Type:String
Description:Display format for binary values. Supported values: HEX, BASE64, or UTF-8 (or UTF8).
Default:HEX

CLIENT_ENCRYPTION_KEY_SIZE

Type:

Account — Can be set only for Account

Data Type:

Integer

Description:

Specifies the AES encryption key size, in bits (128 or 256), used by Snowflake to encrypt/decrypt files stored in internal stages (for loading/unloading data).

Note:

  • This parameter is not used for encrypting/decrypting files stored in external stages (i.e. S3 buckets). Encryption/decryption of these files is accomplished using an external encryption key explicitly specified in the COPY command or in the named external stage referenced in the command.
  • If you are using the JDBC driver (or sfsql) and you wish to set this parameter to 256 (for strong encryption), additional JCE policy files must be installed on each client machine from which data is loaded/unloaded. For more information about installing the required files, see Java Requirements for the JDBC Driver.
  • If you are using the Python connector (or SnowSQL) and you wish to set this parameter to 256 (for strong encryption), no additional installation or configuration tasks are required.
Default:

128

CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX

Type:

Session — Can be set for User ▶ Session

Data Type:

Boolean

Description:

Parameter that determines how the JDBC driver retrieves database metadata using any of the following methods in the DatabaseMetadata class:

  • getTable
  • getColumn
  • getPrimaryKey
  • getForeignKeys
  • getImportedKeys
  • getExportedKeys
  • getCrossReference
  • getSchemas
  • getFunctions

The catalogName and schemaPattern arguments for these methods can be used to specify the database and schema for which metadata is retrieved; however, if the methods are passed NULL values for these arguments, the JDBC driver retrieves metadata for all the databases and schemas in the account. This can negatively impact performance when there are a significantly large number of databases/schemas.

If the value is FALSE, the driver does not use the connection context information when retrieving database metadata. If the catalogName and schemaPattern arguments for the method are NULL, the driver retrieves metadata for all databases and schemas in the account. This can negatively impact driver performance if there are a large number of databases/schemas.

If the value is TRUE, and if the catalogName and schemaPattern arguments for the method are NULL, the driver uses the database and schema the connection is on, if any, to retrieve metadata for only that database and schema.

Note

The parameter has no effect if catalogName and schemaPattern are passed with non-NULL values in the method call; i.e. the parameter is only used if these arguments are passed with NULL values.

Default:

FALSE

CLIENT_SESSION_KEEP_ALIVE

Type:

Session — Can be set for Account ▶ User ▶ Session

Data Type:

Boolean

Description:

Parameter that indicates whether to force a user to log in again after a period of inactivity in a JDBC or ODBC session. If the value is TRUE, Snowflake keeps the session active indefinitely, even if there is no activity from the user. If the value is FALSE, the user must log in again after four hours of inactivity.

Note

Currently, the parameter cannot be set at the session level by executing the ALTER SESSION command. For information on setting the parameter at the session level, see the JDBC or ODBC documentation.

Default:FALSE

CLIENT_TIMESTAMP_TYPE_MAPPING

Type:

Session — Can be set for Account ▶ User ▶ Session

Data Type:

String

Description:

Specifies the TIMESTAMP_* variation to use when binding timestamp variables for JDBC or ODBC applications that use the bind API to load data.

  • TIMESTAMP_LTZ
  • TIMESTAMP_NTZ
Default:

TIMESTAMP_LTZ

DATA_RETENTION_TIME_IN_DAYS

Type:

Object (for databases, schemas, and tables)

Can be set for Account ▶ Database ▶ Schema ▶ Table

Data Type:

Number

Description:

Number of days for which Snowflake retains historical data for performing Time Travel actions (SELECT, CLONE, UNDROP) on the object:

  • For Standard Edition accounts, this parameter can be set to either 1 or 0.
  • For Enterprise Edition accounts, this parameter can be changed to any value from 0 up to 90 days. The value specified defaults to databases, schemas, and permanent tables, and can be overridden for each.

A value of 0 for a database, schema, or table effectively disables Time Travel for the specified object.

For more information, see Understanding & Using Time Travel.

Default:

1

DATE_INPUT_FORMAT

Type:Session — Can be set for Account ▶ User ▶ Session
Data Type:String
Description:Input format for dates or AUTO. AUTO specifies that Snowflake attempts to automatically detect the format of dates stored in the system during the session. For more information about date and time input and output formats, see Date and Time Input / Output.
Default:AUTO

DATE_OUTPUT_FORMAT

Type:Session — Can be set for Account ▶ User ▶ Session
Data Type:String
Description:Display format for date. For more information about date and time input and output formats, see Date and Time Input / Output.
Default:YYYY-MM-DD

ERROR_ON_NONDETERMINISTIC_MERGE

Type:

Session — Can be set for Account ▶ User ▶ Session

Data Type:

Boolean

Description:

Specifies whether to return an error when the MERGE command is used to update or delete a target row that joins multiple source rows and the system cannot determine the action to perform on the target row. Possible values are:

  • TRUE: An error is returned that includes values from one of the target rows that caused the error.
  • FALSE: No error is returned and the merge completes successfully, but the results of the merge are nondeterministic.
Default:

TRUE

ERROR_ON_NONDETERMINISTIC_UPDATE

Type:

Session — Can be set for Account ▶ User ▶ Session

Data Type:

Boolean

Description:

Specifies whether to return an error when the UPDATE command is used to update a target row that joins multiple source rows and the system cannot determine the action to perform on the target row. Possible values:

  • TRUE: An error is returned that includes values from one of the target rows that caused the error.
  • FALSE: No error is returned and the update completes, but the results of the update are nondeterministic.
Default:

FALSE

JDBC_TREAT_DECIMAL_AS_INT

Type:Session — Can be set for Account ▶ User ▶ Session
Data Type:Boolean
Description:If the value is TRUE, JDBC treats a column whose scale is zero as BIGINT instead of DECIMAL.
Default:True

JSON_INDENT

Type:

Session — Can be set for Account ▶ User ▶ Session

Data Type:

Integer

Description:

Specifies the number of blank spaces (from 0 to 16) to indent each new element in JSON output in the session. Also specifies whether to insert newline characters after each element. A value of 0 returns compact output by removing all blank spaces and newline characters from the output.

Note

This parameter does not affect JSON unloaded from a table into a file using the COPY INTO location command. The command always unloads JSON data in the NDJSON format:

  • Each record from the table separated by a newline character.
  • Within each record, compact formatting (i.e. no spaces or newline characters).
Default:

2

LOCK_TIMEOUT

Type:Session — Can be set for Account ▶ User ▶ Session
Data Type:Number
Description:Number of seconds to wait while trying to lock a resource, before timing out and aborting the statement. A value of 0 disables lock waiting, i.e. the statement must acquire the lock immediately or abort. If multiple resources need to be locked by the statement, the timeout applies separately to each lock attempt.
Default:21600

MAX_CONCURRENCY_LEVEL

Type:

Object (for warehouses) — Can be set for Account ▶ Warehouse

Data Type:

Number

Description:

Specifies the maximum number of SQL statements (queries, DDL, DML, etc.) a warehouse cluster can execute concurrently. When the max level is reached:

  • For a single-cluster warehouse or a multi-cluster warehouse (in Maximized mode), additional statements are queued until resources are available.
  • For a multi-cluster warehouse (in Auto-scale mode), additional clusters are started.

Reducing the concurrency level for a warehouse can help query performance because it increases the amount of resources available for the SQL statements that are executing, allowing them to run faster. This parameter can be used in conjunction with the STATEMENT_QUEUED_TIMEOUT_IN_SECONDS parameter to ensure a warehouse is never backlogged.

Note that MAX_CONCURRENCY_LEVEL serves as an upper-boundary for concurrency rather than an absolute limit. The actual number of statements executing may be more or less than the specified max level, due to resource availability:

  • With large, complex statements, fewer statements than the max level may execute concurrently. As each statement is submitted to a warehouse, Snowflake ensures that resources are not over-allocated; if there aren’t enough resources available to execute a statement, it is automatically queued.
  • With small, simple statements, more statements than the max level may execute concurrently. Small statements count as a fraction towards the concurrency level because these statements generally execute on a subset of the warehouse servers.

Due to the automatic resource allocation and queuing that Snowflake performs on all statements as they are submitted, we recommend keeping the default value for this parameter or adjusting it in small increments based on your query usage and performance.

Note

As described, MAX_CONCURRENCY_LEVEL has an impact on multi-cluster warehouses (in Auto-scale mode) because Snowflake automatically starts clusters to avoid queuing SQL statements. Setting a lower level of concurrency for an Auto-scale multi-cluster warehouse can potentially increase the number of active clusters at any given time.

For example, a value of 4 means that, at most, 4 SQL statements can execute simultaneously on a single warehouse cluster. As more statements are submitted, additional clusters are started.

Default:

8

NETWORK_POLICY

Type:

Account — Can be set only for Account

Data Type:

String

Description:

Supports restricting access to your account based on user IP address. References a network policy created using CREATE NETWORK POLICY. Note that NETWORK_POLICY is the only account parameter that can be set by both account administrators and security administrators.

For more information about network policies, see Managing Network Policies.

Default:

None

PERIODIC_DATA_REKEYING

Type:

Account — Can be set only for Account

Data Type:

Boolean

Description:

Enables/disables re-encryption of table data with new keys on a yearly basis to provide additional data protection:

  • TRUE specifies that data is rekeyed after one year has passed since the data was last encrypted. Rekeying occurs in the background so no down-time is experienced and the affected data/table is always available.
  • FALSE specifies that data is not rekeyed.

You can enable and disable rekeying at any time. Enabling/disabling rekeying does not result in gaps in your encrypted data:

  • If rekeying is enabled for a period of time and then disabled, all data already tagged for rekeying is rekeyed, but no further data is rekeyed until you renable it again.
  • If rekeying is re-enabled, Snowflake automatically rekeys all data that has keys which meet the criteria (i.e. key is older than one year).

Note

There are charges associated with data rekeying because, after data is rekeyed, the old data (with the previous key encryption) is maintained in Fail-safe for the standard time period (7 days). Fail-safe charges for rekeying are not listed individually in your monthly statement; they are included in the Fail-safe total for your account each month.

For more information about Fail-safe, see Understanding & Viewing Fail-safe.

Default:

FALSE

QUERY_TAG

Type:Session — Can be set for Account ▶ User ▶ Session
Data Type:String (up to 2000 characters)
Description:Optional string that can be used to tag queries and other SQL statements executed within a session. The tags are displayed in the output of the QUERY_HISTORY , QUERY_HISTORY_BY_* functions.
Default:None

QUOTED_IDENTIFIERS_IGNORE_CASE

Type:

Session — Can be set for Account ▶ User ▶ Session

Data Type:

Boolean

Description:
  • TRUE specifies that the case of letters is ignored for all object identifiers in double quotes. Specifically, all letters are treated as uppercase; e.g. "columnName", "ColumnName", and "COLUMNNAME" are equivalent. They all resolve to "COLUMNNAME".
  • FALSE specifies that double-quoted identifiers are case-sensitive. Snowflake resolves and stores the identifiers in the specified case; e.g. "columnName", "ColumnName", and "COLUMNNAME" are all different values.

For more information, see Identifier Resolution.

Default:

False

ROWS_PER_RESULTSET

Type:Session — Can be set for Account ▶ User ▶ Session
Data Type:Number
Description:Maximum number of rows in a result set. A value of 0 specifies no maximum.
Default:0

SAML_IDENTITY_PROVIDER

Type:

Account — Can be set only for Account

Data Type:

JSON

Description:

Enables federated authentication. The parameter parameter accepts a JSON object, enclosed in single quotes, with the following fields:

{
  "certificate": "",
  "ssoUrl": "",
  "type"  : "",
  "label" : ""
}

Where:

certificate

Specifies the certificate (generated by the IdP) that verifies communication between the IdP and Snowflake.

ssoUrl

Specifies the URL endpoint (provided by the IdP) where Snowflake sends the SAML requests.

type

Specifies the type of IdP used for federated authentication ("OKTA" , "ADFS" , "Custom").

label

Specifies the button text for the IdP in the Snowflake login page. The default label is Single Sign On. If you change the default label, the label you specify can only contain alphanumeric characters (i.e. special characters and blank spaces are not currently supported).

Note that, if the "type" field is "Okta", a value for the label field does not need to be specified because Snowflake displays the Okta logo in the button.

For more information, including examples of setting the parameter, see Configuring Snowflake to Use Federated Authentication.

Default:

None

SIMULATED_DATA_SHARING_CONSUMER

Type:

Session — Can be set for Account ▶ User ▶ Session

Data Type:

String

Description:

Specifies the name of a consumer account to simulate for testing/validating shared data, particularly secure views. When this parameter is set in a session, Data Sharing views will return rows as if executed in the specified consumer account.

For more information, see Introduction to Data Sharing and Providers — Sharing Databases.

Note

This is a session parameter so it can be set at the account level, but it only applies to testing views for Data Sharing and it impacts all queries in a session. Therefore, it should never be set at the account level.

Default:

None

SSO_LOGIN_PAGE

Type:

Account — Can be set only for Account

Data Type:

Boolean

Description:

Disables preview mode for testing SSO (after enabling federated authentication) before rolling it out to users:

  • If TRUE, preview mode is disabled and users will see the button for Snowflake-initiated SSO for your identity provider (as specified in SAML_IDENTITY_PROVIDER) in the Snowflake main login page.
  • If FALSE, preview mode is enabled and SSO can be tested using the following URL:
    • If your account is in US West: https://<account_name>.snowflakecomputing.com/console/login?fedpreview=true
    • If your account is in any other Snowflake Region: https://<account_name>.<region_id>.snowflakecomputing.com/console/login?fedpreview=true

For more information, see Configuring Snowflake to Use Federated Authentication.

Default:

FALSE

STATEMENT_QUEUED_TIMEOUT_IN_SECONDS

Type:

Session and Object (for warehouses)

Can be set for for Account ▶ User ▶ Session; can also be set for individual warehouses

Data Type:

Number

Description:

Maximum amount of time, in seconds, a SQL statement (query, DDL, DML, etc.) can be queued on a warehouse before it is canceled by the system. A value of 0 specifies that no timeout is enforced. This parameter can be used in conjunction with the MAX_CONCURRENCY_LEVEL parameter to ensure a warehouse is never backlogged.

The parameter can be set within the session hierarchy. It can also be set for a warehouse to control the queue timeout for all SQL statements processed by the warehouse. When the parameter is set for both a warehouse and a session, the lowest non-zero value is enforced.

For example, suppose a warehouse has a queued timeout of 120 seconds and the current user session has a queued timeout of 60 seconds. Any statement queued for longer than 60 seconds in the session is canceled.

Default:

0 (i.e. no timeout)

STATEMENT_TIMEOUT_IN_SECONDS

Type:

Session and Object (for warehouses)

Can be set for for Account ▶ User ▶ Session; can also be set for individual warehouses

Data Type:

Number

Description:

Time, in seconds, after which a running SQL statement (query, DDL, DML, etc.) is canceled by the system. A value of 0 specifies that no timeout is enforced.

The parameter can be set within the session hierarchy. It can also be set for a warehouse to control the runtime for all SQL statements processed by the warehouse. When the parameter is set for both a warehouse and a session, the lowest non-zero value is enforced.

For example, a warehouse has a timeout of 1000 seconds and the current user session has a timeout of 500 seconds. Any statement that executes for longer than 500 seconds in the session is canceled.

Default:

0 (i.e. no timeout)

STRICT_JSON_OUTPUT

Type:

Session — Can be set for Account ▶ User ▶ Session

Data Type:

Boolean

Description:

This parameter specifies whether JSON output in a session is compatible with the general standard (as described by http://json.org).

By design, Snowflake allows JSON input that contains non-standard values; however, these non-standard values may result in Snowflake outputting JSON that is incompatible with other platforms and languages. This parameter, when enabled, ensures that Snowflake outputs valid/compatible JSON:

  • Missing and undefined values in input mapped to JSON NULL.
  • Non-finite numeric values in input (Infinity, -Infinity, NaN, etc.) mapped to strings with valid JavaScript representations. This enables compatibility with JavaScript and also allows conversion of these values back to numeric values.

For example:

Non-standard Input Output if STRICT_JSON_OUTPUT = FALSE (Default) Output if STRICT_JSON_OUTPUT = TRUE
[289, 2188,] [ 289, 2188, undefined ] [ 289, 2188, null ]
[undefined, undefined] [ undefined, undefined ] [ null, null ]
[Infinity,inf,-Infinity,-inf] [ Infinity, Infinity, -Infinity, -Infinity ] [ "Infinity", "Infinity", "-Infinity", "-Infinity" ]
[NaN,nan] [ NaN, NaN ] [ "NaN", "NaN" ]
Default:

FALSE

TIMESTAMP_DAY_IS_ALWAYS_24H

Type:Session — Can be set for Account ▶ User ▶ Session
Data Type:Boolean
Description:If TRUE, the DATEADD function (and its aliases) always consider a day to be exactly 24 for expressions that span multiple days. This might not preserve the actual time of day if Daylight Saving Time (DST) is in effect.
Default:FALSE

TIMESTAMP_INPUT_FORMAT

Type:Session — Can be set for Account ▶ User ▶ Session
Data Type:String
Description:Input format for timestamps or AUTO. AUTO specifies that Snowflake attempts to automatically detect the format of timestamps stored in the system during the session. For more information about date and time input and output formats, see Date and Time Input / Output.
Default:AUTO

TIMESTAMP_LTZ_OUTPUT_FORMAT

Type:Session — Can be set for Account ▶ User ▶ Session
Data Type:String
Description:Display format for TIMESTAMP_LTZ. If no format is specified, defaults to TIMESTAMP_OUTPUT_FORMAT. For more information about date and time input and output formats, see Date and Time Input / Output.
Default:None

TIMESTAMP_NTZ_OUTPUT_FORMAT

Type:Session — Can be set for Account ▶ User ▶ Session
Data Type:String
Description:Display format for TIMESTAMP_NTZ. If no format is specified, defaults to TIMESTAMP_OUTPUT_FORMAT. For more information about date and time input and output formats, see Date and Time Input / Output.
Default:YYYY-MM-DD HH24:MI:SS.FF3

TIMESTAMP_OUTPUT_FORMAT

Type:Session — Can be set for Account ▶ User ▶ Session
Data Type:String
Description:Display format for TIMESTAMP. For more information about date and time input and output formats, see Date and Time Input / Output.
Default:YYYY-MM-DD HH24:MI:SS.FF3 TZHTZM

TIMESTAMP_TYPE_MAPPING

Type:

Session — Can be set for Account ▶ User ▶ Session

Data Type:

String

Description:

Specifies the TIMESTAMP_* variation that the TIMESTAMP data type alias maps to:

  • TIMESTAMP_LTZ
  • TIMESTAMP_NTZ
  • TIMESTAMP_TZ
Default:

TIMESTAMP_NTZ

TIMESTAMP_TZ_OUTPUT_FORMAT

Type:Session — Can be set for Account ▶ User ▶ Session
Data Type:String
Description:Display format for TIMESTAMP_TZ. If no format is specified, defaults to TIMESTAMP_OUTPUT_FORMAT. For more information about date and time input and output formats, see Date and Time Input / Output.
Default:None

TIMEZONE

Type:

Session — Can be set for Account ▶ User ▶ Session

Data Type:

String

Description:

Specifies the time zone for the session:

  • Snowflake supports standard iana.org time zones: America/Los_Angeles, Europe/London, UTC, Etc/GMT, etc. However, certain simple time zones, such as PDT, are not currently supported.

    For a list of time zones, see tz database time zones.

  • Time zone names are case-sensitive and must be enclosed in single quotes.

Default:

America/Los_Angeles

TIME_INPUT_FORMAT

Type:Session — Can be set for Account ▶ User ▶ Session
Data Type:String
Description:Input format for times or AUTO. AUTO specifies that Snowflake attempts to automatically detect the format of times stored in the system during the session. For more information about date and time input and output formats, see Date and Time Input / Output.
Default:AUTO

TIME_OUTPUT_FORMAT

Type:Session — Can be set for Account ▶ User ▶ Session
Data Type:String
Description:Display format for TIME. For more information about date and time input and output formats, see Date and Time Input / Output.
Default:HH24:MI:SS

TRANSACTION_ABORT_ON_ERROR

Type:Session — Can be set for Account ▶ User ▶ Session
Data Type:BOOLEAN
Description:If this parameter is TRUE, and a statement issued within a non-autocommit transaction returns with an error, then the non-autocommit transaction is aborted. All statements issued inside that transaction will fail until a commit or rollback statement is executed to close that transaction.
Default:FALSE

TRANSACTION_DEFAULT_ISOLATION_LEVEL

Type:

Session — Can be set for Account ▶ User ▶ Session

Data Type:

String

Description:

Specifies the isolation level for transactions in the user session. Possible values are:

READ_COMMITTED (only currently-supported value)
Default:

READ_COMMITTED

TWO_DIGIT_CENTURY_START

Type:

Session — Can be set for Account ▶ User ▶ Session

Data Type:

Number

Description:

Specifies the “century start” year for 2-digit years, i.e. the earliest year such dates can represent. This parameter prevents ambiguous dates when importing or converting data with the YY date format component used for years represented as 2 digits.

For example, if the parameter is set to:

  1900 1970 1980 1990 2000
00 becomes: 1900 2000 2000 2000 2000
79 becomes: 1979 1979 2079 2079 2079
89 becomes: 1989 1989 1989 2089 2089
99 becomes: 1999 1999 1999 1999 2099

The valid range of values is 1900 to 2100. Specifying a value outside of this range returns an error.

Default:

1970

UNSUPPORTED_DDL_ACTION

Type:

Session — Can be set for Account ▶ User ▶ Session

Data Type:

String

Description:

Specifies whether an unsupported (i.e. non-default) value specified for a constraint property returns an error. Possible values are:

  • IGNORE: Snowflake does not return an error for unsupported values.
  • FAIL: Snowflake returns an error for unsupported values.

Note that this parameter does not determine whether the constraint is created. Snowflake does not create constraints using unsupported values, regardless of how this parameter is set.

For more information, see Constraint Properties.

Default:

IGNORE

USE_CACHED_RESULT

Type:

Session — Can be set for Account ▶ User ▶ Session

Data Type:

Boolean

Description:

Specifies whether to reuse cached query results, if available, when a matching query is submitted. Possible values are:

  • TRUE: When a query is submitted, Snowflake checks for matching query results for previously-executed queries and, if a matching result exists, uses the result instead of executing the query. This can help reduce query time because Snowflake retrieves the result directly from the cache.
  • FALSE: Snowflake executes each query when submitted, regardless of whether a matching query result exists.
Default:

TRUE

WEEK_START

Type:

Session — Can be set for Account ▶ User ▶ Session

Data Type:

Number

Description:

Specifies the first day of the week (used by week-related date functions). Possible values are 0 or 1 to 7:

  • If set to 0, the legacy Snowflake behavior is used (i.e. ISO-like semantics).
  • If set to a value from 1 (Monday) to 7 (Sunday), all the week-related functions use weeks that start on the specified day of the week. 1 is the most common value, based on feedback we’ve received.

For more information, including examples, see Calendar Weeks and Weekdays.

Default:

0 (legacy Snowflake behavior)

WEEK_OF_YEAR_POLICY

Type:

Session — Can be set for Account ▶ User ▶ Session

Data Type:

Number

Description:

Specifies how the weeks in a given year are computed. Possible values are 0 or 1:

  • If set to 0, the semantics used are equivalent to the ISO semantics, in which a week belongs to a given year if at least 4 days of that week are in that year.
  • If set to 1, January 1 is included in the first week of the year and December 31 is included in the last week of the year. This is the most common value, based on feedback we’ve received.

For more information, including examples, see Calendar Weeks and Weekdays.

Default:

0 (ISO-like behavior)