Keys & Identity

A relationship is only as trustworthy as the way you identify a row. Chapter 2 connected entities; this chapter answers the question every connection depends on: how do you point at exactly one row, now and forever? That is the job of keys.

Start with the three broken reports below: diagnose each one and the chapter routes you to the topic that repairs it. Every claim from here on is backed by something you can run, DDL, sample rows, and the exact result or error to expect.

key

A column (or set) whose value uniquely identifies a row.

primary key

The one chosen key that officially identifies each row; unique and not null.

foreign key

A column that must match a key in another table, enforcing a relationship.

referential integrity

The guarantee that every foreign key points at a row that actually exists.

A primary key is the column (or set of columns) you choose to officially identify each row. It must satisfy two rules with no exceptions: every value is unique, and no value is null. Those two rules are what let any other table point at this row unambiguously.

Often a table has more than one column that could serve. Each unique, non-null column is a candidate key. In a customers table, both customer_id and email might be unique, so both are candidates, but you pick exactly one as the primary key and keep the others enforced as unique constraints. Picking well (small, stable, never-changing) matters, which is the next section.

Test that below: tick columns from a real asset register and both rules run against every row, including the third rule people forget, that a candidate key must be minimal.

candidate key

A minimal column set that is unique and non-null, eligible to be the primary key.

superkey

A unique set that still contains a droppable column; every candidate key is a superkey, but not the reverse.

primary key

The single elected candidate key; enforced as unique and not null.

unique constraint

Enforces uniqueness on a column without making it the primary key.

create table customers (
  customer_id  bigint generated always as identity primary key,  -- chosen PK
  email        varchar(255) not null unique,                     -- candidate key
  full_name    text not null,
  phone        varchar(40)                                       -- nullable: not a key
);

insert into customers (customer_id, email, full_name)
values (7, 'mara@ex.com', 'Mara O.');

-- Attempt a second row with the same primary key value:
insert into customers (customer_id, email, full_name)
values (7, 'mara2@ex.com', 'Mara Two');
-- ERROR: duplicate key value violates unique constraint "customers_pkey"

The most consequential key decision is natural vs surrogate. A natural key is a real business value that already identifies the thing, an email, an ISBN, a SKU. A surrogate key is a meaningless, system-generated id (an auto-increment integer or UUID) whose only job is to be a stable handle.

Natural keys are tempting because they carry meaning and need no extra column. But business values change, and by then the natural key has been copied into every related table as a foreign key. Throw the two events below at all three designs: only one survives both, and it is the one that uses a surrogate primary key and a unique constraint on the natural key together.

natural key

A real business value that identifies the entity (email, ISBN, SKU).

surrogate key

A system-generated, meaningless id used purely as a stable handle.

business key

The natural key kept as a UNIQUE constraint alongside a surrogate PK.

create table users (
  user_id  bigint generated always as identity primary key,  -- surrogate: stable handle
  email    varchar(255) not null unique,                     -- natural key: still unique
  name     text not null
);

-- Now an email change is a one-line update that touches NOTHING else,
-- because every relationship joins on the unchanging user_id.
update users set email = 'new@ex.com' where user_id = 7;

This section and the next preview how surrogate generation works in real warehouse loads — deeper water than the rest of this chapter. Read them once now, and return when you are building loads at scale.

In transactional databases an IDENTITY or SEQUENCE column generates the surrogate. Warehouse loads mostly use a deterministic hash key instead: hash the natural-key columns and the output is the surrogate. The same input always produces the same output, so the same source row gets the same surrogate on every load, with no central counter and no coordination between workers.

MD5 (128-bit, 32 hex characters) is faster and narrower, which matters for join performance in wide fact tables; SHA-256 (256-bit, 64 characters) is slower with more collision margin. Neither belongs anywhere near password hashing — MD5 is cryptographically broken — but for identifying business rows rather than guarding secrets, MD5 is standard and accepted. Say so explicitly when you discuss it.

In dbt, `dbt_utils.generate_surrogate_key(['col_a', 'col_b'])` is the standard tool: it concatenates the listed columns with a fixed delimiter (the exact character has changed across versions, so check your pinned one), coalesces NULLs to the literal string '_dbt_utils_surrogate_key_null_', then MD5-hashes the result. Build that expression yourself below, and see what each of those two decisions is protecting you from.

deterministic hash key

A surrogate generated by hashing the natural key columns so the same input always produces the same surrogate.

hash collision

Two distinct inputs that produce the same hash output; negligible at normal volumes for MD5, vanishing for SHA-256.

