Fabric Lakehouse SQL Endpoint Automatic Refresh

[Bill_J99294]

Hello, 

Recently we have noticed that the automatic metadata discovery for our lakehouse has not been working. This has resulted in us manually refreshing the SQL endpoint for our Lakehouse each morning to ensure that the tables are up-to-date. I have been unable to find a method to do this automatically and wonder if anyone has a method of forcing this automatic metadata discovery or a method to automatically trigger the on-demand refresh button. 

Below is a snip from the SQL Analytics End Point Performance Considerations that I have referenced, we have completed the necessary maintinance and it would be an extreme lift to move these lakehouses to individual workspaces. 

 Guidance

• Automatic metadata discovery tracks changes committed to lakehouses, and is a single instance per Fabric workspace. If you are observing increased latency for changes to sync between lakehouses and SQL analytics endpoint, it could be due to large number of lakehouses in one workspace. In such a scenario, consider migrating each lakehouse to a separate workspace as this allows automatic metadata discovery to scale.
• Parquet files are immutable by design. When there's an update or a delete operation, a Delta table will add new parquet files with the changeset, increasing the number of files over time, depending on frequency of updates and deletes. If there's no maintenance scheduled, eventually, this pattern creates a read overhead and this impacts time it takes to sync changes to SQL analytics endpoint. To address this, schedule regular lakehouse table maintenance operations.
• In some scenarios, you might observe that changes committed to a lakehouse are not visible in the associated SQL analytics endpoint. For example, you might have created a new table in lakehouse, but it's not listed in the SQL analytics endpoint. Or, you might have committed a large number of rows to a table in a lakehouse but this data is not visible in SQL analytics endpoint. We recommend initiating an on-demand metadata sync, triggered from the SQL query editor Refresh ribbon option. This option forces an on-demand metadata sync, rather than waiting on the background metadata sync to finish.

https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-analytics-endpoint-performance#guidance

[amokhalgay]

@Anonymous Can we refresh the whole Lakehouse SQL Endpoint using the spark notebook, as the command it looks like it is just going to refresh the table? 

Our customers are facing issue in which when we upload the Delta File in Lakehouse after that table is not visible even after waiting for 5 mins. So is there a way we can do via notebook, please suggest.

[jankuc]

When we discussed this with msft, they said we should run a dummy query (e.g. SELECT count(*) ...) and then wait a bit (they didn't say how long but try to experiment with 30, 60 seconds). This should start the serverless sql endpoint and after that run the automatic refresh. 
We've also tried to call the undocummented API, which was working for us. https://gist.github.com/MarkPryceMaherMSFT/853cdc0d9d421482814b8195aba55434

[Anonymous]

Hi @Bill_J99294 ,

Thanks for the reply from R1k91 .

Is my follow-up just to ask if the problem has been solved?

If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

Thank you very much for your cooperation!

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[R1k91]

they're talking about this also here: 
https://www.reddit.com/r/MicrosoftFabric/comments/1ercfha/sql_analytics_endpoint_performance/

[Anonymous · Accepted solution]

Hi @Bill_J99294 ,

You can create a notebook that connects to the lakehouse corresponding to your SQL endpoint by entering the following command in the cell:

from pyspark.sql import SparkSession

# create Spark session
spark = SparkSession.builder \
    .appName(“Refresh SQL Endpoint Metadata”) \
    .getOrCreate()

# refresh
spark.sql(“REFRESH TABLE salesorders”)

print(“Metadata refresh triggered successfully.”)

Set up a daily refresh in your notebook settings so that this refresh command is executed every day.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[ReneT]

Hi,

I have a lakehouse using schema shortcuts and I want to update the whole lakehouse which essentially contains multiple schemas. Is there a command to refresh by schema or the entire lakeouse as per the manual Metadata Sync button?

Regards