Examples of custom schema tests

Show and Tell
4

We put all of the custom schema tests we find useful in dbt-utils.

One pattern which is super simple but really useful to implement is the use of the expression_is_true test, so you can define your own logic without having to write the sql for a test. I used this a lot in financial models when I had to validate that subtotal columns were equal to a total, that financial values were positive, etc. etc., so my schema.yml would look like:

version: 2

models:
  - name: payments
    tests:
      - dbt_utils.expression_is_true:
          expression: "inbound_amount = payout_amount + refund_amount"
      - dbt_utils.expression_is_true:
          expression: "inbound_amount = inbound_cash_amount + inbound_credit_amount"
4 Likes