SnowPro Associate: Platform Question 196
Single answer● Loading dataA retail company receives new CSV files in an Amazon S3 bucket every hour. The files must be loaded into a Snowflake table as soon as they arrive, with minimal manual effort. The company also wants to avoid reloading the same file more than once. Which Snowflake approach best meets these requirements?
- A
Create an external stage on the S3 bucket, define a pipe with AUTO_INGEST enabled to run COPY INTO when new files arrive, and configure the required cloud event notifications.
- B
Schedule a task to run INSERT statements every hour against the S3 bucket path, because tasks can directly ingest files from cloud storage without staging.
- C
Use SnowSQL to manually execute PUT commands from the S3 bucket into an internal stage, then run COPY INTO on demand for each new file.
- D
Query the CSV files directly from the S3 bucket using a virtual warehouse, because Snowflake automatically persists queried file data into the target table.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use Snowpipe with an external stage on Amazon S3 and AUTO_INGEST enabled. This is the standard Snowflake approach for continuously loading files shortly after arrival with minimal operational overhead. Snowpipe uses event notifications from the cloud provider to trigger loading, rather than relying on manual execution or polling-heavy processes. Snowflake also maintains metadata about loaded files to help avoid duplicate loads of the same staged file under normal loading patterns. In Snowflake documentation, this pattern is described through external stages, COPY INTO
, and Snowpipe auto-ingest using cloud messaging/event notifications. By contrast, PUT is for local-to-internal-stage uploads, tasks are for scheduled SQL orchestration rather than direct event-based file ingestion, and querying staged files does not itself persist data into tables.- A. Correct.
Correct. Snowpipe is designed for continuous data loading from files as they arrive in cloud storage. A common pattern is to create an external stage pointing to Amazon S3, then create a pipe with AUTO_INGEST=TRUE so Snowflake can trigger COPY INTO when cloud event notifications indicate that new files have arrived. Snowpipe also tracks load history for files, which helps prevent the same staged file from being loaded multiple times unintentionally.
- B. Incorrect.
Incorrect. Tasks can schedule SQL statements, but they do not directly ingest files from cloud storage using INSERT statements. Loading files from stages is typically done with COPY INTO, and for near-real-time arrival-based ingestion, Snowpipe is the appropriate service. This option reflects a misconception that tasks replace event-driven file ingestion.
- C. Incorrect.
Incorrect. PUT uploads files from a local file system to an internal stage; it is not used to transfer files from an S3 bucket into Snowflake. In this scenario, the files already land in S3, so an external stage is more appropriate. This option also introduces unnecessary manual effort, which conflicts with the requirement.
- D. Incorrect.
Incorrect. Snowflake can query files in stages or external tables in some patterns, but simply querying CSV files does not automatically load and persist the data into a target table. Persisting data into a Snowflake table requires an explicit load operation such as COPY INTO or a pipeline mechanism like Snowpipe.