SnowPro Associate: Platform Question 221
Single answer○ COPY INTOA data engineering team loads daily CSV files from an internal stage into a Snowflake table named SALES_RAW using a scheduled command: COPY INTO SALES_RAW FROM @daily_stage FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1); One day, a source system issue causes some rows in one file to have an extra column, and the COPY command fails for that file. The team wants the load to continue loading all valid rows from the file while skipping invalid rows, and they also want to capture the rejected rows for later review. Which approach should they use?
- A
Use COPY INTO with ON_ERROR = CONTINUE and then query the validation output for rejected rows
- B
Use COPY INTO with FORCE = TRUE so Snowflake loads valid rows and writes invalid rows to a separate error table automatically
- C
Use COPY INTO with PURGE = TRUE so invalid rows are skipped and removed from the stage after loading
- D
Use COPY INTO with SINGLE = TRUE so Snowflake processes the file one row at a time and ignores malformed rows
Show answer and explanation
Correct answer: A
Explanation
For COPY INTO
, Snowflake supports ON_ERROR options to control behavior when bad records are encountered. If the goal is to continue loading valid rows from a file and skip problematic rows, ON_ERROR = CONTINUE is the appropriate setting. Snowflake also provides ways to inspect rejected data and load issues, including validation-related options and load history/error views. By contrast, FORCE = TRUE only bypasses file load history to reload files, PURGE = TRUE manages staged file cleanup after successful loads, and SINGLE applies to unloading data, not loading. This aligns with Snowflake best practices for resilient batch ingestion when occasional malformed records are expected.- A. Correct.
Correct. Setting ON_ERROR = CONTINUE tells Snowflake to continue loading rows that can be parsed successfully instead of failing the entire file load when encountering bad records. Rejected rows can then be reviewed using COPY validation capabilities, such as VALIDATION_MODE in testing scenarios or load metadata/error inspection patterns supported by Snowflake. This is the standard approach when the requirement is to load good rows while skipping bad ones.
- B. Incorrect.
Incorrect. FORCE = TRUE causes Snowflake to reload files even if they were loaded before; it does not change row-level error handling and does not create a separate error table automatically. A common misconception is that FORCE relates to forcing partial loads, but it only affects load history checks for previously loaded files.
- C. Incorrect.
Incorrect. PURGE = TRUE removes successfully loaded files from the stage after the load completes, depending on stage type and permissions. It does not instruct Snowflake to skip invalid rows or preserve rejected row details. Using PURGE here could even be risky if the team wants to reprocess files later.
- D. Incorrect.
Incorrect. SINGLE is used with unloading data to create a single output file in COPY INTO
., not for loading staged data into a table. It has no role in row-by-row parsing behavior for COPY INTO