SnowPro Associate: Platform Question 194
Single answer● Loading dataA retail company receives hourly CSV files from a partner in an Amazon S3 bucket. New files are appended throughout the day, and occasionally the partner re-sends a previously delivered file with the same name after correcting a few rows. The data engineering team wants to load only new files automatically into a Snowflake table as they arrive, while also being able to reload a corrected file when needed. Which approach best meets these requirements with the least operational effort?
- A
Create an external stage on the S3 bucket, enable AUTO_INGEST on a Snowpipe, and when a corrected file must be reloaded, use ALTER PIPE ... REFRESH to load it again.
- B
Create an external stage on the S3 bucket and schedule a COPY INTO target_table command every hour without any additional options, because COPY INTO automatically reloads files if the file contents change.
- C
Create an external stage on the S3 bucket, configure Snowpipe with AUTO_INGEST for continuous loading of new files, and when a corrected file with the same name needs to be loaded again, run COPY INTO target_table ... FORCE = TRUE for that file.
- D
Use a task to execute INSERT INTO target_table SELECT * FROM @stage every hour, because tasks automatically track which staged files have already been processed and can reload corrected files with the same name.
Show answer and explanation
Correct answer: C
Explanation
The key requirements are: automatically load only new files as they arrive, minimize operational effort, and support reloading a corrected file that arrives with the same name. Snowpipe with AUTO_INGEST is the best fit for continuous, event-driven ingestion from Amazon S3. It reduces operational overhead compared with polling or scheduled COPY jobs. Snowflake tracks staged files that have already been loaded to prevent accidental duplicate ingestion. Because of this, if a corrected file is re-sent with the same filename, Snowpipe or a normal COPY INTO will typically skip it. To explicitly reload that file, use COPY INTO with FORCE = TRUE. This aligns with Snowflake best practices for staged file ingestion and file reprocessing behavior described in Snowflake documentation for Snowpipe, COPY INTO
, and load metadata/history.- A. Incorrect.
Incorrect. Snowpipe with AUTO_INGEST is appropriate for automatically loading new files from S3, but ALTER PIPE ... REFRESH is intended to load files that were missed in cloud storage event notifications or to backfill files in a stage path. It is not the standard mechanism for reloading a file that Snowflake has already processed with the same name. Snowflake tracks loaded files to avoid duplicate loads, so reprocessing a corrected file typically requires a COPY INTO command with FORCE = TRUE.
- B. Incorrect.
Incorrect. A scheduled COPY INTO can load new files from a stage, but COPY INTO does not automatically reload a previously loaded file just because the contents changed if the filename is the same. Snowflake maintains load metadata and skips files it considers already loaded unless explicitly instructed otherwise. This option also creates more operational overhead than event-driven Snowpipe for hourly arrivals.
- C. Correct.
Correct. Snowpipe with AUTO_INGEST is the best low-maintenance approach for continuously loading newly arrived files from Amazon S3. Snowflake uses cloud event notifications to trigger loading as files land in the stage. If the partner later re-sends a corrected file using the same filename, Snowflake's load metadata would normally prevent it from being loaded again automatically. In that case, issuing a targeted COPY INTO command with FORCE = TRUE for that file allows the corrected file to be reloaded.
- D. Incorrect.
Incorrect. Tasks can schedule SQL statements, but INSERT INTO ... SELECT * FROM @stage is not the recommended pattern for staged file loading in this scenario. Tasks do not provide file-load tracking behavior equivalent to COPY INTO or Snowpipe. File ingestion from stages should use COPY INTO or Snowpipe, both of which maintain load history for staged files. This option also does not address controlled reloading of a corrected file.