In dbt, how do you write a custom generic test that can be reused from schema YAML?

## Why write one The four built-in generic tests cover keys, nulls, enumerations and referential integrity. Everything else — value ranges, monotonic timestamps, a percentage column that must sum to 100 per group, a string that must match a pattern — is either a singular test written once, a package test, or a generic test you author. Authoring one is worth it the moment the same rule applies to several models. ## The anatomy ```jinja {% test not_negative(model, column_name) %} select * from {{ model }} where {{ column_name }} < 0 {% endtest %} ``` Three things are happening: 1. `{% test <name> %} ... {% endtest %}` declares a generic test named `not_negative`. Under the hood dbt turns it into a macro called `test_not_negative`, which is why older projects (pre-dbt 0.20) define these as plain macros with a `test_` prefix — both still work, but the `{% test %}` block is the current form. 2. `model` is bound to the relation under test and renders as a fully qualified `database.schema.table` (or the subquery, for an ephemeral model). You never write the table name yourself. 3. `column_name` is bound to the column the test is declared under. Omit it from the signature and you have a **model-level** test, declared under the model's own `tests:` key rather than under a column. ## Where the file goes dbt looks for generic test definitions in the `tests/generic/` directory and in `macros/`. One test per file, named after the test, is the convention. No registration step is needed — dbt parses the project and the name becomes available in YAML. ## Extra arguments Any additional keyword arguments in the signature become YAML keys: ```jinja {% test valid_range(model, column_name, min_value, max_value=None) %} select * from {{ model }} where {{ column_name }} < {{ min_value }} {% if max_value is not none %} or {{ column_name }} > {{ max_value }} {% endif %} {% endtest %} ``` ```yaml - name: discount_pct tests: - valid_range: min_value: 0 max_value: 100 ``` Defaults in the signature make arguments optional, and Jinja conditionals let one test cover several shapes — but resist building a test with eight arguments and nested conditionals. At that point the Jinja is harder to review than four small tests. ## Overriding a built-in A generic test defined in your project shadows one of the same name from a package, and a test defined in the root project shadows dbt's own. Teams occasionally redefine `unique` to add a `where` clause or use a cheaper formulation on a specific warehouse. It works, but it is a heavy hammer: every `- unique` in the project silently changes meaning, so it is worth a comment and a code-review conversation. ## Naming, configuring and running The auto-generated node name follows the pattern `<test_name>_<model>_<column>_<args>`, which gets long; add `name:` to the declaration to override it. You can attach a default config inside the definition with `{{ config(severity='warn') }}` at the top of the test block, and callers can override it per declaration. Run it exactly like any other: `dbt test --select <model>`, or `dbt build`. ## Check the packages first Before hand-rolling, look at `dbt_utils` — it ships `expression_is_true`, `accepted_range`, `unique_combination_of_columns`, `not_null_proportion`, `recency`, `equal_rowcount` and more. `dbt_expectations` ports a large set of Great Expectations-style assertions. A candidate who says "first I check whether dbt_utils already has it" is showing the same instinct as one who checks the standard library before writing a utility function. ## The register in an interview This question is a differentiator rather than a gate. Many competent dbt users have never authored a generic test, and no one is failed for that. What earns credit is knowing that the extension point exists, that it is just Jinja plus SQL with two bound variables, and that packages usually got there first.