COF-C03 Question 40
Single answerPipesA data engineering team loads JSON files from an Amazon S3 bucket into a Snowflake landing table by using a pipe with AUTO_INGEST=TRUE. The cloud administrator recently changed the S3 event notification configuration. Since then, newly uploaded files remain in the stage and are not appearing in the table, but manually running COPY INTO from the same stage loads the files successfully. What is the MOST likely cause of the issue?
- A
The pipe is paused and must be resumed before Snowflake can execute the COPY statement
- B
The S3 event notifications are no longer sending messages to the notification channel that the pipe relies on
- C
The virtual warehouse assigned to the pipe is suspended, so Snowpipe cannot process new files
- D
The stage file format does not support JSON when used with AUTO_INGEST pipes
Show answer and explanation
Correct answer: B
Explanation
The most likely issue is broken event delivery to Snowpipe after the S3 notification configuration changed. In an AUTO_INGEST pipe, Snowflake does not continuously poll S3 for files; instead, it responds to event messages generated when files are added. If those notifications are removed, misrouted, or replaced, Snowpipe stops automatically loading new files even though the files are present and loadable. The fact that manual COPY INTO succeeds confirms that the stage, file format, permissions for reading the files, and target table are likely configured correctly. Snowpipe differs from bulk loading with COPY INTO because Snowpipe uses serverless Snowflake-managed compute rather than a user-managed virtual warehouse. Snowflake documentation on Snowpipe and auto-ingest emphasizes validating the cloud provider event notification configuration and notification integration/channel when files are not being ingested automatically.
- A. Incorrect.
Incorrect. Pipes are not managed by suspending or resuming a virtual warehouse-style object for normal Snowpipe auto-ingest operation. While a pipe can be altered in certain ways, the scenario points to a change in S3 event notifications, which is a classic cause of auto-ingest failure. If manual COPY works, the COPY definition and access to the staged files are likely valid.
- B. Correct.
Correct. Snowpipe with AUTO_INGEST=TRUE depends on cloud messaging triggered by object creation events. For Amazon S3, Snowflake relies on S3 event notifications integrated with the configured notification channel. If the cloud administrator changed the event notification setup, new files may no longer generate the messages Snowpipe needs, so the files remain in the stage until loaded manually.
- C. Incorrect.
Incorrect. Snowpipe uses Snowflake-managed compute resources, not a customer-managed virtual warehouse, to load files. A suspended warehouse does not prevent Snowpipe auto-ingest from processing files. This is a common misconception because manual COPY INTO typically does require a warehouse, but Snowpipe does not.
- D. Incorrect.
Incorrect. JSON is supported for staged file loading and can be used with Snowpipe. Because manual COPY INTO from the same stage works, the file format is clearly not the root cause. The issue is with ingestion triggering, not data format support.