runtime
CRM + ERP Integration · milestone 07 / 12

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.

worked-example.sql
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

current_people4 columns · 2 rows
customer_keyinteger · keycustomer_idtextnametextupdated_attext
1C001Alice Nguyen2025-01-05
2C002Ben2025-01-02

all 2 rows

challenge 1

Insert natural key C000, name New earlier key, updated_at 2025-02-01 into current_people without supplying customer_key. Return customer_id,customer_key for all rows.

Return customer_idcustomer_keyany row ordertable changes are checked too

challenge-1.sql
 
3 steps left · check both queries and pass the concept checkNext milestone
Durable surrogate keys across loads — CRM + ERP Integration · Runtime