SnowPro Associate: Platform Question 48
Single answerData loadingA retail company receives hourly CSV files from a partner in an Amazon S3 bucket. The files contain a header row and occasionally include malformed records. The analytics team wants to load as many valid rows as possible into a Snowflake table while skipping the header row and continuing past bad records. Which approach best meets these requirements?
- A
Create or use a CSV file format with SKIP_HEADER = 1, then run COPY INTO the target table with ON_ERROR = CONTINUE
- B
Run PUT to upload the files from the S3 bucket into an internal stage, then use COPY INTO with PURGE = TRUE
- C
Use Snowpipe with VALIDATION_MODE = RETURN_ERRORS so malformed rows are skipped automatically during continuous loading
- D
Run COPY INTO the target table with FORCE = TRUE so Snowflake ignores malformed rows and reloads only the valid records
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use a CSV file format that skips the header row and a COPY INTO command configured to continue on errors. In Snowflake, header handling for CSV files is controlled through file format options such as SKIP_HEADER. Error handling during bulk loading is controlled with COPY options such as ON_ERROR, where CONTINUE allows valid rows to load even if some rows fail parsing or conversion. This aligns with Snowflake data loading best practices for semi-clean batch files where business requirements prioritize ingestion of usable records. By contrast, PUT is for local-to-internal-stage uploads, not S3-to-Snowflake transfers; VALIDATION_MODE is for testing/diagnosing loads rather than standard ingestion; and FORCE relates to reloading previously loaded files, not malformed-row handling. See Snowflake documentation for COPY INTO
, file formats for CSV, and data loading from Amazon S3.- A. Correct.
Correct. For CSV data, SKIP_HEADER = 1 in the file format skips the header row, and COPY INTO ... ON_ERROR = CONTINUE allows Snowflake to continue loading valid rows when some records are malformed. This is a common pattern when the goal is to maximize ingestion of good data while tolerating a limited number of bad rows.
- B. Incorrect.
Incorrect. PUT is used to upload local files to an internal stage, not to move files from an Amazon S3 bucket into Snowflake. For S3 data, Snowflake typically loads directly from an external stage referencing the bucket. PURGE = TRUE also applies to removing loaded files from stage locations where supported, but it does not address skipping headers or continuing past malformed rows.
- C. Incorrect.
Incorrect. VALIDATION_MODE is used with COPY INTO to validate files and return errors instead of loading data. It is not how Snowpipe skips bad rows during normal continuous ingestion. Snowpipe can load from staged files, but malformed-row handling is not achieved by setting VALIDATION_MODE in the way described here.
- D. Incorrect.
Incorrect. FORCE = TRUE tells Snowflake to reload files even if they were loaded previously and recorded in load metadata. It does not control error handling for malformed rows. Someone might choose this option by confusing reload behavior with row-level error tolerance.