Incremental updates with overlap (delete last N days of records and load "NOW- M days" from source)

[__Vladimir__]

Hi,

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)

Thank you

[#2]

@__Vladimir__ 

Hello,

to remove n days from destination before inserting records in field "pre-copy script" meter code similar to this:

declare @date date
declare @lastNdays int
set @lastNdays = -3
set @date = dateadd(d,@lastNdays, (select top 1 [LastDay] from [dbo].[table] order by [LastDay] desc))
delete from [dbo].[table] where [LastDay] >=@date