topic Re: Data discrepancy between lakehouse view and SQL endpoint view in Data Engineering

Data discrepancy between lakehouse view and SQL endpoint view

_maclura_ — Fri, 23 May 2025 06:12:23 GMT

Hi,

I have a delta table in a lakehouse. This table contains several Date columns.

If I preview the table content from the lakehouse, this is what I see for some Date columns

If I preview the same table content from the lakehouse SQL analytics endpoint, this is what I see for exactly the same Date columns and rows.

Any clue?
Thanks

Re: Data discrepancy between lakehouse view and SQL endpoint view

v-sathmakuri — Fri, 23 May 2025 19:46:06 GMT

Hi @_maclura_ ,

Thank you for reaching out to Microsoft Fabric Community.

I have reproduced your scenario and understood that in many Spark and Delta Lake environments, DateType columns are internally stored as dates (without time), but when displayed, especially in some UIs or query outputs, they often show with a timestamp part like 00:00:00 or sometimes an offset (e.g., 01:00:00). This is a formatting artifact and does not mean the time is actually stored or used the data itself represents date only.

If you want to display dates without the time portion explicitly, the recommended approach is to convert the DateType column to a formatted string. you can use below code for conversion :

from pyspark.sql.functions import date_format

df = df.withColumn("MPSDate_Str", date_format("MPSDate", "yyyy-MM-dd"))

This string column will show just the date consistently both in Spark notebooks and SQL queries.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Thank you!!

Re: Data discrepancy between lakehouse view and SQL endpoint view

v-sathmakuri — Tue, 27 May 2025 08:07:55 GMT

Hi @_maclura_ ,

May I ask if the provided solution helped in resolving the issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you!!

Re: Data discrepancy between lakehouse view and SQL endpoint view

_maclura_ — Tue, 27 May 2025 09:58:39 GMT

Thank you @v-sathmakuri for taking the time to dig into this issue.
Unfortunately your suggested workaround is not an option for my needs: I need a "date" datatype and not a string.
By the way, I also noticed that in all other deltatables where I have a "Timestamp" datatype column, all the values are truncated in the datalake preview, and all you see is the portion hh:mm:ss.
My point is: if I have an "Apple" datatype, I should see an "Apple" whatever is the point of view I look at it. And not seeing an "Apple" from one point of view and an "Orange" from another. Right?

Re: Data discrepancy between lakehouse view and SQL endpoint view

_maclura_ — Wed, 28 May 2025 07:56:07 GMT

OK, here the (not a) solution.
I opened a ticket to Microsoft for this.
It seems to be a known issue that is already in progress for a fix.
This issue only affects the cell value rendering in the Fabric UI and not the actual content of the cell.
Affected datatypes are "Date" and "Timestamp".

Looking forward for a fix.
😉