COF-C03 Question 209
Single answer3.1 Perform data loading and unloadingA data engineering team loads daily CSV files from an Amazon S3 bucket into a Snowflake table using a named external stage and the COPY INTO command. One morning, they discover that the same files were loaded twice after an orchestration job retried the load. The team wants to prevent previously loaded files from being loaded again when using the same stage and target table, while still allowing new files in the bucket to be ingested automatically. Which action should they take?
- A
Use COPY INTO
from the stage without the FORCE option so Snowflake uses load metadata to skip files that were already loaded into that table - B
Set ON_ERROR = CONTINUE in the COPY INTO command so Snowflake ignores duplicate files during future loads
- C
Add PURGE = TRUE to the COPY INTO command so Snowflake marks files as loaded without ingesting them again
- D
Run TRUNCATE TABLE before each COPY INTO operation so Snowflake can detect that the same files were already loaded previously
Show answer and explanation
Correct answer: A
Explanation
For staged file ingestion, Snowflake COPY INTO
maintains metadata about loaded files to help prevent accidental reloading of the same files into the same target table. This makes repeated loads more resilient to orchestration retries when the same stage and target are used. The FORCE option overrides this behavior and reloads files, so it should not be used when the goal is to avoid duplicate ingestion. Options such as ON_ERROR address bad data handling, not duplicate file detection. PURGE can be useful operationally to clean up files after successful ingestion, but it is separate from Snowflake's file load tracking. This aligns with Snowflake documentation and best practices for loading data from stages using COPY INTO.- A. Correct.
Correct. Snowflake tracks load metadata for files loaded by COPY INTO
from a stage. When loading from the same staged files into the same target table, Snowflake can skip files that were already loaded, as long as FORCE = TRUE is not used. This is the standard way to make staged file ingestion idempotent for repeated COPY operations against the same table. - B. Incorrect.
Incorrect. ON_ERROR controls how COPY handles data parsing or conversion errors in rows or files, such as skipping bad records or aborting the load. It does not control duplicate file detection or previously loaded file handling.
- C. Incorrect.
Incorrect. PURGE = TRUE attempts to remove files from the stage after they are loaded successfully. While this can reduce the chance of reprocessing if the files are removed from the stage location, it is not what marks files as already loaded, and it is not the primary mechanism Snowflake uses to skip previously loaded files. Also, PURGE behavior depends on the stage type and permissions.
- D. Incorrect.
Incorrect. TRUNCATE TABLE removes data from the table, but it does not make Snowflake re-evaluate duplicate file prevention in the way described. Load metadata is tracked independently of simply clearing table rows, so truncating the table is not the correct approach for preventing duplicate loads caused by retries.