ARA-C01 Data Engineering Practice Question
A healthcare analytics company stores patient encounter data in a Snowflake table with columns: encounter_id (NUMBER), patient_id (NUMBER), encounter_date (DATE), and diagnosis_code (VARCHAR). The table is 20 TB and grows by 100 GB per day. Most queries filter on encounter_date and join to a patient dimension on patient_id. The architect must design a clustering key to optimize these queries while minimizing reclustering cost. Which clustering key should the architect choose?
⚠ Common exam trap
The trap here is assuming that the join column must come first to optimize joins, when in fact leading with the high-selectivity filter column delivers greater pruning benefits for this workload.
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 BY (encounter_date, patient_id)
The clustering key should lead with the column most frequently used in range filters, which is encounter_date, and then include the join column patient_id. This order enables partition pruning on date ranges and improves join performance by co-locating related patient rows. The reversed order or unrelated columns fail to support the dominant query patterns and would increase scan volume and reclustering overhead.
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 BY (patient_id, encounter_date)
Why it's wrong here
Placing patient_id first means data is ordered by patient rather than date. Queries that filter on encounter_date ranges cannot prune micro-partitions as effectively, because a single date's rows are scattered across many patient-ordered partitions. This increases I/O for the dominant filter pattern and does not leverage the date range pruning that the workload demands.
- ✓
CLUSTER BY (encounter_date, patient_id)
Why this is correct
This key orders data first by encounter_date, which is the primary filter column, and then by patient_id, which supports the join. Snowflake can prune micro-partitions efficiently on date ranges and co-locate related patient rows within each date, reducing scanned data for both filters and joins while keeping reclustering overhead manageable.
- ✗
CLUSTER BY (encounter_id)
Why it's wrong here
Clustering by the primary key encounter_id provides no benefit for queries filtering on encounter_date or joining on patient_id. Because encounter_id is unique and likely monotonically increasing, the data is already naturally ordered by insertion time, and clustering on it adds no pruning power for the date or patient predicates used by the workload.
- ✗
CLUSTER BY (diagnosis_code, encounter_date)
Why it's wrong here
diagnosis_code is not a common filter or join column in the described workload. Leading with a low-selectivity column that is rarely used in predicates forces Snowflake to scan more micro-partitions for date-filtered queries. This choice would increase query latency and reclustering cost without addressing the primary access patterns.
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.