ARA-C01 Question 273
Single answerData ingestionA retail company receives near-real-time order files from hundreds of stores. Each store uploads small compressed JSON files every 1-2 minutes to an Amazon S3 bucket. The architecture team must ingest the data into Snowflake with the following requirements: minimize operational overhead, load new files as they arrive, avoid reloading the same file multiple times, and keep costs reasonable given the high frequency of small file arrivals. Which approach should the architect recommend?
- A
Create an external stage on the S3 bucket, configure an auto-ingest Snowpipe with S3 event notifications, and load the JSON files into a landing table.
- B
Schedule a task to run COPY INTO every minute from the S3 stage, because tasks automatically detect and skip duplicate files more efficiently than Snowpipe.
- C
Use a materialized view on top of the external stage so Snowflake continuously ingests new JSON files without requiring a pipe or COPY command.
- D
Use bulk loading once per day with COPY INTO from the S3 stage, because Snowpipe is intended only for large batch files and is not cost-effective for frequent small files.
Show answer and explanation
Correct answer: A
Explanation
The best choice is to use Snowpipe with auto-ingest on an external stage backed by Amazon S3. This architecture aligns with Snowflake best practices for continuous data ingestion when files arrive incrementally and cloud event notifications are available. Snowpipe is event-driven, reducing the need for scheduled polling jobs and associated operational overhead. It also maintains metadata about loaded files to prevent the same staged file from being loaded repeatedly.
In this scenario, the requirements emphasize near-real-time ingestion, low administration, and duplicate avoidance. A scheduled task with COPY INTO can work for batch-style orchestration, but it is less elegant and typically less efficient than auto-ingest for continuous arrivals. Materialized views are unrelated to file ingestion from stages. Once-per-day bulk loading violates the stated latency requirement.
Relevant Snowflake documentation and best practices include Snowpipe for continuous data loading, auto-ingest integration with Amazon S3 event notifications, and COPY INTO load history behavior for staged files. Architects should also note that although Snowpipe is the right pattern here, file sizing should still be monitored because extremely small files can increase ingestion overhead and cost.
- A. Correct.
Correct. Snowpipe with auto-ingest is designed for continuous file loading from cloud storage using event notifications such as Amazon S3 event messages. It minimizes operational overhead because file arrival notifications trigger loading automatically, rather than relying on polling or manual orchestration. Snowpipe also tracks load history and avoids reloading files that were already loaded, which supports idempotent ingestion behavior for staged files. For near-real-time ingestion of newly arrived files, this is the standard Snowflake pattern.
- B. Incorrect.
Incorrect. A scheduled task can execute COPY INTO statements on a cadence, but this adds more operational management and is not the preferred near-real-time pattern when cloud event notifications are available. COPY INTO does use load metadata to help avoid duplicate file loads, but tasks do not provide a more efficient duplicate-detection mechanism than Snowpipe. Running every minute against many small arriving files can also create unnecessary polling overhead compared with event-driven auto-ingest.
- C. Incorrect.
Incorrect. Materialized views do not ingest files from external stages. External stages reference files in cloud storage, but data is loaded into Snowflake tables using COPY INTO or Snowpipe. A materialized view can accelerate query performance on table or external table data in certain cases, but it does not replace ingestion orchestration for staged JSON files.
- D. Incorrect.
Incorrect. Daily bulk loading would not satisfy the near-real-time requirement. In addition, Snowpipe is specifically intended for continuous data ingestion and is commonly used for frequent small file arrivals. While file sizing is an architectural consideration and very small files can affect cost efficiency, the statement that Snowpipe is intended only for large batch files is false.