Singular tests for business rules

Description

test_is_positive, from the previous lesson, is reusable on purpose: the same macro serves quantity, unit_price, any future numeric column in the project. But there are rules that don't make sense to generalize that way, because they're born from very specific knowledge of this business, about this table, and forcing them inside a generic test would only add complexity with no real reuse gain.

"An order line's revenue (quantity * unit_price) should never be negative" is exactly that case. You could, in theory, apply is_positive to the revenue column — but revenue isn't an input column like quantity or unit_price, it's a calculated column, and the real business rule isn't "revenue must be positive" in the abstract, it's "the product of two columns that, separately, are already protected, shouldn't produce an impossible result." This lesson writes that rule for what it is: a singular test, a complete, specific, hand-written query, that lives outside any column.

Connection to the module. You already know every data test's universal rule since lesson 2 — the SELECT returns the rows that violate the rule, zero rows is success. A singular test applies that exact same rule, with no macro in between: it's, literally, the final SELECT, with no {% test %} wrapping it.

An analogy: the alarm you install for your factory, not for just any factory

Lesson 1 already previewed this analogy: a generic test is the rule you apply to many columns — "every phone must turn on"; a singular test is the alarm you install for one specific situation in your particular factory. A solder oven's temperature sensor makes no sense in a furniture-assembly factory — it's born from understanding, precisely, how this production line works. assert_no_negative_revenue is that alarm for Kiosko: it's born from knowing revenue = quantity * unit_price, that both columns already have their own range rules (lesson 5), and that combining them a certain way (for example, if someday a row arrived with a negative quantity, something is_positive with strict: true should already catch before this) would produce a result no real Kiosko sale could ever have.

Worked example: assert_no_negative_revenue.sql

A singular test is simply a .sql file inside the tests/ folder — the same folder dbt_project.yml reserved since module 1 (test-paths: ["tests"]), and that's been empty until now.

-- tests/assert_no_negative_revenue.sql

-- An order line's revenue (quantity * unit_price) should never be negative:
-- there's no such thing as a sale with "negative revenue" in Kiosko's business.
-- This query returns the lines that violate that rule -- if it returns zero
-- rows, the test passes.
select
    order_id,
    quantity,
    unit_price,
    revenue
from {{ ref('fact_orders') }}
where revenue < 0

Compare this to any generic test you've already written: there's no {% test %}, no {% endtest %}, no model/column_name parameter — it's, directly, the final SELECT, with ref('fact_orders') written by hand, like in any ordinary .sql model. The file's name (assert_no_negative_revenue.sql, minus the extension) automatically becomes the test's name — with you declaring nothing else in any YAML.

Notice the assert_ prefix on the name: it isn't a technical requirement — dbt doesn't require any prefix on a tests/ file — it's the same convention you're going to find in any real dbt project, and one the official documentation already cites in an example sharing this same pattern (assert_total_payment_amount_is_positive). A name starting with assert_ communicates, at a glance, that the file is a claim about the data — "this should be true" — not a model that produces a table.

Running the singular test

You don't need to declare anything in any _models.yml — dbt automatically finds any .sql file inside tests/ and registers it as a test:

dbt test --select assert_no_negative_revenue

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
Found 7 models, 15 data tests, 4 sources, 501 macros

Concurrency: 4 threads (target='dev')

1 of 1 START test assert_no_negative_revenue ................................... [RUN]
1 of 1 PASS assert_no_negative_revenue ......................................... [PASS in 0.02s]

Finished running 1 test in 0 hours 0 minutes and 0.07 seconds (0.07s).

Completed successfully

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1

Found 7 models, 15 data tests — the final number lesson 1 already previewed: 8 inherited from module 2, plus fact_orders's 6 column tests (lessons 3, 4, and 5), plus this singular one, 15 total. And notice something else: dbt test --select fact_orders (without naming the test explicitly) would also include it, even though assert_no_negative_revenue isn't declared inside any fact_orders column in any YAML:

dbt test --select fact_orders

What to expect. The report includes all 7 tests — the 6 column ones plus this singular one — PASS=7. dbt automatically detects this test depends on fact_orders, with the same dependency mechanism ref() already builds in any model: since the file writes {{ ref('fact_orders') }}, dbt knows this test belongs to the same stretch of the DAG, with you never declaring it anywhere but inside the .sql file itself.

Worked example (continued): triggering a real FAIL

