Fabric SQL analytics endpoint shows stale or missing data: Lakehouse metadata sync delays, skipped tables and how to refresh

#Symptoms

• A notebook writes new rows to a Lakehouse Delta table. Spark sees them immediately; queries on the SQL analytics endpoint return the old row count for minutes.
• A new table is visible in the Lakehouse explorer but not in the SQL endpoint.
• Reports built on the endpoint show yesterday's numbers right after the nightly load finishes.

#How the SQL analytics endpoint works

The SQL analytics endpoint is a read-only SQL engine over the Lakehouse's Delta tables. A background metadata sync discovers Delta tables in the Tables area and updates the endpoint's metadata (schemas and the latest Delta version). Queries read the version the endpoint last synced.

So there are two possible delays:

1. New or changed tables must be discovered and their schema registered.
2. New data in known tables appears once the endpoint has picked up the latest Delta commit.

Most of the time this takes seconds to a few minutes; with many tables, many files, or heavy write activity, it can take longer.

#Step 1: compare Spark and SQL

In a notebook:

spark.sql("DESCRIBE HISTORY sales.orders").select("version", "timestamp", "operation").show(5, False)
spark.table("sales.orders").count()

On the SQL analytics endpoint:

SELECT COUNT(*) FROM dbo.orders;

If Spark is ahead, the endpoint hasn't synced the latest version yet.

#Step 2: is the table being skipped?

Tables the sync can't handle don't appear at all, or stop updating. Common reasons (check the current "SQL analytics endpoint limitations" page):

The Lakehouse explorer and the endpoint may show a warning icon or error for tables that failed to sync; check them first.

#Step 3: trigger a refresh

You can refresh the endpoint's metadata manually from the SQL analytics endpoint in the Fabric portal (refresh button in the explorer).

Fabric also provides a REST API to refresh SQL endpoint metadata on demand, which you can call at the end of a pipeline. The operation is long-running and returns per-table sync status. Check the current Fabric REST API reference for the exact path and whether it's GA or preview in your tenant:

# Inside a Fabric notebook, after the load
import sempy.fabric as fabric
client = fabric.FabricRestClient()
workspace_id = fabric.get_workspace_id()
sql_endpoint_id = "<sql-endpoint-item-id>"
resp = client.post(f"/v1/workspaces/{workspace_id}/sqlEndpoints/{sql_endpoint_id}/refreshMetadata", json={})
print(resp.status_code, resp.text[:500])

Treat the call as part of the load: load → refresh endpoint metadata → refresh the Power BI semantic model.

#Step 4: reduce sync time

• Fewer, bigger files: run OPTIMIZE on tables written by frequent small appends; small-file tables take longer to sync and query.
• Fewer tables: thousands of tiny staging tables in the same Lakehouse slow down discovery. Keep staging in a separate Lakehouse.
• Avoid constant rewrites: overwriting entire tables every few minutes creates a stream of versions to sync. Use MERGE or appends where possible.
• Keep checkpoints healthy: very long Delta logs without recent checkpoints take longer to read.

spark.sql("OPTIMIZE sales.orders")
spark.sql("VACUUM sales.orders RETAIN 168 HOURS")

Warning

VACUUM with a short retention can delete files that a lagging reader (including the endpoint, or time travel queries) still needs. Keep the default retention unless you have a specific reason.

#Step 5: Power BI on top

• Direct Lake semantic models read Delta tables directly and have their own framing/refresh behaviour; they can show new data before or after the SQL endpoint depending on settings.
• Import models on the SQL endpoint only see what the endpoint has synced at refresh time. Schedule semantic model refresh after the endpoint refresh in Step 3, not at a fixed clock time.

#Decision table

#Prevent it next time

• End every load pipeline with an explicit endpoint metadata refresh and a row-count check over SQL.
• Keep staging and curated tables in separate Lakehouses.
• Compact small files on frequently appended tables.
• Monitor the Fabric capacity: a throttled capacity slows background operations including sync.