Key Generation Strategies

Concepts covered: Key Generation

The generator behind a surrogate key decides 3 things: how wide the key is, whether consecutive keys arrive in order, and whether 2 writers can mint keys without coordinating. The common options are a database sequence, a UUID, a hash of the natural key, and the natural key itself.

Sequences: compact and ordered, until they run out

A sequence is an atomic counter the database maintains: BIGSERIAL in PostgreSQL, IDENTITY in SQL Server and Snowflake. Each call returns the next integer, so keys arrive in insert order and pack B-tree indexes tightly, every new key landing at the right-hand edge. The key's width is the column's type, 8 bytes for BIGINT, and that's where tables go wrong: SERIAL and INT are 32-bit signed and stop a little past 2.1 billion. FreshCart created order_items with SERIAL in 2021, and at around 2 million lines a day it crossed that limit in its 3rd year. The next insert failed and took checkout down with it. The repair took ALTER SEQUENCE ... AS bigint plus an ALTER COLUMN ... TYPE BIGINT that rewrote the table under an exclusive lock for 2 hours.

• 2,147,483,647

A sequence's other limit is coordination: 1 counter in 1 database, so 400 Spark tasks writing a dimension can't each take the next value without serialising on it. BIGINT is the right default for anything that might pass 100 million rows; the extra 4 bytes a row come to 800 MB on 200 million rows, far cheaper than the outage FreshCart had.

UUIDs: unique anywhere, at a cost in width

A UUID is 128 bits. Version 4 is random, so any process anywhere mints one with no coordination, and with 2^122 possible values a collision isn't worth planning for. A native UUID column stores 16 bytes and the text form 36, so 500 million keys take 18 GB as text against 4 GB as BIGINT, before every foreign key and index copies them. Random values also scatter inserts across the index, fragmenting it. Version 7, standardised in RFC 9562, leads with a 48-bit millisecond timestamp, so keys sort by creation time and inserts cluster at the index's right edge, keeping the coordination-free property without the fragmentation. It's the default for ids minted on clients or across services.

Hash keys: deterministic and replayable

A hash key is MD5 or SHA-256 of the natural key, with a separator so ('ab','c') and ('a','bc') stay apart: md5('FC-0417' || '|' || '2024-03-18'). The same input yields the same key on every run and every worker, so a fact load computes product_key from the natural key in parallel with the dimension load, with no lookup. Sequence keys exist only after the dimension insert, so every fact load waits for it and looks them up. Data Vault builds its hubs and links this way. The costs: MD5 is 16 bytes like a UUID, SHA-256 is 32, and hex text doubles each; the values have no order; and 2 inputs can in principle collide, negligible for SHA-256 and the reason some teams avoid MD5. The key is only as stable as its input: the recycled SKU hashes alike for bagels and oat milk unless the listing date is included.

Natural keys, kept where they belong

A natural key still fits as the primary key where the value is short and set by a standard the business doesn't control, so it rarely changes: ISO country and currency codes, a calendar date_key written as 20240318. The test is unchanged: who can change or reuse the value, and how many rows would have to follow it.

FreshCart loads dim_customer and fact_order into Snowflake from the same nightly extract, each with a Spark job of 400 tasks, and wants the fact job to run alongside the dimension job rather than after it. Which key generation fits dim_customer?

• A Snowflake IDENTITY column, letting the warehouse assign keys on insert.
• UUID v4 generated inside each Spark task.
• md5(customer_natural_id) computed inside each task.
• row_number() over the input, offset by the current maximum key.

Hash keys are worth their width where separate jobs must agree on a key without talking to each other; a single writer is still best served by a BIGINT sequence. The habits below are what the 5 sections add up to.

Do

• Give every table a primary key the business cannot change or reuse; keep natural keys UNIQUE at their grain.
• Default surrogate integers to BIGINT on any table that could pass 100 million rows.
• Put the foreign key on the many side, and test referential integrity in every warehouse load.
• Keep a UNIQUE constraint on the composite columns when a junction table gets a surrogate.
• Use hash keys where parallel jobs must agree on a key, and UUID v7 where services mint ids.

Avoid

• Do not key a table on an email or SKU the source system can edit or recycle, and never on a name.
• Don't store UUIDs as 36-character text when the engine has a 16-byte UUID type.
• Do not CASCADE deletes from a parent whose children carry revenue history.
• Don't put a column in a composite-key table that depends on only part of the key.
• Don't assume a declared FOREIGN KEY in Snowflake, BigQuery or Redshift rejects anything.

About this lesson

This section is part of the Keys & Identity lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.