Skip to main content
Announcements
Global Transformation Awards submissions are open! SUBMIT YOUR STORY
cancel
Showing results for 
Search instead for 
Did you mean: 
bilzekek
Contributor III

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

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) 

Labels (1)
1 Solution

Accepted Solutions
shashi_holla
Support

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,

View solution in original post

3 Replies
shashi_holla
Support

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
Contributor III
Author

Hi @shashi_holla ,

That is an idea, I will look into this. 

aj96
Contributor III

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