Data tests check the data you built
A dbt data test asserts a property of an existing model or seed. A unit test instead checks transformation logic with controlled inputs and expected outputs. This starter covers data tests only. Passing these checks does not prove all business logic correct: missing orders, a wrong revenue definition or stale data can still pass.
Download and run locally
Download the dbt + DuckDB starter ZIP
No form or account. Use Python 3.10. The kit pins dbt-core 1.9.6, dbt-duckdb 1.9.6 and DuckDB 1.3.2. Installing packages requires internet access; running the project needs no warehouse credentials.
Extract the ZIP, enter the dbt-data-tests-starter directory and run these commands on macOS or Linux. On Windows PowerShell, create the environment with py -3.10 -m venv .venv and activate with .venv\Scripts\Activate.ps1, then run the same pip and dbt commands.
python3.10 -m venv .venv
. .venv/bin/activate
python -m pip install -r requirements.txt
dbt debug --profiles-dir .
dbt seed --profiles-dir .
dbt run --profiles-dir .
dbt test --profiles-dir .Expect eight passing tests. Seed loads CSVs, run builds the orders table, and test checks the existing data. Keep --profiles-dir . on every command to use the included profile. No dbt init or dbt deps step is needed.
Where each file goes
Keep these paths relative to the extracted project root. The examples below are the same files included in the download. The README includes the run commands and failure exercises for sharing with your team.
The project YAML declares file paths and seed types. The profile writes starter.duckdb locally. The customers seed supplies parent keys; raw_orders supplies model inputs. The SQL model uses ref to declare its dependency. Put generic checks in models/orders.yml and the standalone negative-amount query in tests/orders_no_negative_amounts.sql.
Four generic checks and one singular SQL test
Generic tests reuse dbt’s built-in assertions through YAML. The standalone SQL test returns violations; zero rows means pass. This kit uses the top-level YAML arguments supported by dbt 1.9, rather than the arguments wrapper shown in newer documentation.
- not_null: order_id, customer_id, status and amount must be populated.
- unique: order_id must identify one row at the chosen order grain.
- relationships: each non-null customer_id must exist in the customers seed. This does not prove that the parent key is unique.
- accepted_values: each non-null status must be placed, shipped or completed.
- The singular test selects order_id and amount where amount < 0. SQL skips null amounts here; the separate not_null check catches those.
- Relationships and accepted_values skip nulls too. Keep not_null when a field is required.
Break an order and read the failure
In seeds/raw_orders.csv change order 102’s amount from 40.50 to -40.50. Run dbt seed --full-refresh --profiles-dir ., then dbt run --profiles-dir ., then dbt test --profiles-dir . again. Expect FAIL 1 for orders_no_negative_amounts and a nonzero exit code. Running test alone does not reload a CSV or rebuild the model.
The terminal identifies the failing test and its failure count. target/run_results.json records status and messages; target/compiled/ contains the generated SQL. The singular query returns order 102 and its negative amount. Generic tests may return grouped duplicates or counts rather than complete source records.
To persist the bad rows locally, run dbt test --select orders_no_negative_amounts --store-failures --profiles-dir . and inspect the failure relation named in the output with a DuckDB SQL client. Restore 40.50 and repeat seed, run and test to return to eight passes.
Try other failures one at a time: blank order 102’s order_id for not_null; change it to 101 for unique; set its customer_id to 999 for relationships; or set its status to cancelled for accepted_values. Restore the CSV and repeat seed, run and test after each experiment.
Adapt the assumptions before the files
Choose your model’s row grain and required fields first. Replace the synthetic seeds with small local samples, align CSV headers with the SQL and set appropriate column types in dbt_project.yml. Use a trusted parent key for relationships and agree the allowed statuses with their owner.
Negative amounts may represent legitimate refunds. Replace that rule with your actual business contract before applying it. This model deliberately keeps invalid records visible instead of filtering them out to make tests pass.
These checks are a starting point for tested models. For help defining the contracts, building the models and investigating failures in a production stack, see the analytics-engineering service.