milestone 9/12 Idempotent incremental UPSERT & stale updates

Repeat incremental updates safely and reject stale changes.

CRM + ERP Integration · Follow extraction reconciliation, authoritative integration, date quarantine, quality gates, stable keys, replay-safe loads, and atomic promotion. Finish with a customer-grain capstone and field-level lineage.

approach

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.

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;

data for this milestone