Built-in Tests in Python

When dbt build prints OK next to a model, it only means the model's SELECT ran. It says nothing about the rows: today stg_orders holds orders 5027, 5040 and 5062 twice, and every model still says OK. A test in dbt is a query that looks for bad rows in a model. dbt build runs a model's tests right after the model: no bad rows means PASS, any bad row means FAIL, and the models that read the failing one are skipped. Data teams write tests so bad data stops where it appears, before it reaches a report.

dbt has four built-in tests that you switch on by name in a .yml file, under a model's column: unique (no value twice), not_null (no empty value), accepted_values (only the values in a list you give) and relationships (every value is found in a column of another model; empty values are skipped). Tests with no settings fit in a short list in square brackets. A test with settings goes on its own line under tests:, after a dash, with its settings in braces like a Python dict: - test_name: {setting: value}. Here is the short list on another model, the customers:

version: 2

models:
  - name: stg_customers
    columns:
      - name: customer_id
        tests: [unique, not_null]

You edit models/schema.yml. It already names the model stg_orders and three of its columns: order_id, status and customer_id. Its tests check these two given models, and models/marts/daily_revenue.sql reads stg_orders:

models/staging/stg_orders.sql

SELECT
    order_id,
    customer_id,
    product,
    TRY_CAST(quantity AS INTEGER) AS quantity,
    CAST(unit_price AS INTEGER) AS unit_price,
    status,
    CAST(ordered_at AS TIMESTAMP) AS ordered_at
FROM {{ source('shop', 'raw_orders') }}

models/staging/stg_customers.sql

SELECT
    customer_id,
    LOWER(TRIM(city)) AS city,
    CAST(joined AS DATE) AS joined_on
FROM {{ source('shop', 'raw_customers') }}

The check runs dbt build, then a query that lists every order_id found more than once in stg_orders. With your tests it must print:

model stg_customers (view): OK
model stg_orders (view): OK
test stg_orders.status accepted_values: PASS
test stg_orders.order_id not_null: PASS
test stg_orders.customer_id relationships: PASS
test stg_orders.order_id unique: FAIL (its query returned 3 rows)
model daily_revenue (view): SKIPPED (something it needs did not build or pass)
order_id | copies
5027 | 2
5040 | 2
5062 | 2
(3 rows)

The FAIL is the point: the unique test found the three copied orders, and dbt skipped daily_revenue so no revenue is built on them. At work you'd type dbt build in a terminal, and the models and tests would run in a warehouse such as Snowflake or BigQuery instead of this DuckDB file.

Your task: in models/schema.yml, give order_id the tests unique and not_null, give status an accepted_values test for 'paid' and 'cancelled', and give customer_id a relationships test: each value must be a customer_id of the model stg_customers.

Assignment
Follow the Coding Tutorial to add the built-in tests to models/schema.yml.

Hint
In YAML the spaces are the structure: tests: lines up with the column's name, eight spaces in, and a test under it goes two more spaces in, after a dash. A tests: with nothing under it yet makes dbt stop reading the project; the next step fixes that. Name the other model with ref('stg_customers'), as in a model's SQL.