SQL Enpoint no longer working

More investigations on this repeating and itermittent issue....

• On First read of a new table from Odata to Delta Table in Fabric Lakehouse
• When we are reading an OData source (we suspect that this may happen on most/any source), we have a mix of varchar, boolean, datetime columns from our source

• SOME of the columns (especially the datetime and Boolean columns) MAY have a NULL value in them…

• We believe the COPY activity in Fabric Pipelines is reading that data type with a NULL and translating it to a varchar (likely some kinda of defaulted data type in a delta table target)
• This means that the above fields that are Boolean or datetime get set to NULL the target Delta table on the INITIAL read from the source
• After looking at several tables, comparing our Odata source to the Delta lake target, this seems to be a consistent Data Type setting pattern (which is ☹)

        

  (in this example, you can see some datetime and boolean fields are coming through correctly and some are not!)

• So first time execution of a new table from scratch, we see no errors in loading or updating (using pyspark/sparksql)… however….

• On Subsequent INCREMENTAL runs of that same table from OData to Delta Table here is what is likely happening:
• The ‘new’ rows from the source are read and the engine now sees datetimes or Booleans and then writes a NEW .parquet file with THAT data type (different from the one that as written in the initial load)
• When we go to do any transformations, updates, reads, or sql endpoint querying… these processes are expecting ALL rows from all parquet files in that table to have the same data type… and then don’t!  and bang!  You get an error like we’ve been seeing since we started using Fabric:

“py4j.GatewayConnection.run(GatewayConnection.java:238)\n\tat java.lang.Thread.run(Thread.java:750)\nCaused by: org.apache.spark.SparkException: Job aborted due to stage failure: Task 1 in stage 24.0 failed 4 times, most recent failure: Lost task 1.3 in stage 24.0 (TID 266) (vm-cdb84346 executor 1): org.apache.spark.sql.execution.QueryExecutionException: Parquet column cannot be converted in file abfss://e7fe3852-3049-4795-a43f-17985a4fa98d@onelake.dfs.fabric.microsoft.com/d1d79935-5097-42b6-bc91-7d57b9ab5a52/Tables/EmployeeEmploymentStatus_Landed/f41f826c-1558-4e73-be43-7007b874c32d.parquet. Column: [Base_Rate_Manually_Set], Expected: string, Found: BOOLEAN\n\tat”