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.