ARA-C01 Question 274
Single answerData ingestionA retail company receives thousands of small JSON files per hour from stores into an Amazon S3 bucket. The data must be available in Snowflake within a few minutes for downstream dashboards, and the architecture team wants to minimize operational overhead. They also want to avoid reloading the same files if notifications are retried. Which approach should the architect recommend?
- A
Create an external stage on the S3 bucket, configure an auto-ingest Snowpipe with S3 event notifications through Amazon SNS/SQS, and load into the target table
- B
Schedule a task to run COPY INTO every 5 minutes with FORCE=TRUE so that any missed files are reprocessed automatically
- C
Use a materialized view on top of the external stage so new JSON files are reflected in Snowflake without loading
- D
Configure a stream on the S3 bucket and use a Snowflake task to read directly from the stream into the target table
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use Snowpipe with auto-ingest on the S3 external stage. This is the standard Snowflake pattern for near-real-time ingestion from cloud object storage when minimizing operational management is important. Snowpipe is event-driven and integrates with cloud messaging for file arrival notifications. In Amazon S3 environments, Snowflake commonly uses S3 event notifications routed through SNS/SQS for auto-ingest. Snowflake also maintains load history metadata so the same file is not loaded repeatedly under normal file-tracking rules, which helps protect against duplicate notification delivery. By contrast, polling with scheduled COPY INTO is more operationally intensive and less responsive, and using FORCE=TRUE explicitly bypasses file load protection. Best-practice references include Snowflake documentation on Snowpipe, auto-ingest with Amazon S3, COPY INTO load history behavior, and staged file loading patterns.
- A. Correct.
Correct. Snowpipe with auto-ingest is designed for continuous data loading from cloud storage with low operational overhead. For Amazon S3, Snowpipe can use event notifications via SNS/SQS to detect new files and load them quickly. Snowflake tracks load metadata for files, which helps prevent duplicate loading when the same file notification is delivered more than once.
- B. Incorrect.
Incorrect. A scheduled COPY INTO can work for batch ingestion, but using FORCE=TRUE causes Snowflake to reload files even if they were loaded previously, which increases the risk of duplicate data unless additional deduplication logic is built. It also creates more operational overhead than event-driven Snowpipe for this near-real-time requirement.
- C. Incorrect.
Incorrect. Materialized views cannot be used to automatically ingest files from an external stage into a native Snowflake table. External stages only define access to files in cloud storage. To make staged file data available in Snowflake tables, a load mechanism such as COPY INTO or Snowpipe is required.
- D. Incorrect.
Incorrect. Snowflake streams track change data on Snowflake tables, views, dynamic tables, and certain external/table-related objects such as external tables, but they are not created on an S3 bucket itself as an ingestion mechanism. A task also cannot directly consume an S3 bucket stream because that concept does not exist in Snowflake.