lesson 83 Warehouse & reporting projects · advanced

Durable surrogate keys across loads

Keep generated keys stable across repeated loads.

the idea

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.

tables in this lesson

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

current_people4 columns · 2 rows

customer_keyinteger · key	customer_idtext	nametext	updated_attext
1	C001	Alice Nguyen	2025-01-05
2	C002	Ben	2025-01-02

all 2 rows

example

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

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;

Watch for: SQLite AUTOINCREMENT is a specific allocation choice, not a universal performance recommendation. SQL Server IDENTITY/SEQUENCE are different syntax.

build it step by step

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

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
sql›
```
SELECT *
FROM current_people;
```
2
Execute the transformation. 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.
step-2.sql
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;
```
3
Test another question. SQLite AUTOINCREMENT is a specific allocation choice, not a universal performance recommendation. SQL Server IDENTITY/SEQUENCE are different syntax.
step-3.sql
sql›
```
UPDATE current_people
SET name='Alice Updated'
WHERE customer_id='C001';
SELECT customer_id,customer_key,name
FROM current_people
```