topic Efficient Detection of Schema Mismatch in CSV Files During Single Pass Reading in Get Started Discussions

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

reuvenk121 — Wed, 27 Dec 2023 19:01:50 GMT

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

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

Wojciech_BUK — Thu, 28 Dec 2023 09:18:06 GMT

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-rescued-data-column

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.