What is the value of schema tests in dbt?

Question

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

Answer 1

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.