DAA-C01 Question 55
Single answerAutomate and implement data pipelinesA retail analytics team needs to automate a near-real-time pipeline in Snowflake that ingests JSON order files from an external stage every few minutes, transforms only newly arrived records, and keeps a curated reporting table up to date with minimal custom orchestration. The team also wants the pipeline to avoid reprocessing files that were already loaded. Which Snowflake design best meets these requirements?
- A
Create a Snowpipe on the external stage to continuously load raw files into a landing table, create a stream on the landing table to capture newly inserted rows, and create a triggered task that transforms stream data into the curated reporting table.
- B
Schedule a task to run COPY INTO the landing table every 5 minutes and then truncate the stage after each load so files are not reprocessed before inserting all landing table rows into the curated reporting table.
- C
Use a materialized view on the external stage to automatically ingest JSON files, then schedule a task to merge the materialized view into the curated reporting table.
- D
Create a stream directly on the external stage and use a task to read only newly arrived files from the stream and insert transformed rows into the curated reporting table.
Show answer and explanation
Correct answer: A
Explanation
The best answer is the Snowpipe + stream + task pattern. Snowpipe is designed for continuous data ingestion from staged files and automatically tracks load history to avoid reloading the same files in normal operation. After raw data lands in a table, a stream records row-level changes so downstream transformations can process only new data. A triggered task can then run when stream data is available, reducing unnecessary polling and custom orchestration. This approach is practical for near-real-time pipelines and is commonly recommended in Snowflake documentation for continuous ingestion, change data capture with streams, and task-based orchestration. By contrast, relying only on scheduled COPY jobs increases latency and operational overhead, and options involving materialized views or streams directly on stages describe unsupported or incorrect capabilities.
- A. Correct.
Correct. This is the standard Snowflake pattern for low-latency automated pipelines with minimal orchestration. Snowpipe provides continuous file ingestion from stages into a table and tracks file load metadata so previously loaded files are not reprocessed by COPY-based ingestion. A stream on the landing table captures change data, allowing downstream processing to consume only newly inserted rows. A triggered task can execute when the stream has data, transforming and loading only new records into the curated table. This design aligns with Snowflake best practices for automated ingestion and incremental ELT.
- B. Incorrect.
Incorrect. A scheduled task running COPY INTO can automate batch loading, but this option is weaker for near-real-time requirements than Snowpipe. Truncating the stage is also not the correct mechanism to prevent reprocessing; Snowflake already tracks loaded files for COPY/Snowpipe under supported conditions. In addition, inserting all landing table rows into the curated table each run ignores incremental processing and can cause duplicate processing or unnecessary compute unless additional logic is added.
- C. Incorrect.
Incorrect. Materialized views cannot be created directly on an external stage to ingest staged JSON files. Staged files must first be loaded or queried via external tables or COPY-based mechanisms, depending on the use case. This option reflects a common misconception that materialized views can replace ingestion services such as Snowpipe.
- D. Incorrect.
Incorrect. Streams can be created on tables, views, dynamic tables, and certain other supported objects, but not directly on an external stage for tracking newly arrived files. To process new files incrementally, Snowflake typically uses Snowpipe for ingestion and streams on tables for downstream CDC-style processing. This option confuses file arrival tracking with table change tracking.