ARA-C01 Question 254
Single answerDomain 3.0: Data Engineering (25%)A retail company loads point-of-sale data into Snowflake every 5 minutes. Each file lands in cloud storage and contains inserts, updates, and occasional deletes for the SALES_ORDER table. The source system does not guarantee event ordering, and duplicate records for the same ORDER_ID can appear across files. The analytics team needs the target table to reflect only the latest version of each order with deleted rows removed, while minimizing custom orchestration and avoiding full-table reloads. Which approach should the architect recommend?
- A
Create a Snowpipe on the stage to continuously load all files into a raw landing table, then use a stream on that raw table and a task that runs a MERGE into SALES_ORDER after deduplicating by ORDER_ID and latest event timestamp, including delete handling.
- B
Load files directly into SALES_ORDER with Snowpipe using ON_ERROR = CONTINUE, and rely on the COPY command to automatically upsert newer rows and remove deleted records based on ORDER_ID.
- C
Use a materialized view on top of the staged files to keep only the latest ORDER_ID record, and expose that materialized view as the production SALES_ORDER table.
- D
Schedule a nightly TRUNCATE and full reload of SALES_ORDER from all historical files in cloud storage, because streams cannot track change data for continuously loaded tables.
Show answer and explanation
Correct answer: A
Explanation
The best recommendation is to separate ingestion from reconciliation: land incoming files with Snowpipe into a raw table, then use Streams and Tasks to process incremental changes into the production table with MERGE logic. This is a standard Snowflake design pattern for file-based CDC-like ingestion. Because the source files can arrive out of order and contain duplicates, the task should first identify the latest record per ORDER_ID, typically using a reliable event timestamp or version column and windowing logic, before applying MERGE operations. Deletes should be handled explicitly in the MERGE when the source row indicates a delete operation.
Why this fits Snowflake best practices:
- Snowpipe is designed for continuous file ingestion from cloud storage with low operational overhead.
- Streams provide change tracking on tables, enabling incremental consumption of newly landed rows.
- Tasks orchestrate SQL-based transformations on a schedule or dependency chain without requiring an external scheduler for each step.
- MERGE is the native Snowflake construct for applying inserts, updates, and deletes incrementally to a target table.
Relevant Snowflake documentation and best-practice areas include Snowpipe for continuous data ingestion, Streams and Tasks for continuous ELT pipelines, and MERGE for upsert/delete processing. This pattern is preferred over attempting to load directly into the target table when business-key deduplication, late-arriving data handling, and delete processing are required.
- A. Correct.
Correct. This is a common Snowflake pattern for near-real-time CDC-style ingestion when files contain inserts, updates, and deletes. Snowpipe is well-suited for continuously ingesting files into a raw table with minimal orchestration. A stream on the raw table captures newly ingested rows, and a task can run SQL logic to deduplicate records and MERGE into the target table. The MERGE can implement business rules such as keeping only the latest event per ORDER_ID and deleting rows when the source record indicates a delete. This avoids full reloads and uses native Snowflake components for incremental processing.
- B. Incorrect.
Incorrect. Snowpipe uses COPY INTO semantics and loads rows into a table, but COPY does not perform automatic upserts or deletes based on business keys such as ORDER_ID. ON_ERROR = CONTINUE only affects error handling during file load; it does not provide CDC logic. To apply updates and deletes, you still need downstream SQL processing such as MERGE.
- C. Incorrect.
Incorrect. Materialized views in Snowflake are not defined directly over staged files for this use case, and they are not the right mechanism for implementing CDC reconciliation with deletes and late-arriving duplicate events. Even when materialized views are used on tables, they are intended for query acceleration on deterministic expressions, not for replacing a target table that requires explicit merge/delete business logic.
- D. Incorrect.
Incorrect. A nightly truncate-and-reload approach contradicts the stated requirement to avoid full-table reloads and does not provide near-real-time freshness. Also, streams can track row-level changes on tables, including tables populated by Snowpipe, making them appropriate for incremental downstream processing. The misconception is assuming streams are incompatible with continuously loaded tables.