ARA-C01 Question 277
Single answerSnowpipeA retail company loads point-of-sale files from Amazon S3 into Snowflake. New files arrive every few minutes in an external stage and must be available for querying within minutes, without running a warehouse continuously. The architecture team also wants to avoid reloading the same file if an upstream process republishes an event for an already ingested object. Which design best meets these requirements?
- A
Create a Snowpipe on the S3 stage with AUTO_INGEST enabled, configure S3 event notifications to the Snowflake-provided notification channel, and use a COPY statement in the pipe to load from the stage into the target table.
- B
Schedule a task to run a COPY INTO command every 5 minutes using a dedicated virtual warehouse, because Snowpipe requires a warehouse to process incoming files.
- C
Use a materialized view on top of the external stage so that new files are automatically incorporated into the target table without event configuration.
- D
Create a stream on the external stage and trigger a serverless task to MERGE files into the target table, because streams on stages provide duplicate-file protection.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use Snowpipe with AUTO_INGEST on the S3 external stage. Snowpipe is purpose-built for continuous file ingestion and is triggered by cloud event notifications, allowing data to be loaded within minutes without provisioning or scheduling a warehouse for the ingestion process. This aligns with the requirement for low-latency availability and reduced operational overhead. Snowpipe also maintains load metadata/history to help prevent the same staged file from being loaded again when duplicate notifications occur for an already loaded object.
Key best-practice points reflected in this scenario:
- Snowpipe supports event-driven continuous loading from cloud storage such as Amazon S3.
- AUTO_INGEST uses cloud messaging/event notifications rather than polling.
- Snowpipe uses Snowflake-managed compute, not a customer-managed virtual warehouse.
- Duplicate event notifications do not necessarily cause duplicate loads of the same file because Snowflake tracks file load history.
These behaviors are consistent with Snowflake documentation for Snowpipe, AUTO_INGEST with cloud storage event notifications, and COPY/Snowpipe load-history behavior.
- A. Correct.
Correct. Snowpipe is designed for continuous data loading of staged files with low-latency ingestion and does not require customers to manage a virtual warehouse for the load operation. With AUTO_INGEST, cloud messaging/event notifications from S3 trigger Snowpipe when new files arrive. Snowpipe also tracks loaded files to avoid reloading the same file for a period, which addresses duplicate event republishes for already ingested objects.
- B. Incorrect.
Incorrect. This can provide near-real-time loading, but it does not best meet the requirement to avoid running a warehouse continuously, because tasks executing COPY INTO use compute resources. The statement that Snowpipe requires a warehouse is false; Snowpipe uses Snowflake-managed compute for loading. This option reflects a common misconception that all ingestion must be warehouse-backed.
- C. Incorrect.
Incorrect. Materialized views cannot be used to automatically ingest staged files into a table. External stages store references to files, and loading into internal tables still requires COPY INTO or Snowpipe. This option confuses query acceleration/maintenance features with ingestion mechanisms.
- D. Incorrect.
Incorrect. Streams track table, view, dynamic table, and some external table changes, but not file ingestion directly from an external stage in the way described here. A stream on an external stage is not the standard pattern for loading files, and duplicate-file protection for staged file loads is a Snowpipe/COPY load-history capability, not a stream capability.