COF-C03 Question 39
Single answerPipesA data engineering team loads JSON files from an Amazon S3 bucket into a Snowflake landing table. They created a pipe with AUTO_INGEST=TRUE and a COPY INTO statement that references the external stage. During testing, manually running the COPY INTO command loads files successfully, but after new files are added to the bucket, the pipe does not load anything automatically. Which action should the engineer take to enable the automatic loading behavior?
- A
Create and configure the required cloud messaging integration so Snowflake can receive event notifications from Amazon S3
- B
Start a virtual warehouse for the pipe because pipes require a running warehouse to process AUTO_INGEST notifications
- C
Alter the pipe to use a task schedule because AUTO_INGEST works only when triggered by a Snowflake task
- D
Convert the external stage to an internal stage because pipes can automatically ingest only from internal stages
Show answer and explanation
Correct answer: A
Explanation
This scenario tests a core Snowpipe concept: AUTO_INGEST depends on event notifications from the cloud storage platform. If the COPY INTO command works manually, the file format, stage, permissions, and target table are likely valid. The missing piece is usually the event notification path required to trigger the pipe when new files land in S3. Snowpipe does not require a running virtual warehouse because it uses serverless Snowflake-managed compute. Tasks are unrelated to AUTO_INGEST triggering, and external stages are fully supported for Snowpipe. According to Snowflake best practices and documentation for Snowpipe with Amazon S3, engineers must configure the supported notification integration and S3 event notification setup so newly arrived files generate events that Snowflake can consume.
- A. Correct.
Correct. For S3 auto-ingest to work, Snowflake must receive event notifications when new files arrive. This requires the proper notification setup between S3 and Snowflake, typically using a configured notification integration and cloud messaging path supported by Snowflake. A pipe with AUTO_INGEST=TRUE does not poll S3 by itself; it relies on event notifications.
- B. Incorrect.
Incorrect. Snowpipe uses Snowflake-managed compute, not a user-managed virtual warehouse, to perform continuous data ingestion. A warehouse is not required to process files through an auto-ingest pipe, which is why manual COPY can work separately while AUTO_INGEST still fails due to missing notifications.
- C. Incorrect.
Incorrect. Tasks are used to schedule SQL statements, but they are not required for Snowpipe AUTO_INGEST. AUTO_INGEST is designed to react to cloud storage event notifications, not task schedules. Using a task would be a different loading pattern and would not fix missing event notification configuration.
- D. Incorrect.
Incorrect. Snowpipe can load from external stages, including Amazon S3, and this is a common production pattern. Converting to an internal stage is unnecessary and does not address the actual problem, which is that the pipe is not being triggered automatically.