ARA-C01 Data Engineering Practice Question
A healthcare analytics team uses Snowflake to analyze patient records. They have a large fact table 'ENCOUNTERS' that is clustered by 'PATIENT_ID' and 'ENCOUNTER_DATE'. The team frequently runs queries that filter on 'FACILITY_ID' and 'DIAGNOSIS_CODE', which are not part of the clustering key. These queries perform poorly. The architect needs to improve performance without changing the existing clustering key, as it benefits other queries. What should the architect do?
⚠ Common exam trap
The trap here is assuming that you can add a second clustering key or that reclustering can target new columns, when Snowflake only supports one clustering key per table.
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
✓
Create a search optimization service on the columns FACILITY_ID and DIAGNOSIS_CODE.
The search optimization service is designed to accelerate queries with filters on columns that are not part of the clustering key. It creates a persistent data structure that enables efficient pruning for equality and IN filters. This allows the team to keep the existing clustering key for other queries while improving performance for FACILITY_ID and DIAGNOSIS_CODE filters. It is the appropriate solution without altering the clustering key.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Create a search optimization service on the columns FACILITY_ID and DIAGNOSIS_CODE.
Why this is correct
The search optimization service can significantly improve performance for selective point lookups and filtered queries on columns not in the clustering key. It maintains a search access path that allows efficient pruning. This is ideal when the existing clustering key must remain for other queries. It does not require changing the clustering key and can be added to specific columns.
- ✗
Add a secondary clustering key on FACILITY_ID and DIAGNOSIS_CODE to the table.
Why it's wrong here
Snowflake tables support only one clustering key. You cannot have multiple clustering keys on the same table. Therefore, adding a secondary clustering key is not possible. The architect must choose a single clustering key that balances all query patterns, or use other techniques like search optimization or materialized views.
- ✗
Create a materialized view on ENCOUNTERS that includes FACILITY_ID and DIAGNOSIS_CODE, and rewrite queries to use the materialized view.
Why it's wrong here
Materialized views can improve performance for repeated queries, but they are not a substitute for clustering. They also have limitations: they cannot be used if the query has filters on columns not in the view, and they add storage and maintenance overhead. Moreover, the materialized view would need to be refreshed, and it might not be automatically used by all queries, requiring query rewrites.
- ✗
Recluster the table manually using ALTER TABLE ... RECLUSTER, specifying the new columns.
Why it's wrong here
The RECLUSTER command reorganizes the table according to the existing clustering key. It cannot specify new columns. To change the clustering key, you would need to drop and recreate it, which affects the entire table and may degrade performance for queries using the original key. This does not address the need to keep the original clustering key while improving other filters.
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.