Test delete on Incremental loading

Question

Hi,

I followed the incrementally loading data from ASQL to ADLS https://learn.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-portal?source=docs. When a record is deleted from a table in a Azure sql database, the deleted record is not copied to data storage. But the deletion should be updated in the sink. How can make the deleted record into the sink? Thanks!

Answer 1

Hey,

unfortunately w.r.t delta / incremental feeds , the deleted records at source cannot be synced in at the sink because of below aspects :

1. The source query itself wont be able to fetch the physically deleted record. And there might be other old records which have yet not been updated in source which might not be pulled in source query via watermark.
2. So the missing records in source query might be either due to a record being deleted or record not being updated post the prior load.
3. Hence , you cannot sync those aspects within the sink.

For those aspects, the records shouldnt be physically deleted from the source and must be logically deleted by some flagcolumn within source like IsDelete or IsActive , so that those column attribute would get synced to sink once the record is logically deleted