COF-C03 Practice Question: Performance Optimization, Querying, and Transformation
A data engineer is optimizing a query that filters on a high-cardinality column in a very large table. The query currently performs a full table scan. The engineer decides to add a clustering key on that column. After clustering, the query performance improves significantly for some queries but remains poor for others that filter on a different low-cardinality column. What is the most likely reason for the inconsistent performance?
⚠ Common exam trap
The trap here is assuming that clustering on one column will speed up all queries regardless of their filter columns, when pruning only works for the clustering key or correlated columns.
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
✓
Clustering keys only improve performance for queries that filter on the clustering key column; other filters benefit only if they are correlated with the clustering key.
Clustering improves pruning only for predicates on the clustering key or on columns that are correlated with it. When queries filter on a different, uncorrelated column, the micro-partitions cannot be pruned based on that column, so performance remains poor. The engineer should consider whether the low-cardinality column is correlated with the clustering key or consider a different clustering strategy, such as clustering on an expression that combines both columns if that aligns with query patterns.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Clustering keys only improve performance for queries that filter on the clustering key column; other filters benefit only if they are correlated with the clustering key.
Why this is correct
Clustering organizes data by the clustering key, so queries filtering on that column can skip many micro-partitions. Queries filtering on a different, uncorrelated column cannot benefit because the data is not sorted by that column. The low-cardinality column likely does not correlate with the high-cardinality clustering key, so pruning is ineffective. This explains why some queries improved while others did not.
- ✗
The clustering key must be defined on all columns used in WHERE clauses to achieve any performance improvement.
Why it's wrong here
Snowflake allows only one clustering key per table, which can be a single column or an expression. It is not required to include all filtered columns. Clustering on one column can still improve queries that filter on that column or on columns correlated with it. Defining clustering on all columns is not possible and would not guarantee better performance for every query.
- ✗
The automatic clustering service has not yet reclustered the table, so the clustering key is not effective for any query.
Why it's wrong here
If automatic clustering is enabled, it continuously reclusters as data changes. However, the scenario states that performance improved for some queries, indicating that clustering is effective for those. The issue is not that reclustering hasn't occurred, but that the other queries filter on a column that is not the clustering key and is not correlated with it.
- ✗
Clustering keys only benefit queries that use the CLUSTER BY clause in the SELECT statement.
Why it's wrong here
The CLUSTER BY clause is not used in SELECT statements; it is used in CREATE TABLE or ALTER TABLE to define the clustering key. Queries do not need to reference the clustering key explicitly to benefit; the optimizer automatically uses clustering information for pruning. This option misstates how clustering works and is not the reason for inconsistent performance.
About these practice questions
Courseiva writes every COF-C03 question from scratch — 280 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 COF-C03 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 COF-C03 exam.