ARA-C01 Data Engineering Practice Question
Exhibit
{
"query_id": "01b2345...",
"statistics": {
"spilling_to_local_storage": "45GB",
"spilling_to_remote_storage": "120GB",
"partitions_scanned": 1500,
"partitions_total": 1500
}
}Refer to the exhibit. An architect observes these statistics in the Query Profile for a nightly batch job. What is the most effective architectural change to address the performance bottleneck shown?
⚠ Common exam trap
Candidates often suggest optimizing the query SQL or adding indexes, missing that 'spilling to remote storage' is a hardware/resource constraint that can only be solved by increasing the warehouse size.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Increase the virtual warehouse size (e.g., from Medium to Large or X-Large).
The exhibit shows significant 'spilling to remote storage', which occurs when the local SSD of the virtual warehouse is exhausted during a memory-intensive operation like a large join or sort. Remote spilling is much slower than local spilling. The primary solution is to increase the warehouse size to provide more memory and local disk.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Implement a Search Optimization Service on the join columns to reduce the number of partitions scanned.
Why it's wrong here
Search Optimization Service (SOS) is designed to speed up point-lookup queries that filter on specific values. It does not help with large-scale data spilling during joins or aggregations, as shown in the exhibit, where the bottleneck is memory/disk capacity rather than the efficiency of the initial scan.
- ✗
Enable the Query Acceleration Service (QAS) for the warehouse to handle the excess data volume.
Why it's wrong here
Query Acceleration Service is primarily used to offload massive table scans to a shared compute tier. It does not typically resolve spilling issues related to complex joins or sorts that exceed the memory of the primary warehouse, as those operations still require local warehouse resources for execution.
- ✓
Increase the virtual warehouse size (e.g., from Medium to Large or X-Large).
Why this is correct
Scaling up the warehouse provides each compute node with more RAM and larger local SSD storage. Since the query is spilling significantly to remote storage, a larger warehouse will likely keep more data in memory or local disk, drastically reducing the latency caused by slow network-based I/O.
- ✗
Rewrite the query to use a LATERAL FLATTEN on the largest tables to optimize memory usage.
Why it's wrong here
LATERAL FLATTEN is used for exploding semi-structured arrays into multiple rows and actually increases the volume of data processed in memory. Using it would likely worsen the spilling problem rather than solve it, as it creates more intermediate rows that need to be tracked and sorted.
About these practice questions
Courseiva writes every ARA-C01 question from scratch — 209 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Snowflake exam blueprint
This ARA-C01 practice question is part of Courseiva's free Snowflake certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the ARA-C01 exam.