SQL compilation error: invalid parameter ‘validation_mode’ Assuming the syntax is correct, what is the cause of this error?

Consider the following COPY command which is loading data with CSV format into a Snowflake table from an internal stage through a data transformation query.

This command results in the following error:

SQL compilation error: invalid parameter ‘validation_mode’ Assuming the syntax is correct, what is the cause of this error?
A . The VALIDATION_MODE parameter supports COPY statements that load data from external stages only.
B . The VALIDATION_MODE parameter does not support COPY statements with CSV file formats.
C . The VALIDATION_MODE parameter does not support COPY statements that transform data during a load.
D . The value return_all_errors of the option VALIDATION_MODE is causing a compilation error.

View Answer

Answer: C

Explanation:

The VALIDATION_MODE parameter is used to specify the behavior of the COPY statement when loading data into a table. It is used to specify whether the COPY statement should return an error if any of the rows in the file are invalid or if it should continue loading the valid rows. The VALIDATION_MODE parameter is only supported for COPY statements that load data from external stages1.

The query in the question uses a data transformation query to load data from an internal stage. A data transformation query is a query that transforms the data during the load process, such as parsing JSON or XML data, applying functions, or joining with other tables2.

According to the documentation, VALIDATION_MODE does not support COPY statements that transform data during a load. If the parameter is specified, the COPY statement returns an error1. Therefore, option C is the correct answer.

Reference: COPY INTO <table>: Transforming Data During a Load