dbt generic tests vs singular tests

TL;DR

Generic tests are dbt's reusable built-in checks (unique, notnull, acceptedvalues, relationships): you declare them in YAML and they run against any model or column. Singular tests are custom SQL files in the tests folder that assert a query returns zero rows: full freedom, zero reuse. Start with generic tests; write singular tests only for logic too specific to express with the built-ins.

The query

dbt generic tests vs singular tests

Use this when

• Adding data quality checks to a new dbt project
• A check feels copy-pasted across many models
• Deciding where a tricky business rule belongs

Not for

• Testing that sources arrived on time (use source freshness)
• Orchestration or scheduling concerns
• Checks that belong in the ingestion layer instead

Steps

1. Cover the basics with generic tests in the schema YAML. They are one-liners, self-documenting, and every data person recognizes them:

models:
  - name: orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'cancelled']

Expected output: dbt test runs the built-ins and they pass on clean data.

1. Reach for singular tests when the rule needs joins, windows, or business logic. A singular test is a SELECT that must return zero rows; anything returned is a failure. Name it clearly, e.g. tests/assertordershavevalidtotals.sql.

Expected output: a SQL file whose empty result means the business rule holds.

1. Do not duplicate: if you find the same singular test logic in three places, promote it to a custom generic test (a macro + test block) so it becomes reusable like the built-ins.

Expected output: one generic test definition replacing three near-identical singular tests.

1. Run tests in CI on every pull request, not just in production. A test that only runs nightly catches problems a day late.

Expected output: CI runs dbt test and blocks merges on failures.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_TEJ39-WkNCCT2059XkgxYA