Generic and Singular Tests

A dbt 37,942 test is a query that returns the rows breaking a rule; zero rows pass. Generic tests are parameterized and attached to columns in YAML. Four ship with dbt: unique, not_null, accepted_values and relationships, the last being a foreign-key check for engines that don't enforce one. The fact model's tests:

models/marts/_marts.yml (excerpt): generic tests on fct_salesYAML

  - name: fct_sales
...
    columns:
      - name: book_key
        data_tests:
          - relationships:
              name: fct_sales_book_fk
              arguments: {to: ref('dim_book'), field: book_key}
...
      - name: status
        data_tests:
          - accepted_values:
              name: fct_sales_status_valid
              arguments:
                values: [pending, paid, shipped, delivered, returned, cancelled]

Arguments go under arguments:, the form current dbt expects; name: replaces a long generated name. A singular test is a SQL file in tests/ for a rule specific to one project, here the reconciliation of BookNest's Star Schema:

tests/assert_fct_sales_reconciles.sql: a singular testSQL

-- Singular test: fails (returns a row) if the fact table and the source disagree.
select f.lines, s.lines as source_lines, f.gross, s.gross as source_gross
from (select count(*) as lines, sum(gross_amount) as gross from {{ ref('fct_sales') }}) f
cross join (select count(*) as lines, sum(qty * unit_price) as gross
            from {{ source('shop', 'order_items') }}) s
where f.lines <> s.lines or f.gross <> s.gross

The test earned its place. After the two sample orders of Incremental Models were deleted from the shop database, the fact table still held their three lines, because an incremental run only adds and replaces rows:

Testing the fact model after source rows were deleted

dbt test --select fct_sales

Output

17:27:38  2 of 5 PASS fct_sales_book_fk .......................... [PASS in 0.99s]
17:27:38  1 of 5 FAIL 1 assert_fct_sales_reconciles .............. [FAIL 1 in 1.11s]
...
17:27:38  Done. PASS=4 WARN=0 ERROR=1 SKIP=0 NO-OP=0 REUSED=0 TOTAL=5

dbt build --select fct_sales --full-refresh rebuilt all 137,944 lines and all six nodes passed. Incremental models never see deletes, so pair them with a reconciliation. A test with config: {severity: warn} reports without stopping the build; Lakehouses, Data Quality and Governance goes further with data quality tools.

MENU