ARA-C01 Question 264
Single answerStreaming dataA retail company is ingesting clickstream events from Kafka into Snowflake continuously. The events arrive out of order and occasionally contain updates for the same business event ID within a few minutes of the original message. Architects must design a near-real-time pipeline that makes new data queryable within minutes, minimizes custom code, and ensures downstream tables reflect the latest version of each event ID without reprocessing the entire dataset. Which solution best meets these requirements?
- A
Use Snowpipe Streaming to ingest events into a landing table, then create a stream on that table and use a task to MERGE changes into a curated table keyed by event ID.
- B
Use a materialized view on top of the landing table with DISTINCT on event ID so downstream users always see only the newest record automatically.
- C
Batch-load Kafka files every hour into an internal stage, then run COPY INTO followed by INSERT OVERWRITE on the target table to refresh all records.
- D
Use external tables over the Kafka topic data and schedule a task to run UPDATE statements directly against the external table whenever new events arrive.
Show answer and explanation
Correct answer: A
Explanation
The best design is to use Snowpipe Streaming for low-latency ingestion into a landing table, then use a stream and task to apply incremental changes into a curated table with MERGE logic. This is a standard Snowflake architecture for streaming data when records may arrive out of order or be updated shortly after initial ingestion. Streams track row-level changes in Snowflake tables, and tasks schedule SQL-based processing, which reduces custom code and avoids repeatedly scanning the entire dataset. MERGE is the key operation because it allows inserts and updates to be applied based on the business key, such as event ID, so downstream consumers see the latest known version of each event. Snowflake documentation and best practices for continuous ingestion emphasize Snowpipe Streaming for low-latency data arrival, streams for CDC-style tracking, and tasks for orchestration of incremental ELT workflows.
- A. Correct.
Correct. Snowpipe Streaming is designed for low-latency ingestion into Snowflake without relying on file staging, making it suitable for near-real-time Kafka-style event ingestion. A stream on the landing table captures change data for newly ingested rows, and a task can execute a MERGE into a curated target table keyed by event ID. This pattern supports incremental processing, minimizes custom orchestration, and allows the target table to keep only the latest version of each business event without rescanning the full dataset each run.
- B. Incorrect.
Incorrect. A materialized view can accelerate certain query patterns, but it is not the right mechanism for maintaining latest-record semantics for out-of-order updates by business key. Using DISTINCT does not reliably select the newest version of an event ID, and materialized views have SQL and maintenance constraints that make them a poor fit for deduplicating mutable event streams. This option reflects a common misconception that materialized views can replace CDC-driven merge logic.
- C. Incorrect.
Incorrect. This approach increases latency because data is loaded only hourly, which does not meet the requirement for queryability within minutes. It also refreshes the target more broadly than necessary. INSERT OVERWRITE or similar full refresh patterns reprocess large portions of data unnecessarily and are less efficient than incremental ingestion plus MERGE for continuously arriving event streams with late updates.
- D. Incorrect.
Incorrect. External tables expose data in external storage, not directly over a Kafka topic. In addition, external tables are not updated with DML in the same way as regular Snowflake tables. This option combines two unsupported ideas: querying a Kafka topic as an external table and issuing UPDATE statements against it. It is a plausible distractor because architects may associate external tables with streaming-adjacent ingestion, but it does not match Snowflake capabilities.