ETL+ Load Upsert Example 2 -- Source without a PK
Upsert example of the less common case where there isn’t a unique key / PK (Primary Key) in the data source / source table. Results can be unexpected.
What this Example Illustrates
Upsert technically ‘works’ when the source table doesn’t have a unique key – columns designated by the Key+ checkboxes are unique values in the source table – but the results effectively render Upsert unusable in nearly all cases.
When using Upsert with source data from .CSV or Excel files for instance it’s up to the user to ensure that the data has a unique key, that is, the values in a column or columns are always unique in the file or table.
References to “unique keys” on the user interface refer only to the source table, not the target table.
For processing efficiency, indexes are created on the target table based on the settings of the Key+ checkboxes on the Design page.
In most cases the source table will have a unique key. This example is about a Upsert from a source table without a unique key.
ETL+ Configuration for this Example
Load panel from target table in screenshot above
Design page from target table above
Initial Load Upsert
Data Source
Target Table After 2nd Load Upsert
Data from ETL+ Preview page
Data from target table in data warehouse
ETL Log: (4 rows loaded)
2nd Load Upsert
Highwater mark determined from Sequence column in target table before Upsert is 99
Data Source
Target Table After 2nd Load Upsert
Changes.
Sequence column in last row changed to 100 since last Load.
Data from target table in data warehouse
Deletes all columns with Source_key =
1or2Then Inserts all rows with highwater mark >=
99.
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.
