DAA-C01 Question 51
Single answer1.5 Implement data processing solutions.A retail analytics team receives hourly sales files in an internal stage. Analysts need a curated table that is refreshed automatically as new files arrive, with transformations that standardize timestamps, cast numeric fields, and derive gross margin. The team wants a solution that minimizes operational overhead and avoids reprocessing unchanged source data. Which approach best meets these requirements in Snowflake?
- A
Create a task that runs every hour and executes a full INSERT ... SELECT from the staged files into the curated table.
- B
Create a pipe with Snowpipe to load raw staged files into a landing table, then create a stream on the landing table and a task that MERGEs only stream-captured changes into the curated table with the required transformations.
- C
Create a materialized view directly on the staged files and query it for transformed data whenever analysts need refreshed results.
- D
Use dynamic data masking policies on the landing table to standardize timestamps and calculate gross margin during query execution.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use Snowpipe for continuous ingestion into a landing table, then use a stream and task for incremental transformation and loading into the curated table. This aligns with Snowflake best practices for implementing low-maintenance data processing solutions: Snowpipe handles automated file ingestion from stages, streams provide change data capture semantics on tables by tracking inserts/updates/deletes since the last consumed offset, and tasks schedule SQL-based downstream processing such as MERGE operations. This design minimizes reprocessing because the task reads only changed rows from the stream rather than rescanning all source files. It also keeps the pipeline modular: raw ingestion is separated from business transformations. Relevant Snowflake documentation areas include Snowpipe for continuous data ingestion, Streams for change tracking on tables, and Tasks for scheduling SQL workflows.
- A. Incorrect.
This is inefficient and does not meet the requirement to avoid reprocessing unchanged data. A scheduled full INSERT ... SELECT from staged files typically rereads all available source files unless additional file-tracking logic is built. That increases operational overhead and can lead to duplicate processing or more complex custom controls. While tasks can orchestrate SQL-based processing, using only a periodic full load is not the best fit for incremental processing.
- B. Correct.
This is correct. Snowpipe is designed to continuously load arriving files from stages into a table with low operational overhead. A stream on the landing table tracks row-level changes since the last offset consumption, and a task can use those changes to incrementally apply transformations and MERGE into the curated table. This pattern is a common Snowflake best practice for near-real-time, incremental ELT pipelines that avoid reprocessing unchanged data.
- C. Incorrect.
This is incorrect because materialized views cannot be created directly over staged files. Materialized views are defined on base tables, not on raw files in a stage. Even if a table existed, materialized views are not the primary mechanism for orchestrating file ingestion plus custom transformation logic like derived gross margin and controlled merge behavior.
- D. Incorrect.
This is incorrect because masking policies are a governance feature for protecting sensitive data, not a data processing mechanism for transformation pipelines. They do not ingest files, standardize values in storage, or calculate persisted derived measures as part of an automated incremental refresh process.