COF-C03 Question 208
Single answer3.1 Perform data loading and unloadingA data engineering team receives hourly CSV files in an Amazon S3 bucket. Some files occasionally contain a few malformed rows, but the business wants Snowflake to load all valid rows and keep the load running instead of failing the entire job. The team also needs to avoid loading the same file twice if a retry occurs. Which approach best meets these requirements when loading the files into a Snowflake table?
- A
Use COPY INTO with ON_ERROR = CONTINUE and load from the S3 stage into the target table
- B
Use COPY INTO with FORCE = TRUE so Snowflake skips malformed rows but prevents duplicate file loads
- C
Use PUT to upload the files directly from S3 into an internal stage, then use COPY INTO without any error handling options
- D
Use COPY INTO with VALIDATION_MODE = RETURN_ERRORS so valid rows load while invalid rows are skipped automatically
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use COPY INTO from the S3 stage with ON_ERROR = CONTINUE. In Snowflake, COPY INTO is the standard bulk-loading command for staged data. When source files contain a small number of bad records but valid rows should still be ingested, ON_ERROR can be set to CONTINUE so that row-level errors do not abort the whole file load. To avoid duplicate loads on retries, Snowflake maintains load history metadata for files and does not reload the same staged files by default; using FORCE = TRUE overrides that behavior and is therefore inappropriate here. VALIDATION_MODE is for checking files without loading them, and PUT is only for local-to-internal-stage uploads. These behaviors align with Snowflake documentation and best practices for staged file loading and error handling.
- A. Correct.
Correct. COPY INTO supports loading data from an external stage such as Amazon S3. Setting ON_ERROR = CONTINUE allows Snowflake to continue loading valid rows when some rows are malformed instead of failing the entire load. By default, Snowflake tracks load metadata for staged files and helps prevent the same file from being loaded again accidentally on retries, as long as FORCE = TRUE is not used. This matches both requirements in the scenario.
- B. Incorrect.
Incorrect. FORCE = TRUE tells Snowflake to reload files even if they were loaded previously, which works against the requirement to avoid duplicate file loads during retries. Also, FORCE does not control malformed-row behavior. Error-handling behavior is managed through options such as ON_ERROR, not FORCE.
- C. Incorrect.
Incorrect. PUT is used to upload files from a local file system to an internal stage, not to move files from Amazon S3 into Snowflake. Since the files are already in S3, the standard approach is to reference them through an external stage. In addition, omitting error-handling options would not satisfy the requirement to continue loading valid rows when bad records exist.
- D. Incorrect.
Incorrect. VALIDATION_MODE is used to validate data files and return errors without actually loading the data into the table. It is useful for testing or troubleshooting a load, but it does not load valid rows while skipping invalid ones. Therefore, it does not meet the business requirement.