SnowPro Associate: Platform Question 224
Single answer○ COPY INTOA data engineer needs to load daily CSV files from an internal stage into an existing Snowflake table named SALES_RAW. The files occasionally contain a few malformed rows, but the business requirement is to load all valid rows and continue processing the rest of each file. The engineer also wants to avoid reloading files that were already loaded successfully in previous runs. Which COPY INTO statement option best meets these requirements?
- A
Use COPY INTO SALES_RAW FROM @daily_stage FILE_FORMAT = (TYPE = CSV) ON_ERROR = CONTINUE;
- B
Use COPY INTO SALES_RAW FROM @daily_stage FILE_FORMAT = (TYPE = CSV) FORCE = TRUE ON_ERROR = CONTINUE;
- C
Use COPY INTO SALES_RAW FROM @daily_stage FILE_FORMAT = (TYPE = CSV) VALIDATION_MODE = RETURN_ERRORS;
- D
Use COPY INTO SALES_RAW FROM @daily_stage FILE_FORMAT = (TYPE = CSV) ON_ERROR = ABORT_STATEMENT;
Show answer and explanation
Correct answer: A
Explanation
For loading staged files into a Snowflake table, COPY INTO supports error-handling behavior through the ON_ERROR parameter. When the requirement is to ingest as many valid records as possible while skipping malformed rows, ON_ERROR = CONTINUE is the appropriate choice. Snowflake also maintains load metadata for staged files and, by default, prevents the same files from being loaded again in subsequent COPY operations unless options such as FORCE = TRUE are used. This makes option 1 the best fit for the scenario. In contrast, VALIDATION_MODE checks files without loading them, and ON_ERROR = ABORT_STATEMENT stops the load on the first error. These behaviors align with Snowflake documentation for COPY INTO
, staged file loading, and load status tracking best practices.- A. Correct.
Correct. ON_ERROR = CONTINUE allows COPY INTO to skip problematic rows and continue loading valid rows from the file set. By default, Snowflake tracks loaded files for staged data loads and avoids loading the same file again unless explicitly forced, so this meets both requirements: tolerate malformed rows and prevent reloading previously loaded files.
- B. Incorrect.
Incorrect. ON_ERROR = CONTINUE does allow valid rows to load while bad rows are skipped, but FORCE = TRUE overrides Snowflake's load metadata behavior and reloads files even if they were loaded successfully before. That violates the requirement to avoid reloading previously processed files.
- C. Incorrect.
Incorrect. VALIDATION_MODE = RETURN_ERRORS is used to validate data and return errors instead of actually loading the data. This can help diagnose file issues, but it does not satisfy the requirement to load valid rows into the target table.
- D. Incorrect.
Incorrect. ON_ERROR = ABORT_STATEMENT causes the entire COPY statement to fail when an error is encountered. This does not meet the requirement to continue loading valid rows when some malformed rows exist.