CODEWITHSUNDEEP
sqlvalidationtestingdbt
A Poor
A Poor

Reputation: 1064

What is the value of schema tests in dbt?

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:

Upvotes: 1

Views: 977

Answers (2)

Ilmari Aalto
Ilmari Aalto

Reputation: 261

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

Upvotes: 4

Anders Swanson
Anders Swanson

Reputation: 3961

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.

Upvotes: 3

Related Questions