topic Re: updating records using copy job? in Data Engineering

updating records using copy job?

Jerome22 — Fri, 27 Jun 2025 02:02:17 GMT

Hi,

when Data copy will support updates and not just adding rows?

I have a lot of tables which can have updated records, so I want to update my destination not just adding new rows.

thanks.

Re: updating records using copy job?

v-sdhruv — Fri, 27 Jun 2025 10:43:28 GMT

Hi @Jerome22 ,

Unfortunately, in Microsoft Fabric, the Copy Data activity does support an "Upsert" behavior. You can however use workarounds like Dataflows or SQL logic.
You can refer-
upsert option missing in copy activity of microsoft fabric data pipeline

Hope this helps!
If the response has addressed your query, please accept it as a solution so other members can easily find it.
Thank You

Re: updating records using copy job?

v-sdhruv — Tue, 01 Jul 2025 09:17:39 GMT

Hi @Jerome22 ,
Hope you were able to get some clarity on the issue. Let us know if need any help.
If the response has addressed your query, kindly mark it as accepted solution so that the other members can find it easily.
Thank you

Re: updating records using copy job?

smeetsh — Fri, 04 Jul 2025 01:41:52 GMT

There is no option for warehouse for this yet, I did see an upsert (preview) button appear on a lakehouse though. The best way for now is to do it using SQL code, whwere you check for an existing record, which you than update (UPDATE TABLE, set column = etc etc ) and when a record does not exist you will do an insert

Re: updating records using copy job?

v-sdhruv — Tue, 08 Jul 2025 07:10:06 GMT

Hi @Jerome22 ,
Hope you were able to get some clarity on the issue.
If any of the responses has addressed your query, kindly mark it as accepted solution so that the other members can find it easily.
Thank you

Re: updating records using copy job?

Srisakthi — Tue, 08 Jul 2025 07:13:01 GMT

Hi @Jerome22 ,

Copy Job now supports Upsert!!

There is an update please check this

https://blog.fabric.microsoft.com/en-in/blog/simplifying-data-ingestion-with-copy-job-incremental-copy-ga-lakehouse-upserts-and-new-connectors?ft=All

Regards,

Srisakthi

Re: updating records using copy job?

BhaveshPatel — Tue, 08 Jul 2025 08:42:16 GMT

Copy jobs in Data Factory means there is no need for updates. Overwrite where ever possible ( Data Lake and Data Lakehouse ) ( Apache Spark and Delta Lake ) or ( Merge in Microsoft Fabric or

Incremental Refresh with below code ( Append in Microsoft Fabric )

### Variables

LakehouseName = "Demo"

TableName = "DimTables"

ColName = "Surrogate_Key_Demo"

NumberOfRowsToRemove = "10"

### Remove Old rows

Reload = spark.sql("SELECT Max({0})-{1} as ReLoadValue FROM {2}.{3}".format(ColName,NumberOfRowsToRemove,LakehouseName,TableName)).collect()

Reload = Reload[0].ReLoadValue

display(Reload)

## ColName should be integer at all times to work (Incremental Refresh)

spark.sql("Delete from {0}.{1} where {2} > {3}".format(LakehouseName, TableName, ColName, Reload))

so it means you should always use Notebooks. ( Concept of Data Lake and Data Lakehouse)

Re: updating records using copy job?

Jerome22 — Tue, 08 Jul 2025 15:16:59 GMT

ok... unfortunately it's not supporting deletes.

and the update appear to update all the rows matching the ID without option to select a hash or timestamp column.

well... I'll still use SQL statements for now. still faster with more control than relying on external tools like the copy job.

Re: updating records using copy job?

smeetsh — Tue, 08 Jul 2025 22:37:57 GMT

What you could do is incorporate a where statement in the update statement, where you do something like

"WHERE hash in (select hash from TABLE) AND time_stamp_column > [your timestamp]

The above code is rough and probaly has some syntax errors, but I hope you understand the geste of the logic behind it 🙂

Cheers

Hans

Re: updating records using copy job?

smeetsh — Tue, 08 Jul 2025 22:41:39 GMT

We often do a clear out of tables or certain rows in tables, but we have a few datasets that are so large that is slows things down.
This can de done with just pipeline activities (copy and script activity), notebooks are not always needed. I would only use a notebook when i need to do something with a lakehouse.

Re: updating records using copy job?

BhaveshPatel — Wed, 09 Jul 2025 07:34:32 GMT

@smeetsh We always have Notebooks. No issues. Instead of Data Factory, Use Notebooks wherever we can. Databricks has implemented Delta Lake and then Microsoft follows Databricks. ( Delta Lake is based on Python and Scala ).

Re: updating records using copy job?

smeetsh — Tue, 22 Jul 2025 20:42:46 GMT

I could be wrong here, but I don't think a notebook can write to a warehouse, which for us at least would be the issue and it may be as well for the topic starter