COF-C03 Question 227
Single answer3.2 Perform automated data ingestionA retail company receives new CSV sales files every few minutes in an Amazon S3 bucket. The analytics team wants Snowflake to load these files into a target table as soon as they arrive, with minimal operational overhead and without polling the stage on a schedule. Which solution should be implemented?
- A
Create a Snowpipe on the external stage and configure S3 event notifications to trigger automatic ingestion.
- B
Schedule a task that runs COPY INTO every minute against the external stage.
- C
Use a stream on the external stage and query the stream continuously to detect new files.
- D
Create a materialized view on the stage and enable auto-refresh to ingest arriving files into the target table.
Show answer and explanation
Correct answer: A
Explanation
The best solution is Snowpipe with auto-ingest using S3 event notifications. Snowpipe is the Snowflake feature intended for near-continuous, automated loading of staged data files as they arrive. In AWS, this is typically implemented by configuring S3 event notifications, often through Amazon SQS integration, so Snowflake is informed of new objects and executes the pipe's COPY INTO statement. This pattern minimizes manual intervention and avoids scheduled polling.
By contrast, using tasks with COPY INTO is a valid batch automation approach but is not event-driven and requires a warehouse to run on a schedule. Streams are for change data capture on supported Snowflake objects, not for detecting new files in a stage for ingestion. Materialized views do not load files from stages.
This aligns with Snowflake best practices for automated ingestion: use Snowpipe for low-latency file loading, and reserve scheduled tasks for orchestration or periodic processing when event-driven ingestion is not required.
- A. Correct.
Correct. Snowpipe supports automated, continuous data loading from staged files and is specifically designed for low-maintenance ingestion as files arrive. For Amazon S3, the standard pattern is to define a pipe with a COPY INTO statement and configure cloud event notifications so Snowflake is notified when new files land. This avoids scheduled polling and reduces operational overhead compared to repeatedly scanning the stage.
- B. Incorrect.
Incorrect. A task that runs COPY INTO on a schedule can load files, but it relies on periodic polling rather than event-driven automated ingestion. This introduces unnecessary latency, consumes warehouse resources for each run, and requires more operational management than Snowpipe. It does not best satisfy the requirement for ingestion as soon as files arrive with minimal overhead.
- C. Incorrect.
Incorrect. Streams track row-level changes to tables, external tables, directory tables, and views in supported scenarios; they do not continuously monitor an external stage for newly arrived files to drive ingestion into a table. This option reflects a common misconception that streams are a general file-arrival detection mechanism.
- D. Incorrect.
Incorrect. Materialized views cannot be used to ingest files from a stage into a table. A stage is a file location, not a base table for a materialized view that loads data into a target table. Auto-refresh for materialized views applies to keeping query results current based on changes to underlying table data, not loading staged files.