Today, fact_orders has no row with negative revenue — that's why the test passes. To see the FAIL with your own eyes, temporarily add a file with a single deliberately broken order, with a negative quantity (something is_positive with strict: true, from lesson 5, should already catch on its own — you're going to confirm both at once):

-- raw_data/kiosko/orders_2026-08-11.csv (temporary, only for this demonstration)
order_id,store_id,product_id,quantity,unit_price,order_ts
ORD-9001,S01,P001,-2,0.55,2026-08-11T08:00:00

dbt run --select fact_orders
dbt test --select assert_no_negative_revenue

What to expect.

1 of 1 START test assert_no_negative_revenue ................................... [RUN]
1 of 1 FAIL 1 assert_no_negative_revenue ....................................... [FAIL 1 in 0.02s]

Completed with 1 error, 0 partial successes, and 0 warnings:

[ERROR]: in test assert_no_negative_revenue (tests/assert_no_negative_revenue.sql)
  Got 1 result, configured to fail if != 0

  compiled code at target/compiled/kiosko_analytics/tests/assert_no_negative_revenue.sql

Done. PASS=0 WARN=0 ERROR=1 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1

FAIL 1 — exactly one row, the one you just added. Confirm which one, with the test's same query but run by hand:

dbt show --inline "select order_id, quantity, unit_price, revenue from {{ ref('fact_orders') }} where revenue < 0"

What to expect.

Previewing inline node:
| order_id | quantity | unit_price | revenue |
| -------- | -------- | ---------- | ------- |
| ORD-9001 |       -2 |       0.55 |    -1.1 |

ORD-9001: -2 * 0.55 = -1.1 — revenue really is negative, the arithmetic confirms exactly what the test detected. And, as the worked example previewed, this same row also fails lesson 5's test on quantity, because -2 isn't a positive value:

dbt test --select fact_orders

What to expect. Two FAILs, not one: assert_no_negative_revenue (FAIL 1, from revenue < 0) and is_positive_fact_orders_quantity__True (FAIL 1, from quantity <= 0). The other five tests (unique, not_null, accepted_values, relationships, is_positive on unit_price) still show PASS — ORD-9001 has a unique, non-null order_id, a valid product_id and store_id, and a positive unit_price. Two different tests, looking at the same row from two different angles, each catching the problem for its own reason — the same kind of defense in depth you already saw in lesson 4, when unique_stg_orders_order_id and unique_fact_orders_order_id would catch the same duplicate at two different layers.

Undo the demonstration before continuing:

rm raw_data/kiosko/orders_2026-08-11.csv
dbt run --select fact_orders
dbt test

What to expect. Back to PASS=15 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=15, and fact_orders back to 40 rows and 106.15 total revenue.

Going deeper: why not just trust is_positive on quantity and unit_price?

It's a fair question: if is_positive on quantity (strict) and on unit_price (permissive) already guarantees neither input column is negative, isn't it guaranteed, by arithmetic, that their product (revenue) also can't be negative? The answer is yes, today — with fact_orders.sql's current formula (quantity * unit_price), it's mathematically impossible for revenue to be negative if neither factor is. But a singular test on revenue doesn't depend on that indirect guarantee: if someday someone changed the formula — for example, to subtract a discount (quantity * unit_price - discount_amount) — the "positive by arithmetic" guarantee would automatically stop holding, and only a test that looks at revenue directly would keep protecting the real property that matters to the business: that the reported revenue is never negative, no matter how it was calculated.

This is the same lesson lesson 4 already left with relationships: a test isn't unnecessary just because it always passes today for an indirect reason — it's there for the day that indirect reason changes.

Common mistakes

Writing a singular test's SELECT to return the valid rows, instead of the invalid ones. What happens: someone, reasoning "I want to confirm revenue is positive," writes where revenue >= 0 instead of where revenue < 0. Why it happens: it's a subtle inversion of logic — "checking something is true" feels like "selecting the cases where it's true," when dbt's real convention is the opposite. How to spot it: with this inverted version, the test would only pass if every row had negative revenue (a nonsensical result for real Kiosko data), and would constantly fail over completely healthy data, with a FAIL equal to the table's total row count. How to fix it: remember lesson 2's universal rule — a singular test always returns the rows that violate the rule, never the ones that satisfy it; if your test fails with a suspiciously high row count over data you know is fine, first check whether you inverted the condition.

