lesson 85 Warehouse & reporting projects · advanced

Idempotent incremental UPSERT & stale updates

Repeat incremental updates safely and reject stale changes.

the idea

An incremental load inserts new keys and updates only newer source versions. Replay the same batch and the final rows must stay the same. A timestamp watermark alone can miss late records or equal timestamps; real pipelines need a defined tie-breaker/replay window, delete handling, and transactional checkpointing.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

INSERT INTO current_people(customer_id,name,updated_at)
SELECT customer_id,name,updated_at
FROM incoming_people
WHERE 1 ON CONFLICT(customer_id) DO
UPDATE
SET name=excluded.name,updated_at=excluded.updated_at
WHERE excluded.updated_at>current_people.updated_at;
INSERT INTO current_people(customer_id,name,updated_at)
SELECT customer_id,name,updated_at
FROM incoming_people
WHERE 1 ON CONFLICT(customer_id) DO
UPDATE
SET name=excluded.name,updated_at=excluded.updated_at
WHERE excluded.updated_at>current_people.updated_at;
SELECT customer_id,name,updated_at
FROM current_people;

Watch for: Do not use INSERT OR REPLACE as a synonym: replacement can delete/reinsert and break keys. This demonstration is not CDC or orchestration.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Understand the source contract. Fictional extracts, load counts, invalid dates, versioned dimensions, refresh staging and a data catalog. The foundation project is latest-state; separate history and incremental labs demonstrate extensions.

   step-1.sql Run

   sql›

   SELECT *
   FROM incoming_people;

2. 2

   Execute the transformation. An incremental load inserts new keys and updates only newer source versions. Replay the same batch and the final rows must stay the same. A timestamp watermark alone can miss late records or equal timestamps; real pipelines need a defined tie-breaker/replay window, delete handling, and transactional checkpointing.

   step-2.sql Run

   sql›

   INSERT INTO current_people(customer_id,name,updated_at)
   SELECT customer_id,name,updated_at
   FROM incoming_people
   WHERE 1 ON CONFLICT(customer_id) DO
   UPDATE
   SET name=excluded.name,updated_at=excluded.updated_at
   WHERE excluded.updated_at>current_people.updated_at;
   INSERT INTO current_people(customer_id,name,updated_at)
   SELECT customer_id,name,updated_at
   FROM incoming_people
   WHERE 1 ON CONFLICT(customer_id) DO
   UPDATE
   SET name=excluded.name,updated_at=excluded.updated_at
   WHERE excluded.updated_at>current_people.updated_at;
   SELECT customer_id,name,updated_at
   FROM current_people;

3. 3

   Test another question. Do not use INSERT OR REPLACE as a synonym: replacement can delete/reinsert and break keys. This demonstration is not CDC or orchestration.

   step-3.sql Run

   sql›

   INSERT INTO current_people(customer_id,name,updated_at)
   SELECT customer_id,name,updated_at
   FROM incoming_people
   WHERE 1 ON CONFLICT(customer_id) DO
   UPDATE
   SET name=excluded.name,updated_at=excluded.updated_at
   WHERE excluded.updated_at>current_people.updated_at;
   SELECT customer_id,customer_key
   FROM current_people