Skip to main content
Skip table of contents

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

Load Upsert panel.
Watermark is the name of the highwater column

image-20240923-211036.png

Design page. 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

  • row2 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.

from SSMS

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.