Efficient Detection of Schema Mismatch in CSV Files During Single Pass Reading

[reuvenk121]

Hello, when I read a CSV file with a schema object, if a column in the original CSV contains a value of a different datatype than specified in the schema, the result is a null cell. Is there an efficient way to identify these cases without having to read the CSV file twice—first with the inferSchema option set to FALSE (reading all columns as strings) and then again with a schema object—followed by comparing the count of nulls for each column?

Thank you

[Wojciech_BUK]

Maybe you can try to read the data and let AutoLoader move missmatch data e.g. to rescueColumn

https://learn.microsoft.com/en-us/azure/databricks/ingestion/auto-loader/schema#--what-is-the-rescue...

Then you can decide what you do with rescue data.

If you think that there will be a lot of issues with data types, you can also try loading everything as STRING to BRONZE table, then create Silver table with specific schema and set up loading procedure.
You can then move bad records to badRecordsPatch in case of schema missmatch.