ARA-C01 Question 265
Single answerSnowpipeA retail company needs to ingest point-of-sale files into Snowflake within a few minutes of arrival. Files are written continuously to an Amazon S3 bucket by an external application, and the data volume is unpredictable throughout the day. The architect wants to minimize operational overhead and avoid keeping a warehouse running just for ingestion. The company also needs to prevent duplicate loads if the same file event is delivered more than once by the cloud provider. Which solution best meets these requirements?
- A
Create a Snowpipe on an external stage and configure S3 event notifications to trigger automatic ingestion into the target table.
- B
Schedule a task that runs COPY INTO every minute using a dedicated virtual warehouse sized for peak ingestion.
- C
Use a materialized view on the external stage so new files are automatically reflected in the target table without loading.
- D
Configure a stream on the S3 stage and use it to capture newly arrived files for continuous ingestion.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use Snowpipe with auto-ingest on an external S3 stage. This is the standard Snowflake pattern for near-real-time ingestion of files arriving in cloud storage when the goal is to reduce administration and avoid running a warehouse solely for loading. Snowpipe is serverless for the ingestion operation, unlike scheduled COPY statements that require warehouse compute. In addition, Snowpipe uses file metadata and load history to prevent reloading the same files unintentionally, which is important because cloud event notifications can sometimes be delivered more than once. This makes Snowpipe well suited for event-driven ingestion with unpredictable volume. Relevant Snowflake documentation and best practices include Snowpipe auto-ingest with Amazon S3 event notifications, serverless loading behavior, and COPY/Snowpipe load-history semantics for duplicate file protection.
- A. Correct.
Correct. Snowpipe is designed for continuous, near-real-time data loading from staged files with low operational overhead. When used with an external stage on Amazon S3 and auto-ingest enabled through S3 event notifications (typically via SNS/SQS integration managed for Snowflake), Snowpipe loads files as they arrive without requiring a customer-managed warehouse to run continuously. Snowpipe also tracks load history for files, which helps avoid duplicate loading of the same staged file even if duplicate event notifications are received.
- B. Incorrect.
Incorrect. A scheduled task running COPY INTO can load files on a frequent cadence, but it requires a virtual warehouse to execute the statement. That increases operational overhead and may be inefficient for unpredictable arrival patterns because the warehouse must be available regularly. While COPY INTO also has load-history protections for duplicate file loading, this approach does not best satisfy the requirement to avoid keeping compute allocated just for ingestion.
- C. Incorrect.
Incorrect. Materialized views cannot be used to automatically ingest files from an external stage into a regular table. External tables can expose data in external files, but that is different from loading data into Snowflake-managed tables. This option reflects a common misconception between querying external data and ingesting staged files.
- D. Incorrect.
Incorrect. Streams track change data on supported Snowflake objects such as tables and some external tables, but they do not monitor an S3 stage for file arrivals. A stream is not a file-arrival mechanism and cannot replace Snowpipe for automated ingestion from cloud storage events.