How do BigQuery NOT ENFORCED keys affect join optimization?

Prompt and scope

You own a BigQuery star schema: store_sales is a fact table and customer is a dimension table. The team wants primary and foreign key declarations so the optimizer can use uniqueness and relationship metadata to reduce joins. The interviewer asks three questions: what an unenforced constraint guarantees, which equivalent rewrites are safe, and how to prevent silent errors when the data drifts.

Assume the query projects only fact columns, the fact-table foreign key is nullable, and the dimension primary key is unique and non-null. Google Cloud documents that BigQuery does not enforce these constraints; the owner must keep the data valid, and queries over violated constraints can return incorrect results.

What the interviewer evaluates

• Whether you distinguish optimizer metadata from write-time integrity checks.
• Whether you derive join elimination from uniqueness and optional matching instead of saying a key is an index.
• Whether you know NOT ENFORCED does not reject duplicate keys or orphan foreign keys.
• Whether you turn the contract into load gates, monitoring, and rollback rather than stopping at DDL.

A weak answer says “the primary key is unique and the foreign key references it.” A strong answer says that an optimizer may rewrite a query using a false declaration, so the failure is wrong data rather than merely slower data; every declaration needs repeatable evidence.

Clarifications before answering

1. Is this a native BigQuery table or an external table? Constraint support and rewrite rules differ, so scope the answer first.
2. Does the query project only left-side columns? Selecting right-side columns usually prevents join elimination.
3. Is the foreign key nullable? NULL means no match is required and changes the filter in an equivalent rewrite.
4. Is the constraint maintained by one pipeline or cross-system replication? Cross-system loads need checks both before and after the table is written.

These answers change the result: selecting right-side columns, duplicate primary keys, or non-null orphan keys make elimination unsafe. If only eventual consistency is available, the check result must become a release gate.

A 30-second answer framework

“BigQuery keys are declarative metadata and are NOT ENFORCED by default. They do not reject duplicate primary keys or orphan foreign keys at write time. Their value is giving the optimizer uniqueness and relationship facts; for example, a join that returns only fact columns can sometimes be reduced to a non-null filter. The data must satisfy the declaration or the rewrite can return wrong results. I first confirm projection and NULL semantics, then run duplicate-key, orphan-key, and count-reconciliation checks after every load. A failed check blocks the constraint release or rolls back the batch.”

Step-by-step solution

1. Separate the two roles of a key

A primary key declaration means each row is unique and non-null. A foreign key declaration means every non-null value should appear in the referenced primary key. BigQuery can read those declarations for optimization, but it does not perform write validation. NOT ENFORCED is an explicit contract; the defect is invalid data, not invalid syntax.

2. Derive inner-join elimination

Consider a query that selects only fact columns:

SELECT ss.*
FROM store_sales AS ss
JOIN customer AS c
  ON ss.sales_customer = c.customer_name;

If customer.customername is a unique non-null primary key and every non-null ss.salescustomer matches one customer, the join cannot duplicate a fact row. The optimizer can rewrite it as:

SELECT *
FROM store_sales
WHERE sales_customer IS NOT NULL;

The rewrite depends on two facts: a non-null foreign key has a match, and that match is at most one row. It is not equivalent when the query needs right-side columns, needs to distinguish unmatched rows, or the primary key is duplicated.

3. Boundaries for outer joins and join order

A left outer join may also be removable when the right join key is unique and only left-side columns are projected. In a multi-join query, key metadata can provide cardinality information for join reordering. These are metadata-based deductions; BigQuery does not scan the right table at runtime to prove uniqueness.

4. Put correctness in the load gate

Run at least three checks for every batch:

-- Duplicate or null primary keys
SELECT customer_name, COUNT(*) AS n
FROM customer
GROUP BY customer_name
HAVING customer_name IS NULL OR n > 1;

-- Orphan foreign keys
SELECT COUNT(*) AS orphan_count
FROM store_sales AS ss
LEFT JOIN customer AS c
  ON ss.sales_customer = c.customer_name
WHERE ss.sales_customer IS NOT NULL
  AND c.customer_name IS NULL;

Reconcile batch row counts, non-null foreign-key counts, and matched-key counts. Store the result in a data-quality table. Only a version that passes the gate should publish a new constraint declaration or allow downstream queries to rely on the optimization.

5. Choose declaration, validation, or a rewrite

Declarations fit stable, testable dimension keys and let the optimizer use the relationship automatically. Application validation fits ingress paths that must reject bad data early. An explicit query rewrite fits a migration period when the contract is not trusted, at the cost of duplicated logic. They can coexist: validate in the pipeline, declare the trusted relationship, and reconcile critical reports.

6. Design the failure path

On a failed check, keep the last trusted table or view, mark the current batch as not publishable, and alert the data owner. Do not delete the constraint as the only “fix”; that hides the cause. Trace duplicate-key sources, replication lag, or delete ordering, then choose replay, deduplication, or dimension repair.

High-quality sample answer

“I treat BigQuery primary and foreign keys as an optimization contract, not a transaction constraint. First I confirm that the query projects only fact columns, define the meaning of nullable foreign keys, and verify dimension-key uniqueness. Under those conditions an inner join can become a non-null fact filter, some left joins can disappear, and join order can use declared cardinality. The risk is that BigQuery does not enforce the contract: duplicate primary keys or orphan foreign keys let the optimizer rewrite using false metadata, which can silently change results.

“Before release I check null and duplicate primary keys, orphan foreign keys, and row-count reconciliation for every batch. A failed batch cannot publish the table or new constraint; the previous trusted version remains active. During a migration with untrusted metadata, I disable dependent rewrites or use an explicit join until several batches pass. That preserves the optimization benefit while making correctness auditable.”

Common mistakes

• Mistake: saying PRIMARY KEY rejects duplicates → Why it fails: BigQuery documents the constraint as unenforced → Fix: put duplicate detection in the load gate.
• Mistake: removing every join that has a foreign key → Why it fails: the key may be orphaned and the query may need right-side columns → Fix: verify data and projection conditions first.
• Mistake: checking historical data once → Why it fails: incremental loads, backfills, and replication lag can reintroduce bad keys → Fix: run batch checks continuously and retain metrics.
• Mistake: deleting the constraint after a wrong result → Why it fails: optimization metadata disappears while the source defect remains → Fix: freeze release, find the cause, and restore a trusted version.

Follow-ups and responses

What if the dimension gets duplicate keys today?

Pause queries that rely on the declaration, switch to an explicit deduplicated or trusted snapshot, mark affected batches, and replay the reconciliation. Restore the declaration only after the dimension is repaired and checks pass.

Why does the nullable foreign key require a non-null filter?

NULL means the fact row has no customer match. An inner join drops that row, so the equivalent rewrite must keep WHERE sales_customer IS NOT NULL; omitting it changes the result.

How do you prove join elimination preserves results?

Run the original and rewritten queries side by side on representative partitions. Compare row counts, primary-key sets, and aggregates, and record the constraint-check version. Promote the rewrite only after both data-quality checks and result reconciliation pass.

When would you avoid constraint-based optimization?

Avoid it while constraints come from slow or unauditable replication, backfills are frequent, or there is no batch gate. One extra scan is preferable to a cheaper query that trusts false metadata.

Can BigQuery constraints replace a cross-table transaction?

No. They do not enforce write consistency or provide an atomic commit across tables. Transaction semantics belong in the upstream system or load orchestrator, with the final validation result carried into the warehouse.

References

• Google Cloud Documentation: BigQuery primary and foreign keys.
• Google Cloud Blog: Join Optimizations with BigQuery Primary and Foreign Keys.