SQL Endpoint lakehouse: Intermittent error in the Refresh SQL analytics endpoint activity

Hi smeetsh​,

On your continue-on-failure idea, one option that gives you a deterministic answer instead of guessing is to call the refresh API directly rather than through the pipeline activity, because the API returns a status per table.

POST https://api.fabric.microsoft.com/v1/workspaces/{workspaceId}
     /sqlEndpoints/{sqlEndpointId}/refreshMetadata

Two optional body parameters in the Refresh SQL Endpoint Metadata API help with your exact situation:

tables   -> refresh only the tables this pipeline wrote
            (up to 25 per call)
timeout  -> defaults to 15 minutes, can be raised

The response lists each table with a status (Success, Failure or NotRun) and its lastSuccessfulSyncDateTime.

A minimal notebook version, which you can call from the pipeline in place of the activity:

import time, requests

ws_id, ep_id = "<workspaceId>", "<sqlEndpointId>"
token = notebookutils.credentials.getToken("pbi")
h = {"Authorization": f"Bearer {token}"}
url = (f"https://api.fabric.microsoft.com/v1/workspaces/{ws_id}"
       f"/sqlEndpoints/{ep_id}/refreshMetadata")

body = {
    "tables": [{"schema": "dbo",
                "tableNames": ["trev_checklist_data"]}],
    "timeout": {"timeUnit": "Minutes", "value": 30},
}

r = requests.post(url, headers=h, json=body)
if r.status_code == 202:  # long-running operation
    op_url = r.headers["Location"]
    while True:
        time.sleep(int(r.headers.get("Retry-After", 15)))
        r = requests.get(op_url, headers=h)
        if r.json().get("status") in ("Succeeded", "Failed"):
            break
    r = requests.get(op_url + "/result", headers=h)

statuses = r.json()["value"]
failed = [t["tableName"] for t in statuses
          if t["status"] != "Success"]
notebookutils.notebook.exit(
    "OK" if not failed else "FAILED:" + ",".join(failed))

Then in the pipeline:

Notebook (scoped refresh)
-> If Condition on the exit value
-> OK      : continue to Copy data
-> FAILED  : Fail activity with the table names

That gives you the "continue anyway" behavior you described, but only when the tables you actually depend on have synced, rather than assuming they did because the activity ran for 12 minutes.

Scoping each call to a few tables also means a pipeline is no longer asking for a full endpoint sync while other pipelines are still writing to the Lakehouse, which should reduce the lock contention behind these timeouts.

So my overall approach would be:

scoped refresh per pipeline
-> per-table status check
-> continue only when the tables you need are synced
-> short retry with delay for genuine transient errors

That keeps your pipelines independent without giving up the guarantee that downstream steps read current data.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.