Forum Discussion

sharp1FL's avatar
sharp1FL
Icon for Helper I rankHelper I
8 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.

Vinodh247's avatar
Vinodh247
Icon for Super User rankSuper User
8 months ago

The mismatch happens because the two engines do not read the same physical data.

 

In Fabric:

• SQL endpoint reads the Delta table as published in the Lakehouse catalog, including any committed transactions.

• Notebooks read the underlying files directly via Spark, and if the shortcut target has staging files, uncommitted transactions, or files not in the Delta log, Spark may skip or include different data.

 

possible causes:

  1. Shortcut points to a table where the Delta log is ahead, but the notebook session is reading cached metadata.

  2. Autoloader or pipelines have created files that are not yet committed to the Delta log.

  3. The shortcut root includes extra folders that spark interprets differently.

  4. Notebook is using display() on a path instead of reading the registered table.

Fixes that you can try:

• Restart the Spark session and run spark.sql("REFRESH TABLE <table>").
• Ensure the shortcut maps to the Delta table root (with _delta_log).
• Query using spark.sql("SELECT COUNT(*) FROM <table>"), not by path.

 

In short, SQL endpoint reads the authoritative Delta table catalog; notebooks may be reading stale or non-Delta-consistent files.

 

 

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.