This is a companion discussion topic for the original entry at What's a Primary Key and Why Do We Test Them? | dbt Developer Blog
Very recently GCP-BigQuery has started the primary key / foreign key support. Would like to know about dbt’s support plan on the GCP-BigQuery based PK/FK .
What I would say - Primary key is just your ‘unique’ or distinct value within a dimension.
What we do is just perform a unique test on that column to make sure we aren’t getting any dups from our process or from our source system.
Also worth noting that, in Snowflake, you get performance benefits when using primary keys.
While they are still not enforced, you can tell the query compiler to RELY on the PK/FK constraint. I.e., you can pinky promise that the column is indeed a real primary key (not null, unique) and that a FK will find exactly one matching PK. If you do this the compiler will make certain optimizations that it otherwise can’t make because it can’t be sure of their correctness. For example, it will remove redundant joins which can be a huge time save.