generate_surrogate_key

A dbt_utils macro that concatenates, null-coalesces, and MD5-hashes listed columns to produce a stable surrogate.

Identity/sequence columns (AUTO_INCREMENT, GENERATED ALWAYS AS IDENTITY, Snowflake AUTOINCREMENT) work perfectly for transactional databases where rows are inserted one at a time or in small batches. At warehouse scale, where you load hundreds of millions of rows in parallel across many workers, they become a bottleneck.

The reason is central coordination: a monotonic sequence means every worker that needs the next id must serialize on the counter. In a distributed load with dozens of parallel tasks writing to one table, that serialization either stalls workers or forces the warehouse to assign non-sequential id blocks — which defeats the "monotonic" property you may have relied on for ordering.

Four strategies are in common use and none of them wins on every axis. Cross them below with the four questions that actually decide a load: parallelism, what a re-run does, how the key behaves in a clustered index, and what it costs per row.

sequence bottleneck

The serialization point that arises when concurrent load workers must all request the next value from a central IDENTITY/SEQUENCE counter.

idempotent load

A load that produces the same output each time it runs on the same source data. Full treatment: Data Pipeline KB, Idempotency, Retries & Exactly-Once.

UUIDv7 / ULID

Monotonic (time-sortable), globally unique identifiers that are clustered-index friendly, unlike the fully-random UUIDv4.

-- Sequence/IDENTITY surrogate: one central counter.
-- Workers must coordinate on every insert; parallelism is limited.
create table dim_customer_seq (
  customer_sk  bigint generated always as identity primary key,
  customer_id  bigint not null,
  email        text not null,
  unique (customer_id)            -- natural key constraint
);

-- Hash surrogate: each row computed independently.
-- Any number of workers can run simultaneously with zero coordination.
create table dim_customer_hash (
  customer_sk  char(32) not null primary key,  -- MD5 hex
  customer_id  bigint not null,
  email        text not null,
  unique (customer_id)
);

-- Load expression for dim_customer_hash (BigQuery example):
-- INSERT INTO dim_customer_hash
-- SELECT
--   md5(coalesce(cast(customer_id as string), '_null_')) as customer_sk,
--   customer_id, email
-- FROM source.customers;
-- Re-running this on the same source produces the exact same customer_sk.

Sometimes no single column identifies a row, and it takes a combination. That is a composite key: a primary key made of two or more columns together. The classic case is the bridge table from Chapter 2, and any line-item table, where one column repeats by design.

Take order_items. The same order_id appears on every line of an order, and the same sku appears across many orders, so neither alone is unique. But the pair (order_id, sku) is unique: an order has each product at most once. That pair is the correct composite key, and it also declares the grain (one row per product per order).

Getting it wrong fails in two opposite directions, and the simulator below runs both: a key that is too narrow refuses a legitimate row, while a key with an extra column bolted on accepts a duplicate. Only the correct pair does neither, which is why choosing the composite key is really choosing the grain, the subject of the next chapter.

composite key

A primary key made of two or more columns that are unique only in combination.

line-item grain

One row per item within a parent (one row per product per order).

key check

GROUP BY the candidate columns and look for any count greater than one.

create table order_items (
  order_id  bigint not null references orders(order_id),
  sku       text   not null,
  qty       int    not null check (qty > 0),
  unit_price numeric(10,2) not null,
  primary key (order_id, sku)    -- the PAIR is unique, neither column alone is
);

A foreign key is how a relationship from Chapter 2 becomes real and enforced. It is a column whose value must match a key in another table. orders.customer_id is a foreign key referencing customers.customer_id: it says "every order must belong to a customer that actually exists."

That guarantee is called referential integrity, and the database enforces it on every write: an order for a customer who does not exist is refused, and so (by default) is deleting a customer who still has orders. Without it you get orphans, child rows pointing at parent ids that are not there, which break joins and counts without ever raising an error. Load the batch below both ways and reconcile the two totals.

Analytical warehouses break this contract silently: BigQuery, Snowflake, and Redshift accept PRIMARY KEY, FOREIGN KEY, and UNIQUE declarations but do NOT enforce them at write time. Each warehouse phrases the trust differently — Redshift treats constraints as always informational only; Snowflake requires an explicit RELY (default NORELY) before the optimizer trusts a constraint; BigQuery requires the NOT ENFORCED qualifier (ENFORCED is not supported). In every case the optimizer uses the declaration as a hint — eliminating joins, assuming uniqueness — based purely on what you declared. It trusts you blindly.