Putting a semicolon at the end of the query. What happens: someone, out of habit from writing SQL in other tools, ends the file with where revenue < 0;. Why it happens: a trailing semicolon is an almost automatic habit in many SQL editors. How to spot it: dbt wraps your test file's content inside its own query (to be able to count the rows), and an extra semicolon usually produces a syntax error at compile time, with a message pointing at the end of the file. How to fix it: never end a singular test file (or any dbt .sql model) with a semicolon — dbt controls how and when to wrap your query, and an extra ; breaks that mechanism.

Forgetting a singular test also shows up in dbt build, not only in dbt test. What happens: someone assumes that, since this test lives in a separate folder (tests/, not models/), it only runs when explicitly asked for. Why it happens: the physical folder separation incorrectly suggests a behavior separation. How to spot it: this guide's module 8 is going to use dbt build, the command that runs models, snapshots, and tests together, in DAG order — at that point, assert_no_negative_revenue runs automatically after fact_orders gets built, with no one selecting it by hand. How to fix it: treat any test — singular or generic — as an integral part of the project, not a separate optional step; dbt test and dbt build treat it exactly that way.

Exercises

Exercise 1 — Write a second singular test, for a different rule. Kiosko should never have an order with quantity greater than 100 units in a single line (a reasonable business limit for a convenience store, not a wholesale supermarket). Write tests/assert_no_excessive_quantity.sql expressing that rule, and confirm it passes over Kiosko's real data.

-- tests/assert_no_excessive_quantity.sql

-- No order line should exceed 100 units: Kiosko is a convenience store, not a
-- wholesaler. This query returns the lines that violate that rule -- if it
-- returns zero rows, the test passes.
select
    order_id,
    quantity
from {{ ref('fact_orders') }}
where quantity > 100

dbt test --select assert_no_excessive_quantity

Exercise 2 — Break exercise 1's test on purpose. Temporarily add an order with quantity = 500 (using this lesson's same temporary-file pattern), run dbt test --select assert_no_excessive_quantity, and confirm the FAIL.

-- raw_data/kiosko/orders_2026-08-11.csv (temporary)
order_id,store_id,product_id,quantity,unit_price,order_ts
ORD-9002,S02,P002,500,1.20,2026-08-11T09:00:00

dbt run --select fact_orders
dbt test --select assert_no_excessive_quantity

1 of 1 START test assert_no_excessive_quantity ................................. [RUN]
1 of 1 FAIL 1 assert_no_excessive_quantity ..................................... [FAIL 1 in 0.02s]

Exercise 3 — Argue why assert_no_negative_revenue needs no parameter at all, unlike test_is_positive. In 2-3 sentences, explain why it makes sense for a singular test, unlike a generic one, to receive no configurable argument like model, column_name, or strict.

Summary and next step

In this lesson you wrote the project's first singular test: assert_no_negative_revenue.sql, a complete query in tests/, with no macro in between, checking that revenue is never negative. You confirmed PASS over Kiosko's real data, triggered a real FAIL 1 with a deliberate negative-quantity order, and saw, with that same row, how two different tests (assert_no_negative_revenue and is_positive on quantity) can catch the same problem from different angles.

Before moving on you should be able to: write a singular test's minimal structure from memory (a SELECT that returns the invalid rows, with no {% test %} or any additional YAML); and explain when a singular test is the right tool instead of a generic one.

With the project's seven tests already written — the six column ones from lessons 3 through 5, plus this singular one — lesson 7 stops on something you've already used several times without stopping to fully explain it: how to read, precisely, dbt test's complete report — the difference between PASS, WARN, ERROR/FAIL, and SKIP, and what decision to make in the face of each one.

Resources

• dbt Developer Hub — "Add data tests to your DAG," the singular tests section, with the official example (assert_total_payment_amount_is_positive) that inspires this lesson's naming convention. docs.getdbt.com/docs/build/data-tests. In English.
• dbt Developer Hub — "About dbt build," which documents how dbt build runs singular tests in the same DAG order as models and snapshots — the basis for this module's lesson 8 and for all of module 8. docs.getdbt.com/reference/commands/build. In English.
• DuckDB — "Comparison Operators," which documents three-value behavior (true/false/NULL) in comparisons, the technical basis for why revenue < 0 never "catches" a null revenue by accident. duckdb.org/docs/stable/sql/expressions/comparison_operators. In English.