Currently, when Lakehouse SQL Endpoint is queried using an API, the query gets timed out due to lakehouse cold start. If the query has already run, cached data is available and subsequent users running the same query within the cache window (around 15 minutes) does not experience the timeout. If no recent execution has occurred and the system is in a cold state, the first execution can take around 30 seconds, which causes the downstream API layer to time out.
For Production workspaces, this can cause an issue.
To make things works smoothely, we have built an internal query pipeline to hit SQL Endpoint at scheduled times. to keep it warm. We are also checking Capacity Metrics App to identify non-peak hours to run our pipeline.
It would be easier if the underlying SQL Engine is kept up and warm using some internal Fabric capability instead of building a customised workaround.
