lesson 83 Warehouse & reporting projects · advanced

Durable surrogate keys across loads

Keep generated keys stable across repeated loads.

the idea

A surrogate key identifies a row inside the warehouse and must never change once assigned, even when the natural key’s attributes change. Let the database assign new keys (INTEGER PRIMARY KEY) on insert, and update attributes in place without touching the key.

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

   Browse assigned surrogate keys. customer_key is stable warehouse identity; customer_id is the business natural key you load and update.the pattern

   example.sql Run

   sql›

   SELECT customer_key, customer_id, contact_name
   FROM current_people
   ORDER BY customer_key
   LIMIT 10;

2. 2

   Insert without supplying a key. INTEGER PRIMARY KEY assigns the next customer_key automatically when you omit it from INSERT.

   step-2.sql Run

   sql›

   INSERT INTO current_people (customer_id, contact_name, updated_at) VALUES ('N0009', 'Test Contact', '2011-12-08 09:00:00');
   SELECT customer_key, customer_id
   FROM current_people
   ORDER BY customer_key DESC
   LIMIT 3;

3. 3

   Update a name, keep the key. Changing contact_name does not change customer_key—facts that stored the old key still point at the same customer.

   step-3.sql Run

   sql›

   UPDATE current_people
   SET contact_name = contact_name || ' (verified)'
   WHERE customer_key = 1;
   SELECT customer_key, customer_id, contact_name
   FROM current_people
   WHERE customer_key = 1;

common mistake

Rebuilding the table with ROW_NUMBER() renumbers existing customers whenever a new one sorts before them, breaking every fact row that stored the old key.