Simple Upsert example. No deleted source records, no changes to key.
ETL Configuration
Sequence is the highwater column
For upsert processing the column(s) marked as Key+ function as a logical unique key.
Initial Load Upsert
Data Source
Target Table After Initial Load Upsert
highwater mark in Sequence column after Load Upsert is
104
ETL Log: (4 rows loaded)
2nd Load Upsert
The first step of the upsert process extracts the highwater mark from the target table. In this example the highwater mark is before the from Sequence column in target table before Upsert is 104.
Before: The highwater mark is 104. (See Sequence column in Target Table after step above)
Data Source
Target Table After 2nd Upsert
Sequence = 105
new highwater mark is
105
ETL Log: (2 rows upserted)
Alternative View:
Target Table in Database
Included for those interested. Very few users need to know the internals of the database tables storing the data warehouse.
