COF-C03 Question 71
Single answerData loadingA data engineering team loads hourly CSV files from an Amazon S3 bucket into a Snowflake table using the COPY INTO command. Occasionally, a file is delivered twice with the same name and unchanged contents. The team wants to prevent duplicate rows from being loaded while still allowing corrected files with a different name to be loaded later. Which approach should the team use?
- A
Use the COPY INTO command to load from the stage directly into the target table, relying on Snowflake load metadata to skip files that were already loaded successfully.
- B
Set ON_ERROR = CONTINUE in the COPY INTO command so duplicate files are ignored automatically during future loads.
- C
Use FORCE = TRUE in every COPY INTO command so Snowflake checks file contents before deciding whether to reload the file.
- D
Load the files into a temporary table first, because Snowflake only tracks file load history for temporary and transient tables.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to rely on Snowflake's built-in load metadata when using COPY INTO
from a stage. By default, Snowflake keeps track of which staged files have already been loaded successfully and avoids reloading the same files again, which is a standard best practice for batch ingestion from cloud storage. This behavior is especially useful when upstream systems occasionally resend the same file. A corrected file with a new filename can still be loaded because Snowflake identifies files by stage path and filename in load tracking, not by business meaning of the data. ON_ERROR is unrelated because it handles row-level or file-level load errors. FORCE = TRUE overrides duplicate protection and reloads files intentionally, which would be counterproductive here. Relevant Snowflake documentation includes COPY INTO- A. Correct.
Correct. Snowflake tracks load metadata for staged data files that have been loaded with COPY INTO
. If the same staged file is loaded again with the same name and path, Snowflake can skip it by default because it was already loaded successfully. This helps prevent duplicate loads for re-delivered files. If a corrected file arrives later with a different name, Snowflake treats it as a new file and it can be loaded. - B. Incorrect.
Incorrect. ON_ERROR controls how COPY handles parsing or data conversion errors in records, such as skipping bad rows or aborting the load. It does not control duplicate-file detection or previously loaded file handling.
- C. Incorrect.
Incorrect. FORCE = TRUE tells Snowflake to reload files even if they were loaded before. That would increase the risk of duplicate rows rather than prevent them. It is used when intentionally reprocessing files, not for duplicate prevention.
- D. Incorrect.
Incorrect. Snowflake maintains load history metadata for files loaded into tables with COPY INTO
; this behavior is not limited to temporary or transient tables. Using a temporary table first does not solve the duplicate-file problem by itself.