Stronger UTF-8 validation for external files

This behavior change has been implemented with the 7.34 release. For the most up-to-date details about behavior changes, see the Behavior Change Log.

Snowflake has stronger UTF-8 validation for external files.

Before the change

When you query external Avro, Parquet, Orc, CSV, JSON, or XML files that contain invalid UTF-8 data, the queries usually succeed.

After the change

When you query external Avro, Parquet, Orc, CSV, JSON, or XML files that contain invalid UTF-8 data, the queries fail.

If you load external files with COPY INTO <table> or Snowpipe that contain invalid UTF-8 data, Snowflake proceeds with the copy option ON_ERROR = CONTINUE. Snowflake considers the record that contains invalid UTF-8 data as an error and continues to load the file.

To avoid UTF-8 validation errors, Snowflake recommends that you specify REPLACE_INVALID_CHARACTERS = TRUE for your file format so that any invalid UTF-8 characters will be replaced with the Unicode replacement character ().

For Parquet files, you can also set BINARY_AS_TEXT = FALSE for your file format so that the columns with no defined logical data type will be interpreted as binary data instead of as UTF-8 text.

Note that this behavior change does not apply to existing accounts that are currently loading invalid UTF8. It only affects new accounts. For any issues, contact Snowflake Support.

Ref: 1013 1014