1.2 Snowflake Parameter Hierarchy & Configuration

1.2 Snowflake Parameter Hierarchy & Configuration

Snowflake provides a comprehensive parameter configuration subsystem that controls database behavior, query timeouts, transaction semantics, data retention, and security boundaries. As an enterprise architect, you must understand how parameters cascade through the three-tier parameter hierarchy, how inheritance rules resolve conflicting definitions, and how to tune critical operational parameters to prevent financial waste and performance degradation.

---

The Parameter Hierarchy

Snowflake documents three types of parameters — account, session, and object — and each type resolves along its own chain:

• Session parameters (for example AUTOCOMMIT, TIMEZONE, ABORT_DETACHED_QUERY): Account » User » Session.
• Object parameters (for example DATA_RETENTION_TIME_IN_DAYS): Account » Database » Schema » Table; others are set on warehouses or users.
• Account parameters (for example NETWORK_POLICY, PERIODIC_DATA_REKEYING): set only at the account level.

The simplified picture below shows how the levels relate:

+-------------------------------------------------------------------------+
|                              ACCOUNT LEVEL                              |
|         Global baselines set via ALTER ACCOUNT SET <parameter>          |
+-------------------------------------------------------------------------+
                                     │
                                     ▼
+-------------------------------------------------------------------------+
|                              OBJECT LEVEL                               |
|   User | Warehouse | Database  ──►  Schema  ──►  Table / View           |
+-------------------------------------------------------------------------+
                                     │
                                     ▼
+-------------------------------------------------------------------------+
|                              SESSION LEVEL                              |
|       Active connection overrides set via ALTER SESSION SET ...         |
+-------------------------------------------------------------------------+

1. Account-Level Parameters

Account-level parameters establish organization-wide default baselines across an entire Snowflake account. Only the ACCOUNTADMIN system role (or a role granted explicit administrative privileges) can set account-level parameters using ALTER ACCOUNT SET ....

2. Object-Level Parameters

Object-level parameters attach directly to first-class Snowflake securable objects, including:

• User Level: Governs behaviors specific to a user identity across all sessions they initiate (e.g., default warehouse, user statement timeouts).
• Warehouse Level: Controls execution parameters for queries running on that specific virtual warehouse (e.g., statement execution timeout, queued query timeout).
• Database Level: Establishes defaults for all schemas and tables contained within the database (e.g., data retention time, default DDL collation).
• Schema Level: Overrides database settings and sets defaults for contained tables and views.
• Table Level: Defines table-specific properties (e.g., table-specific Time Travel data retention).

3. Session-Level Parameters

Session-level parameters apply strictly to the currently active user session or connection. They can be set dynamically by client applications, BI tools, or individual users via ALTER SESSION SET .... Session parameters remain in effect only until the session terminates or the parameter is explicitly reset. Within the session chain, a session value overrides a user value, and a user value overrides the account default.

---

Parameter Precedence and Inheritance Rules

When a query executes, Snowflake resolves conflicting parameter configurations by evaluating specificity: the most specific level overrides the broader level.

The Fundamental Precedence Cascade

Within each chain, the most specific level that has a value wins: $\text{Session} \succ \text{User} \succ \text{Account}$ Object parameters follow the container chain instead (next subsection).

Granular Object Inheritance Chains

For storage and structural parameters: $\text{Table} \succ \text{Schema} \succ \text{Database} \succ \text{Account}$

• If DATA_RETENTION_TIME_IN_DAYS is set to 30 at the Database level, any new schema created in that database inherits 30 days.
• If a specific schema is created with DATA_RETENTION_TIME_IN_DAYS = 10, all tables inside that schema inherit 10 days.
• If a specific table inside that schema is altered to DATA_RETENTION_TIME_IN_DAYS = 1, the table's setting of 1 day overrides the schema (10), database (30), and account defaults.

Timeout Precedence: The Lowest Non-Zero Value Wins

STATEMENT_TIMEOUT_IN_SECONDS and STATEMENT_QUEUED_TIMEOUT_IN_SECONDS are special: they can be set in the session hierarchy (Account » User » Session) and on individual warehouses. Snowflake resolves them in two steps:

1. Resolve the session hierarchy normally — a session value overrides the user value, which overrides the account value.
2. Compare that result with the warehouse's value. The timeout that applies is the lowest non-zero value of the two.

Example: account 7,200, user 3,600, session 1,200, warehouse 900 → the session chain yields 1,200, and the enforced timeout is 900 because it is lower. A user cannot raise a warehouse's timeout with ALTER SESSION; they can only lower it.

Critical Architect Parameters & Operational Tuning

Enterprise architects must master the behavior, default values, and operational risks of the following key parameters:

1. AUTOCOMMIT

• Default Value: TRUE
• Valid Levels: Account, User, Session
• Mechanics: When TRUE, each individual DML statement (INSERT, UPDATE, DELETE, MERGE) executes inside its own implicit transaction and commits immediately upon completion.
• When set to FALSE, transactions remain open across multi-statement sequences until an explicit COMMIT or ROLLBACK is executed.

CRITICAL EXAM TRAP: In Snowflake, all Data Definition Language (DDL) statements (such as CREATE TABLE, ALTER WAREHOUSE, DROP SCHEMA) are non-transactional and implicitly commit any active transaction! If a script begins an explicit multi-statement DML transaction with AUTOCOMMIT = FALSE, and subsequently runs a DDL command (such as creating a temporary staging table), the pending DML transaction is immediately committed to the database, rendering subsequent ROLLBACK commands completely ineffective.

-- Autocommit behavior demonstration
ALTER SESSION SET AUTOCOMMIT = FALSE;

