Cross-dataset Foreign Key referencing in BigQuery

In one of my previous posts I've written about the then-newly-added Primary Key/Foreign Key constraints in BigQuery.

While they are not enforced like traditional RDBMS, they can still provide an improvement to query performance.

They have one important catch though - tables with a primary key and foreign key relationships must be in the same dataset - see error in (1)

How do you go around that?

Well, you could just do a regular copy of the referenced table into your dataset, but it would incur additional storage costs. Maybe not worth it if the table is big.

But there's another BQ feature we can use - table clones.

We can create a table clone of the table we want to reference in our desired dataset (2).

Then, we can reference the table clone when defining the Foreign Key constraints. (3)

We should keep in mind that identical data from source table and clone table is charged only one - so you'd only pay for the storage of different data, if that's the case.

Found it useful? Check out to my Analytics newsletter at notjustsql.com.

---

Enjoyed this? Here are some related articles you might find useful:

• Cross-dataset foreign key relationships in BigQuery