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 existing ones in one statement: INSERT … ON CONFLICT(customer_id) DO UPDATE SET … . Guard the update with WHERE excluded.updated_at > current_people.updated_at so stale records cannot overwrite newer data, and the same batch can be applied twice with the same result.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Compare incoming with current timestamps. Side-by-side updated_at values show new keys, stale replays, and rows that should win on conflict.the pattern

   example.sql Run

   sql›

   SELECT i.customer_id, i.updated_at AS incoming, c.updated_at AS current
   FROM incoming_people i
   LEFT JOIN current_people c ON c.customer_id = i.customer_id;

2. 2

   Count keys that would conflict. These incoming customer_ids already exist—rerunning a plain INSERT would fail without ON CONFLICT.

   step-2.sql Run

   sql›

   SELECT COUNT(*) AS would_conflict
   FROM incoming_people i
   JOIN current_people c ON c.customer_id = i.customer_id;

3. 3

   See which incoming rows are newer. The newer flag is the UPSERT guard: only when incoming updated_at beats current should contact_name and updated_at change.

   step-3.sql Run

   sql›

   SELECT
     i.customer_id,
     i.updated_at AS incoming,
     c.updated_at AS current,
     i.updated_at > c.updated_at AS newer
   FROM incoming_people i
   JOIN current_people c ON c.customer_id = i.customer_id;

common mistake

Without the timestamp guard, replaying an old record overwrites a newer value; without ON CONFLICT, rerunning the batch fails on duplicate keys.