Snowflake for SQL Server Users - Part 16 - Primary and Foreign Key Constraints

The Usual Situation

In general database terminology, a primary key is one or more columns that can be used to identify a particular row in a table. The key needs to be unique, and it can’t be null. An example would be a CustomerID or CustomerKey in a Customers table.

A foreign key is one or more columns that refer to a key in another table. A common example would be a CustomerID column in an Orders table. Foreign keys can be nullable and are checked when they contain a value.

Foreign Keys in Data Warehouses

I like to see foreign keys in data warehouses. The most common objection to them is performance. And yet when I ask those same people if they’ve ever tested it, I’m usually told that they haven’t but that their brother’s friend’s cousin read it somewhere on the Internet.

Don’t be that person. 

I also hear “the app does that”, and so on. Yet, almost every time I check a substantial data warehouse for consistency, when it’s run for a while without foreign keys being checked, I invariably find problems. When I show them to people, I then hear “oh yep, we had that bug a while back…” and so on, but there’s almost always an issue.

Even if the app checks the data, other apps touching the same data might not. And what if your ETL (or ELT) processes have bugs? You need to find out about it immediately.

Primary and Foreign Keys in Snowflake

I wish it wasn’t so, but while you can define primary and foreign keys on tables in Snowflake, note this:

Yep, they are ignored. They are not checked at all.

Same deal for unique constraints. (Mind you, unique constraints are really a broken concept in SQL Server as well. While they are checked, SQL Server only allows a single row where the key is null. That’s not good either).

And that’s why there are people complaining in the Snowflake forums about issues caused by duplicate primary keys.

I really think this aspect of the product needs to be reconsidered and I encourage the Snowflake team to do so. For many sites I work at, this single aspect would be a showstopper.

 

For an index to all posts in this series, see the first post here.

2019-11-22