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