The danger is that if you declare RELY or NOT ENFORCED on a key that is not actually unique — because your load pipeline has a bug, a late-arriving duplicate, or a missed deduplication step — the optimizer produces wrong query results with no error. Aggregates double-count; joins collapse; group-bys return phantom rows. You get a number, not an exception. This is one of the highest-impact silent footguns in analytical modeling and is exactly what separates mid-level from senior warehouse modelers in interviews.

foreign key

A column required to match a key in another (parent) table.

parent / child

The referenced table is the parent; the table holding the foreign key is the child.

orphan row

A child row whose foreign key points at a parent that does not exist.

create table orders (
  order_id     bigint primary key,
  customer_id  bigint not null references customers(customer_id),
  total        numeric(10,2) not null
);

-- dim_products in Snowflake, declared with a RELY primary key.
-- Your load pipeline has a bug: product_id 42 was inserted TWICE.
create table dim_products (
  product_id   integer not null,
  product_name text    not null,
  category     text    not null,
  constraint pk_dim_products primary key (product_id) rely
  -- ^ RELY tells Snowflake: "trust that product_id is unique"
  -- ^ Snowflake does NOT verify this. It is your promise.
);

-- The buggy load inserted:
-- (42, 'Widget Pro', 'Electronics')
-- (42, 'Widget Pro', 'Electronics')   <-- exact duplicate, no error raised

-- A query that looks correct:
select p.category, sum(f.revenue) as total_revenue
from fact_sales f
join dim_products p on p.product_id = f.product_id
group by p.category;

Referential integrity raises a question: if an order must belong to a customer, what happens when you delete a customer who has orders? You decide, in advance, with a referential action on the foreign key. There are three you will use.

You decide in advance, with a referential action on the foreign key: RESTRICT blocks the delete while children exist, CASCADE deletes the children too, and SET NULL keeps them while clearing the link. Delete a parent below under each rule and watch how far the deletion travels.

The right choice depends on meaning. Cascade suits truly owned children (delete an order, delete its order_items). Restrict suits records you must not lose by accident. Set null suits optional links. Choosing on purpose is how you avoid both surprise data loss and unexpected orphans.

referential action

The rule (RESTRICT/CASCADE/SET NULL) applied to children when a parent is deleted or updated.

cascade

Automatically delete (or update) child rows along with the parent.

ownership

Whether a child only exists as part of its parent; the basis for choosing cascade vs restrict.

-- RESTRICT: cannot delete a customer who still has orders.
create table orders (
  order_id    bigint primary key,
  customer_id bigint not null references customers(customer_id) on delete restrict
);

-- CASCADE: deleting an order deletes its items automatically (items are owned).
create table order_items (
  order_id bigint not null references orders(order_id) on delete cascade,
  sku      text   not null,
  primary key (order_id, sku)
);

-- SET NULL: keep the order, but clear the link (FK must be nullable).
create table orders_optional (
  order_id     bigint primary key,
  promo_id     bigint references promos(promo_id) on delete set null
);

Knowing the types of keys is not enough; you have to verify identity in real, messy data. The single most useful skill is the duplicate check: before you trust a column as a key or add a unique constraint, group by it and look for counts above one. Audit three columns below, and note that reading the result takes judgment the query cannot supply.

Then think ahead about durability. Because surrogate keys never change, they are the anchor that lets you keep history: the same donor keeps one durable id even as their donor number, centre, and email change. That is the foundation of slowly changing dimensions in Chapter 11; here, just internalize that a stable key is what makes tracking change possible at all.

duplicate check

GROUP BY a candidate key with HAVING count(*) > 1 to find violations before enforcing it.

durable key

A stable identifier (surrogate) that stays constant so history can be tracked against it.

enforce

Turn an assumed key into a real PRIMARY KEY or UNIQUE constraint the database guarantees.

create table donors (
  donor_id    bigint generated always as identity primary key,  -- stable join key
  email       varchar(255) not null unique,                     -- enforced business key
  donor_no    text unique,                                      -- second candidate: unique, but nullable
  centre_code text not null,
  created_at  timestamptz not null default now()
);
-- Joins use donor_id forever; the email and the centre can change without breaking
-- anything; and the durable donor_id is what lets us track history later.

Five design scenarios for this chapter, on the ERD canvas. Each one is graded against a real rubric, seeded with real rows, and each has one fault planted in the data rather than in the diagram.

Build them with the chapter closed. If one goes wrong, come back to the section it belongs to rather than re-reading the whole thing.