Account-Level Parameters
Snowflake's behavior can be customized using Parameters. Parameters control everything from how long a query is allowed to run before timing out, to the default timezone, to the network policies securing the account.
While parameters can be set at various levels (Account, User, Session, Warehouse, Object), Account-Level Parameters serve as the global defaults for the entire Snowflake environment.
Topics Covered
- Setting and Querying Parameters
- Parameter Precedence Hierarchy
- Key Account-Level Parameters
- Best Practices for Parameter Governance
Setting and Querying Parameters
To view the parameters currently configured on your account, you use the SHOW PARAMETERS command.
(To alter account parameters, you must be using the ACCOUNTADMIN role. To check your current user and role, execute SELECT CURRENT_USER(); and SELECT CURRENT_ROLE();)
1. Database Setup for the Lab
Let's set up a scratchpad database for our experiments.
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_CONFIG_DB;
USE DATABASE BEEMS_CONFIG_DB;
CREATE SCHEMA IF NOT EXISTS PARAM_LAB;
USE SCHEMA PARAM_LAB;
2. Viewing Parameters
USE ROLE ACCOUNTADMIN;
-- View all parameters at the account level
SHOW PARAMETERS IN ACCOUNT;
-- View a specific parameter (e.g., Timezone)
SHOW PARAMETERS LIKE 'TIMEZONE' IN ACCOUNT;
3. Altering Parameters
Let's assume our data engineering team in India prefers to see all timestamps in IST (Indian Standard Time) by default, rather than the Snowflake default of UTC (America/Los_Angeles).
-- Change the global default timezone to Asia/Kolkata
ALTER ACCOUNT SET TIMEZONE = 'Asia/Kolkata';
-- Verify the change
SHOW PARAMETERS LIKE 'TIMEZONE' IN ACCOUNT;
Parameter Precedence Hierarchy
Snowflake resolves parameters using a strict hierarchy. A parameter set at a lower (more specific) level will override the parameter set at a higher (more global) level.
The hierarchy, from highest precedence (most specific) to lowest precedence (most global), is:
- Session Level (e.g.,
ALTER SESSION SET TIMEZONE = 'UTC') - Object Level (e.g., a specific Database, Schema, or Warehouse)
- User Level (e.g.,
ALTER USER ROHAN_SHARMA SET TIMEZONE = 'UTC') - Account Level (The global default)
Demonstration of Precedence
Let's create a user, Rohan Sharma, who works on a project requiring UTC timestamps, even though the account default is now Asia/Kolkata.
USE ROLE SECURITYADMIN;
-- Create a mock user
CREATE OR REPLACE USER ROHAN_SHARMA
PASSWORD = 'TemporaryPassword123!'
DEFAULT_ROLE = 'SYSADMIN';
-- Override the account parameter at the user level
ALTER USER ROHAN_SHARMA SET TIMEZONE = 'UTC';
When Rohan logs in, his session will use UTC. When any other user logs in (who doesn't have a user-level override), their session will use the account default Asia/Kolkata.
Key Account-Level Parameters
There are dozens of parameters, but these are the most critical for an ACCOUNTADMIN to understand and configure:
1. STATEMENT_TIMEOUT_IN_SECONDS
Controls how long a query can run before Snowflake automatically cancels it.
- Default: 172800 seconds (48 hours).
- Best Practice: Change this! 48 hours is incredibly dangerous. If a user writes a bad Cartesian join, it could burn compute credits for two days. Set the account default to something reasonable, like 3600 seconds (1 hour).
ALTER ACCOUNT SET STATEMENT_TIMEOUT_IN_SECONDS = 3600;
2. STATEMENT_QUEUED_TIMEOUT_IN_SECONDS
Controls how long a query will wait in the queue for a Virtual Warehouse to become available before timing out.
- Default: 0 (Wait forever).
- Best Practice: Set this to 600 (10 minutes) so users don't wait indefinitely if the warehouse is completely saturated.
3. NETWORK_POLICY
The most critical security parameter. It dictates which IP addresses are allowed to reach the Snowflake account.
-- Assuming a policy named 'INDIA_OFFICE_VPN' exists
ALTER ACCOUNT SET NETWORK_POLICY = INDIA_OFFICE_VPN;
4. PREVENT_UNLOAD_TO_INLINE_URL
A data exfiltration prevention parameter. If set to TRUE, it prevents users from using the COPY INTO <location> command to unload data to a raw S3/Azure URL, forcing them to use an Administrator-approved Storage Integration instead.
ALTER ACCOUNT SET PREVENT_UNLOAD_TO_INLINE_URL = TRUE;
Summary
- Account-Level Parameters establish the baseline configuration for your entire Snowflake environment.
- The Precedence Hierarchy dictates that Session > Object > User > Account.
- Always modify
STATEMENT_TIMEOUT_IN_SECONDSto prevent runaway queries from draining your credit balance. - Use
ALTER ACCOUNTandSHOW PARAMETERS IN ACCOUNTto manage these global settings.