topic Re: Incremental refresh og selected refresh using data pipeline in Pipelines

Incremental refresh og selected refresh using data pipeline

pbi_taken — Mon, 19 Aug 2024 06:18:57 GMT

Hi,

Wondering if anyone has done this successfully?

I'm using the copy activity to get data from my on-prem sql server. I would like to not overwrite my entire table on each refresh.

I each row has a "UpdatedDate" and I would like to use this to check if it's greater than previous and then refresh, otherwise keep as is and shorten the refresh time.

Does anyone know if this is possible?

Thanks!

Re: Incremental refresh og selected refresh using data pipeline

rubayatyasmin — Mon, 19 Aug 2024 10:38:53 GMT

Hi, pbi_taken

You can use this upsert option to get your desired result.

Re: Incremental refresh og selected refresh using data pipeline

pbi_taken — Tue, 20 Aug 2024 06:44:42 GMT

Hi rubayatyasmin ,

Thanks! Maybe my question was a little misleading. My destination is a Lakehouse and with that it does not give me the option of upserting.

Could an expression builder solve this, and if so, I do not know..

Re: Incremental refresh og selected refresh using data pipeline

frithjof_v — Tue, 20 Aug 2024 07:19:24 GMT

Are you looking to append the updated rows, or are you looking to do upsert (insert new rows and update existing rows with changes)?

If you want to do an upsert, I think you will need to include a Notebook to do the upsert for a Lakehouse destination.

Re: Incremental refresh og selected refresh using data pipeline

pbi_taken — Tue, 20 Aug 2024 07:19:12 GMT

looking to upsert.. append is an option, but not useable in my situation

Re: Incremental refresh og selected refresh using data pipeline

frithjof_v — Tue, 20 Aug 2024 07:38:01 GMT

I think you will need to use Notebook to upsert into Lakehouse.

There is an example of upsert here:

https://microsoftlearning.github.io/mslearn-fabric/Instructions/Labs/03b-medallion-lakehouse.html

There is an overview of some functions here:

https://docs.delta.io/latest/delta-update.html

Re: Incremental refresh og selected refresh using data pipeline

frithjof_v — Tue, 20 Aug 2024 07:28:06 GMT

Please vote for this idea:

Support UPSERTs and DELETEs when copying data into Lakehouse Tables from Pipeline copy activity, as opposed to Appending new rows

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=87f3d168-6022-ee11-a81c-6045bdc01ce4

On the Ideas site, it says this is under planning, but it has been under planning for a year already. Your vote can highlight the urgency for getting this much-needed feature.

Re: Incremental refresh og selected refresh using data pipeline

mikeburek — Tue, 20 Aug 2024 14:58:22 GMT

There is not currently a way to do this with only the Copy Data activity.

If you want to use auxiliary/raw/landing tables, you cannot update a Lakehouse table with the SQL Endpoint. You can with a Notebook.

If you want to write with SQL, you can use auxiliary/raw/landing tables in a Warehouse and make a shortcut inside the Lakehouse pointing to the Warehouse table.