ETL+ Load Upsert Example 1
Example of Upsert in action. A simple example that shows how Upsert works with a few rows and columns of data.
ETL Configuration
The source data has 3 fields – a key, a sequence (sequentially assigned
Initial Load Upsert
Data Source
Description | Key | Watermark |
---|---|---|
row1 | 1 | 90 |
row2 | 2 | 90 |
row3 | 3 | 91 |
row4 | 4 | 93 |
Target Table After Initial Load Upsert
Description | Key | Watermark |
---|---|---|
row1 | 1 | 90 |
row2 | 2 | 90 |
row3 | 3 | 91 |
row4 | 4 | 93 |
The highwater mark is now 93
ETL Log: (4 rows loaded)
2nd Load Upsert
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 markrow2
Deleted
Target Table After Upsert
Description | Key | Watermark |
---|---|---|
row1 | 1 | 95 |
row2 | 2 | 90 |
row3 | 3 | 91 |
row4 | 4 | 93 |
ETL Log: (2 rows upserted)
Target Table in Database
Included for those interested. Very few users need to know the internals of the database tables storing the data warehouse.