ARA-C01 Question 336
Single answer3.3 Determine the appropriate data transformation solution to meet business needs.A retail company loads clickstream files into a LANDING schema every 5 minutes. The data engineering team must transform this raw data into a curated FACT_CLICKS table with the following requirements: transformations must run automatically when new files arrive, duplicate processing must be avoided, orchestration logic should remain inside Snowflake as much as possible, and the solution should be easy to monitor and maintain. Which solution best meets these requirements?
- A
Create a stream on the raw landing table and a triggered task that executes a MERGE into FACT_CLICKS when the stream has data.
- B
Schedule a stored procedure with a cron-based task to truncate and reload FACT_CLICKS from the landing table every 5 minutes.
- C
Use dynamic tables on the landing table because they are designed to execute immediately when files arrive and guarantee exactly-once processing of external loads.
- D
Configure a materialized view on the landing table to replace FACT_CLICKS because materialized views support procedural transformation logic and downstream deduplication.
Show answer and explanation
Correct answer: A
Explanation
The best fit is a stream plus a triggered task that performs an incremental MERGE into the curated table. This design satisfies the business requirements by: 1) running automatically when new data arrives in the source table, 2) minimizing duplicate processing through change tracking and merge logic, 3) keeping orchestration inside Snowflake, and 4) remaining straightforward to monitor using TASK_HISTORY and related metadata. In Snowflake best practices, streams are commonly used to capture row-level changes for downstream consumption, while tasks orchestrate SQL or stored procedure execution. Triggered tasks are appropriate when execution should occur based on data availability rather than only on a fixed schedule. Dynamic tables are valuable for declarative transformation pipelines, especially when target lag-based refresh is acceptable, but they are not the best answer for this event-driven CDC-style requirement. Materialized views are primarily for query performance optimization, not full transformation orchestration. Relevant Snowflake documentation includes guidance on Streams, Tasks, Triggered Tasks, MERGE, and Dynamic Tables.
- A. Correct.
Correct. A stream records change data capture information on the landing table, and a triggered task can run when new data is available rather than on a fixed polling schedule. Using a MERGE into the curated table supports incremental processing and deduplication logic, helping avoid reprocessing the same rows. This approach keeps orchestration within Snowflake and is aligned with best practices for event-driven ELT pipelines that need maintainability and monitoring through Snowflake tasks and task history.
- B. Incorrect.
Incorrect. A scheduled task can automate execution, but truncating and reloading the full FACT_CLICKS table every 5 minutes is inefficient and does not address duplicate processing as well as an incremental pattern. It also increases compute usage and can introduce unnecessary churn for downstream consumers. This option reflects a common misconception that frequent full refreshes are simpler, but they are usually less appropriate than incremental transformations for continuously arriving data.
- C. Incorrect.
Incorrect. Dynamic tables are useful for declarative pipeline transformations, but this option is inaccurate because they do not execute immediately based on file arrival events in the way triggered tasks respond to upstream changes, and the statement about guaranteeing exactly-once processing of external loads is overstated. They are driven by target lag and dependency refresh behavior, not direct file-arrival triggers. Candidates might choose this because dynamic tables are a modern transformation feature, but the scenario specifically emphasizes automatic execution on arrival and duplicate avoidance through explicit incremental orchestration.
- D. Incorrect.
Incorrect. Materialized views are intended for query acceleration on a single base table with limited transformation patterns, not as a replacement for a curated fact table that requires procedural transformation logic and deduplication workflows. They do not support the broader ETL/ELT orchestration needed here. This distractor targets the misconception that materialized views are a general-purpose transformation framework.