Forum Discussion
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 the inconsistency?
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.
6 Replies
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:
Shortcut points to a table where the Delta log is ahead, but the notebook session is reading cached metadata.
Autoloader or pipelines have created files that are not yet committed to the Delta log.
The shortcut root includes extra folders that spark interprets differently.
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.
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.
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.
Hello sharp1FL, let me clarify your scenario. Please correct me if I misunderstood.
- You have a SQL database in Fabric.
- You have a Lakehouse in Fabric.
- You created a table shortcut in your Lakehouse to a table in Fabric SQL database table.
- You are testing the number of rows in the Fabric SQL database table using both SQL endpoints and notebook.
Please clarify the following:
- When you open a SQL query in Fabric SQL database SQL endpoint what number of rows do you get?
- When you open a SQL query in Lakehouse SQL endpoint what number of rows do you get?
- When you run a Spark SQL query in in your notebook what number of rows do you get?
- When you run a PySpark cell your notebook what number of rows do you get?
- When you run a T-SQL query in in your notebook what number of rows do you get?
Because of the differences of how SQL endpoints and Spark notebooks work against the delta tables plus the delay that may happen between the shortcut delta table (which is a replicated data) you might see time-sensitive differences. But, I would suggest to use a SQL endpoint for a SQL table as a source of truth.
There may be a reason why you want to have a shortcut in a Lakehouse for a SQL table, but keep in mind that while it can be considered as a raw data in a Medallion architecture, you are not using a batch ingestion with this approach. Shortcut is a result of automatic replication from a transactional SQL database into analytical store based on delta tables. Both data sets should eventually have the same data but it's not guaranteed at which point. So, why do you want to compare row count for different sources?
This is not exactly an answer to your question, but I hope the though chain could be useful to determine your course of actions.Hi sharp1FL ,
Could you please let us know if your issue has been resolved, or if you need any additional information.
Thank you.
