title: Performing a delete in an ADF mapping data flow - Updated with potential solution
body: <p>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.</p>
  <p>If a Sha2 hash does not match then upsert.
  if CustomerID is missing from the rawSource data, then delete (see image below for settings)</p>
  <p><a href="https://i.stack.imgur.com/SU98f.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SU98f.png" alt="enter image description here" /></a></p>
  <p>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.</p>
  <p>Is there a prescribed design pattern for this scenario?</p>
  <p>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.</p>
  <p><strong>Update</strong>:
  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)</p>
  <p>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.</p>
  <p>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.</p>