- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @shashi_holla ,
That is an idea, I will look into this.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can create a deleted flag column using operation indicator in replicate. Check for soft delete whitepaper.