Writing your own generic test: `test_is_positive`

Description

unique, not_null, accepted_values, and relationships cover four universal properties — uniqueness, presence, list membership, referential integrity. But fact_orders has two numeric columns, quantity and unit_price, that need a different property, and none of the four out-of-the-box tests expresses it: "this value must be positive." It isn't a closed list (like accepted_values), it isn't a reference to another table (like relationships) — it's a range, the same kind of rule data-engineering-foundations-guide (module 5) already validated by hand, in plain Python, over this same data.

This lesson writes that rule once, as a reusable Jinja macro — a custom generic test — and applies it to the two columns that need it, with a parameter that adjusts its behavior with no SQL line duplicated. By the end, you're going to have the same kind of tool you already used four times without writing it yourself — a rule, applicable to any numeric column of any future model in this project — and you're going to understand, with a real demonstration, a classic SQL trap a badly written range test can let slip through silently.

Connection to the module. Lessons 3 and 4 declared the four tests that already existed inside dbt-core. This lesson is this guide's first time you write a test's logic from scratch — the same conceptual leap you already took in module 3 when you went from using ref() to understanding what it builds under the hood.

An analogy: the template with a field that fills in differently case by case

Think of a customs form with a "declared value, in whichever currency applies" field — the same template serves for declaring dollars, euros, or pesos, with the form never changing: only what gets written in the field changes. A generic test macro works the same way: the SELECT is always the same template, with a gap ({{ column_name }}) that fills in differently depending on which column uses it. And, like the customs form that sometimes needs an extra box ("is it a donation? Yes/No," which changes what rules apply), your test can also accept an optional parameter that adjusts its behavior with no need to write a new form for every case.

Worked example: the test_is_positive macro

Create the file in the folder dbt_project.yml already reserved for macros since module 1:

-- macros/test_is_positive.sql
{% test is_positive(model, column_name, strict=true) %}

with validation as (

    select {{ column_name }} as value_to_check
    from {{ model }}

),

validation_errors as (

    select value_to_check
    from validation
    where value_to_check is null
        or {% if strict %}
            value_to_check <= 0
        {% else %}
            value_to_check < 0
        {% endif %}

)

select *
from validation_errors

{% endtest %}

Read this piece by piece, because each one answers a concrete decision.

{% test is_positive(model, column_name, strict=true) %} ... {% endtest %}. This block, not an ordinary {% macro %}, is the specific syntax dbt recognizes as a generic test's definition — the name in parentheses (is_positive) is, from this file on, a new word you can use under data_tests:, exactly like you already used unique or accepted_values. model and column_name are the two parameters every generic test receives automatically — dbt fills them in for you with the model and column where you declared the test; strict=true is a third parameter, specific to this test, with a default value (if you don't specify it when declaring the test, it assumes true).

