handle deletes from Replicate full load task (with do nothing option selected)

[bilzekek]

Hi,

In the following scenario, how would you handle deletes in a DWH setup with Compose;

- Data is moved to DWH landing area by a Replicate Full load only task, that has task setting  "if target table already exists: Do nothing" selected.  The reason for this, is that  we want to keep history of table development, and for our Replicate target (Azure Synapse) , the "archive and create table" option is not supported

I was looking at the White papir "Incremental Load Patterns for NonReplicate Data Sources" and are considering the Process Flag option. But how will Compose know if a row is suddenly removed from the source?

For example, If I load data on day 1 and lets say 10 rows are added. On day 2, the source table has changed and only contains 9 rows (same keys as on day 2, but 1 row on from day 1 is now missing/deleted) 

[shashi_holla · Accepted solution]

Hi @bilzekek 

One option I can think of is having a Post Load ETL script to compare the key columns between the source and target and match the data during each run. It's a crude way of handling it but I'll check more and see if there are better options.

Thanks,

[bilzekek]

Hi @shashi_holla ,

That is an idea, I will look into this. 

[aj96]

You can create a deleted flag column using operation indicator in replicate. Check for soft delete whitepaper.