Snowflake中VALIDATION_MODE='RETURN_ERRORS'未正确验证文件的问题咨询

My Scenario #

I tried using Snowflake's COPY INTO command with VALIDATION_MODE='RETURN_ERRORS' to validate a CSV data file, but didn't get the error I expected. Here's the command I ran:

copy into users from @%users/user_account.txt.gz file_format=(type = 'CSV') validation_mode='RETURN_ERRORS';

The first two lines of my CSV file look like this:

id,name,emailid,signupdate
1,user1,user1@gmail.com,13/10/2021

Since the first line is a text-based header, and I didn't specify SKIP_HEADER or DATE_FORMAT in the file format options, I expected the command to throw a data type mismatch error (the header's id string can't map to a numeric column in my users table). Instead, the command succeeded and returned 0 error records.

When I switched to VALIDATION_MODE='RETURN_1_ROWS' with this command:

copy into users from @%users/user_account.txt.gz file_format=(type = 'CSV') validation_mode='RETURN_1_ROWS';

I did get a data type error for the header row. This makes me wonder—did I use the first RETURN_ERRORS command incorrectly?

The Root Cause: Different Behavior Between Validation Modes #

Snowflake handles these two validation modes with distinct logic, which explains the discrepancy:

• RETURN_1_ROWS: This mode forces Snowflake to parse and validate the very first row of the file, regardless of whether it looks like a header. Since your first row is text-based (and doesn't match your table's column types), it throws the expected error.
• RETURN_ERRORS: This mode focuses on validating rows that Snowflake identifies as actual data. A key detail here is that Snowflake has automatic header detection logic: if the first row's values match your target table's column names (and subsequent rows have matching data types), it will automatically skip the header row without you specifying SKIP_HEADER=1.

In your case, Snowflake recognized the first line as a header and skipped it, only validating the second row of actual data. If that second row is compatible with your table's schema (e.g., 13/10/2021 can be implicitly converted to a date using your session's default date format, or signupdate is a VARCHAR column), there are no errors to return—hence the 0 error records.

How to Verify This #

To confirm this behavior, try modifying your command to force Snowflake to treat all rows as data:

copy into users from @%users/user_account.txt.gz 
file_format=(type = 'CSV', SKIP_HEADER=0) 
validation_mode='RETURN_ERRORS';

By setting SKIP_HEADER=0, you disable automatic header detection. Snowflake will now validate the header row as data, and you should see the data type mismatch error you initially expected.

Summary #

You didn't misuse the RETURN_ERRORS command—its behavior is just different from RETURN_1_ROWS:

• RETURN_1_ROWS returns results for the first row, no matter its content.
• RETURN_ERRORS skips rows it identifies as headers and only reports errors in actual data rows.

If you want to catch header-related errors with RETURN_ERRORS, explicitly disable automatic header detection using SKIP_HEADER=0.

内容的提问来源于stack exchange，提问作者Pawan Rawat