ARA-C01 Question 253
Select 2Domain 3.0: Data Engineering (25%)A retail company loads clickstream files every few minutes into an Amazon S3 bucket. A Snowpipe process continuously ingests the raw JSON files into a Snowflake landing table. Downstream, analysts need a curated session-level table with deduplicated records and derived attributes available within 10 minutes of file arrival. The company wants to minimize operational overhead and avoid reprocessing unchanged data. Which TWO actions should the architect recommend?
- A
Create a stream on the landing table and a scheduled task that MERGEs changed rows into the curated session table.
- B
Replace Snowpipe with a daily bulk COPY INTO job, then run a stored procedure to rebuild the curated table from scratch.
- C
Create a materialized view on top of the raw landing table to perform deduplication and sessionization logic automatically.
- D
Use a triggered task on a stream over the landing table so transformations run only when new CDC records are present.
- E
Use TRUNCATE on the landing table after each transformation run so the next task execution processes only newly loaded files.
Show answer and explanation
Correct answers: A, D
Explanation
The best design is to keep Snowpipe for continuous ingestion into a landing table, then use Snowflake Streams and Tasks for incremental transformation into the curated layer. A stream records change data capture information for the source table, allowing downstream logic to process only new or changed rows. A MERGE is commonly used to deduplicate and upsert curated records. To further reduce overhead, a triggered task can execute when stream data is available rather than re-running on a schedule with no work to do. This aligns with Snowflake best practices for incremental ELT pipelines. By contrast, rebuilding from scratch increases cost and latency, materialized views are not a substitute for full transformation orchestration, and truncating landing data undermines replayability and raw data retention. Relevant Snowflake documentation includes guidance on Snowpipe for continuous data ingestion, Streams for change data capture, Tasks including triggered tasks for orchestration, and MERGE for incremental loading patterns.
- A. Correct.
Correct. A stream on the landing table captures row-level change data for inserts made by Snowpipe. A task can then consume only the changed rows and apply a MERGE into the curated table for deduplication and upserts. This is a standard low-maintenance pattern for incremental ELT in Snowflake and avoids reprocessing unchanged data.
- B. Incorrect.
Incorrect. Replacing continuous Snowpipe ingestion with a daily bulk load would fail the near-real-time requirement of having curated data available within 10 minutes. Rebuilding the curated table from scratch also increases compute cost and operational overhead compared with incremental processing.
- C. Incorrect.
Incorrect. Materialized views can improve performance for certain query patterns, but they are not the right tool for complex transformation pipelines involving sessionization, deduplication, and procedural curation logic. Materialized views also have limitations on supported expressions and are intended for query acceleration rather than orchestrating downstream incremental data engineering workflows.
- D. Correct.
Correct. Triggered tasks can be used with streams so processing starts when the stream has data, instead of relying only on a fixed schedule. This reduces unnecessary task executions and supports the goal of minimizing operational overhead while processing only newly arrived changes.
- E. Incorrect.
Incorrect. Truncating the landing table would remove the raw ingested data and can disrupt auditability, replay, and downstream processing patterns. Streams already track change data without requiring table truncation. Clearing the landing table after each run is a common misconception from traditional ETL systems, but it is not a best practice for Snowflake landing zones.