milestone 7/12 Durable surrogate keys across loads

Keep generated keys stable across repeated loads.

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

A warehouse key should remain stable when a new earlier-sorting natural key arrives. The lab current_people table assigns a key once via INTEGER PRIMARY KEY AUTOINCREMENT and retains it on update. ROW_NUMBER is an analytical rank, not a durable assignment mechanism. These keys persist within this script; the app resets afterward.

INSERT INTO current_people(customer_id,name,updated_at) VALUES('C000','New earlier key','2025-02-01');
SELECT customer_id,customer_key
FROM current_people;

data for this milestone