I am looking into the possibilities of replacing our current legacy ETL setup with Azure dedicated pool and Data Factory.
The source will be PostgreSQL, the destination should be Azure dedicated pool.
One thing I need help with is whether it is possible and how to perform the following:
an "upsert" operation, or in more simple terms, identify the last day of data in the destination (denote here as LastDay) remove last N days of records (the last few days could be stale) from the ETL destination (Azure pool), then query the source for the new data (i.e. newer than "LastDay - N days") and update the records in the destination
(I have found this scenario: https://docs.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-multiple-tables-powers... regarding incremental update - however, I need that overlap (removal of last N days of possibly stale records)