topic Re: refresh online table: How to get update_id and check status of a specific update in Data Engineering

refresh online table: How to get update_id and check status of a specific update

lauraxyz — Wed, 18 Dec 2024 23:50:00 GMT

Hi!

I have a workflow job to trigger a refresh of an online table. How can I get the update_id with this specific refresh?

Also, is it possible to get the status from this specific update_id?

Thanks!

Re: refresh online table: How to get update_id and check status of a specific update

Walter_C — Thu, 19 Dec 2024 00:51:38 GMT

To get the update_id for a specific refresh of an online table and retrieve its status, you can follow these steps:

Obtaining the update_id

When you trigger a refresh of an online table through a workflow job, you can typically access the update_id in one of two ways:

Workflow output: Many workflow systems provide a way to capture the output or return value of the refresh operation. Check your workflow execution logs or output variables for the update_id.
Query the event log: If the workflow doesn't directly provide the update_id, you can query the event log to find the most recent update for your table.

SELECT origin.update_id AS id
FROM event_log_raw
WHERE event_type = 'create_update'
ORDER BY timestamp DESC
LIMIT 1;

This query will return the update_id of the most recent update operation

Retrieving the status

Once you have the update_id, you can query the status of that specific update:

SELECT status
FROM event_log_raw
WHERE event_type = 'update_progress'
AND origin.update_id = '<your_update_id>'
ORDER BY timestamp DESC
LIMIT 1;

Replace <your_update_id> with the actual update_id you obtained

Re: refresh online table: How to get update_id and check status of a specific update

lauraxyz — Thu, 19 Dec 2024 23:11:53 GMT

Thanks!

Yes i'm able to get update_id. For getting status, is there a way to get it with Databricks SDK, something like `w.refresh_monitors.get_refresh({update_id})`?

Re: refresh online table: How to get update_id and check status of a specific update

lauraxyz — Thu, 19 Dec 2024 23:20:21 GMT

Another qq: Since online table has 3 sync mode: Snapshot, Triggered, and Continuous.

when refreshing the online table with

w.pipelines.start_update(pipeline_id='{pipeline_id}', full_refresh=True)

which sync mode is used by default?

Re: refresh online table: How to get update_id and check status of a specific update

RiyazAliM — Mon, 23 Dec 2024 09:45:14 GMT

Hi @lauraxyz

Given you have your update_id, we can retrieve the status of the DLT pipeline using the below class from Databricks SDK.

Cheers!

Re: refresh online table: How to get update_id and check status of a specific update

RiyazAliM — Mon, 23 Dec 2024 09:58:58 GMT

Hi @lauraxyz, the class pipelines.StartUpdate, doesn't take any argument to specify mode. I'm assuming that it will trigger in the same mode in which the pipeline is created. Kindly let me know if you find anything to contradict my understanding.

Re: refresh online table: How to get update_id and check status of a specific update

lauraxyz — Thu, 26 Dec 2024 18:36:31 GMT

Thanks Riz! I think you are right, the sync mode was already specified when online table was created.