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.