topic Re: I want to know how to update and insert data without changing or deleting the Guid values. in Pipelines

I want to know how to update and insert data without changing or deleting the Guid values.

minseok_kr — Wed, 31 Jul 2024 01:03:08 GMT

There is customer data in Azure SQL DB. Each customer stored here has a Guid value. I want to incrementally load this customer data into a Lakehouse.
If the customer data is modified, I want to keep the Guid value and only update the changed information. If new customer data is created, I want to simply insert it.
I want to configure this using all the resources of Fabric without using Query statements.
Is there a method that can be used universally in various projects?

Re: I want to know how to update and insert data without changing or deleting the Guid values.

NandanHegde — Wed, 31 Jul 2024 03:31:55 GMT

unfortunately UPSERT option is not available in Copy / Dataflow gen2 with lakehouse as a sink.

you would have write some custom logic to handle the same , Below blog provides some details :

https://www.linkedin.com/pulse/implementing-incremental-load-lakehouse-table-insert-update-malshan-bp2bc

Re: I want to know how to update and insert data without changing or deleting the Guid values.

smpa01 — Sat, 03 Aug 2024 04:57:47 GMT

If you are building a managed delta table in a lakehouse to bring the data from Sql, you can use Delta Table API in notebook to perform a fully qualified UPSERT using merge between source and target delta table.

from delta.tables import *

deltaTablePeople = DeltaTable.forPath(spark, '/tmp/delta/people-10m')
deltaTablePeopleUpdates = DeltaTable.forPath(spark, '/tmp/delta/people-10m-updates')

dfUpdates = deltaTablePeopleUpdates.toDF()

deltaTablePeople.alias('people') \
  .merge(
    dfUpdates.alias('updates'),
    'people.id = updates.id'
  ) \
  .whenMatchedUpdate(set =
    {
      "id": "updates.id",
      "firstName": "updates.firstName",
      "middleName": "updates.middleName",
      "lastName": "updates.lastName",
      "gender": "updates.gender",
      "birthDate": "updates.birthDate",
      "ssn": "updates.ssn",
      "salary": "updates.salary"
    }
  ) \
  .whenNotMatchedInsert(values =
    {
      "id": "updates.id",
      "firstName": "updates.firstName",
      "middleName": "updates.middleName",
      "lastName": "updates.lastName",
      "gender": "updates.gender",
      "birthDate": "updates.birthDate",
      "ssn": "updates.ssn",
      "salary": "updates.salary"
    }
  ) \
  .execute()