INSERT INTO accounts_payable VALUES (101, 'Vendor A', 50000.00);
UPDATE general_ledger SET balance = balance - 50000.00 WHERE account_id = 4001;

-- The following DDL statement implicitly COMMITS the INSERT and UPDATE above!
CREATE TEMPORARY TABLE batch_audit (step VARCHAR, logged_at TIMESTAMP);

-- This ROLLBACK has NO EFFECT on the INSERT or UPDATE;
-- both changes are already permanently committed to micro-partitions!
ROLLBACK;

2. STATEMENT_TIMEOUT_IN_SECONDS

• Default Value: 172800 seconds (exactly 48 hours / 2 days)
• Valid Levels: Account, User, Session, and individual warehouses (lowest non-zero value of the session chain and warehouse applies)
• Architectural Purpose: Caps the total time a statement may take — including queued, locked, compilation, and execution time — before Snowflake cancels it.
• Production Best Practice: The 48-hour default represents an extreme financial liability. A runaway Cartesian cross-join on a 4X-Large warehouse (128 credits/hour) could burn over 6,100 credits (~$18,000+) before terminating. Architects should aggressively tune this parameter by workload:
  • Account baseline: 7200 seconds (2 hours)
  • Ad-hoc / BI Warehouses: 1800 seconds (30 minutes)
  • High-throughput Micro-batch Warehouses: 300 seconds (5 minutes)
  • Heavy Nightly ELT Warehouses: 14400 seconds (4 hours)

3. STATEMENT_QUEUED_TIMEOUT_IN_SECONDS

• Default Value: 0 (queries queue indefinitely until warehouse resources free up)
• Valid Levels: Account, User, Session, and individual warehouses (lowest non-zero value applies)
• Architectural Purpose: Determines how long a query can remain in the warehouse queue waiting for compute resources before being aborted with an error.
• Production Best Practice: In high-concurrency reporting workloads, stale queries waiting in a queue degrade downstream pipelines. Setting this parameter to 300 (5 minutes) ensures queued queries fail fast, prompting multi-cluster warehouse auto-scaling or notifying client applications.

4. DATA_RETENTION_TIME_IN_DAYS

• Default Value: 1 day
• Valid Levels: Account, Database, Schema, Table
• Architectural Purpose: Controls the Time Travel historical data retention window.
• Edition Constraints:
  • Standard Edition: Maximum of 1 day (values can be 0 or 1).
  • Enterprise Edition and higher: 0 to 90 days for permanent objects; maximum 1 day for transient and temporary objects.
  • Setting to 0 effectively disables Time Travel for that object; it does not remove the 7-day Fail-safe period for permanent tables.
  • MIN_DATA_RETENTION_TIME_IN_DAYS (account-level, ACCOUNTADMIN only) sets a floor: the effective retention of a permanent table is the higher of the two parameters.

5. DEFAULT_DDL_COLLATION

• Default Value: NULL (binary collation; case-sensitive, byte-order comparison)
• Valid Levels: Account, Database, Schema, Table
• Architectural Purpose: Specifies the default string collation specification for newly created VARCHAR / STRING columns (e.g., 'en-ci' for English case-insensitive).
• Architectural Impact: Collation changes comparison and sorting semantics for every new string column, so set it deliberately and test query behavior and performance before applying it at the database or account level.

6. ABORT_DETACHED_QUERY

• Default Value: FALSE
• Valid Levels: Account, User, Session (it is not a warehouse parameter)
• Architectural Purpose: Controls what happens to in-progress queries when connectivity is lost because a session ends abruptly (network outage, browser closed, client crash).
• When FALSE (default), in-progress queries run to completion, which can waste warehouse credits.
• When TRUE, in-progress queries are aborted 5 minutes after connectivity is lost.
• Note: a client that explicitly logs out of its session is different — queries still running in a logged-out session are cancelled after a couple of minutes even when this parameter is FALSE.

Architectural Parameter Summary Table

---

Auditing Parameter Changes & Configuration Drift

Enterprise governance mandates verifying effective parameter values and tracking historical configuration alterations to prevent drift.

1. Point-in-Time Parameter Inspection

The SHOW PARAMETERS command inspects active parameters and indicates the precise level at which the value was established (ACCOUNT, DATABASE, SCHEMA, TABLE, WAREHOUSE, USER, or SESSION).

-- Check session-effective parameters
SHOW PARAMETERS LIKE 'STATEMENT_TIMEOUT%' IN SESSION;

-- Check warehouse-specific timeout overrides
SHOW PARAMETERS LIKE 'STATEMENT_TIMEOUT%' IN WAREHOUSE etl_wh;

-- Check storage retention cascading to a table
SHOW PARAMETERS LIKE 'DATA_RETENTION%' IN TABLE core_db.finance.general_ledger;

2. Historical Parameter Audit via QUERY_HISTORY

ACCOUNT_USAGE has no dedicated parameter-change view. To reconstruct who changed a setting and when, search the statements themselves in SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY (retained for one year), and use SHOW PARAMETERS to confirm the current effective value:

-- Find parameter changes made with ALTER ... SET over the past 90 days
USE ROLE ACCOUNTADMIN;

SELECT start_time, user_name, role_name, query_text
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD('day', -90, CURRENT_TIMESTAMP())
  AND query_type LIKE 'ALTER%'
  AND (query_text ILIKE '%STATEMENT_TIMEOUT_IN_SECONDS%'
       OR query_text ILIKE '%ABORT_DETACHED_QUERY%'
       OR query_text ILIKE '%AUTOCOMMIT%')
ORDER BY start_time DESC;

For declarative control, many teams manage account and warehouse parameters as code (Terraform or deployment scripts in Git) so drift is visible in version control.