Incremental refresh og selected refresh using data pipeline

[pbi_taken]

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!

[mikeburek]

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.

[frithjof_v]

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.

[frithjof_v]

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.

[pbi_taken]

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

[frithjof_v · Accepted solution]

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

[rubayatyasmin]

Hi, @pbi_taken 

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

[pbi_taken]

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..