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
Watermark is the name of the highwater column
key column marked as the Key+ column.For upsert processing the column(s) marked as Key+ function as a logical unique key.
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 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)
Target Table in Database
Included for those interested. Very few users need to know the internals of the database tables storing the data warehouse.