CODEWITHSUNDEEP
azure-data-factoryazure-synapse
wilson_smyth
wilson_smyth

Reputation: 1516

Performing a delete in an ADF mapping data flow - Updated with potential solution

I am trying to do upsert and delete in a mapping data flow. There is a dimension table, DimCustomer. It is being populated with data from a file.

If a Sha2 hash does not match then upsert. if CustomerID is missing from the rawSource data, then delete (see image below for settings)

enter image description here

The upsert works, but the delete does not. Its likely because in the sink i have selected the customerID column as the key, but this means it can never delete a record if the entire record, including key is missing from source.

Is there a prescribed design pattern for this scenario?

The easiest solution i can think of is a 2nd dataflow, in which the only customerID's sent to the sink are ones where there is no matching customerID in the source (effectively a right outer join), but want to see if this is indeed the best way to do this.

Update: The best solution i can come up with for this is, to the above dataflow, add an additional column, the formula for which is coalesce(RawCustomerData@CustomerID,DimCustomer@CustomerID)

This ensures there is a CustID column that always has a value. In the sink, i change the mapping so that this custID maps to the sink CustomerID.

The delete now works as expected. Still unsure if this is the best solution but it works and doesn't appear to cause a major performance issue.

Upvotes: 1

Views: 2170

Answers (1)

Leon Yue
Leon Yue

Reputation: 16431

Per my experience, I think that's the best solution, add a new column can solve the problem is much easier than other operations. The way which simplest and effective is the best solution. You don't need create another data flow actives to achieve it or re-design the Alter active logic.

Your Solution:

Add an additional column CustID, the formula for which is: coalesce(RawCustomerData@CustomerID,DimCustomer@CustomerID)

This ensures there is a CustID column that always has a value. In the sink, you change the mapping so that this custID maps to the sink CustomerID.

The delete now works as expected.

Upvotes: 0

Related Questions