DAA-C01 Question 249
Single answerBuild automated and repeatable tasksA retail analytics team loads new sales files into an internal stage several times per day. They need a repeatable Snowflake-native process that runs without manual intervention, ingests only newly arrived files into a raw table, and then refreshes a reporting table after the load completes. The team wants to minimize custom orchestration code and ensure the second step runs only after new data is successfully loaded. Which approach best meets these requirements?
- A
Create a scheduled task that runs a COPY INTO command from the stage into the raw table, and create a second task with an AFTER dependency to refresh the reporting table.
- B
Create a stream on the stage, then create a task that reads from the stream and automatically loads new files into the raw table before updating the reporting table.
- C
Create a materialized view on the staged files, and use a task to refresh the materialized view so the reporting table updates automatically.
- D
Create a single task that runs ALTER STAGE ... REFRESH and expect Snowflake to automatically detect new files, load them into the raw table, and refresh the reporting table.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use Snowflake tasks to orchestrate a repeatable SQL-based pipeline: one task runs COPY INTO to load new files from the stage into a raw table, and a dependent task runs AFTER the load task succeeds to refresh or rebuild the reporting table. This aligns with Snowflake best practices for native automation when the goal is to minimize external orchestration. COPY INTO is the standard mechanism for staged file ingestion, and Snowflake maintains load history to prevent duplicate loading of the same files under normal operation. Task graphs support predecessor-successor relationships using AFTER, which is appropriate when one step must run only after another completes successfully. Relevant Snowflake documentation includes topics for Tasks, Task Graphs, and COPY INTO
behavior for staged file loading and load metadata tracking.- A. Correct.
Correct. A task can run SQL on a schedule, including a COPY INTO command to ingest files from a stage into a table. Snowflake tracks load metadata for staged files loaded by COPY INTO, helping avoid reloading the same files unless explicitly forced. A second task can be linked with an AFTER dependency so it executes only after the first task completes successfully. This is the most direct Snowflake-native way to build an automated, repeatable pipeline with task orchestration and minimal external code.
- B. Incorrect.
Incorrect. Streams track change data on supported objects such as tables, views, dynamic tables, and Apache Iceberg tables in supported patterns, but they are not used to track files arriving in a stage for ingestion. A common misconception is that streams can monitor any upstream source of change, including stages. For staged file ingestion, COPY INTO is the correct loading mechanism, often orchestrated by tasks.
- C. Incorrect.
Incorrect. Materialized views are not designed to read staged files directly or replace file-ingestion pipelines. They accelerate query performance for certain SELECT statements on base tables. They do not ingest files from a stage into a raw table. Someone might choose this option because materialized views do refresh automatically, but that capability applies to derived query results over tables, not staged file processing.
- D. Incorrect.
Incorrect. ALTER STAGE ... REFRESH is associated with refreshing metadata for external stages in specific contexts, not with loading files into tables or orchestrating downstream transformations. It does not by itself execute COPY INTO or refresh a reporting table. This option reflects a misunderstanding that stage metadata refresh is equivalent to data ingestion and workflow orchestration.