How it Works

The expression_is_true test from dbt-utils™ is one of the most flexible dbt™ tests available. It validates that a custom SQL expression evaluates to true for every row (or every row matching an optional where condition). This lets you encode any business rule or constraint that can be expressed in SQL.

Steps and Conditions

1. Write Expression: Provide the SQL boolean expression using expression.

2. Optional Where Clause: Scope the test to specific rows using where.

3. Execution: The expression is evaluated for each row.

4. Outcome: Pass if all rows evaluate to true; fail if any evaluate to false or null.

Example Usage: Financial Validation

A financial model requires that revenue is always greater than or equal to cost_of_goods_sold for profitable product lines.

models:
  - name: product_financials
    tests:
      - dbt_utils.expression_is_true:
          expression: "revenue >= cost_of_goods_sold"
          where: "product_line = 'core'"

models:
  - name: product_financials
    tests:
      - dbt_utils.expression_is_true:
          expression: "revenue >= cost_of_goods_sold"
          where: "product_line = 'core'"

models:
  - name: product_financials
    tests:
      - dbt_utils.expression_is_true:
          expression: "revenue >= cost_of_goods_sold"
          where: "product_line = 'core'"

Any core product line record where costs exceed revenue will fail the test, flagging a potential data entry error or pricing anomaly.