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 mark -
Watermark on
row4= Highwater mark -
row2Deleted
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.
