SnowPro Associate: Platform Question 280
Single answer● Use COPY INTO <table> statementsA data engineering team loads daily CSV files from an internal stage into the SALES_RAW table using a COPY INTO
statement. Sometimes a few rows in a file contain bad numeric values in the AMOUNT column, but the business wants the valid rows loaded while invalid rows are skipped and reported for later review. Which COPY INTO approach best meets this requirement?- A
Use COPY INTO SALES_RAW ... ON_ERROR = CONTINUE
- B
Use COPY INTO SALES_RAW ... FORCE = TRUE
- C
Use COPY INTO SALES_RAW ... VALIDATION_MODE = RETURN_ERRORS
- D
Use COPY INTO SALES_RAW ... PURGE = TRUE
Show answer and explanation
Correct answer: A
Explanation
The best answer is ON_ERROR = CONTINUE because COPY INTO
supports error-handling behavior for data loads, and this option allows Snowflake to load rows that are valid while skipping rows that produce parsing or conversion errors. In contrast, VALIDATION_MODE is for checking files without loading them, FORCE is for reloading files regardless of load history, and PURGE is for removing staged files after successful load. This aligns with common Snowflake data-loading practices where imperfect source files are ingested incrementally and exceptions are reviewed afterward. See Snowflake documentation for COPY INTO- A. Correct.
Correct. ON_ERROR = CONTINUE tells Snowflake to continue loading rows that can be parsed successfully while skipping rows that cause errors. This is the standard approach when the goal is to load good records from a file even if some rows are malformed or contain invalid values. Rejected rows can then be investigated separately using load history and validation techniques.
- B. Incorrect.
Incorrect. FORCE = TRUE tells Snowflake to reload files even if they were previously loaded and tracked in load metadata. It does not control row-level error handling. Someone might choose this option if they confuse reprocessing files with skipping bad records, but it would not satisfy the requirement to load valid rows while ignoring invalid ones.
- C. Incorrect.
Incorrect. VALIDATION_MODE = RETURN_ERRORS is used to validate files and return errors instead of actually loading data into the target table. This is useful for testing or troubleshooting, but it does not load the valid rows. A candidate might pick this because it mentions errors and reporting, but it prevents the desired partial load behavior.
- D. Incorrect.
Incorrect. PURGE = TRUE removes staged files after they are loaded successfully. It affects file lifecycle management, not row-level error handling. This could even be risky operationally if selected without understanding that the main requirement is to preserve valid-row loading while identifying bad rows.