Lakehouse data discrepancy between notebook and SQL endpoint or Dataflow

Hi there,

It sounds like you're encountering a synchronization issue between your Microsoft Fabric Lakehouse table and the notebook interface. This kind of issue can occur due to inconsistencies in metadata or caching between the Delta table and the notebook's runtime.

Here are a few steps you could try to resolve the issue:

1. Verify Table Metadata:

   Use the SQL endpoint to check the table's metadata and schema:

   DESCRIBE DETAIL your_table_name;

   Compare the schema against what your notebook expects. If the table was deleted and reloaded, the metadata may not have updated properly.

2. Manually Refresh the Delta Table Metadata:

   You can refresh the Delta table metadata in your notebook using the following:

   from delta.tables import DeltaTable
   
   # Refresh the table metadata
   DeltaTable.forPath(spark, "path_to_your_table").refresh()
               

3. Clear Spark Cache:

   If your notebook is caching old metadata, try clearing the Spark cache:

   spark.catalog.clearCache()

4. Recheck Delta Table Path:

   Confirm that the notebook is pointing to the correct Delta table path. If the table was reloaded, the path might have been updated, but your notebook could still be referencing the old location:

   df = spark.read.format("delta").load("path_to_your_table")
   df.show()
               

5. Inspect Logs for Errors:

   Look into the notebook's logs for errors related to access permissions or inconsistencies when reading the table.

6. Rebuild the Table if Necessary:

   If none of the above work, you might need to rebuild the table. This can involve dropping and re-creating it from the Delta table storage:

   DROP TABLE IF EXISTS your_table_name;
   CREATE TABLE your_table_name USING DELTA LOCATION 'path_to_your_table';
               

Hopefully, one of these steps helps resolve the issue. Let us know how it goes or if you encounter any specific errors!

Best regards,

Jaseph

Hi Anonymous ,

Can you provide some screenshot information describing exactly what you did to access the data in different ways? Everything works fine in my tests.

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Possibly related topic: https://community.fabric.microsoft.com/t5/Data-Engineering/Duplicated-rows-between-notebook-and-SQL-Endpoint/m-p/3707317

In that case (and another thread also https://community.fabric.microsoft.com/t5/Data-Engineering/Duplicated-Rows-In-Tables-Built-By-Notebook/m-p/3661271) the issue was not that the Notebook returned blank. However there was a discrepancy between data displayed in Notebook (which gave unexpected results) and SQL Analytics Endpoint (which gave expected results).

Do you get any error message when creating the dataframe? (I.e. table missing, table name not found, or something like that).

Or it just returns 0 rows?

Did you try querying the table using %%sql in the Notebook?

%%sql

SELECT * FROM tableName;

or 

%%sql

DESCRIBE HISTORY tableName;

I have a similar error where I have the correct amount of rows in my sql endpoint but when I access the data from a notebook, there is only 698 row in the table. When I go on file view in the lakehouse, there is only two files + _delta_log, even thoough the table have been uptdated more times. I am only experiencing this error with one table. Using the same code (with different table name), I can access all the rows. The same error persist across different workspaces for the same table.

Hi

Hi there,

It sounds like you're encountering a synchronization issue between your Microsoft Fabric Lakehouse table and the notebook interface. This kind of issue can occur due to inconsistencies in metadata or caching between the Delta table and the notebook's runtime. Here are a few steps you could try to resolve the issue:

1. Verify Table Metadata

• Use the SQL endpoint to check the table's metadata and schema:

DESCRIBE DETAIL your_table_name;

• Compare the schema against what your notebook expects. If the table was deleted and reloaded, the metadata may not have updated properly.

2. Manually Refresh the Delta Table Metadata

• You can refresh the Delta table metadata in your notebook using the following:

from delta.tables import DeltaTable

# Refresh the table metadata
DeltaTable.forPath(spark, "path_to_your_table").refresh()

3. Clear Spark Cache

• If your notebook is caching old metadata, try clearing the Spark cache:

spark.catalog.clearCache()

4. Recheck Delta Table Path

• Confirm that the notebook is pointing to the correct Delta table path. If the table was reloaded, the path might have been updated, but your notebook could still be referencing the old location:

df = spark.read.format("delta").load("path_to_your_table")
df.show()

5. Inspect Logs for Errors

• Look into the notebook's logs for errors related to access permissions or inconsistencies when reading the table.

6. Rebuild the Table if Necessary

• If none of the above work, you might need to rebuild the table. This can involve dropping and re-creating it from the Delta table storage:

DROP TABLE IF EXISTS your_table_name;
CREATE TABLE your_table_name USING DELTA LOCATION 'path_to_your_table';

Hopefully, one of these steps helps resolve the issue. Let us know how it goes or if you encounter any specific errors!

Best regards,
Jaseph