DAA-C01 Question 248
Single answerBuild automated and repeatable tasksA retail analytics team maintains a DAILY_SALES_SUMMARY table that must be refreshed every morning after new files land in an external stage. The team wants a solution inside Snowflake that is automated, repeatable, and efficient: the refresh SQL should run only when new staged data is available, and the orchestration should not require an external scheduler. Which implementation best meets these requirements?
- A
Create a stream on the external stage and a scheduled task that checks the stream for new files before running the refresh query.
- B
Create a task with a WHEN condition that evaluates SYSTEM$STREAM_HAS_DATA on a stream built on a staging table populated from Snowpipe, and have the task run the refresh SQL.
- C
Create a materialized view on the staged files and use a task to refresh the materialized view only when new files arrive.
- D
Create an alert that monitors the stage for new files and directly executes the refresh SQL when the alert condition is met.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use Snowpipe to ingest new staged files into a landing or staging table, create a stream on that table, and define a task with a WHEN clause using SYSTEM$STREAM_HAS_DATA. This pattern is efficient because the task runs only when new change data exists, and it is fully Snowflake-native, so no external scheduler is required. In Snowflake, tasks are used to schedule and orchestrate SQL work, while streams provide change data capture semantics on supported objects such as tables. SYSTEM$STREAM_HAS_DATA is specifically intended to let a task determine whether a stream contains unconsumed records before executing downstream logic. This is a common best practice for building automated and repeatable tasks in analytics pipelines. Relevant Snowflake documentation areas include Tasks, Streams, Snowpipe, and the SYSTEM$STREAM_HAS_DATA function.
- A. Incorrect.
Incorrect. Snowflake streams track change data on supported objects such as tables, including standard and external tables, but not directly on an external stage object. A common misconception is that stages themselves can be streamed. In practice, new files in an external stage are typically ingested into a table using Snowpipe or COPY INTO, and then a stream can track row-level changes in that table. Because the stream target here is invalid, this design does not correctly solve the problem.
- B. Correct.
Correct. This is the standard Snowflake-native pattern for automated, repeatable processing without an external scheduler. New files are ingested into a staging table via Snowpipe, a stream captures new rows in that table, and a task uses a WHEN clause with SYSTEM$STREAM_HAS_DATA to run only when the stream contains unconsumed change data. This avoids unnecessary task executions and keeps orchestration inside Snowflake. It is well aligned with Snowflake best practices for event-driven ELT pipelines.
- C. Incorrect.
Incorrect. Materialized views are not created directly over staged files. They are defined over supported query results from base tables. Also, the requirement is to refresh a target summary table only when new data arrives, not to maintain a materialized view over staged data. This option confuses query acceleration features with ingestion and orchestration features.
- D. Incorrect.
Incorrect. Alerts in Snowflake are designed to execute actions based on the result of a SQL condition, but they are not the primary mechanism for stage-file-driven data pipeline orchestration. More importantly, monitoring a stage directly for file arrival is not the typical native pattern for this use case. Tasks combined with streams and Snowpipe are the appropriate approach for automated repeatable data refresh workflows.