ARA-C01 Data Engineering Practice Question
A Snowflake architect is implementing a Type-2 slowly changing dimension (SCD2) on the CUSTOMER_DIM table using a Stream on the source table CUSTOMER_RAW and a task that runs every 5 minutes. The task currently reads the stream and applies a MERGE that only handles inserts and updates. Historical versions are lost. The architect must preserve prior attribute values for changed customers and mark each row with effective and end timestamps. Which approach should the architect use to meet this requirement?
⚠ Common exam trap
The trap here is assuming that adding a version counter and updating values in place preserves history, when in fact overwriting the attributes destroys the prior versions that SCD2 must retain.
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
✓
Use a MERGE that, for matched rows whose tracked attributes changed, sets the end timestamp and an is_current flag to false on the existing row, and inserts a new row with a new effective timestamp and is_current true.
SCD2 requires that a changed business key produce a new row while the previous row is expired rather than overwritten. A MERGE driven by the stream can detect changed attributes, close the existing current row with an end timestamp and a false current flag, and insert a new current row with a new effective timestamp. This preserves complete history and keeps exactly one active version per 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.
- ✗
Add a row-version column to CUSTOMER_DIM and use a MERGE with a WHEN MATCHED THEN UPDATE clause that increments the version and overwrites the attribute values in place.
Why it's wrong here
Incrementing a version column while overwriting attributes in place destroys the prior values, so no history survives. A MERGE that only performs updates cannot produce a new row for each change, and SCD2 requires old rows to remain queryable with their original attributes and an end timestamp. This approach also fails to set effective/end dates correctly and would collapse all versions into a single current record.
- ✗
Configure the CUSTOMER_DIM table with CHANGE_TRACKING = TRUE and query the table's change tracking metadata to reconstruct prior versions on demand.
Why it's wrong here
Change tracking records which rows changed between two points in time for incremental processing, but it retains only limited metadata and does not store the prior attribute values needed to reconstruct historical dimension rows. It cannot supply effective and end timestamps, and the change data expires according to the table's data retention, so long-term SCD2 history would be unavailable.
- ✗
Replace the task with a Materialized View over CUSTOMER_RAW that automatically retains a copy of each historical version whenever the base table changes.
Why it's wrong here
Materialized views in Snowflake do not maintain historical versions of rows; they store only the current result and are refreshed as the base table changes. They also cannot express SCD2 semantics such as effective and end timestamps or an is_current flag, and they have restrictions on joins and aggregation that make them unsuitable for this dimension. No history would be preserved.
- ✓
Use a MERGE that, for matched rows whose tracked attributes changed, sets the end timestamp and an is_current flag to false on the existing row, and inserts a new row with a new effective timestamp and is_current true.
Why this is correct
This is the standard SCD2 pattern in Snowflake: the MERGE detects attribute changes from the stream, expires the current row by setting its end timestamp and clearing the current flag, and inserts a fresh version with a new effective date. It preserves full history, keeps exactly one current row per business key, and can be driven entirely by the stream's change metadata inside a scheduled task.
About these practice questions
One of 209 original ARA-C01 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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.