Study guide

1.2 Snowflake Parameter Hierarchy & Configuration

SnowPro Advanced: Architect ARA-C01

Track your progress as you read

Finished sections get a check mark.

1 Introduction: The ARA-C01 Exam

About the SnowPro Advanced: Architect (ARA-C01) Exam

2 Chapter 1: Account Strategy & Organization Architecture

1.1 Multi-Account Strategy & Snowflake Organizations 1.2 Snowflake Parameter Hierarchy & Configuration 1.3 Database and Schema Design Architecture

3 Chapter 2: Identity, Access Control & Storage Integrations

2.1 Role-Based Access Control Architecture & Hierarchy 2.2 Secondary Roles, Object Ownership & Delegated Administration 2.3 Cloud Storage Integrations & IAM Trust Policies 2.4 Identity Federation, SSO, SCIM & Authentication Policies

4 Chapter 3: Governance, Data Privacy, Network Security & Compliance

3.1 Column-Level Masking & Row Access Policies 3.2 Object Tagging, Tag-Based Masking & Audit Governance 3.3 Encryption, Key Management & Tri-Secret Secure 3.4 Aggregation Policies, Projection Policies & External Tokenization 3.5 Network Policies, Network Rules, External Access & Private Connectivity 3.6 Snowflake Editions & Compliance Architecture (PCI DSS, PII & PHI)

5 Chapter 4: Data Modeling in Snowflake

4.1 Dimensional Modeling: Star vs. Snowflake Schemas 4.2 Data Vault 2.0 Architecture in Snowflake 4.3 Relational Constraints & Query Optimizer Integration

6 Chapter 5: Data Sharing & Collaboration Solutions

5.1 Direct Data Sharing Architecture & Security 5.2 Managed Reader Accounts for External Consumers 5.3 Snowflake Marketplace, Data Exchange & Clean Rooms

7 Chapter 6: Multi-Cloud, Multi-Region & Business Continuity

6.1 Database & Account Replication Mechanics 6.2 Disaster Recovery Architecture & Client Redirect 6.3 Cross-Cloud Auto-Fulfillment & Listing Replication 6.4 Data Protection: Time Travel, Fail-safe & Storage Costs

8 Chapter 7: Environments, Lakehouse, DevOps & AI/ML Applications

7.1 Multi-Tier Environment Strategy & Zero-Copy Cloning 7.2 Data Lakehouse Architecture & Apache Iceberg 7.3 DataOps, CI/CD & Snowflake Git Integration 7.4 AI/ML Pipelines & Applications: Cortex AI, ML Functions, Snowpark Container Services, Streamlit & Native Apps

9 Chapter 8: Data Ingestion Architecture

8.1 Bulk Ingestion Architecture: COPY INTO Optimization 8.2 Snowpipe Continuous Automated Ingestion 8.3 Snowpipe Streaming & Real-Time Ingestion 8.4 Data Unloading Architecture & Cloud Export Patterns 8.5 Schema Detection, Schema Evolution & Source-Change Architecture

10 Chapter 9: Continuous Pipelines & Stream Transformation

9.1 Change Data Capture (CDC) with Snowflake Streams 9.2 Task Orchestration, DAGs & Serverless Scheduling 9.3 Declarative Data Pipelines with Dynamic Tables 9.4 Views, Materialized Views & Transformation Table Design

11 Chapter 10: Semi-Structured Data & Ecosystem Tooling

10.1 Semi-Structured Data: VARIANT, OBJECT & ARRAY Processing 10.2 Snowflake Ecosystem Connectors, Drivers & SQL API 10.3 Snowpark, User-Defined Functions & Stored Procedures

12 Chapter 11: Virtual Warehouse Architecture & Concurrency

11.1 Virtual Warehouse Architecture, Sizing & Credit Economics 11.2 Multi-Cluster Warehouses & Concurrency Scaling Policies 11.3 Warehouse Types & Query Acceleration Service

13 Chapter 12: Query Profiling & Performance Diagnostics

12.1 Query Profile Deep-Dive & Bottleneck Analysis 12.2 Memory Spilling, Warehouse Queuing & Timeout Governance 12.3 Three-Tier Caching Architecture & Result Reuse

14 Chapter 13: Storage Optimization, Cost Governance & Observability

13.1 Micro-Partitioning & Automatic Clustering 13.2 Search Optimization Service (SOS) & Point Lookup Acceleration 13.3 Resource Monitors, Financial Governance & Usage Observability 13.4 Warehouse Monitoring, Alerts, Event Tables & Performance Explorer

1.2 Snowflake Parameter Hierarchy & Configuration

Key Takeaways Explain This Topic AI

Snowflake has account, session, and object parameters: session parameters resolve Account » User » Session, object parameters resolve Account » Database » Schema » Table (or are set on warehouses and users), and the most specific setting in each chain wins.

AUTOCOMMIT defaults to TRUE in Snowflake; setting it to FALSE requires explicit transaction control, but any subsequent DDL statement will implicitly commit the active transaction.

STATEMENT_TIMEOUT_IN_SECONDS defaults to 172,800 seconds (48 hours); when it is set both in the session hierarchy and on the warehouse, Snowflake enforces the lowest non-zero value of the two.

ABORT_DETACHED_QUERY defaults to FALSE (Account » User » Session); when TRUE, in-progress queries are aborted 5 minutes after the client's connectivity is lost instead of running to completion.

Audit parameters point-in-time with SHOW PARAMETERS (the level column shows where a value was set) and historically by searching ACCOUNT_USAGE.QUERY_HISTORY for ALTER ... SET statements; there is no PARAMETERS_HISTORY view.

Last updated: September 2026

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: Session ≻ User ≻ Account \text{Session} \succ \text{User} \succ \text{Account} Session ≻ User ≻ Account

Object parameters follow the container chain instead (next subsection).

Granular Object Inheritance Chains

For storage and structural parameters: Table ≻ Schema ≻ Database ≻ Account \text{Table} \succ \text{Schema} \succ \text{Database} \succ \text{Account} Table ≻ Schema ≻ Database ≻ 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:

Resolve the session hierarchy normally — a session value overrides the user value, which overrides the account value.

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

  • Parameter Name Default Value Configurable Levels Primary Architectural Risk / Purpose
  • AUTOCOMMIT TRUE Account, User, Session Explicit transaction control; invalidated by DDL implicit commits

STATEMENT_TIMEOUT_IN_SECONDS 172800 (48h) Account, User, Session + Warehouse (lowest non-zero wins) Runaway query cost mitigation across virtual warehouses

STATEMENT_QUEUED_TIMEOUT_IN_SECONDS 0 (Infinite) Account, User, Session + Warehouse (lowest non-zero wins) Preventing pipeline blockages under warehouse saturation

DATA_RETENTION_TIME_IN_DAYS 1 day Account, Database, Schema, Table Storage cost vs. Time Travel recovery window (0–90 days; Fail-safe unaffected)

DEFAULT_DDL_COLLATION NULL (Binary) Account, Database, Schema, Table String comparison rules; non-binary impacts metadata pruning

ABORT_DETACHED_QUERY FALSE Account, User, Session Prevents credit leakage when client applications disconnect

TIMEZONE 'America/Los_Angeles' Account, User, Session Timestamp interpretation consistency across global user bases

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.

Loading diagram... Snowflake Parameter Precedence and Inheritance Hierarchy