SnowPro Associate: Platform Question 45
Single answerData loadingA retail company receives hourly CSV files from a third-party logistics provider in an Amazon S3 bucket. The files are loaded into a Snowflake table using a named external stage and a COPY INTO command. During a network interruption, the operations team reran the same COPY INTO command after connectivity was restored. They want to avoid loading the same files twice while still allowing Snowflake to continue loading any new files that appear in the stage. Which approach best meets this requirement?
- A
Rerun the same COPY INTO command against the stage without modifying the load options, because Snowflake tracks which staged files have already been loaded into the target table
- B
Use COPY INTO with FORCE = TRUE so Snowflake can compare file contents and skip duplicate rows automatically
- C
Delete all files from the stage after each load so Snowflake has no chance to reload them
- D
Replace COPY INTO with INSERT ... SELECT from the external stage, because INSERT statements automatically prevent duplicate file loads
Show answer and explanation
Correct answer: A
Explanation
The best answer is to rerun the same COPY INTO command without FORCE. Snowflake maintains load metadata for staged files loaded into a table with COPY INTO, allowing it to skip files that were already successfully loaded into that table. This makes COPY INTO idempotent for previously loaded files under normal usage and is especially useful when jobs are rerun after operational failures. FORCE = TRUE overrides this protection and reloads files, which is only appropriate for intentional reload scenarios. Snowflake documentation on COPY INTO
and data loading describes file load tracking, load history, and the effect of FORCE. For SnowPro Associate, candidates should know that COPY INTO is the preferred bulk loading mechanism and that Snowflake helps prevent duplicate file ingestion at the file level, not by comparing individual rows inside files.- A. Correct.
Correct. By default, Snowflake records load metadata for files loaded with COPY INTO a table and uses that metadata to avoid reloading the same staged files into the same target table. This is a common best practice for staged file ingestion when rerunning a load after a failure or interruption. As long as FORCE is not used, Snowflake can continue loading new files while skipping files it has already loaded.
- B. Incorrect.
Incorrect. FORCE = TRUE tells Snowflake to load files even if they were previously loaded. It does not perform row-level deduplication by comparing file contents. This option would increase the risk of duplicate data in the target table when rerunning a load.
- C. Incorrect.
Incorrect. Deleting files after each load is not required to prevent duplicate loads. Snowflake's load history already helps prevent reloading previously loaded files with COPY INTO. Also, deleting staged files too aggressively can make troubleshooting and replay more difficult.
- D. Incorrect.
Incorrect. INSERT ... SELECT from a stage does not provide the same built-in file load tracking behavior as COPY INTO. COPY INTO is the standard bulk data loading method for staged files and is specifically designed to manage file-based ingestion efficiently.