topic Re: Coping up with SQL end point lag in Data Engineering

Coping up with SQL end point lag

smpa01 — Mon, 16 Sep 2024 14:42:14 GMT

How is every1 coping up with the SQL end point lag which is currently killing my prod element.

This is lakehouse snapshot through spark sql after I made a delta merge in the table (through notebook)

(with baked in v order in the same notebook)

and the same table with endpoint

The ingestion_run_at is not being updated at all in the sql endpoint.

My workflow involves running a notebook to update the lakehouse table, followed by triggering Power BI immediately after (for end-users to consume the report). However, there is clearly a lag, which makes the entire process unreliable.

If the lag is intentional by design, what is the average delay? Knowing this would allow me to programmatically introduce a delay before refreshing the semantic model. Also, if the lag is here to stay, what the author needs to do to ensure thre is no lag (partition etc if req).

The data from the lakehouse can be accessed using either the Sql.Database connector or the AzureStorage.DataLake connector. Which connector is NOT impacted by the lag? I prioritize accuracy over performance in this case. AzureStorage.DataLake is probably better from accuracy standpoint compared to Sql.Database connector. Please confirm.

The Sql.Database connector has the advantage of supporting fully qualified SQL queries, making it more desirable than the AzureStorage.DataLake connector (since writing Power Query is not ideal). Therefore, if the lag is known (point #2), I could introduce a fixed delay between the notebook execution and the semantic model refresh.

Is Microsoft aware of this delay? Are there any official documents that acknowledge this issue or provide a potential solution with a timeline?

At present, the SQL endpoint is unusable for production without this information.

Re: Coping up with SQL end point lag

Anonymous — Tue, 17 Sep 2024 06:29:44 GMT

Hi @smpa01 ,

Under normal circumstances, the lag time should be less than a minute, and you can refer to the following document for more information:

SQL analytics endpoint performance considerations - Microsoft Fabric | Microsoft Learn

There are several reasons why you get stale data when querying the sql endpoint. If you load data into the 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 runs for a long time, you will get stale data for all subsequent queries.

You can try the workaround mentioned here:

Solved: SQL Endpoint Slow To Reflect Changes In Lakehouse - Microsoft Fabric Community

Between the Sql.Database connector and the AzureStorage.DataLake connector, the latter is generally more accurate as it directly accesses the data in the lakehouse. However, the Sql.Database connector supports fully qualified SQL queries, which might be more convenient for your use case. If accuracy is your priority, AzureStorage.DataLake is the better choice.

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!

Re: Coping up with SQL end point lag

smpa01 — Tue, 17 Sep 2024 12:54:03 GMT

Can you please elaborate with example

"the first query you run on the endpoint will freeze the data for all other queries until it completes. If the initial query runs for a long time, you will get stale data for all subsequent queries"

Are you suggesting that, if different semantic models send out different queries to sql end point at the same time , they could be completely inaccurate due to the condition mentioned above?