ETL+ Load Upsert Example 1 With 'Force Replace with Upsert'
Example of loading a loading table regularly serviced with Upsert with Load Replace. example. Shows how the Load Replace processes deleted source records.
Work in progress
Load Upsert does not delete records. Load Replace does. This examples shows the results of a Upsert followed by a Replace. This example follows the Upsert example at https://dataself.atlassian.net/wiki/spaces/DS/pages/2085257217 .
NOTE: The Load type on the Job page supports periodically running Load Replace and Load All on tables. The support for Replace is the Force Replace with Upsert, default for others option on Load type.
Most Recent Load Upsert
Continued from https://dataself.atlassian.net/wiki/spaces/DS/pages/2085257217
Target Table Before Upsert
Description | Key | Watermark |
|---|---|---|
row1 | 1 | 95 |
row2 | 2 | 90 |
row3 | 3 | 91 |
row4 | 4 | 93 |
Highwater mark = 93
Source Data
Description | Key | Watermark |
|---|---|---|
row1 | 1 | 95 |
row3 | 3 | 91 |
row4 | 4 | 93 |
NOTES
Watermark on
row1> Highwater markWatermark on
row4= Highwater markrow2Deleted
Target Table After Upsert
Description | Key | Watermark |
|---|---|---|
row1 | 1 | 95 |
row2 | 2 | 90 |
row3 | 3 | 91 |
row4 | 4 | 93 |
ETL Log: (2 rows upserted)
Periodically Refresh with Load Replace
The Force Replace with Upsert option on the Job Page supports periodically loading the Load Upsert table with Load Replace.
Target Table Before Replace
Description | Key | Watermark |
|---|---|---|
row1 | 1 | 95 |
row2 | 2 | 90 |
row3 | 3 | 91 |
row4 | 4 | 93 |
ETL Log: (2 rows upserted)
Source Data
Same data loaded with the most recent Upsert example above.
Description | Key | Watermark |
|---|---|---|
row1 | 1 | 95 |
row3 | 3 | 91 |
row4 | 4 | 93 |
NOTES
row2recently deleted from the source.
Target Table After Replace
Description | Key | Watermark |
|---|---|---|
row1 | 1 | 95 |
row3 | 3 | 91 |
row4 | 4 | 93 |
ETL Log: (3 rows replaced)
Screenshots
Rough examples work in progress
