SnowPro Associate: Platform Question 193
Single answer● Loading dataA data engineering team loads daily CSV files from an Amazon S3 bucket into a Snowflake table named SALES_RAW using a named external stage. The files occasionally contain malformed rows, but the team wants to load all valid rows and review the bad rows later. They also want to avoid loading the same file more than once if the COPY statement is accidentally rerun. Which approach best meets these requirements?
- A
Use COPY INTO SALES_RAW with ON_ERROR = CONTINUE and rely on Snowflake's load history to prevent the same staged file from being loaded again
- B
Use COPY INTO SALES_RAW with ON_ERROR = ABORT_STATEMENT and FORCE = TRUE so malformed rows are skipped and duplicate file loads are prevented
- C
Use INSERT INTO SALES_RAW SELECT * FROM @stage and set ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE to skip bad rows and track loaded files automatically
- D
Use COPY INTO SALES_RAW with VALIDATION_MODE = RETURN_ERRORS so valid rows are loaded and invalid rows are stored for later review
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use COPY INTO with ON_ERROR = CONTINUE. In Snowflake, COPY INTO is the standard bulk-loading command for staged data. When malformed records are present, ON_ERROR = CONTINUE allows valid rows to load while problematic rows are skipped. To investigate issues later, teams commonly review COPY results, validation output, or rejected records details using validation queries. Snowflake also maintains load metadata for files loaded through COPY INTO from stages, which helps prevent the same file from being loaded multiple times unless options such as FORCE = TRUE are used. This is a common best practice for resilient batch ingestion. Relevant Snowflake documentation includes COPY INTO
, data loading considerations, ON_ERROR behavior, VALIDATION_MODE usage, and metadata-based duplicate file prevention for staged file loads.- A. Correct.
Correct. COPY INTO supports ON_ERROR = CONTINUE, which allows Snowflake to load valid rows while skipping rows that cause parsing or conversion errors. Snowflake also tracks loaded files for staged data loads in load metadata, so rerunning the same COPY command does not reload the same file by default unless explicitly forced. This directly addresses both requirements: partial loading of valid data and protection against accidental duplicate file ingestion.
- B. Incorrect.
Incorrect. ON_ERROR = ABORT_STATEMENT does the opposite of the requirement to load valid rows despite malformed rows; it stops the load when an error is encountered. FORCE = TRUE also overrides Snowflake's duplicate-file protection and can reload files that were already loaded, increasing the risk of duplicates rather than preventing them.
- C. Incorrect.
Incorrect. While querying staged files with SELECT from @stage is possible in some loading patterns, INSERT ... SELECT from staged files does not provide the same file load tracking behavior as COPY INTO for staged file ingestion. Setting ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE only addresses one specific file format issue and does not generally provide robust bad-row handling or automatic duplicate-file prevention.
- D. Incorrect.
Incorrect. VALIDATION_MODE is used to validate files and return errors without loading data into the target table. It is useful for troubleshooting, but it does not load valid rows while skipping invalid ones. Therefore, it does not meet the requirement to ingest good data immediately.