Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started

Reply
alozovoy
Advocate II
Advocate II

SQL Endpoint Slow To Reflect Changes In Lakehouse

I have been using notebooks to create and modify delta tables for several months. I run the notebooks in the browser app and then I query the table using the SQL endpoint to validate the changes. I use SSMS 19 to excecute the queries.

 

I have noticed that recently it takes much longer for the changes to show up through the SQL endpoint. I just did a test where I modified a table in a notebook and then repeatedly ran a query every few seconds until I saw the changes. It took almost 10 minutes from notebook completion until the table changes were reflected in the results of the query.

 

I'm curious if other users have noticed this delay recently and what delay should be considered normal. It has never been instant, but the current delay is a huge constraint to being able to test and validate changes.

1 ACCEPTED SOLUTION

Microsoft support told me that it is a known issue and is in their internal issues tracker. However, it will not be added to the public Fabric Known Issues page. There is currently no timeline for a fix.

View solution in original post

13 REPLIES 13
Ascarba
New Member

Tenemos el mismo problema desde prinipios de año, no hemos obtenido respuestas al respecto ni planes de solución, hemos levantadio varios tickets. Este problema se nos presenta en Dataflows v2 y Notebooks que escriben datos en Lakehouse.

FabianSchut
Super User
Super User

I found this blog post that triggers the refresh of a lakehouse with a Python script. It works like a charm for me. I had the problem that the lakehouse had a lag of 30+ minutes. This Python script (which I used in a notebook) reduced it to 6 minutes and it waits until the sql endpoint is refreshed. So you are sure that other activities which are executed on completion of the Python notebook will have the refreshed sql endpoint.
https://www.obvience.com/blog/fix-sql-analytics-endpoint-sync-issues-in-microsoft-fabric-data-not-sh...

Anonymous
Not applicable

This is a known issue with the metadata sync process which results in stale data when queries through the sql endpoint. 

The fix was scheduled for July 31 but has unfortunately been pushed back to September. 

The workaround is to create a separate workspace and Lakehouse, shortcut your existing Lakehouse tables into the new Lakehouse and the data will be fresh. 

Thanks for the update and also providing a workaround!

 

Is this issue about the case where we do some changes to the table schema?

(E.g. add columns, remove columns, rename columns, etc.)

 

Or is it about regular update of the data in the table (with no alterations to columns)?

 

Or both scenarios?

Anonymous
Not applicable

There are a few reasons why you will get stale data when querying your sql endpoint. If you are loading data into a table via a notebook, the first query you run on the endpoint will freeze the data for all other queries until it completes. If the initial query is long running, you will get stale data presented by all subsequent queries. This is the fix Microsoft are implementing. 

We have also observed cases where data lags because the metadata sync job can be blocked by stats generation. 

HenrikS2
Frequent Visitor

Were you able to get this fixed? I can't get it to refresh at all in the past several hours.

JosueMolina
Helper III
Helper III

Here adding that I have experienced the same thing, where a new column or renaming has happened on the Lakehouse, the SQL will not reflect it immediately. Something that happened before with no issue. We are also unable to see accesses given to Lakehouses being reflected in the SQL endpoint for Report consumers

It's been about 4 hours and the SQL Endpoint is still not updated the lakehouse schema changes. Is this problem becoming worse? I remember it being about 10-30 minutes to refresh. 

Microsoft support told me that it is a known issue and is in their internal issues tracker. However, it will not be added to the public Fabric Known Issues page. There is currently no timeline for a fix.

Hello, I found a workaround that you might want to try (example code below the listing):

 

1. Take your Pandas df either by converting a spark one to Pandas (does not have to be loaded from SQL but could also be loaded via spark.read.csv('file') or spark.read.parquet('file') etc.

2. process you data as usual

3. Get list of columns and list of tuples of data. IMPORTANT: no special characters (even whitespaces) allowed in column names. you might need to rename them.

4. Do some spark stuff I do not know what it does, seems to generate a new spark like DataFrame.

5. Save you data. IMPORTANT: option('delta.columnMapping.mode','name') will create the table but it wont be accessible at the SQL endpoint.

6. If the writing fails, you might need to manually change the data type in the columns. This has to be done before step 3.

7. Refresh you lake/warehouse and use your tables :D.

 

 

# 1.
df = spark.sql("SELECT * FROM Lakehouse.SQLData").toPandas()

# 2.
test_df = ### processing(df)

# 3.
cols = list(test_df.columns.values) # gets list of columns
ncols = []
for c in cols:
ncols.append(re.sub(r'[-\%#\s\/]','_',c)

data = list(test_df.itertuples(index=False, name=None)) # gets data as list of tuples

# 4.
rdd = spark.sparkContext.parallelize(data)
n_df = rdd.toDF(cols)

# 5.
### Make sure that your column names do not include any special character, also not white space#
n_df.write.format("delta").saveAsTable('auto_test_3')

 

Anonymous
Not applicable

Hi @alozovoy ,

Thanks for using Fabric Community. 

While I wasn't able to replicate the long delay you're experiencing, here are two things you can try:

  • Give it some time: Sometimes things can be slow momentarily. Wait a few minutes and try querying again.
  • Refresh your connection: Closing and reopening the Fabric

If the issue persists after trying these suggestions, please let me know

The issue continues even after refreshing.

 

I have opened a Microsoft Support ticket #2402160010002646.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

JanFabricDE_carousel

Fabric Monthly Update - January 2025

Explore the power of Python Notebooks in Fabric!

JanFabricDW_carousel

Fabric Monthly Update - January 2025

Unlock the latest Fabric Data Warehouse upgrades!