DAA-C01 Question 60
Single answerRespond to processing failuresA retail analytics team uses a TASK to run every 5 minutes and populate a reporting table from a STREAM on top of an orders staging table. On Monday morning, analysts report that the reporting table is stale. Investigation shows the task failed several times overnight because the SQL logic referenced a dropped column. The developer has already fixed the SQL text in the task definition. The team wants to resume processing with minimal data loss and without reprocessing rows that were already consumed successfully before the failures. What should the data analyst do next?
- A
Resume the task and allow it to consume the existing unconsumed stream records after the SQL fix is in place.
- B
Recreate the stream so the task starts from the current table state and avoids duplicate processing.
- C
Run ALTER TASK ... RETRY LAST to force Snowflake to replay every previously successful task run.
- D
Truncate the target reporting table, then resume the task so the stream can repopulate everything from the source table.
Show answer and explanation
Correct answer: A
Explanation
This scenario tests recovery from processing failures in a common Snowflake pattern: TASK + STREAM for incremental ELT. Best practice is to understand stream consumption semantics during failures. A stream records table changes relative to an offset, and those records are consumed only when the DML transaction that reads the stream commits successfully. If the task fails before commit, the stream still retains those unconsumed changes. Therefore, once the task SQL is fixed, the correct action is to resume task execution so it processes the backlog from the existing stream state. Recreating the stream or truncating the target can introduce data loss or unnecessary reprocessing. Snowflake documentation on streams and tasks explains that streams support transactional consistency for CDC consumption and that task recovery should be aligned with whether prior runs committed successfully.
- A. Correct.
Correct. When a task that reads from a stream fails, the stream offset is not advanced unless the transaction successfully commits. This means unconsumed change records remain available in the stream. After fixing the task SQL, resuming the task is the appropriate recovery action to continue processing pending changes with minimal data loss and without reprocessing already committed work.
- B. Incorrect.
Incorrect. Recreating the stream resets its offset semantics and can discard access to the backlog of unconsumed CDC records that accumulated while the task was failing. This is the opposite of the stated goal, which is to preserve pending changes and continue from the last successful consumption point.
- C. Incorrect.
Incorrect. RETRY LAST is used to retry the last failed task graph run in supported task graph scenarios; it does not replay all previously successful executions, nor is it the normal mechanism for recovering a standalone recurring task after the SQL definition has been corrected. The key issue here is preserving and consuming the stream backlog, which occurs by resuming successful task execution.
- D. Incorrect.
Incorrect. Truncating the target table is unnecessary and risky. A stream does not automatically repopulate the entire source table; it tracks change data from an offset. Truncating the target would remove good historical results and could require a separate full reload strategy, which does not align with the requirement to avoid reprocessing successfully consumed rows.