Immense SQL endpoint lag with external data share

Hi NotebookEnjoyer​,

I would separate the freshness of the external share from the freshness of the SQL analytics endpoint here.

Microsoft's external data sharing documentation describes the consumer shortcut as live, in-place access, so changes in Tenant A should be available in Tenant B without an ETL/copy step.

The Lakehouse SQL analytics endpoint has a separate metadata/data synchronization layer though. Microsoft's SQL endpoint performance guidance says that under normal conditions the lag between a Lakehouse and its SQL endpoint should be less than one minute. So multiple hours, and especially days, is definitely not something I would consider normal shortcut latency.

Since you already see the new data in the Lakehouse within a minute, that seems to isolate the delay quite well to the SQL endpoint side.

Besides total data size, I would check the number of Lakehouses in that workspace and the Delta file profile. Microsoft notes that many Lakehouses in one workspace, large numbers of small Parquet files, and heavy Delta change activity can increase metadata-sync latency.

For another diagnostic, the SQL endpoint metadata refresh REST API can refresh specific tables rather than the entire endpoint. If only a few tables feed the DBT job, I would test a targeted refresh and compare the timing with the full Sync Metadata operation.

There is also a new metadata sync architecture currently in preview which Microsoft says can make Lakehouse changes available through SQL within seconds. One important limitation is that it only applies to new SQL endpoints created after enabling the workspace setting, so I would use a new test Lakehouse/endpoint to compare behaviour rather than changing the production design immediately.

If a fresh test endpoint against the same shared data still takes hours to synchronize, I agree with tayloramy​ that this deserves a Microsoft support case. I would include the Lakehouse/SQL endpoint IDs, affected table names, timestamp when the data became visible through the shortcut, and timestamp when it finally became queryable through SQL.