SQL error when querying parquet table added to Lake database via GUI

Question

I created a Lake database and added a table via the "+Table / From data lake" option in the GUI, using a parquet file residing in my data lake.
When trying to query the added table I get the following error:
Column 'col' of type 'REAL' is not compatible with external data type 'Parquet physical type: DOUBLE', please try with 'FLOAT'. File/External table name: 'testtable'.
However the column type was correctly inferred as float, at least that is what shows up in the GUI

Answer 1

Hi @Nikolai Kobyzev @Andreas Nord ,

I've had the same problem sometimes when I wanted to read a table in synapse. It also happened to me with other data types: Integer, decimal, Boolean and date.

I had the same problem again when loading the data from my table into power BI.

The problem is solved when I add a condition on its columns in my synapse dataflow (I use a derived column with my column as reference).

I replace the null values (perhaps null values are explicitly translated as "null" instead of " ") , I remove the spaces in the numerical values, replace the "," by "." for the decimal values and indeed there are no more problems, it works correctly now.

Perhaps make sure that the columns concerned also have these conditions.

I hope this answer will help someone,

Answer 2

The same happened to me with a Date field, trying to load from lakehouse into warehouse.
I tried setting the mapping tab, but did not work.

The way it worked was to load data from lakehouse into warehouse as varchar, and then read data from the table that contain the varchar and transform the column with a cast to date. This cast created a new column with the correct date data type: