ARA-C01 Question 278
Single answerSnowpipeA retail company loads JSON order files into an Amazon S3 bucket throughout the day and uses Snowpipe to ingest them into a raw Snowflake table with minimal delay. During a network incident, several files were uploaded to S3 but the cloud event notifications were not delivered to the Snowpipe queue. After the incident is resolved, the architect must ensure the missing files are loaded quickly without creating duplicate rows from files that Snowpipe may have already processed. Which action should the architect take?
- A
Run ALTER PIPE <pipe_name> REFRESH to have Snowpipe check the stage and enqueue eligible files that were not already loaded
- B
Resume the virtual warehouse used by the target table so Snowpipe can automatically scan the stage for missed files
- C
Recreate the pipe with AUTO_INGEST = TRUE so Snowpipe reloads all files currently in the S3 bucket
- D
Execute COPY INTO
FROM @FORCE = TRUE to make Snowpipe metadata recognize previously missed files without duplicates
Show answer and explanation
Correct answer: A
Explanation
Snowpipe with AUTO_INGEST depends on cloud messaging/event notifications to know when new files arrive. If notifications are missed, Snowflake best practice is to use ALTER PIPE <pipe_name> REFRESH to make the pipe list files from the stage location and enqueue eligible ones for loading. Snowpipe maintains file load metadata so previously loaded files are not loaded again under normal refresh behavior, which helps prevent duplicates. Snowpipe is a serverless ingestion service, so it does not require a user-managed warehouse. Recreating the pipe is not the recommended remediation for missed events, and using COPY INTO ... FORCE = TRUE would intentionally ignore prior load history and can duplicate rows. This aligns with Snowflake documentation on Snowpipe recovery and ALTER PIPE refresh behavior for missed cloud event notifications.
- A. Correct.
Correct. ALTER PIPE ... REFRESH is the appropriate recovery mechanism when event notifications were missed. It causes Snowpipe to scan the referenced stage path and enqueue files for loading, while still using load history to avoid reloading files that Snowpipe has already processed. This is the standard operational approach for backfilling missed notifications in auto-ingest pipelines.
- B. Incorrect.
Incorrect. Snowpipe is serverless and does not rely on a customer-managed virtual warehouse to detect or load files. Resuming a warehouse will not cause Snowpipe to scan for missed files. This distractor reflects the common misconception that Snowpipe behaves like a scheduled COPY INTO process that requires warehouse compute.
- C. Incorrect.
Incorrect. Recreating the pipe is unnecessary and can complicate operations. AUTO_INGEST enables event-driven ingestion for new notifications, but recreating the pipe does not inherently reload all existing files in the bucket. Recovery for missed notifications is handled with ALTER PIPE ... REFRESH, not by recreating the pipe.
- D. Incorrect.
Incorrect. COPY INTO ... FORCE = TRUE bypasses file load history checks and can reload files that were already loaded, which increases the risk of duplicate data. It also does not update Snowpipe's queued notification state in the intended way. This option is tempting because COPY INTO can backfill data, but FORCE = TRUE is specifically the wrong choice when avoiding duplicates is a requirement.