Inconsistent behaviour when using read_files to read UTF-8 BOM encoded csv

JackR
New Contributor II

‎11-27-2025 04:51 AM

I have a simple piece of code to read a csv file from an AWS s3 bucket:

 

SELECT
    *
  FROM
    read_files(
      myfile,
      format => 'csv',
      header => true,
      inferSchema => true,
      mode => 'FAILFAST')
 
It's a large file with over 100 columns and it has been sufficient to infer the schema.  However, the input csv has changed to be encoded as UTF-8 BOM (previously UTF-8) and now the data types are not being inferred and everything is being read as a string.  However, this is not consistent, as I tried the same thing with a 100-record sample and the data types were identified correctly.
 
What's more, if I read in the data using equivalent pyspark code, it seems to be fine:
 
df_infer_schema = spark.read.format("csv") \
  .option("InferSchema", "True") \
  .option("header", "True") \
  .option("sep", ",") \
  .load(file_to_use)
 
Is anyone able to shed light on what's happening?  Why is the SQL method behaving weirdly?  And why is it behaving differently to pyspark?
0 Kudos