COF-C03 Question 233
Single answerStreamsA data engineering team loads raw order records into a Snowflake table named ORDERS_RAW throughout the day. They created a standard stream named ORDERS_RAW_STRM on that table and run a task every 15 minutes to merge new and changed rows into a curated table. After a weekend with no task runs, the Monday morning job fails because some change records are no longer available from the stream. The team wants to prevent this issue in the future while keeping the same source table and stream-based pattern. Which action should they take?
- A
Increase the data retention period for the source table so the stream can access change tracking metadata for a longer time
- B
Convert the standard stream to an external table stream so Snowflake stores change records permanently
- C
Recreate the stream as an append-only stream because append-only streams do not depend on table version history
- D
Clone the source table every day and point the stream to the latest clone to preserve unconsumed stream records
Show answer and explanation
Correct answer: A
Explanation
Streams in Snowflake record an offset against a source object and use change tracking metadata to expose inserted, deleted, and in some cases updated row changes since the last consumed offset. They do not store an unlimited backlog of changes independently of the source object. If a stream is not consumed before the source table's retention window expires, the stream can become stale because Snowflake can no longer access the necessary historical metadata. The practical fix is to align the source table's DATA_RETENTION_TIME_IN_DAYS with the expected maximum delay between stream consumptions. This is a common operational consideration for task-driven pipelines that may pause over weekends or holidays. Best practice is to ensure regular consumption or set an adequate retention period on the source table/schema/database, depending on governance requirements.
- A. Correct.
Correct. A stream relies on the source object's change tracking metadata and table version history. If the stream is not consumed within the source object's data retention window, Snowflake can no longer reconstruct the change records needed by the stream, and the stream can become stale. Increasing the table's data retention period gives the team a longer window to consume the stream after periods of inactivity, such as weekends.
- B. Incorrect.
Incorrect. Streams can be created on external tables, but that does not mean Snowflake permanently stores change records for a standard table stream by converting it to an external table stream. External tables are for data in external storage and are a different source object type. This option changes the architecture rather than solving the retention issue for the existing table.
- C. Incorrect.
Incorrect. Append-only streams track inserted rows only and are useful when updates and deletes are not needed, but they still depend on the underlying table's change tracking/version history within the retention window. Changing to append-only would also lose visibility into updates and deletes, which is risky in a merge-based curation process.
- D. Incorrect.
Incorrect. Cloning a table does not preserve an existing stream's unread offset in a way that solves staleness for the original source table and stream. Pointing a stream to different clones would complicate lineage and processing, and it does not address the root cause: the source table's retention window being too short for the stream consumption pattern.