Constraints without enforcement: still worth it?

A non-enforced constraint is still an executable claim.

BigQuery won’t reject a duplicate primary key or an orphaned foreign key at write time. It may still use declared constraints while optimizing queries. If the declaration is false, the result can be wrong rather than merely slower.

A constraint therefore isn’t documentation sprinkled onto a table that ought to be unique someday. It gives engineers and the optimizer permission to assume the relationship already holds.

Declare identity only after proving it

A primary key says one row represents one stable entity at the declared grain. Before adding it, the team should be able to state:

• what one row means
• which columns identify that row over time
• whether any key component can be null
• how duplicates are prevented or reconciled
• how backfills, retries, and late data preserve uniqueness

If the answer depends on “normally,” the key isn’t ready.

Source-system identifiers also need scrutiny. An order_id may be unique inside one legal entity but not across countries. A customer number may be reused after migration. A line number may only be unique with its parent order. The analytical key follows the model’s grain, not the most convenient source column.

This is the same contract required by analytical incrementals. The DDL should describe identity the model already enforces through its build logic.

Foreign keys describe a reliable relationship

A foreign key says every non-null child value resolves to a valid parent key. That claim can fail for ordinary operational reasons: sources load out of order, parents are deleted, identifiers are remapped, or one pipeline refreshes while another is stale.

Declare the relationship only when those states are either prevented or explicitly represented. A temporary orphan during a multi-step load may still violate the optimizer’s assumption while it exists. “It catches up later” isn’t a useful guarantee if queries can run in between.

Sometimes the honest model includes an unknown or unresolved parent. Sometimes the child should wait until the parent exists. Sometimes referential integrity is weak enough that the constraint should remain undocumented in DDL and live only as a monitored quality expectation.

The declaration follows the actual contract, not the relationship drawn on a whiteboard.

Validation replaces write-time enforcement

Because BigQuery doesn’t enforce the constraint, the platform has to.

For a primary key, test both nullability and uniqueness. For a foreign key, test unresolved child values against the parent. Run those checks on the same production data and lifecycle that the declaration covers, including incremental builds and backfills.

-- Primary-key validation
select
  order_id,
  count(*) as row_count
from mart.orders
group by order_id
having order_id is null or count(*) > 1;

-- Foreign-key validation
select distinct
  l.order_id
from mart.order_lines as l
left join mart.orders as o using (order_id)
where l.order_id is not null
  and o.order_id is null;

The assertions should fail the build or page an owner according to the risk. A constraint whose validation merely appears in a dashboard nobody checks is an aspiration with syntax.

Incremental checks can keep routine cost low, but periodic full validation still matters. A narrow affected-key test won’t find historical drift introduced by a broken backfill six months ago.

Treat constraints as versioned contracts

Add the constraint and its validation in the same change. Record the intended grain and relationship in the model documentation. Make the owner explicit.

Then monitor the claim like any other production interface. Schema migrations, source replacements, changes in deduplication, and new incremental paths should trigger a constraint review. If the data no longer satisfies the declaration, remove or disable the constraint before investigating performance. Keeping a false hint active while the team debates ownership is the worst option.

The removal isn’t an admission that constraints are useless. It’s the platform refusing to lie while the model is repaired.

What truthful constraints buy

The semantic value comes first. Readers can see identity and relationships without inferring them from joins and sample data. Reviewers can challenge a key change as a contract change rather than one more column edit.

The optimizer may also eliminate redundant joins or make stronger planning decisions when it trusts the declarations. That benefit is earned only by continuous correctness. We don’t add constraints solely to chase a faster plan. Performance is the consequence of a model strong enough to make the claim.

When to leave them out

Don’t declare a key when duplicates are part of normal source behavior and the model hasn’t resolved them. Don’t declare a foreign key when orphaned records are accepted and meaningful. Don’t declare either when validation has no owner or repair path.

Use ordinary documentation and quality tests until the contract becomes true. Honest incompleteness is safer than authoritative fiction.

Non-enforced constraints are worth using precisely because they are consequential. Declare them after identity and relationships are real, keep independent validation running, and remove the claim the moment the warehouse can no longer justify it.