COF-C03 Question 226
Single answer3.2 Perform automated data ingestionA retail company receives thousands of small JSON files every hour in an Amazon S3 bucket. The files must be loaded into a Snowflake table within minutes of arrival, with minimal operational overhead. The company wants Snowflake to automatically detect new files and trigger loading without relying on external schedulers. Which solution best meets these requirements?
- A
Create an external stage on the S3 bucket, define a Snowpipe on the target table, and configure auto-ingest with S3 event notifications routed to Snowflake.
- B
Schedule a task that runs a COPY INTO command every hour against the external stage and relies on Snowflake to discover all new files automatically.
- C
Create a materialized view on top of the external stage so that Snowflake refreshes the data automatically as new files arrive in S3.
- D
Use a stream on the external stage and a triggered task to load arriving JSON files into the target table.
Show answer and explanation
Correct answer: A
Explanation
For automated ingestion of newly arrived files from cloud storage, the best practice is to use Snowpipe with auto-ingest. Snowpipe is specifically built for continuous file loading and can use event notifications from Amazon S3 so Snowflake is informed as soon as new files are available. This reduces latency and removes the need for external orchestration or frequent polling. By contrast, scheduled COPY INTO operations are batch-oriented and depend on polling intervals, which increases latency and operational effort. Materialized views do not load staged files, and streams/triggered tasks are intended for change tracking and downstream processing of table data rather than cloud storage event detection. See Snowflake documentation on Snowpipe, auto-ingest with Amazon S3 event notifications, and COPY INTO data loading patterns.
- A. Correct.
Correct. Snowpipe is Snowflake's serverless continuous data ingestion service designed for near-real-time loading from staged files. With Amazon S3, auto-ingest is implemented by configuring cloud event notifications so Snowflake is notified when new files arrive. This approach minimizes operational overhead and avoids external schedulers while loading files within minutes.
- B. Incorrect.
Incorrect. A scheduled task running COPY INTO can automate loading, but it does not satisfy the requirement for event-driven ingestion with minimal latency. Running every hour is too slow for files that must load within minutes, and polling via scheduled COPY also creates more management overhead than Snowpipe auto-ingest.
- C. Incorrect.
Incorrect. Materialized views do not ingest files from stages into tables. They maintain query results based on underlying table data, not raw files in an S3 stage. This option reflects a misunderstanding of materialized views versus ingestion services.
- D. Incorrect.
Incorrect. Streams track change data on supported objects such as tables and views, not file arrival events on an external stage. Triggered tasks can react to stream data changes, but they are not used to detect new files landing in S3 for ingestion. This confuses change data capture features with file ingestion automation.