SnowPro Associate: Platform Question 281
Single answer● Use COPY INTO <table> statementsA data engineer needs to load daily CSV files from an internal stage into the SALES_RAW table. The files sometimes contain a few malformed records, but the business requirement is to load all valid rows and continue processing without stopping the entire load. The engineer also wants to avoid reloading files that were successfully loaded in previous runs. Which COPY INTO
statement best meets these requirements?- A
COPY INTO SALES_RAW FROM @daily_stage FILE_FORMAT = (TYPE = CSV) ON_ERROR = CONTINUE;
- B
COPY INTO SALES_RAW FROM @daily_stage FILE_FORMAT = (TYPE = CSV) FORCE = TRUE ON_ERROR = CONTINUE;
- C
COPY INTO SALES_RAW FROM @daily_stage FILE_FORMAT = (TYPE = CSV) ON_ERROR = ABORT_STATEMENT;
- D
COPY INTO SALES_RAW FROM @daily_stage FILE_FORMAT = (TYPE = CSV) LOAD_MODE = FULL ON_ERROR = CONTINUE;
Show answer and explanation
Correct answer: A
Explanation
The best answer is Option 1. In Snowflake, COPY INTO
maintains metadata about loaded staged files, so successfully loaded files are not loaded again by default. This makes COPY suitable for recurring ingestion from stages without custom deduplication logic at the file level. To handle malformed records while still loading good data, ON_ERROR = CONTINUE is the appropriate setting. Option 2 is a common mistake because FORCE = TRUE seems useful for ensuring data loads, but it overrides the default protection and reloads previously loaded files. Option 3 fails because ABORT_STATEMENT stops the load on errors. Candidates should know that COPY INTO- A. Correct.
Correct. COPY INTO
tracks load metadata for staged files and, by default, does not reload files that were already loaded successfully. Setting ON_ERROR = CONTINUE allows the load to continue past problematic rows so valid records can still be ingested. This directly satisfies both requirements. - B. Incorrect.
Incorrect. ON_ERROR = CONTINUE does allow valid rows to load despite malformed records, but FORCE = TRUE tells Snowflake to load files even if they were previously loaded. That violates the requirement to avoid reloading files that were already processed successfully.
- C. Incorrect.
Incorrect. ON_ERROR = ABORT_STATEMENT is the default behavior and causes the entire COPY statement to fail when an error is encountered in a file. This does not meet the requirement to continue loading valid rows when some records are malformed.
- D. Incorrect.
Incorrect. LOAD_MODE = FULL is not the appropriate choice for this requirement and is not used to preserve the normal file load tracking behavior in the way described here. The key behavior needed is Snowflake's default avoidance of reloading previously loaded files, combined with ON_ERROR = CONTINUE.