COF-C03 Question 229
Single answerSnowpipeA data engineering team needs to load JSON files from an Amazon S3 bucket into a Snowflake table within a few minutes of each file arriving. They want to minimize operational overhead and avoid running a warehouse continuously just to poll for new files. Which solution best meets these requirements?
- A
Create a Snowpipe that uses auto-ingest with S3 event notifications to load new files from an external stage into the target table.
- B
Schedule a task to run a COPY INTO command every minute using a dedicated virtual warehouse that stays running all day.
- C
Use a stream on the external stage so Snowflake can detect newly arrived files automatically and load them without notifications.
- D
Configure a materialized view on top of the staged files so Snowflake refreshes the target table whenever new files land.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use Snowpipe with auto-ingest. Snowpipe is Snowflake's continuous data ingestion service for loading files from stages into tables as files become available. For Amazon S3, the common pattern is to configure S3 event notifications so Snowpipe receives events and loads files automatically. This avoids the need to run a warehouse continuously for polling because Snowpipe uses Snowflake-managed compute for the load operation. By contrast, a task plus COPY INTO is a batch-oriented workaround that consumes warehouse resources and increases administration. Streams and materialized views are unrelated to detecting or ingesting new files from an external stage. This aligns with Snowflake documentation and best practices for near-real-time file ingestion using Snowpipe and auto-ingest.
- A. Correct.
Correct. Snowpipe is designed for continuous data loading of new files as they arrive in cloud storage. With Amazon S3, Snowpipe can use auto-ingest based on event notifications so files are loaded shortly after arrival without requiring a customer-managed warehouse to poll for changes. This is the standard low-overhead near-real-time ingestion pattern for Snowflake.
- B. Incorrect.
Incorrect. A scheduled task that repeatedly runs COPY INTO can load files, but it requires using a virtual warehouse for execution and introduces more operational overhead than Snowpipe. It also does not align with the requirement to avoid running compute continuously just to poll for files. Tasks are useful for orchestration, but Snowpipe is the purpose-built service for this scenario.
- C. Incorrect.
Incorrect. Streams do not monitor external stages for newly arrived files. Streams track change data capture for supported Snowflake objects such as tables and views. They are not a mechanism for detecting new files in cloud storage or triggering ingestion from an external stage.
- D. Incorrect.
Incorrect. Materialized views do not ingest raw files from stages into tables. They are used to store precomputed query results based on table data to improve query performance. They cannot be configured to watch staged files and load them into a target table.