ARA-C01 Question 255
Single answer3.1 Determine the appropriate data loading or data unloading solution to meet business needs.A retail company receives point-of-sale data files in Amazon S3 every 5 minutes from hundreds of stores. Files arrive as compressed JSON and must be available in Snowflake for analytics within 10 minutes of landing in S3. The company wants a solution that minimizes operational overhead, avoids reloading the same file twice, and can handle occasional schema evolution in the JSON payload. Which solution BEST meets these requirements?
- A
Create an external stage on the S3 bucket, define a pipe with AUTO_INGEST enabled to load from the stage into a VARIANT column, and use streams/tasks later to transform the data into relational tables.
- B
Schedule a task to run COPY INTO every 5 minutes from the S3 stage into a structured table with explicit column mapping, and rely on the task schedule to prevent duplicate file loads.
- C
Use Snowpipe Streaming directly from Amazon S3 so Snowflake can continuously ingest the files without cloud event notifications.
- D
Use GET to download the files from the external stage to a client host, then PUT them into an internal stage and run COPY INTO a target table to ensure exactly-once loading.
Show answer and explanation
Correct answer: A
Explanation
The best solution is Snowpipe with AUTO_INGEST on an external S3 stage. For file-based, near-real-time ingestion from cloud object storage, Snowpipe is the native Snowflake service designed to minimize operational overhead by triggering loads from cloud event notifications. It is well suited to frequent file arrivals such as every 5 minutes and generally supports low-latency ingestion within the stated SLA. Since the source is compressed JSON with occasional schema evolution, landing the raw payload into a VARIANT column is the most resilient design; the data can then be transformed downstream into curated relational tables using standard ELT patterns such as streams and tasks. Snowflake documentation and best practices distinguish between Snowpipe for file ingestion from stages and Snowpipe Streaming for application-driven streaming records. They also note that Snowflake tracks loaded files to help avoid duplicate loading in COPY-based ingestion workflows, including Snowpipe-managed loads.
- A. Correct.
Correct. Snowpipe with AUTO_INGEST is designed for near-real-time file ingestion from cloud storage using event notifications, which fits the 10-minute SLA with low operational overhead. Loading the JSON into a VARIANT column is practical when the payload may evolve, because semi-structured data can be ingested without rigid upfront schema enforcement. Snowpipe and COPY history help prevent reloading the same file unintentionally, satisfying the duplicate-avoidance requirement. Transforming later with streams/tasks is a common architectural pattern for semi-structured ingestion pipelines.
- B. Incorrect.
Incorrect. A scheduled task running COPY INTO can work for micro-batch loading, but it creates more operational management than Snowpipe for this scenario and is less aligned with the requirement to minimize overhead. Also, duplicate prevention is not achieved simply because the task runs on a schedule; it depends on Snowflake load metadata and how files are managed. More importantly, loading directly into a structured table with explicit mapping is brittle when the JSON schema can evolve, making this a poor fit for the stated requirements.
- C. Incorrect.
Incorrect. Snowpipe Streaming is intended for row-level streaming ingestion from client applications using the Snowpipe Streaming API, not for reading files directly from Amazon S3. A common misconception is that Snowpipe Streaming replaces all near-real-time ingestion patterns, but file-based cloud storage ingestion still uses Snowpipe with stages and event notifications.
- D. Incorrect.
Incorrect. This approach adds unnecessary data movement and operational complexity. GET downloads files from a stage to a local environment, and PUT uploads local files to an internal stage; using both would increase latency and administration rather than reduce it. It also does not provide a better exactly-once guarantee than Snowpipe for this file-arrival pattern and is not a best-practice architecture for continuous ingestion from S3.