The file's name (macros/test_is_positive.sql) follows a convention, not a technical requirement. You could call it my_rules.sql and it would work the same — what defines the test's name is what goes in parentheses after {% test, not the file's name — but prefixing with test_ is the convention you're going to find in any real dbt project, the same reason _sources.yml and _models.yml carry an underscore since module 2: it makes the file's purpose obvious just by looking at the folder listing.

The validation CTE isolates the column being tested, before evaluating anything. Instead of writing the condition directly against {{ model }}, it first selects {{ column_name }} under a fixed alias (value_to_check) — a pattern that makes the second CTE (validation_errors) identical no matter which column or which model it receives, with a single reference to {{ column_name }} in the whole file, not two.

where value_to_check is null or ... — the is null isn't decoration. A null value isn't "positive" or "negative": it's the absence of a value, and no direct numeric comparison (< 0, <= 0) detects it, for a SQL reason this lesson's Common mistakes section demonstrates with real data. This test explicitly decides that an absent value also doesn't count as valid for "is_positive" — you can't confirm something absent is positive, so it's treated as a failure.

{% if strict %} ... {% else %} ... {% endif %} — Jinja deciding, at compile time, what SQL to generate. If strict is true, the failure condition is value_to_check <= 0 (zero also counts as invalid). If strict is false, the condition is value_to_check < 0 (zero is valid, only negative fails). This is the same asymmetry data-engineering-foundations-guide (module 5) already validated by hand in Python: quantity must be strictly greater than zero (no one buys zero units of anything), but unit_price can be exactly zero (a product given away in a promotion is still a real sale). With a single parameterized test, you express both rules with no logic duplicated.

Worked example (continued): applying the test to quantity and unit_price

Extend, once more, models/marts/_models.yml:

# models/marts/_models.yml (fragment, inside fact_orders: columns:)
      - name: quantity
        data_tests:
          - is_positive:
              arguments:
                strict: true
      - name: unit_price
        data_tests:
          - is_positive:
              arguments:
                strict: false

quantity uses strict: true — foundations' same strict rule, quantity > 0. unit_price uses strict: false — the permissive rule, unit_price >= 0. Notice both declarations use the same test name (is_positive), the same arguments: syntax you already used in lesson 4 for accepted_values and relationships — a custom generic test gets declared exactly the same way as an out-of-the-box one, because to dbt there's no difference between the two once they're written.

Running the two new tests

dbt test --select fact_orders

What to expect.

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

Concurrency: 4 threads (target='dev')

1 of 6 START test accepted_values_fact_orders_product_id__P001__P002__P003__P004  [RUN]
2 of 6 START test is_positive_fact_orders_quantity__True ....................... [RUN]
3 of 6 START test is_positive_fact_orders_unit_price__False .................... [RUN]
4 of 6 START test not_null_fact_orders_order_id ................................ [RUN]
4 of 6 PASS not_null_fact_orders_order_id ...................................... [PASS in 0.05s]
2 of 6 PASS is_positive_fact_orders_quantity__True ............................. [PASS in 0.06s]
3 of 6 PASS is_positive_fact_orders_unit_price__False .......................... [PASS in 0.06s]
1 of 6 PASS accepted_values_fact_orders_product_id__P001__P002__P003__P004 ..... [PASS in 0.06s]
5 of 6 START test relationships_fact_orders_store_id__store_id__ref_dim_store_ . [RUN]
6 of 6 START test unique_fact_orders_order_id .................................. [RUN]
6 of 6 PASS unique_fact_orders_order_id ........................................ [PASS in 0.02s]
5 of 6 PASS relationships_fact_orders_store_id__store_id__ref_dim_store_ ....... [PASS in 0.02s]

Finished running 6 data tests in 0 hours 0 minutes and 0.15 seconds (0.15s).

Completed successfully

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

Notice the autogenerated name: is_positive_fact_orders_quantity__True and is_positive_fact_orders_unit_price__False — dbt includes strict's exact value (capitalized, True/False, because that's how Jinja renders a Python boolean) right in the name, the same self-explanatory pattern you already saw with accepted_values's values in lesson 4. And 501 macros in the header, one more than in previous lessons (500 macros) — the project's total macro count, including your new test macro, alongside the ones dbt-core ships with.

Confirm the compiled SQL, with lesson 2's same technique:

dbt compile --select is_positive_fact_orders_quantity__True
cat target/compiled/kiosko_analytics/models/marts/_models.yml/is_positive_fact_orders_quantity__True.sql

What to expect.

with validation as (

    select quantity as value_to_check
    from "kiosko"."main"."fact_orders"

),

validation_errors as (

    select value_to_check
    from validation
    where value_to_check is null
        or
            value_to_check <= 0


)

select *
from validation_errors

Jinja already resolved the {% if strict %} at compile time: since strict is true for quantity, the final SQL only contains the value_to_check <= 0 branch — the else branch never shows up in the compiled file. This is the same conditional-compilation technique underlying any dbt macro, applied here for the first time to a test you wrote yourself.

Common mistakes

Forgetting the is null and letting an absent value slip through silently. This is the lesson's most important trap, and it's worth seeing with real data, not just in theory. Imagine you wrote the test without the first condition:

-- broken version, WITHOUT the null check -- do NOT use this version
where {% if strict %}
        value_to_check <= 0
    {% else %}
        value_to_check < 0
    {% endif %}

If unit_price had a row with a null value — exactly what you're going to see in lesson 8, when you add a data batch with an empty price — this broken version reports PASS, with no error at all. Why: in SQL, any comparison against NULL (NULL < 0, NULL <= 0, NULL = 5, any of them) doesn't evaluate to true or false — it evaluates to a third value, unknown, and a WHERE clause only keeps rows where the condition is true. A row with NULL simply disappears from the results, neither included nor excluded on purpose — it gets silently filtered out, as if it never existed. How to spot it: if a numeric range test never catches a column you know has empty values, immediately suspect a comparison with no explicit IS NULL. How to fix it: any range test that must also treat absent values as invalid needs an explicit OR column IS NULL — never assume a numeric comparison "also" catches nulls, because in standard SQL, it never does.

