ARA-C01 Data Engineering Practice Question
A healthcare analytics team stores patient encounter records in a Snowflake table that is updated continuously by an external ETL tool using MERGE statements. The team needs to build a downstream transformation that incrementally processes only the rows that were inserted or changed since the last run. They want to avoid reprocessing the entire table and do not want to add triggers or modify the ETL tool. Which Snowflake feature should the architect use to capture these changes?
⚠ Common exam trap
The trap here is assuming that any stream will capture all changes, when append-only streams deliberately ignore updates and deletes.
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 standard Stream on the encounter table to capture inserts, updates, and deletes.
A standard stream on the encounter table captures inserts, updates, and deletes by reading the table's change tracking metadata. This is exactly what the team needs because the ETL tool uses MERGE, which produces updates as well as inserts. Append-only streams would miss updates, the CHANGES clause lacks persistent offset tracking, and materialized views do not expose change data.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use a materialized view that refreshes automatically to expose only changed rows.
Why it's wrong here
Materialized views in Snowflake do not expose change data; they store precomputed results and are refreshed by Snowflake's background service. They cannot be used to identify which specific rows changed since the last pipeline run. The team needs a change data capture mechanism, and a materialized view does not provide that capability.
- ✗
Create a Stream on the encounter table with an append-only stream type.
Why it's wrong here
An append-only stream only captures inserted rows and ignores updates and deletes. Since the ETL process uses MERGE statements, many changes will be updates to existing rows. Those updated rows would be invisible to this stream, causing the downstream transformation to miss critical changes. It is suitable only when the source is strictly insert-only, which is not the case here.
- ✓
Create a standard Stream on the encounter table to capture inserts, updates, and deletes.
Why this is correct
A standard stream captures all three change types—inserts, updates, and deletes—by leveraging the table's change tracking metadata. This aligns perfectly with the MERGE-based ETL that modifies existing rows. The stream provides a reliable, offset-based change record without requiring any changes to the ETL tool or adding triggers, enabling incremental downstream processing as requested.
- ✗
Enable change tracking on the encounter table and query the CHANGES clause directly.
Why it's wrong here
The CHANGES clause can return change records, but it requires specifying a time range or a stream. Without a stream, you cannot easily track the last processed offset across multiple runs. It is more of a manual, ad-hoc mechanism and does not provide the persistent offset semantics needed for reliable incremental processing in a scheduled pipeline.
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.