I am trying to do some data transformations on a dataset in Data Factory. I wanted to delete a set of rows based on certain conditions. This is the data flow so far:
So in AlterRow1 I deleted the rows I wanted, and this is the result when I click on data preview:
As you can see, 6 rows get deleted, exactly what I wanted. However, in sink1 this is the data preview I'm getting:
The rows I wanted to delete are back and won't get deleted when I run this pipeline. I'll add that the source is an excel file from the blob storage and sink is a csv file in my blob storage.
What am I doing wrong?
EDIT: There are no settings in the sink to allow deletion.
CodePudding user response:
Although you seem to be able to get the preview, Alter row transformation can result in a row (or rows) being inserted, updated, deleted, or upserted (DDL & DML actions) against your database only.
See, Alter row transformation in mapping data flow
I did try to repro your exact scenario and I do see the same behavior. I can see in AlterRow transformation's Data review the rows marked X
to be deleted. But the sink preview doesn't show them and all the rows from source are seen.
I could not find any particular details as to this behavior, you can reach out here and here for official response.