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.

apturlov's avatar
apturlov
Icon for Super User rankSuper User
9 months ago

Hello sharp1FL, let me clarify your scenario. Please correct me if I misunderstood.

  1. You have a SQL database in Fabric.
  2. You have a Lakehouse in Fabric.
  3. You created a table shortcut in your Lakehouse to a table in Fabric SQL database table.
  4. You are testing the number of rows in the Fabric SQL database table using both SQL endpoints and notebook.

Please clarify the following:

  1. When you open a SQL query in Fabric SQL database SQL endpoint what number of rows do you get?
  2. When you open a SQL query in Lakehouse SQL endpoint what number of rows do you get?
  3. When you run a Spark SQL query in in your notebook what number of rows do you get?
  4. When you run a PySpark cell your notebook what number of rows do you get?
  5. 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.