Snowflake ARA-C01: Performance Optimization Practice Questions

When a root task has both a SCHEDULE and child tasks have AFTER clauses pointing to it, the scheduler can trigger a new run of the root task on schedule even before the previous graph run completes, causing child tasks from successive runs to overlap. The correct fix is to ensure the root task's schedule interval is longer than the expected maximum run time of the full graph, or to use a single root task with no schedule and trigger it externally. Option C is incorrect because AFTER clauses do enforce dependency ordering within a single run. Option B is incorrect because serverless tasks fully support dependency ordering via AFTER clauses. Option A is incorrect because Snowflake task graphs support up to 1,000 tasks, far more than twelve.

Correct A: Upgrading the warehouse size increases the per-node memory available for in-memory sort and aggregation buffers, directly addressing spilling caused by insufficient RAM. Correct C: Rewriting the query to reduce intermediate row counts before the final aggregation (e.g., pre-aggregating in a subquery or CTE) decreases the amount of data the aggregation node must hold in memory at once, reducing or eliminating spilling. Distractor B is wrong because multi-cluster auto-scaling adds clusters for concurrency (multiple simultaneous queries), not for a single query's memory requirements — a single query runs on one cluster. Distractor D is wrong because partitioning by an unrelated column does not affect the intermediate data volume produced by the aggregation node. Distractor E is wrong because RESULT_SCAN retrieves a previously cached result; it does not help a query that is currently spilling. Distractor F is wrong because clearing the warehouse cache removes the local SSD data cache for table scans, which could actually make performance worse and does nothing to address in-memory aggregation spilling.

Correct (A): Without WHERE clause filters, all 800 GB and 900 GB of data participate in the join. Applying selective filters is always the first optimization — reducing input row counts before the join is the highest-leverage change.

Correct (D): Full outer joins retain ALL rows from both sides, including non-matching rows. If the business requirement is actually satisfied by an inner or left join, switching eliminates the unnecessary retention of non-matching rows from the right side, dramatically reducing intermediate result sizes.

Why B is wrong: Rewriting a FULL OUTER JOIN as LEFT JOIN + UNION ALL is a valid technique in some databases but does not inherently provide a performance benefit in Snowflake, which is optimized for standard join syntax.

Why C is wrong: Snowpark-optimized warehouses provide extra memory for ML/Snowpark Python workloads. They do not specifically accelerate standard SQL join operations beyond the memory benefit of a larger node.

Why E is wrong: Clustering keys help with pruning during table scans, not with join execution itself. Snowflake's join algorithm choice (broadcast vs hash) is based on table sizes, not clustering.

Why F is wrong: QAS helps with scan-heavy queries with high partition counts. A join-heavy query with no filters and massive intermediate results would not be meaningfully helped by QAS — the bottleneck is the join computation, not partition scanning.

Option A is correct: average_overlap measures how many micro-partitions on average contain overlapping ranges of the clustering key. A value of 7.4 (out of a scale where 1.0 is perfect) means that for a given key range, approximately 7–8 partitions overlap, so Snowflake cannot prune them away and must scan all of them — confirming low pruning efficiency.

Option F (index 5) is correct: average_depth specifically measures the average number of micro-partitions that a given clustering key value appears in. A depth of 1.0 indicates perfect clustering (each key value exists in exactly one partition); 8.2 means significant cross-partition spread, confirming poor clustering.

Option B is incorrect: Automatic clustering does NOT reorganize all affected partitions in a single pass. Snowflake's clustering service runs incrementally in the background, prioritizing the most overlapping partitions first. For a table with 42,000 partitions under constant high-velocity inserts, clustering lag is ongoing and a single maintenance cycle will not fully resolve overlap — new inserts continuously reintroduce it.

Option C is partially plausible but subtly wrong: the continuous inserts DO contribute to clustering degradation, but the mechanism is not that each micro-partition has a 'narrow range' of EVENT_DATE — Snowpipe batches create micro-partitions with mixed REGION values and very recent EVENT_DATE values, which overlap with existing recent partitions. The described mechanism oversimplifies this. More precisely, the issue is that constant writes on the leading edge of EVENT_DATE perpetually create overlap in the most recent partitions, and automatic clustering must continuously work to re-cluster them.

Option D is incorrect: manually running RECLUSTER on only the current date partition would leave historical overlap unresolved and would not be more cost-effective given 42,000 partitions and 2M rows every 5 minutes. Automatic clustering is generally more efficient for continuously ingested tables.

Option E is incorrect: the order of columns in a clustering key DOES matter. Snowflake prunes based on the leftmost key column first (similar to a composite index). Putting EVENT_DATE first vs. REGION first significantly affects pruning for queries that filter on only one of the two columns, though for queries filtering both it matters less. The claim that order has 'no effect' is factually wrong.

Correct A: If the Dynamic Table's transformation query contains operations that Snowflake cannot compute incrementally (such as certain window functions with unbounded frames, aggregations without GROUP BY on the change key, or non-deterministic functions), Snowflake falls back to a FULL refresh mode. A full refresh must scan and recompute the entire result set, which on a large or continuously changing source table can take many minutes — far exceeding a 1-minute lag target. Correct C: The lag target is a target, not a guaranteed SLA. If the underlying computation (scanning, joining, aggregating) takes 8 minutes to complete, the actual lag will be at least that duration regardless of the configured target. The target instructs the scheduler how often to attempt a refresh, but it cannot make the computation faster. Distractor B is wrong because Snowflake supports Dynamic Table lag targets as low as 1 minute (or even in seconds for certain configurations); 1 minute is a valid target. Distractor D is wrong because transient vs. permanent schema classification does not disable incremental refresh for Dynamic Tables. Distractor E is wrong because cross-database dependencies do not introduce a fixed scheduling overhead penalty. Distractor F is wrong because Dynamic Table refreshes in serverless mode are not deprioritized by warehouse user queries; they run on separate Snowflake-managed compute.