When would you want to use a dbt schema tests (unique, not_null, accepted_values, & relationships) when you could instead use SQL schema constraints?

For example, here are some SQL schema constraints that could replace each of the dbt schema tests:

• unique: UNIQUE constraint
• not_null: NOT NULL constraint
• accepted_values: FOREIGN KEY constraint to a lookup table
• relationships: FOREIGN KEY constraint to another table

CodePudding user response：

You can't have the above-listed table constraints on a view, or a CTE. The value of dbt is that users are allowed to build a DAG of SELECT queries without having to worry about DDL or configuration. At a later point of time they can be configured to be tables instead of the default view, and no syntax need be changed.

CodePudding user response：

dbt tests are more flexible than database constraints. If you try to insert a null value to a not null target your process will fail, end of story. With dbt tests you can execute first, test after, which might be interesting.

More examples:

• You can classify tests are warnings instead of errors
• You can define error thresholds
• You can specify a filter to apply the test only to a subset of values
• Test can behave differently in different environments (prod/dev etc.)
• dbt tests are easier to modify than database contraints