Performance Optimization Study Guide — ARA-C01

Performance Optimization

This domain is one of the larger topic areas on the SnowPro Advanced: Architect exam (ARA-C01) under the current (carried-over, unverified) blueprint weighting — tied with Account Security and Data Protection, and second to Design. It tests your ability to diagnose bottlenecks from query profiles, select the correct caching layer, design clustering strategies, tune loading pipelines, and configure serverless compute features. Expect scenario-based questions that require you to read a query profile, identify the root cause, and select the correct remediation from similar-sounding options.

Query Profile: Operator Statistics, Spilling, Pruning, Explode Nodes

The Query Profile (accessible via Snowsight or QUERY_HISTORY views) is the primary diagnostic tool for query performance.

Key Operator Nodes

Spilling to Disk

Spilling occurs when an operator cannot fit its working set in local memory. Two tiers exist:

• Spill to local SSD — slower than memory; visible as "Bytes spilled to local storage"
• Spill to remote storage (S3/Azure/GCS) — significantly slower; visible as "Bytes spilled to remote storage"

Remediation for spilling: Upsize the warehouse (more memory per node), rewrite aggregations to reduce intermediate data, or pre-filter before joining.

Pruning Statistics

Pruning is reported in the TableScan node:

• Partitions scanned vs Partitions total — the ratio shows pruning efficiency.
• Ideal: scanned << total.
• Poor pruning means clustering is misaligned with query predicates.

Explode Nodes

An Explode node appears when a LATERAL FLATTEN or UNNEST causes a multiplicative row expansion. A single JSON array of 10,000 elements flattened against a 1M-row table creates 10B rows. Watch for this in semi-structured pipelines.

Common traps:

• Confusing "spill to local" (SSD, bad) with "spill to remote" (object storage, worse). Remote spill is an order of magnitude more severe.
• Assuming a large "Bytes scanned" number always indicates a missing index — Snowflake has no indexes; the fix is clustering or better pruning.
• Thinking an Explode node is a JOIN node — they look similar in size but have different root causes and fixes.
• Ignoring the percentage of query time spent per node; the longest bar is the bottleneck.

Worked scenario: A query on a 10 TB table scans 9.8 TB. The TableScan node shows "Partitions scanned: 980 of 1000." Clustering keys are on customer_id but the query filters on order_date. Fix: define a clustering key on order_date (or a compound key (order_date, customer_id)) to improve pruning.

Result Cache, Metadata Cache, Warehouse Cache

Snowflake has three distinct caching layers. The exam tests which layer is invoked, how long it persists, and what invalidates it.

Result Cache

• Stores the exact result set of a previously executed query.
• Hit requires: identical SQL text (case-sensitive including whitespace), same role, same warehouse size is not required (no warehouse needed at all for a cache hit).
• Invalidated when any underlying micro-partition referenced by the query changes (INSERT, UPDATE, DELETE, or DML).
• USE_CACHED_RESULT = FALSE at session or account level disables result cache.

Metadata Cache

• Stores table-level statistics: row count, MIN/MAX per column per micro-partition, NULL counts.
• Used automatically for queries like SELECT COUNT(*) or MIN/MAX on unfiltered columns — these execute in 0 seconds without touching the warehouse.
• No TTL; it stays current via the Cloud Services layer.

Warehouse Cache

• Each warehouse node holds recently scanned micro-partitions on local SSD.
• Larger warehouses (more nodes) have more aggregate cache capacity.
• Suspending a warehouse evicts the cache — this is a critical exam trap.
• A multi-cluster warehouse with auto-suspend can thrash cache if clusters spin down frequently.

Common traps:

• Stating the result cache lasts 48 hours — it is 24 hours.
• Believing a warehouse must be active to serve a result cache hit — it does not.
• Confusing metadata cache (counts/min/max with no DML dependency) with result cache (full result set, DML-sensitive).
• Suspending a warehouse to "save credits" without recognizing the cache eviction cost on resume.