SnowPro Associate: Platform Question 195
Single answer● Loading dataA data engineering team loads hourly CSV files from an Amazon S3 bucket into a Snowflake table named SALES_RAW. The files occasionally contain extra columns at the end because the source system adds new fields without notice. The team wants the load to continue without failing, while still loading all columns that exist in the current target table. Which approach should they use?
- A
Use COPY INTO SALES_RAW with MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE so Snowflake automatically ignores any source columns that are not in the table
- B
Use COPY INTO SALES_RAW with the file format option ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE so extra trailing columns in the CSV files do not cause the load to fail
- C
Use COPY INTO SALES_RAW with ON_ERROR = CONTINUE so rows with extra columns are skipped and the valid rows load successfully
- D
Use COPY INTO SALES_RAW with FORCE = TRUE so Snowflake reloads files even if the column count changes
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use a file format with ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE. For CSV and other delimited text files, Snowflake expects the parsed field count to align with the target definition or SELECT transformation. If source files sometimes include extra trailing columns, this setting prevents the COPY operation from failing due to column-count mismatch and allows Snowflake to load the fields that correspond to the target table columns. This is a common practical approach when ingesting text files from upstream systems with minor schema drift. By contrast, FORCE only affects whether previously loaded files are reprocessed, and ON_ERROR controls error-handling behavior rather than this specific parsing rule. Candidates should know that handling load resilience often involves choosing the correct file format options for the source file type. See Snowflake documentation for CREATE FILE FORMAT and COPY INTO
, especially the ERROR_ON_COLUMN_COUNT_MISMATCH behavior for CSV loads.- A. Incorrect.
Incorrect. MATCH_BY_COLUMN_NAME is used with supported semi-structured and columnar file formats such as JSON, Avro, ORC, Parquet, and certain load scenarios, but it is not the mechanism for handling extra trailing columns in standard CSV files. A common misconception is that Snowflake can automatically map arbitrary CSV columns by name during COPY INTO a table in this way.
- B. Correct.
Correct. For delimited text files such as CSV, setting ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE in the file format allows Snowflake to load rows even when the number of fields in the file does not exactly match the number of columns expected. When there are extra trailing fields, Snowflake ignores those extra fields rather than failing the load, which matches the requirement to keep loading the columns that exist in SALES_RAW.
- C. Incorrect.
Incorrect. ON_ERROR = CONTINUE tells Snowflake how to behave when row-level errors occur, but it does not solve the underlying column-count mismatch behavior for CSV parsing in the intended way. In this scenario, the goal is not to skip affected rows, but to accept rows with extra trailing columns. Using ON_ERROR alone could still lead to unwanted rejected rows rather than a clean load.
- D. Incorrect.
Incorrect. FORCE = TRUE tells Snowflake to load files even if they were loaded previously, bypassing load history checks. It has nothing to do with handling schema drift or column-count mismatches in CSV files. This option targets reloading behavior, not parsing behavior.