Forum Discussion

sharp1FL's avatar
sharp1FL
Icon for Helper I rankHelper I
9 months ago
Solved

Fabric SQL table lake house shortcut query inconsistency

I created a lake house shortcut to a fabric table. When I perform a count(*) query in SQL and the notebook, I get different values with a much smaller count from the notebook. Can anyone help explain...
data engineering
lakehouse
  • V-yubandi-msft's avatar
    V-yubandi-msft
    8 months ago

    Hi sharp1FL ,

    Thank you for for sharing your observations. From your investigation, it appears this is expected behavior in Fabric when using Lakehouse shortcuts. In certain cases, Spark notebooks may continue to use cached shortcut metadata from when the shortcut was first created, and restarting the Spark session or refreshing the table might not always update to the latest Delta state.

     

    Since this was a one time process and the table is only updated during off hours, materializing the data into a native Lakehouse table is the most reliable and supported way to ensure consistent results across SQL and Spark.

     

    Thanks for confirming the results and sharing your workaround, as it may help others facing similar issues.

sharp1FL's avatar
sharp1FL
Icon for Helper I rankHelper I
8 months ago

Since this was a one-time process, I don't have actual numbers readily available. But I did find enough information to point to a caching discrepancy that I was unable to resolve.

 

The SQL query showed about 100,000 more rows that the shortcut. Additionally, I investigated the shortcut and found it showed a date of 12/8 which was when the shortcut was initially created.  

 

I tried several things to resolve it. I did the following as suggested above:

1. I restarted the Spark session and ran spark.sql("REFRESH TABLE <table>"). There was not change in the counts.
2. I double checked that the shortcut mapping looked correct.
3. All my queries were using spark.sql("SELECT COUNT(*) FROM <table>"), not by path.

 

So I was not able to resolve this. It was simpler for me to move the data from the SQL table to a lake house table to work with, particularly since I didn't have to worry about transient data while I was working with it because this table is only update during off hours.

 

Thanks for the suggestions.

V-yubandi-msft's avatar
V-yubandi-msft
Icon for Community Support rankCommunity Support
8 months ago

Hi sharp1FL ,

Thank you for for sharing your observations. From your investigation, it appears this is expected behavior in Fabric when using Lakehouse shortcuts. In certain cases, Spark notebooks may continue to use cached shortcut metadata from when the shortcut was first created, and restarting the Spark session or refreshing the table might not always update to the latest Delta state.

 

Since this was a one time process and the table is only updated during off hours, materializing the data into a native Lakehouse table is the most reliable and supported way to ensure consistent results across SQL and Spark.

 

Thanks for confirming the results and sharing your workaround, as it may help others facing similar issues.