SnowPro Associate: Platform Question 46
Single answerData loadingA data engineering team receives daily CSV files in an Amazon S3 bucket and needs to load them into a Snowflake table named SALES_RAW. The files sometimes contain a few malformed rows, but the business wants all valid rows loaded as quickly as possible while keeping a record of any rejected rows for troubleshooting. Which Snowflake approach best meets these requirements?
- A
Run a COPY INTO SALES_RAW command with ON_ERROR=CONTINUE and then use the VALIDATE function to review rejected rows after the load.
- B
Run a COPY INTO SALES_RAW command with ON_ERROR=ABORT_STATEMENT so the entire file is rejected if any row has an error, ensuring rejected rows can be reviewed.
- C
Use INSERT statements to load the CSV data row by row so invalid rows can be skipped and logged automatically by Snowflake.
- D
Use CREATE STAGE with DIRECTORY = (ENABLE = TRUE) so Snowflake automatically loads valid rows and stores invalid rows in the stage directory table.
Show answer and explanation
Correct answer: A
Explanation
For staged file ingestion into Snowflake, COPY INTO is the recommended and most efficient command for bulk loading structured data such as CSV files from S3. When a business requirement is to load good records even if some rows are bad, ON_ERROR=CONTINUE is appropriate because it skips rows with errors and continues processing. To investigate what failed, Snowflake provides the VALIDATE function, which returns errors encountered during a previous COPY operation. In contrast, ON_ERROR=ABORT_STATEMENT is better when strict all-or-nothing loading is required. This scenario reflects a common operational pattern described in Snowflake documentation for loading data from staged files and handling data load errors.
- A. Correct.
Correct. COPY INTO is the standard bulk-loading mechanism for staged files such as CSV files in S3. Setting ON_ERROR=CONTINUE allows Snowflake to load valid rows and skip problematic rows instead of failing the entire load. Afterward, the VALIDATE table function can be used to retrieve error details for rows that failed during a previous COPY operation, which supports troubleshooting while still meeting the requirement to load valid data quickly.
- B. Incorrect.
Incorrect. ON_ERROR=ABORT_STATEMENT stops the load when an error is encountered, which does not meet the requirement to load all valid rows. While this may help enforce strict data quality, it is not the best choice when the business explicitly wants successful rows loaded despite some malformed records.
- C. Incorrect.
Incorrect. Row-by-row INSERT statements are much less efficient than Snowflake bulk loading for staged files and are not the recommended approach for loading daily CSV files at scale. Also, Snowflake does not automatically provide the same rejected-row handling pattern for ad hoc INSERT statements that COPY INTO with validation functions provides.
- D. Incorrect.
Incorrect. Enabling a stage directory table helps track files in a stage, especially for unstructured data and file listing use cases, but it does not perform data loading or capture rejected table rows from CSV parsing errors. This option confuses stage metadata management with data load error handling.