BigQuery Cross-Dataset Foreign Keys Using Table Clones

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

Cross-dataset Foreign Key referencing in BigQuery

Comments

Practical SQL

More from this blog

pre_ and post_operations in Dataform

BigQuery Object Tables: A Practical Introduction

BigQuery BigLake Tables Explained: What They Are and When to Use Them

BigQuery Saves Your Query Results — Here's How to Find Them

Parameters in BigQuery

Command Palette

Comments

Practical SQL

More from this blog