BigQuery primary keys are NOT ENFORCED: what to validate

Declaring a primary key in BigQuery won’t stop a load from inserting duplicate keys. It can still change how BigQuery executes a query. That combination makes a constraint useful only when the pipeline maintains the property the constraint declares.

We’d add primary and foreign keys to stable, validated serving tables. We’d usually leave them off raw ingestion and candidate tables, where duplicates, incomplete relationships, and replayed records may legitimately exist while processing is underway. Document the intended grain there without asking the optimizer to assume it’s already true.

NOT ENFORCED doesn’t mean ignored

A BigQuery primary key declares that its columns identify each row uniquely and aren’t null. A foreign key declares a relationship to a primary key in another table, with nullability subject to the foreign-key rules. BigQuery doesn’t enforce those declarations when data is written.

It can use them for optimizations such as eliminating eligible joins. If the declarations are false, those optimizations can produce incorrect results. Google’s primary and foreign key documentation explicitly assigns responsibility for data integrity to the user.

Consider an order table joined to customers. If two customer rows share the same supposedly unique customer_id, the join may multiply an order. The declared primary key tells the optimizer that this multiplication cannot happen. That is a stronger statement than a column description saying the ID is “normally unique.”

Start with the row identity you actually have

Before choosing a key, state what one row represents. A customer ID that is unique inside one source system isn’t necessarily unique after combining three systems. In that case, the key may need both source_system and customer_id. An order-level key doesn’t identify individual order lines either.

Check uniqueness over the complete table, not just the latest batch. A batch can contain unique IDs while duplicating rows already in the target. For an incremental model, validate both the incoming keys and the resulting table or a correction scope whose completeness you can justify.

The following read-only check assumes an unconstrained candidate table with one intended row per customer. It returns every invalid key, including null:

select
  customer_id,
  count(*) as row_count,

from
  candidate.customers

group by
  customer_id

having
  customer_id is null or count(*) > 1;

An empty result is the required outcome. For a composite key, group by every key column and check each one for null. Run validation against unconstrained candidate data so the check isn’t relying on the same unverified optimizer assumptions it is meant to establish.

Check the relationship, not just each table

A unique customer table doesn’t prove that every order points to it. Check the foreign-key relationship against the exact customer version that will be published with the orders:

select distinct
  o.customer_id,

from
  candidate.orders as o

where
  o.customer_id is not null
  and not exists (
    select
      1,

    from
      candidate.customers as c

    where
      c.customer_id = o.customer_id
  );

This example treats a null customer reference as permitted. If every order must have a customer, add a separate non-null check. A foreign-key declaration and a required business relationship aren’t interchangeable.

For late-arriving parents, you can delay publication, map unresolved references to a documented unknown member, or publish a model without declaring the relationship. Don’t declare a foreign key while routinely allowing the relationship to be broken between loads.

After validating the data and arranging the publication process, declarations on existing serving tables could look like this:

alter table mart.customers
add primary key (customer_id) not enforced;

alter table mart.orders
add constraint fk_orders_customers
foreign key (customer_id)
references mart.customers (customer_id) not enforced;

These statements add metadata; they don’t repair existing rows or validate the next load.

Validation needs to happen before readers see the change

An alert about duplicate keys is useful for investigation. It doesn’t protect queries that ran against the invalid table before someone responded. For a table carrying optimizer-visible constraints, prefer a candidate, validation, and publication sequence that keeps failed data away from readers.

Keep the checked candidate stable until publication finishes. Serialize conflicting runs or use run-specific candidates. When several related tables change, plan their publication order or use a versioned release so a new child table doesn’t temporarily reference parents that aren’t available yet. A dependency graph alone doesn’t make a multi-table release atomic.

In Dataform, distinguish three different features. An incremental table’s top-level uniqueKey controls merge matching. assertions.uniqueKey checks uniqueness. A BigQuery primary-key declaration exposes a constraint to the warehouse. None of those substitutes for the other two. Google’s Dataform table guide and assertion guide describe the separate configuration points.

Also make validation a dependency of publication. Merely attaching assertions to the table doesn’t mean downstream actions will wait for them; see Dataform assertions that block downstream models.

Leave out a constraint you can’t maintain

Adding a source system, changing a key, or changing deletion handling can invalidate an existing constraint without changing its DDL. Include constraint checks in those migrations and periodically reconcile the full table to catch gaps in incremental validation.

When a violation reaches production, stop further invalid publication and assess the dependent queries. Repair the data or remove the false declaration while repairing the pipeline; don’t leave it in place solely because it was intended to be true. Recheck the complete relationship before restoring it.