Snowflake COF-C03: Performance monitoring and optimization Practice Questions

When intermediate query data exceeds the available memory of the warehouse nodes, Snowflake writes the overflow to local disk (and potentially remote storage). This is called 'spilling.' The most direct remedy is to increase the warehouse size, which provides larger per-node memory and reduces or eliminates the spill. — Option C is wrong: partition pruning relates to micro-partition scanning, not memory overflow. — Option D is wrong: a join explosion causes excessive row multiplication, not disk writes per se, and rewriting with a semi-join addresses row count, not memory limits. — Option A is wrong: the Result Cache stores final query results and has no bearing on in-flight memory pressure during execution.

Correct (B): SUSPEND_IMMEDIATE kills all currently running and queued queries immediately when the threshold is reached, then suspends the warehouse. Combined with a DAILY interval and a quota of 200, this matches the requirement exactly. Incorrect (A): NOTIFY only sends an alert; it does not stop any queries or suspend the warehouse. Running and queued queries continue uninterrupted. Incorrect (C): SUSPEND waits for currently running queries to complete before suspending the warehouse. This does not 'immediately kill' running queries, which is the stated requirement. Incorrect (D): A MONTHLY interval means the 200-credit quota is measured over the entire month, not per day. The requirement is a daily limit, so the interval must be DAILY.

Automatic Clustering uses Snowflake-managed serverless compute credits charged to the account separately from warehouse credits. Tables that receive frequent DML (especially out-of-order inserts or updates that disrupt existing clustering) require more frequent re-clustering operations, directly increasing credit consumption. — Option C is wrong: Automatic Clustering is NOT free; it incurs serverless credit charges. — Option A is wrong: clustering is an ongoing maintenance operation; as new data is ingested and disrupts the cluster depth, Snowflake continuously re-clusters, generating recurring costs. — Option E is wrong: clustering organises micro-partitions by column value ranges to improve pruning; it does not apply additional compression algorithms or reduce storage costs.

Correct (E): QAS is specifically designed for 'outlier' queries — queries where a single large, parallelizable operation (typically a large table scan or heavy processing step) consumes a disproportionate share of execution time. Snowflake identifies these automatically. Correct (D): QAS works by offloading eligible portions of a query to Snowflake-managed shared compute resources that can scale beyond the warehouse's own capacity. The query must have components that are parallelizable and suitable for this offload. Incorrect (B): Auto-suspend affects warehouse idle behavior but has no bearing on QAS eligibility or effectiveness. Incorrect (C): Partition pruning reduces the data scanned before execution; it is a complementary optimization but is not a criterion Snowflake uses to determine QAS eligibility. Highly pruned queries may not even need QAS. Incorrect (A): QAS eligibility is based on query characteristics, not on the role or identity of the user executing the query.

QAS billing has two important characteristics: it uses a scale factor (a multiplier applied to the warehouse's per-credit rate) to determine QAS-specific charges, meaning costs can compound beyond the base warehouse cost (C); and the optimizer selectively identifies eligible queries — only those with large, non-uniform scans benefit, so QAS is not invoked for every query (E). — Option F is wrong: QAS has its own scale factor and is NOT billed at the same flat rate as the warehouse. — Option A is wrong: QAS does not accelerate every query; the optimizer evaluates eligibility, and many queries are unaffected. — Option B is wrong: there is no provision in Snowflake's billing model that waives QAS charges for failed queries. — Option D is wrong: Snowflake does not provide a free tier for QAS based on warehouse size; charges apply regardless of warehouse size when QAS is enabled and used.