Snowflake-Labs/dbt_constraints

Snowflake-Labs/dbt_constraints

Description: This package generates database constraints based on the tests in a dbt project

Language: SQL

License: Apache-2.0

Stars: 174

Forks: 36

Open issues: 8

Created: 2022-03-17T12:54:10Z

Pushed: 2026-05-28T19:15:12Z

Default branch: main

Fork: no

Archived: no

README:

dbt Constraints Package

This package generates database constraints based on the tests in a dbt project. It is currently compatible with Snowflake, PostgreSQL, Oracle, Redshift, and Vertica only.

How the dbt Constraints Package differs from dbt's Model Contracts feature

This package focuses on automatically generating constraints based on the tests already in a user's dbt project. In most cases, merely adding the dbt Constraints package is all that is needed to generate constraints. dbt's recent model contracts feature allows users to explicitly document constraints for models in yml. This package and the core feature are 100% compatible with one another and the dbt Constraints package will skip generating constraints already created by a model contract. However, the dbt Constraints package will also generate constraints for any tests that are not documented as model contracts. As described in the next section, dbt Constraints is also designed to provide join elimination on Snowflake.

Why data engineers should add referential integrity constraints

The primary reason to add constraints to your database tables is that many tools including DBeaver and Oracle SQL Developer Data Modeler can correctly reverse-engineer data model diagrams if there are primary keys, unique keys, and foreign keys on tables. Most BI tools will also add joins automatically between tables when you import tables that have foreign keys. This can both save time and avoid mistakes.

In addition, although Snowflake doesn't enforce most constraints, the query optimizer can consider primary key, unique key, and foreign key constraints during query rewrite if the constraint is set to RELY. Since dbt can test that the data in the table complies with the constraints, this package creates constraints on Snowflake with the RELY property to improve query performance. Some database query optimizers also consider not null constraints when building an execution plan.

Many databases including Snowflake, PostgreSQL, Oracle, SQL Server, MySQL, and DB2 can use referential integrity constraints to perform "Join Elimination" to remove tables from an execution plan. This commonly occurs when you query a subset of columns from a view and some of the tables in the view are unnecessary. In addition, on databases that do not support join elimination, some BI and visualization tools will also rewrite their queries based on constraint information, producing the same effect.

Finally, although most columnar databases including Snowflake do not use or need indexes, most row-oriented databases including PostgreSQL and Oracle require indexes on their primary key columns in order to perform efficient joins between tables. A primary key or unique key constraint is typically enforced on databases using such indexes. Having dbt create the unique indexes automatically can slightly reduce the degree of performance tuning necessary for row-oriented databases. Row-oriented databases frequently also need indexes on foreign key columns but that is something best added manually.

Please note

When you add this package, dbt will automatically begin to create __unique keys__ for all your existing unique and dbt_utils.unique_combination_of_columns tests, __foreign keys__ for existing relationship tests, and __not null constraints__ for not_null tests. The package also provides three new tests (primary_key, unique_key, and foreign_key) that are a bit more flexible than the standard dbt tests. These tests can be used inline, out-of-line, and can support multiple columns when used in the tests: section of a model. The primary_key test will also cause a not null constraint to be created on each column.

Disabling automatic constraint generation

The dbt_constraints_enabled variable can be set to false in your project to disable automatic constraint generation. By default dbt Constraints only creates constraints on models. To allow constraints on sources, you can set dbt_constraints_sources_enabled to true. The package will verify that you have sufficient database privileges to create constraints on sources.

vars:
# The package can be temporarily disabled using this variable
dbt_constraints_enabled: true

# You can control which types of constraints are enabled globally
dbt_constraints_pk_enabled: true
dbt_constraints_uk_enabled: true
dbt_constraints_fk_enabled: true
dbt_constraints_nn_enabled: true

# The package can also add constraints on sources if you have sufficient privileges
dbt_constraints_sources_enabled: false

# You can also be specific on which constraints are enabled for sources
# You must also enable dbt_constraints_sources_enabled above
dbt_constraints_sources_pk_enabled: true
dbt_constraints_sources_uk_enabled: true
dbt_constraints_sources_fk_enabled: true
dbt_constraints_sources_nn_enabled: true

# Enable this parameter if you want to skip using RELY for join elimination
dbt_constraints_always_norely: true

Installation

1. Add this package to your packages.yml following these instructions. Please check this link for the latest released version.

packages:
- package: Snowflake-Labs/dbt_constraints
version: 1.0.9
# for the latest version tag.
# You can also pull the latest changes from Github with the following:
# - git:…