Your Own Test

The built-in tests catch copies, empty values, values outside a list and broken links between models. Many rules belong to your shop alone, like "an order's quantity must be more than 0". For those you write a singular test: one SELECT in a .sql file in the project's tests/ folder that returns the rows that break the rule. dbt build runs it: no rows means PASS, any row means FAIL, and the models that read the tested model are skipped. Data teams write them so a business rule is checked on every build, not only when someone thinks to look.

A test reads models with ref, just as a model does, and that tells dbt which model it checks, so it runs right after that model. It needs no .yml: its name is its file name. For example, tests/no_negative_revenue.sql checks that no day of daily_revenue has a revenue below zero:

SELECT
    day,
    revenue
FROM {{ ref('daily_revenue') }}
WHERE revenue < 0

Write it upside down: the WHERE describes the bad rows, not the good ones. Mind NULL, too: a comparison with NULL is never true, so order 5030, whose quantity 'two' became NULL in stg_orders, is not returned (catching empty values is not_null's job). Your test reads this given model, and models/marts/daily_revenue.sql reads it too:

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') }}

The check builds the project, has the loader add orders 5076 (quantity 0) and 5077 (quantity -3) to the raw table, and builds again. It must print:

model stg_orders (view): OK
test positive_quantity: PASS
model daily_revenue (view): OK
The loader adds orders 5076 (quantity 0) and 5077 (quantity -3):
model stg_orders (view): OK
test positive_quantity: FAIL (its query returned 2 rows)
model daily_revenue (view): SKIPPED (something it needs did not build or pass)

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: tests/positive_quantity.sql starts with SELECT and order_id,. Finish it: read stg_orders with ref, add the column quantity, and keep only the orders whose quantity is 0 or less.

Assignment
Follow the Coding Tutorial to write the singular test tests/positive_quantity.sql.

Hint
Read the model with {{ ref('stg_orders') }}, not the raw table. The rule is "more than 0", so the wrong rows have quantity <= 0; < 0 would let the order with quantity 0 through. quantity is the last column before FROM: no comma after it.