ARA-C01 Question 267
Single answerChange Data Capture (CDC)A retail company stores order records in a Snowflake table named ORDERS_RAW. Downstream systems need a near-real-time feed of inserts and updates only, and each changed row must be delivered exactly once to a curated table. Deletes should be ignored. The current design uses ad hoc queries against ORDERS_RAW with timestamp filters, but late-arriving updates and overlapping job windows have caused duplicate and missed records. As the Snowflake architect, which approach should be implemented to provide the most reliable CDC pattern with minimal custom state management?
- A
Create a stream on ORDERS_RAW and have a task consume the stream with a MERGE into the curated table, filtering out DELETE actions.
- B
Use Time Travel queries on ORDERS_RAW every 5 minutes and compare the current version to the version from 5 minutes ago, then insert differences into the curated table.
- C
Query QUERY_HISTORY to identify DML statements against ORDERS_RAW and replay the affected rows into the curated table.
- D
Add a LAST_MODIFIED timestamp column to ORDERS_RAW and schedule a task that selects rows where LAST_MODIFIED is greater than the previous run time stored in a control table.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a stream on the source table and a task to consume that stream into the curated target. In Snowflake, streams provide change tracking for tables and expose CDC metadata columns such as METADATA$ACTION and METADATA$ISUPDATE, enabling downstream logic to distinguish inserts, updates, and deletes. Tasks can schedule or orchestrate the stream consumption logic. This pattern is generally preferred over hand-built watermark solutions because the stream maintains an offset, which reduces the need for external state management and helps avoid missed or duplicate processing caused by overlapping windows or late updates. For this scenario, deletes can be ignored during the MERGE by filtering out rows where the stream metadata indicates a DELETE. This aligns with Snowflake best practices for CDC using streams and tasks rather than relying on Time Travel diffs, query history analysis, or custom timestamp tracking.
- A. Correct.
Correct. Snowflake streams are designed to capture table change data between offsets and are a standard CDC mechanism for downstream processing. A stream records row-level changes for inserts, updates, and deletes. A task can reliably consume the stream and MERGE changes into a target table. Because updates are represented through CDC metadata and the stream advances only when consumed in a DML transaction, this pattern minimizes custom watermark management and reduces duplicate or missed processing. DELETE actions can be excluded by checking stream metadata such as METADATA$ACTION during consumption.
- B. Incorrect.
Incorrect. Time Travel allows querying historical table states, but using periodic diffs between versions is more complex, more expensive at scale, and requires custom logic to identify net changes and manage processing windows. It is not the preferred built-in CDC mechanism for ongoing downstream delivery. This approach also increases the risk of operational complexity compared with streams.
- C. Incorrect.
Incorrect. QUERY_HISTORY contains metadata about executed queries, not a row-level CDC feed that can be safely replayed into downstream tables. DML history does not provide a supported, reliable mechanism for exactly-once row delivery, especially when statements affect many rows or use complex predicates. It is useful for auditing and troubleshooting, not as the primary CDC source.
- D. Incorrect.
Incorrect. A timestamp-based watermark pattern is common in some systems, but it depends on the application or pipeline correctly maintaining LAST_MODIFIED values and handling clock skew, late-arriving updates, and retries. It also requires custom state tracking and can still produce missed or duplicated rows around boundary conditions. Snowflake streams are specifically intended to solve this CDC use case more reliably.