How to document and test foreign keys in Snowflake

Intro

Referential integrity constraints in Snowflake

In the context of Snowflake, referential integrity constraints are informational, which means that they are not enforced by the database. This means that data can be entered into the database that does not meet the constraints, and the database will not prevent or reject this data. However, the constraints are still useful as they provide valuable metadata about the structure and relationships of the data in the database.

Why you should define referential integrity?

Consider a database with two tables: CUSTOMERS and ORDERS. The CUSTOMERS table contains information about each customer, such as their name and address, and the ORDERS table contains information about each order, such as the order number and the customer's ID.

To perform a JOIN between these two tables, you would need to use a common column, such as the customer's ID, to link the two tables together. This is where referential integrity constraints come in. By establishing a foreign key constraint on the customer's ID in the ORDERS table, you can ensure that the customer's ID in the ORDERS table always refers to a valid customer in the CUSTOMERS table. This makes it easier to perform accurate and consistent JOINs between the two tables.

Preparation

Installation of the necessary tools

Install Dataedo and create repository - Dataedo installation (desktop). After preparing the information in the desktop application, it is a great advantage to use the Datedo Portal to consume the documentation - installation of Dataedo Portal.

Connect to Snowflake and import schema

Connect to Snowflake and import schema - Connecting to Snowflake database.

After a successful metadata import in the Relationship tab, you will be able to see the foreign keys defined in the imported Snowflake database.

Document and test relationships

Document relationship

There are two main ways to complete the documentation with missing entity relationships.

Using the Relationships tab

Click on Add Relationship button.

Using the Columns tab

Select the column to be the foreign key (you can select more than one). Right-click and select Add Relationship.

Fill in the information on the relationship

Complete the information about the primary key and foreign key and give a name for this relationship. Optionally, you can title the relationship, modify the default cardinality or add a description. Click Save to add the relationship you have defined.

Remember that in Dataedo you can also add a composite foreign key.

Result

Under the Relationships tab you will be able to see the added relationship.

Test Relationship

To make sure that the relationship we just added is correct we can test it. To do this we need to:

• make sure that the columns for the primary key contain unique values and do not contain Null values
• make sure that each value in the foreign key has a matching value in the primary key, and therefore also cannot contain Null values
• it's worth checking how many values both tables contain to assess the usefulness of this test, we'll take a different approach if there are a million of them, and a different approach if each has only one row, then there's a better chance that it's just a coincidence

To check all of the above conditions for a valid relationship, all you need to do is click Test at the relationship definition stage.

To test an already added relationship, right-click on it and select Edit relationship. You will then be able to test it as above.

Results

Once the test is complete, you will be able to see three results:

• OK - all tests passed, the relationship is correct
• UNKNOWN - any of the columns is empty and you can't determine if the relationship is correct
• FAILED - the relation did not pass the tests, so it is incorrect

Show on diagram

Dataedo Portal

We can see the relationship diagram using Dataedo Portal.

Dataedo Desktop

To see the relationship diagram we need to create a Subject Area and add tables to ERD - click for more information.

You can also copy diagram to clipboard. Right-click blank part of diagram pane and choose Copy to clipboard option.

Export to HTML

We can export our documentation to a .html file - click for more information. You will be able to see the relationship by going to the tab of the table that has it.

To see the diagram after exporting you must create a Subject Area and add tables to ERD - click for more information.