Unenforced Primary Keys

Concepts covered: Group Filtering, Grouping

A key the warehouse records and never checks

The line 'order_id is the primary key' is part of the test. Snowflake records PRIMARY KEY and UNIQUE in the catalog, along with FOREIGN KEY, and enforces none of them; the constraint it enforces is NOT NULL. BigQuery makes you write it down: PRIMARY KEY (order_id) NOT ENFORCED. Redshift and Databricks treat keys as informational. A second order 5000 inserts cleanly on every one of them.

CREATE TABLE orders (
  order_id         NUMBER NOT NULL,
  order_date       DATE,
  status           VARCHAR,
  amount           NUMBER(12,2),
  shipping_address VARCHAR,
  last_lsn         NUMBER,
  PRIMARY KEY (order_id)
);

INSERT INTO orders VALUES
  (5000, '2024-03-15', 'shipped', 120.00, '14 Elm St, Apt 2', 81247);
INSERT INTO orders VALUES
  (5000, '2024-03-15', 'shipped', 120.00, '14 Elm St, Apt 2', 81247);
-- Snowflake: both succeed; orders now holds 2 rows for order 5000

This is how Thursday happened. The loader's INSERT committed and then the loader timed out. The orchestrator retried, so the same 1,400 rows went in again under a declared key that accepted them. A candidate who argues from the constraint ('the key would have rejected them') loses the thread right here.

Postgres backs a primary key with a unique index, so the second INSERT there fails with 'duplicate key value violates unique constraint'. An engineer who learned constraints on Postgres expects that error from the warehouse and never gets it.

What trusting the declaration costs

Silent duplicates are the first cost and usually the smallest. The larger costs come from software that believes the declaration and acts on it.

• Optimizers: Snowflake with RELY, and BigQuery, may drop a join the key says can't fan out.
• MERGE: a downstream MERGE reading orders as its source errors when 2 source rows match 1 target row.
• Semantic layers: Looker computes symmetric aggregates from the primary key, so a repeated key returns wrong sums.
• Analysts: anyone who reads the key in the catalog writes sums with no dedup step and inherits every copy.

Where uniqueness actually comes from

The sentence the interviewer is waiting for is that the constraint is documentation and uniqueness is a property of the loader. That takes 2 pieces. First, the load is keyed: a MERGE on order_id whose source already holds 1 row per key, so a retried batch updates rows instead of appending them. Second, a check runs after every load and fails the pipeline when any key repeats.

MERGE INTO orders AS t
USING orders_merged AS s  -- 1 row per order_id
ON t.order_id = s.order_id
WHEN MATCHED AND s.last_lsn > t.last_lsn THEN
  UPDATE SET status = s.status,
             amount = s.amount,
             shipping_address = s.shipping_address,
             last_lsn = s.last_lsn
WHEN NOT MATCHED THEN
  INSERT (order_id, order_date, status, amount, shipping_address, last_lsn)
  VALUES (s.order_id, CAST(s.created_at AS DATE), s.status, s.amount,
          s.shipping_address, s.last_lsn);

The MERGE depends on its source, orders_merged, holding exactly 1 row per order_id, which keeps it legal and deterministic. The guard s.last_lsn > t.last_lsn lets a replayed or older batch match and change nothing, so running the same load twice leaves orders as it was. Had Thursday's retry gone through this MERGE instead of an INSERT, revenue would never have been 3% high.

-- Run after every load; any row returned fails the pipeline
SELECT order_id, COUNT(*) AS copies
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

dbt's built-in unique test compiles to essentially this query, so on a dbt project the check is 1 line of YAML. Setting RELY on the key, which lets Snowflake's optimizer act on it, is a separate decision. Grant it only to a key this check verifies after every load, because RELY turns a duplicate from a wrong total into a wrong plan.

Keep the declaration anyway

Declare it. The key documents the grain for every engineer and tool that reads the table. Catalogs read it, and so do ERD generators and BI tools. The risk lies in believing it. In the room: 'I'd keep the declared key as a statement of intent, and make the loader and the test the things that make it true.'

Do

• Ask which aggregates duplicates can touch before deduplicating anything
• Order the ROW_NUMBER by commit order and add a tie-breaker that is unique
• Take columns that change together from the same winning version
• State expected row counts before a delete and verify them after
• Back every declared key with a keyed MERGE and a uniqueness test

Avoid

• Don't write SUM(DISTINCT amount) to fix an inflated total
• Don't treat arrival time as the order of truth
• Don't use MAX on a text column to mean latest
• Don't delete by key with HAVING COUNT(*) > 1; every copy goes
• Don't let the optimizer rely on a key nothing checks

About this lesson

This section is part of the Deduplication: Intermediate 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.