DEA-C02 Performance Optimization Practice Question
While reviewing a Query Profile, a data engineer notices a 'Join' operator where the number of output rows is significantly larger than the sum of the input rows. Which TWO steps should be taken to resolve this performance issue? (Select TWO)
⚠ Common exam trap
Candidates often assume the join is just slow because the warehouse is too small. They fail to spot the 'exploding join' symptom, which is a logic error rather than a resource issue.
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
✓
Verify if the join keys have a many-to-many relationship.
An output row count significantly larger than input row counts indicates an 'exploding join' or Cartesian product, usually caused by many-to-many relationships or missing join conditions. Validating the join predicates and ensuring the join keys are unique or properly filtered can stop the exponential growth of the intermediate result set.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Increase the size of the warehouse to handle the extra rows.
Why it's wrong here
Increasing the warehouse size might allow the query to finish, but it does not fix the underlying logic error of an exploding join. This approach treats the symptom rather than the cause, leading to unnecessary credit consumption for a query that is producing more data than intended.
- ✓
Verify if the join keys have a many-to-many relationship.
Why this is correct
A many-to-many relationship on join keys causes each row in the first table to match multiple rows in the second, leading to an explosion of output rows. Identifying this allows the engineer to decide if the data should be aggregated before the join to ensure a one-to-many relationship.
- ✓
Check for missing join predicates that cause a Cartesian product.
Why this is correct
If a join predicate is missing, Snowflake performs a Cartesian product, matching every row of one table with every row of the other. This is a common cause of query failure and massive performance degradation, and it can be fixed by correctly defining the relationship between the tables.
- ✗
Enable the Search Optimization Service on the join columns.
Why it's wrong here
Search Optimization Service helps with finding specific rows in a table but does nothing to prevent an exploding join once the data has been identified. It is a retrieval optimization, not a join logic optimization, and will not reduce the number of rows generated by the join operator.
- ✗
Replace the JOIN with a UNION ALL operation.
Why it's wrong here
A UNION ALL combines result sets vertically, while a JOIN combines them horizontally based on a relationship. These operations serve completely different logical purposes. Replacing a join with a union is not a performance optimization strategy; it would fundamentally change the result set and the meaning of the query.
Visual reference
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.