How to document and test foreign keys in Snowflake

Hubert Książek - Dataedo Team Hubert Książek 21st March, 2024
Applies to: Dataedo 23.x versions, Article available also for: 24.x (current)
You are looking at documentation for an older release.
Switch to the documentation for Dataedo 24.x (current).

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.

Example of entity relationship diagram (ERD)

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. Dataedo will import all relationships from Snowflake

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. Where to click to add a relationship in Dataedo using the Relationships tab

Using the Columns tab

Select the column to be the foreign key (you can select more than one). Right-click and select Add Relationship. Where to click to add a relationship in Dataedo using the Columns tab

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. Filling  the information on the relationship

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

Result

Under the Relationships tab you will be able to see the added relationship. The outcome of documenting the 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. Where to click to test the relationship

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

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

Possible results after testing the relationship

Show on diagram

Dataedo Portal

We can see the relationship diagram using Dataedo Portal. Relationship diagram in 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. Relationship diagram in Dataedo Desktop

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

Image title

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. Relationship after exporting to .html

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