ARA-C01 Question 268
Single answerChange Data Capture (CDC)A retail company lands order events into a Snowflake table named RAW_ORDERS throughout the day. Multiple downstream teams need near-real-time change data capture (CDC) from this table to populate dimensional models and trigger quality checks. The architect wants a solution that minimizes custom code, allows each consumer to progress independently through the change stream, and supports inserts, updates, and deletes. Which approach best meets these requirements?
- A
Create a standard stream on RAW_ORDERS for each downstream consumer and have each consumer read from its own stream.
- B
Create a single standard stream on RAW_ORDERS and let all downstream consumers query the same stream because streams do not track read offsets.
- C
Enable Time Travel retention on RAW_ORDERS and have each consumer use AT | BEFORE queries to infer changes since its last run instead of using streams.
- D
Create a materialized view on RAW_ORDERS and have each consumer query the materialized view because materialized views automatically expose row-level CDC metadata.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create a separate standard stream on RAW_ORDERS for each downstream consumer. Snowflake streams are designed for change data capture by storing an offset against the underlying table's transactional version history and exposing changed rows for downstream processing. Since each stream maintains its own position, multiple consumers that run on different schedules or have different retry behaviors should not share a single stream. This is a key architectural best practice for fan-out CDC patterns in Snowflake. Time Travel is useful for historical querying and recovery but typically requires custom comparison logic and is not the preferred mechanism for operational CDC. Materialized views are unrelated to CDC offset tracking and do not replace streams. These behaviors align with Snowflake documentation on streams, tasks, and change tracking/CDC design patterns.
- A. Correct.
Correct. In Snowflake, a stream records CDC information for a source object, including inserts, updates, and deletes, by tracking an offset relative to the source object's version history. Because a stream maintains consumption state, different consumers that need independent progress should use separate streams on the same source table. This minimizes custom CDC logic and is the standard Snowflake design pattern when multiple downstream processes consume changes at different cadences.
- B. Incorrect.
Incorrect. This reflects a common misconception about streams. A stream does track an offset, and consuming changes from a single shared stream advances that stream's position. If multiple downstream consumers share one stream, they cannot independently control which changes they have processed. One consumer could effectively consume changes needed by another, making this design unsuitable for independent downstream pipelines.
- C. Incorrect.
Incorrect. Time Travel can be used to query prior table states, but it is not a purpose-built CDC mechanism for multiple independent consumers. Each consumer would need custom logic to compare versions or timestamps and infer inserts, updates, and deletes, which increases complexity and can be error-prone. This approach also does not provide the streamlined, offset-based consumption model that streams offer for CDC workloads.
- D. Incorrect.
Incorrect. Materialized views improve query performance for defined query results, but they do not provide CDC semantics or expose row-level change metadata in the way streams do. They are not designed to track independent consumer progress or to serve as a replacement for Snowflake CDC patterns involving inserts, updates, and deletes.