ARA-C01 Data Engineering Practice Question
A financial services firm uses Snowflake to store transactional data. The data engineering team needs to implement a process that automatically copies new files from an external stage into a raw table and then triggers a series of dependent transformations. The team wants to minimize manual intervention and ensure that transformations run only when new data is available. They also want to avoid running transformations on empty batches. Which combination of Snowflake features should the architect use?
⚠ Common exam trap
The trap here is overlooking the need for a WHEN clause to prevent tasks from running on empty streams, which can waste resources.
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
✓
Snowpipe with a stream on the raw table and a task with a WHEN clause checking SYSTEM$STREAM_HAS_DATA.
The combination of Snowpipe, a stream on the raw table, and a task with a WHEN clause using SYSTEM$STREAM_HAS_DATA ensures automatic ingestion and conditional transformation execution. Snowpipe loads new files, the stream tracks changes, and the task runs only when data is present, minimizing manual intervention and avoiding empty batches.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Snowpipe with a stream on the raw table and a task that runs on a fixed schedule without a WHEN clause.
Why it's wrong here
Without a WHEN clause, the task would run on schedule regardless of whether new data exists, potentially processing empty streams and wasting compute resources. The team explicitly wants to avoid running transformations on empty batches, so this approach does not meet the requirement. The stream alone does not prevent the task from executing.
- ✗
A task that calls a stored procedure to copy files from the external stage and then run transformations sequentially.
Why it's wrong here
While a stored procedure can orchestrate the copy and transformations, it does not automatically detect new files unless the procedure itself lists and compares files. This adds complexity and may not scale well. It also lacks the event-driven nature of Snowpipe and the change tracking of streams, making it less efficient and more error-prone.
- ✗
Snowpipe with a task that runs on a fixed schedule and checks the row count of the raw table.
Why it's wrong here
Checking the row count of the raw table does not indicate whether new data has arrived since the last run; it could trigger on old data or miss new data if the count hasn't changed. This approach lacks the precise change tracking of a stream and may cause redundant or missed transformations. It also does not prevent empty batches reliably.
- ✓
Snowpipe with a stream on the raw table and a task with a WHEN clause checking SYSTEM$STREAM_HAS_DATA.
Why this is correct
Snowpipe automatically loads new files from the external stage into the raw table. A stream on the raw table captures the newly loaded rows. A task scheduled to run periodically can use a WHEN clause with SYSTEM$STREAM_HAS_DATA to check if the stream has data before executing. This ensures transformations run only when new data exists, avoiding empty batches and manual intervention.
About these practice questions
Courseiva writes every ARA-C01 question from scratch — 209 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 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.