DEA-C02 Performance Optimization Practice Question
When designing a clustering key for a table that is frequently joined with other tables, which strategy generally provides the best performance for the join operation?
⚠ Common exam trap
Candidates often choose clustering keys based on the primary key or date, regardless of query patterns. They overlook the importance of matching the clustering key to the join predicate.
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
✓
Cluster on the column used as the join key.
Clustering a table on the columns used in join predicates (the join keys) ensures that Snowflake can use join pruning and potentially more efficient join algorithms. When data is co-located within micro-partitions based on the join key, the engine can skip entire partitions that do not have matching keys in the other table, reducing I/O.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Cluster on the most frequently used filter column in the WHERE clause.
Why it's wrong here
While clustering on filter columns helps with data retrieval, it does not necessarily optimize the join process itself. If the join key is different from the filter column, the engine may still need to perform a wide scan or shuffle during the join phase, missing out on significant performance gains.
- ✗
Cluster on the column with the highest cardinality in the table.
Why it's wrong here
Highest cardinality columns are often poor clustering keys because they create too many distinct values, leading to overlapping micro-partitions. Effective clustering requires a column with a cardinality that allows for meaningful grouping of data within partitions, usually targeting columns with moderate cardinality or date/time dimensions.
- ✓
Cluster on the column used as the join key.
Why this is correct
Clustering on join keys allows Snowflake to perform partition pruning during the join. If both tables in a join are clustered on their respective join keys, the optimizer can significantly reduce the amount of data read and processed, as it only needs to compare partitions with overlapping key ranges.
- ✗
Cluster on a column that is never updated or changed.
Why it's wrong here
While clustering on static columns reduces maintenance costs, it does not guarantee any performance benefit for joins or filters. The choice of a clustering key should be driven by the query patterns and the need for data pruning, not just the frequency of DML operations on the column.
About these practice questions
One of 229 original DEA-C02 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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.