DEA-C02 Performance Optimization Practice Question
Exhibit
{
"Node": "Join",
"Status": "Success",
"Statistics": {
"PartitionsScanned": 4500,
"PartitionsTotal": 4500,
"SpillageToLocalDisk": "150GB",
"SpillageToRemoteDisk": "1.2TB"
}
}Refer to the exhibit. A data engineer is analyzing a Query Profile for a long-running join operation. Based on the provided JSON statistics, what is the most effective action to improve the performance of this specific query?
⚠ Common exam trap
Candidates often try to optimize the query SQL or add indexes. They fail to recognize that remote disk spillage is a hardware-capacity issue that requires more memory per node via scaling.
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
✓
Scale up the virtual warehouse to a larger size.
The exhibit shows significant spillage to both local and remote disk, with 1.2TB reaching remote storage. Remote spillage is a critical performance bottleneck because it involves network latency to S3/Azure Blob. Moving to a larger warehouse provides more memory and local storage per node, allowing the join to stay in-memory or on faster local SSDs.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Enable the Search Optimization Service on the join columns.
Why it's wrong here
Search Optimization Service is designed for point lookups on specific values, not for large-scale joins that are already scanning all partitions. Since the exhibit shows 4500 out of 4500 partitions being scanned, the bottleneck is the join processing and data volume, not the initial partition pruning or lookup efficiency.
- ✗
Apply a clustering key to the tables involved in the join.
Why it's wrong here
Clustering might help with initial partition pruning, but the exhibit indicates that the query is already performing a full scan of all partitions. Furthermore, clustering does not address the memory exhaustion issue evidenced by the remote disk spillage, which is the primary cause of the extreme latency in this specific operation.
- ✗
Rewrite the query to use a Common Table Expression (CTE).
Why it's wrong here
Rewriting a query to use CTEs is primarily for readability and does not change how the Snowflake optimizer processes the physical join. It will not reduce the amount of data being processed or the memory required for the join hash table, thus failing to resolve the disk spillage problem.
- ✓
Scale up the virtual warehouse to a larger size.
Why this is correct
Scaling up to a larger warehouse increases the available RAM and local SSD space for each compute node. This allows the join operation to be processed entirely in memory or reduces spillage to local disk, completely avoiding the highly latent remote disk spillage that is currently degrading the query performance.
Quick reference
AWS S3 Storage Class Comparison
| Storage Class | Min Duration | Retrieval | Use Case |
|---|---|---|---|
| S3 Standard | None | Immediate | Frequently accessed data |
| S3 Standard-IA | 30 days | Immediate | Infrequent access, rapid retrieval |
| S3 One Zone-IA | 30 days | Immediate | Non-critical infrequent data |
| S3 Intelligent-Tiering | None | Immediate–hours | Unknown or changing access patterns |
| S3 Glacier Instant | 90 days | Milliseconds | Archive with instant retrieval |
| S3 Glacier Flexible | 90 days | Minutes–hours | Archive, flexible retrieval |
| S3 Glacier Deep Archive | 180 days | Hours | Long-term compliance archive |
About these practice questions
Courseiva writes every DEA-C02 question from scratch — 229 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 DEA-C02 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 DEA-C02 exam.