Performance Optimization: free practice questions

Correct: QAS is designed for queries that have large, parallelizable scan-and-filter-aggregation patterns — where a significant portion of the query time is spent scanning micro-partitions. QAS offloads the scan portions to additional serverless compute, reducing wall-clock time for these 'outlier' queries. Distractor A is wrong because small lookup joins that already return quickly have little to gain from QAS; the service targets long-running scan-heavy queries. Distractor C is wrong because QAS does not address warehouse queuing/concurrency — that is solved by multi-cluster warehouses or query queuing policies. Distractor D is wrong because spilling during hash joins is a memory pressure problem (solved by increasing warehouse size or rewriting the query), not a scan acceleration problem; QAS primarily accelerates the scan phase, not in-memory hash join operations.

Correct (A): The WindowFunction node is spilling to local storage, which is a memory pressure problem. Upsizing the warehouse provides more total RAM across more nodes, allowing the sort and partition operations within the window function to complete in-memory rather than spilling to disk. Correct (E): Applying a WHERE clause filter (e.g., limiting to a date range) reduces the total number of rows the window function must process. Fewer rows means smaller sort buffers, less memory pressure, and reduced or eliminated spilling — directly addressing the bottleneck. Why B is wrong: Automatic Clustering on (customer_id, event_time) would help pruning for queries that filter on customer_id, but window functions must process all data within each partition value across the full dataset. Clustering reduces scan I/O but does not eliminate the need to sort and partition rows during window function execution. Why C is wrong: ROW_NUMBER() cannot be trivially replaced with GROUP BY because GROUP BY collapses rows while ROW_NUMBER() assigns a rank to each row within its partition. These are semantically different operations. Why D is wrong: QAS accelerates large partition-scan workloads (filters, aggregations over many micro-partitions). It does not offload window function computation to separate serverless nodes — window functions are executed within the warehouse compute.

Correct: Filter pushdown is an optimization where filter predicates are applied as early as possible — ideally during the scan phase — so that fewer rows are materialized and passed to downstream operators like joins. When the Query Profile shows a separate, expensive Filter node after the TableScan rather than the filter being absorbed into the scan, it indicates that filter pushdown did not occur. This can happen with certain query constructions (e.g., filters on computed columns or expressions) that prevent the optimizer from pushing the filter into the scan. Rewriting the query to use simple column filters often enables pushdown. Distractor A is wrong because partition pruning is a metadata-level operation that happens before scan execution — it would show up as a difference between 'Partitions total' and 'Partitions scanned', not as a slow Filter node in the profile. Distractor C is wrong because join reordering affects which table is the build vs. probe side; it does not explain a slow Filter node upstream of the join. Distractor D is wrong because broadcast join selection is about eliminating shuffle for small tables; it does not affect whether a filter is applied during or after a scan.

Spilling to remote storage occurs when the warehouse exhausts both available memory AND local SSD space — data overflows all the way to remote object storage, which is the slowest tier. Option B correctly identifies that upgrading warehouse size increases available memory (and local disk), which eliminates or reduces the need to spill. Option A incorrectly implies local SSD was exhausted first (if local SSD were exhausted first, 'Bytes spilled to local storage' would be non-zero); in the scenario, local spill is zero, meaning the spill went directly past local SSD — this is unusual but possible when local SSD is already heavily utilized by cached data from other queries. Option C is incorrect because partition pruning reduces data scanned but does not directly address the aggregation memory pressure causing the spill. Option D is incorrect; remote storage spilling severely degrades performance and should always be investigated.

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.