ARA-C01 Question 266
Select 2SnowpipeA company loads JSON application logs from Amazon S3 into Snowflake. New files arrive every few minutes and must be available for querying within a few minutes without running a scheduled warehouse-based COPY INTO job. The architect creates an external stage pointing to the S3 bucket and a target table. Which TWO actions should the architect take to implement the most operationally efficient Snowpipe design?
- A
Create a pipe with AUTO_INGEST = TRUE that executes a COPY INTO command from the external stage into the target table, and configure S3 event notifications to send events to the Snowflake-provided queue/integration.
- B
Create a task that runs every minute and executes ALTER PIPE ... REFRESH so that Snowpipe continuously discovers new files.
- C
Configure the pipe to use a user-managed virtual warehouse sized for the expected file volume so ingestion latency stays low.
- D
Ensure the COPY INTO statement in the pipe references the stage and includes the required file format and transformations supported by Snowpipe for loading JSON data.
- E
Use a stream on the external stage so Snowpipe can detect which files are new before loading them.
Show answer and explanation
Correct answers: A, D
Explanation
Snowpipe is designed for continuous, serverless file ingestion and is the best fit when files arrive frequently and need to be queryable within minutes without managing scheduled warehouse-based COPY jobs. In AWS, the architect should create a pipe with a COPY INTO statement and enable AUTO_INGEST so S3 event notifications can inform Snowflake about newly arrived files. This avoids polling-based orchestration and reduces operational burden. The COPY statement inside the pipe must correctly reference the stage, target table, and file format, and may include supported transformations for semi-structured formats such as JSON. Snowpipe does not run on a customer-managed virtual warehouse; it uses Snowflake-managed compute. Also, ALTER PIPE ... REFRESH is primarily for backfill or reprocessing scenarios rather than steady-state continuous loading. Relevant Snowflake documentation covers Snowpipe auto-ingest architecture, cloud event notification setup for Amazon S3, COPY INTO usage in pipes, and the distinction between Snowpipe serverless compute and warehouse-based bulk loading.
- A. Correct.
Correct. For near-real-time continuous ingestion from S3 without scheduled warehouse-based loading, the recommended pattern is Snowpipe with auto-ingest. A pipe contains a COPY INTO statement, and for Amazon S3 the stage notifications must be configured so cloud events notify Snowflake when new files arrive. This is the standard event-driven Snowpipe design and minimizes operational overhead.
- B. Incorrect.
Incorrect. ALTER PIPE ... REFRESH is used to load missed or existing files from the stage metadata path and is not the primary mechanism for continuous ingestion. Running a task every minute to refresh the pipe adds operational complexity and is less efficient than native auto-ingest notifications. It is useful for backfill or recovery scenarios, not as the normal eventing pattern.
- C. Incorrect.
Incorrect. Snowpipe does not use a customer-specified virtual warehouse for standard file ingestion. Snowpipe uses Snowflake-managed compute and is charged based on serverless ingestion, so assigning a warehouse to the pipe is a common misconception.
- D. Correct.
Correct. A pipe must include a valid COPY INTO statement from the stage to the table. The statement should specify the appropriate file format and any supported transformations needed for the JSON load. This is essential for the pipe to load data correctly once notifications trigger ingestion.
- E. Incorrect.
Incorrect. Streams track row-level table changes, not new files in an external stage. They are not used by Snowpipe to identify arriving files. Snowpipe detects files through cloud messaging for auto-ingest or by refresh/listing mechanisms, not through streams on stages.