Performance monitoring and optimization Study Guide — COF-C03

Performance and Cost Optimization

This domain accounts for a moderate share of the SnowPro Core COF-C03 blueprint under the current estimated domain weighting. It tests your ability to diagnose query performance problems using Snowflake-native tooling, choose the right scaling strategy for a warehouse, reduce storage costs, and select the correct optimization service for a given workload pattern. Expect scenario-based questions that require you to distinguish between closely related features (e.g., Search Optimization Service vs. materialized views) and to recall specific defaults and limits.

Query Profile: Spilling, Pruning, and Join Explosions

The Query Profile is accessible in Snowsight (and the Classic UI) via the query history. It visualizes the execution plan as a directed acyclic graph of operator nodes, each showing time consumed, rows processed, and bytes passed. Understanding the three major performance anti-patterns is essential for the exam.

Spilling

Spilling occurs when an operator cannot fit its working data set in memory and must write overflow data to local disk (spill to local storage) or, when local disk is also exhausted, to remote storage (S3/Azure Blob/GCS). Spilling dramatically increases query latency.

• Spill to local storage: Slower than memory; visible in Query Profile as "Bytes spilled to local storage."
• Spill to remote storage: Far slower; visible as "Bytes spilled to remote storage."
• Root cause: Warehouse is undersized for the data volume being processed (e.g., large sorts, aggregations, hash joins).
• Fix: Scale up (increase warehouse size) to provide more memory per node. Spilling is a signal to resize, not to add clusters.

Pruning

Partition pruning is Snowflake's mechanism for skipping micro-partitions that cannot contain the query's filter values. The Query Profile shows Partitions scanned vs. Partitions total. Good pruning = low scan ratio.

• Pruning is automatic based on min/max metadata stored per micro-partition per column.
• Poor pruning (scanning most partitions) indicates the data is not well-ordered on the filter columns.
• Remedy: Define a clustering key on the high-cardinality filter columns.
• Partition elimination happens at compile time using stored metadata — no data is read from storage for skipped partitions.

Join Explosions

A join explosion (also called a "cartesian explosion" or "fanout") occurs when a join produces far more rows than either input table — typically from missing or incorrect join conditions, or joining on a non-unique key without expecting duplication.

• Visible in Query Profile: output row count of a join node is orders of magnitude larger than both inputs.
• Common cause: a many-to-many join where one side was assumed to be unique.
• Fix: validate data uniqueness constraints upstream, rewrite the query, or use aggregation to deduplicate before joining.

Common traps

• The exam may suggest adding clusters (scaling out) to fix spilling — wrong. Spilling is a memory problem; scale up.
• "Partitions scanned = 0" is not possible if the query returns data — do not confuse pruning with caching.
• A join explosion is not fixed by increasing warehouse size; it is a logic error.

Worked scenario: A query scans 10,000 of 10,200 partitions on a 2 TB table. The filter is WHERE order_date = '2024-01-15'. What is the correct action? → Pruning is poor because order_date is not a clustering key (natural ingestion order scattered dates). Define a clustering key on order_date.

Warehouse Sizing vs. Multi-Cluster Scaling Decisions

Warehouse Sizes and Credit Consumption

Snowflake virtual warehouses scale from X-Small to 6X-Large (and up to 7X-Large on some editions). Each size step doubles the credit consumption per hour and doubles the compute resources.

Credits are billed per second with a 60-second minimum per start. A suspended warehouse does not consume credits.

Scale Up vs. Scale Out

Scaling up (resizing) gives each query more memory and CPU — appropriate for:

• Complex queries, large sorts/aggregations, spilling
• Single long-running queries
• Data loading / transformations

Scaling out (multi-cluster warehouses) adds parallel warehouse clusters — appropriate for:

• High concurrency (many simultaneous users/queries)
• Queuing problems (queries waiting, not slow)
• Unpredictable workload bursts

Multi-cluster warehouses are available on Enterprise Edition and above. You define Min Clusters and Max Clusters. Two scaling policies control when clusters start/stop:

Auto-suspend default: 10 minutes for a standard warehouse (configurable). Auto-resume is enabled by default.

Common traps

• Multi-cluster warehouses are not available on Standard Edition — exam distractors offer it as a solution without mentioning edition.
• Scaling up a warehouse does not help with queuing; it only helps individual query performance.
• The Economy policy saves credits but may let queries queue longer — use when cost matters more than response time.
• Auto-suspend of 1 minute is fine for sporadic workloads; the default 10 minutes wastes credits if the warehouse is idle.

Decision flow for warehouse optimization:

Diagram 1 — Decision tree: scale up vs. scale out vs. other fix