DAA-C01 Question 56
Single answerAutomate and implement data pipelinesA retail analytics team receives hourly sales files in an Amazon S3 bucket. They need a Snowflake pipeline that automatically ingests new files into a raw table, transforms only the newly arrived rows into a curated fact table, and minimizes operational overhead. The team also wants to avoid reprocessing unchanged data each hour. Which Snowflake design best meets these requirements?
- A
Create an external stage on the S3 bucket, use Snowpipe to automatically load new files into the raw table, create a stream on the raw table, and use a triggered task to MERGE stream records into the curated fact table.
- B
Schedule a task to run COPY INTO from the S3 bucket every hour into the raw table, then run INSERT OVERWRITE on the curated fact table from the full raw table to ensure all changes are captured.
- C
Use a materialized view on the external stage to detect new files, then schedule a task to refresh the materialized view and insert its rows into the curated fact table.
- D
Load files manually into a temporary table, create a dynamic table on top of that temporary table, and rely on the dynamic table to permanently retain the incremental changes after the temporary table is dropped.
Show answer and explanation
Correct answer: A
Explanation
The best-practice Snowflake pattern for this scenario is: external stage + Snowpipe for automatic file ingestion, then stream + task for incremental downstream processing. Snowpipe is designed to continuously load data from cloud storage as files arrive, reducing manual scheduling overhead. Streams provide change data capture semantics on Snowflake tables by recording an offset and exposing inserted, updated, or deleted rows since the last consumption point. Tasks orchestrate SQL execution on a schedule or in response to a condition, and triggered tasks are commonly paired with streams so transformations run only when new data is available. Using MERGE into the curated fact table allows idempotent incremental upserts instead of repeatedly rebuilding from the full raw dataset. This aligns with Snowflake documentation and recommended design patterns for automated ELT pipelines, especially when minimizing reprocessing and operational overhead is important.
- A. Correct.
Correct. This design uses Snowpipe for automated continuous file ingestion from S3 into a Snowflake table, which minimizes operational overhead compared to scheduled polling with custom logic. A stream on the raw table tracks row-level changes since the last consumption offset, allowing downstream processing of only new or changed records rather than rescanning the full dataset. A triggered task can execute when the stream has data and apply a MERGE into the curated fact table, which is a standard Snowflake pattern for incremental ELT pipelines.
- B. Incorrect.
Incorrect. A scheduled task running COPY INTO every hour can ingest data, but it is less automated and typically less responsive than Snowpipe for event-driven continuous file loads. More importantly, rebuilding or overwriting the curated fact table from the entire raw table each hour does not meet the requirement to transform only newly arrived rows and avoid reprocessing unchanged data. This pattern increases compute usage and can complicate downstream consistency.
- C. Incorrect.
Incorrect. Materialized views are not used to detect new files in an external stage. External stages hold references to files, but a materialized view is created on a query against base tables, not directly as a file arrival detection mechanism for staged data ingestion. This option reflects a common misconception that materialized views can replace ingestion orchestration.
- D. Incorrect.
Incorrect. Temporary tables are session-scoped and are not appropriate as the durable foundation of an automated production ingestion pipeline. Dynamic tables can simplify transformation pipelines, but they do not preserve source data independently if the upstream temporary table is dropped. This approach is operationally fragile and does not align with the requirement for automated ingestion from S3.