Forgetting strict's default value, and breaking any declaration that doesn't specify it. What happens: someone writes {% test is_positive(model, column_name, strict) %}, with no =true after strict, and later declares is_positive on a column without passing arguments: strict: .... Why it happens: it's easy to forget that, in Jinja as in Python, a parameter with no default value is required — if whoever declares the test doesn't provide it, dbt fails to compile, with a missing-argument error. How to spot it: dbt parse (or any command that compiles the project) fails with a message about a required argument that wasn't provided. How to fix it: always define a reasonable default value for any optional parameter of your test — strict=true, as in this lesson's example — so declaring the test with no such parameter (plain - is_positive, no arguments:) stays valid and uses that default behavior.

Thinking a custom generic test needs to live in tests/, like a singular test. What happens: someone, familiar with the tests/ folder they're going to use in lesson 6, saves test_is_positive.sql there instead of in macros/. Why it happens: the folder's name (tests/) sounds like the obvious place for "anything test-related." How to spot it: if dbt doesn't recognize is_positive as a valid test under data_tests:, and the error mentions it can't find any generic test with that name, check which folder you saved the file in. How to fix it: a generic test — reusable, parameterized, defined with {% test %} — lives in macros/ (or a subfolder configured as macro-paths), because it technically is a Jinja macro; a singular test — a complete, specific query, with no parameter at all — lives in tests/, as you're going to see in lesson 6. The right folder depends on what type of test you're writing, not on the word "test" being in the file's name.

Exercises

Exercise 1 — Confirm the asymmetry with dbt show. Without running dbt test, use dbt show --inline to write the query that would confirm, by hand, that no row of fact_orders has unit_price exactly equal to zero today — and another one confirming that, if it did, is_positive with strict: false would let it through.

dbt show --inline "select count(*) as n from {{ ref('fact_orders') }} where unit_price = 0"

Previewing inline node:
|  n |
| -- |
|  0 |

Exercise 2 — Add a third use of the test, on a hypothetical column. If fact_orders had a discount_pct column (discount percentage, which should never be negative but can be exactly zero when there's no discount), what data_tests: block would you write, reusing is_positive as it is, with no change to the macro?

      - name: discount_pct
        data_tests:
          - is_positive:
              arguments:
                strict: false

Exercise 3 — Argue why is_positive shouldn't accept an arbitrary min_value, instead of just strict. In 2-3 sentences, explain why this lesson chose a simple boolean parameter (strict) instead of a more general design, like the column_values_between(min_value, max_value) dbt officially documents, even though both solve a similar problem.

Summary and next step

In this lesson you wrote your first custom generic test: test_is_positive.sql, a macro with the {% test %}/{% endtest %} syntax, parameterized with a boolean (strict) that expresses, with a single piece of reusable logic, the two asymmetric range rules data-engineering-foundations-guide already validated by hand — strict quantity > 0, permissive unit_price >= 0. You applied it to the two columns with the same arguments: syntax you already knew, confirmed PASS=6 on fact_orders, and saw, with a real comparison, why forgetting IS NULL in a range test lets absent values through in complete silence.

Before moving on you should be able to: write a generic test's minimal structure from memory ({% test name(model, column_name) %} ... {% endtest %}); and explain why a numeric comparison alone (< 0) never detects a NULL value in SQL.

Lesson 6 switches tools: instead of a reusable generic test, you're going to write a singular test — a complete, hand-written query, living in tests/ instead of macros/ — for a business rule that doesn't fit naturally into any pattern you already know: that every order line's revenue is never negative.

Resources

• dbt Developer Hub — "Custom generic data tests," the complete official reference for {% test %} syntax, including defining additional parameters beyond model/column_name. docs.getdbt.com/best-practices/writing-custom-generic-tests. In English.
• dbt Developer Hub — "Jinja and macros," the general reference for Jinja macros in dbt — a generic test is, technically, a special kind of macro. docs.getdbt.com/docs/build/jinja-macros. In English.
• PostgreSQL — "Comparison Functions and Operators," the section on NULL's behavior in comparisons (IS [NOT] NULL versus operators like <, <=), the SQL standard DuckDB also follows and that explains this lesson's common mistake. postgresql.org/docs/current/functions-comparison.html. In English.