ARA-C01 Performance Optimization Practice Question
A data architect is analyzing a slow-performing query that joins a large fact table with a small dimension table. The Query Profile shows that the join is executed as a broadcast join, and the dimension table is small enough to fit in memory. However, the query still takes a long time because the fact table is not pruned effectively. The fact table is clustered by date, and the query filters on a specific date range. Which action should the architect take to improve pruning and overall performance?
⚠ Common exam trap
The trap here is assuming that adding more clustering keys or increasing warehouse size will solve pruning issues, when the real problem is often that the existing clustering key is not being utilized due to predicate pushdown failures or stale clustering.
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
✓
Ensure that the date filter is applied as a predicate pushdown and that the clustering key is active.
The fact table is already clustered by date, and the query filters on a date range, so pruning should be effective if the predicate is pushed down and the clustering key is active. The architect should verify that the date filter is applied at the scan level and that the clustering key is not stale. This directly addresses the excessive data scanning shown in the Query Profile, whereas other options do not target the pruning problem.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Convert the broadcast join to a hash join by increasing the warehouse size.
Why it's wrong here
Increasing warehouse size does not change the join strategy from broadcast to hash join; that is determined by the optimizer based on table sizes and statistics. A broadcast join is appropriate when one table is small, and it is generally efficient. The problem is not the join type but the lack of pruning on the fact table. Increasing warehouse size would add cost without addressing the root cause, and it might not improve performance if the bottleneck is scanning too many partitions.
- ✗
Create a materialized view that pre-joins the fact and dimension tables.
Why it's wrong here
A materialized view could improve performance for repetitive queries, but it is not a targeted fix for pruning issues. Materialized views have restrictions and may not support all query patterns, and they add storage and maintenance overhead. In this scenario, the immediate problem is ineffective pruning on the fact table, which can be resolved by ensuring the date filter is pushed down and the clustering key is active. A materialized view would not address the root cause and could introduce complexity.
- ✓
Ensure that the date filter is applied as a predicate pushdown and that the clustering key is active.
Why this is correct
Predicate pushdown ensures that the date filter is applied at the scan level, allowing Snowflake to prune micro-partitions based on the clustering key. If the filter is not pushed down or if the clustering key is not active (e.g., due to stale clustering), pruning will be ineffective. Verifying that the clustering key is active and that the query uses the filter correctly can restore pruning efficiency and reduce the amount of data scanned, directly improving performance.
- ✗
Add a clustering key on the join key of the fact table.
Why it's wrong here
Adding a clustering key on the join key might help join performance, but it does not address the pruning issue on the date filter. The fact table is already clustered by date, and the query filters on date, so pruning should be effective. However, if the clustering key is not properly maintained or if the date filter is not selective enough, adding another clustering key could increase reclustering costs without solving the root cause. The issue may be that the date filter is not being pushed down properly.
About these practice questions
This ARA-C01 question is part of Courseiva's 209-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam 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.