# Add Unit Tests to a dbt Project

Yes. An agent finds dbt models with logic and no unit test, writes given and expect fixtures in each model's YAML, runs them on DuckDB and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/add-unit-tests/dbt/). Opens the console with this prompt ready to send, in a Project that has your repository.

## What the agent does

The agent takes stock of what is already unit tested before it writes a fixture. In a project shaped like dbt-labs/jaffle-shop, `dbt_project.yml` requires dbt `>=2.0.0`, names the profile `default`, keeps data tests in `data-tests/` and builds `staging` as views and `marts` as tables. Three models already carry a `unit_tests:` block in their YAML: `orders` checks that food and drink counts become booleans, `order_items` checks that supply costs sum per item, and `stg_locations` checks that `opened_at` truncates to a date from the `raw_stores` source. The rest have none, and several of them hold the kind of logic a unit test is for: `customers` derives `customer_type` as `returning` when `count(distinct order_id) > 1` and `new` otherwise, with lifetime sums and first and last order dates over a left join; `stg_products` turns `type = 'jaffle'` and `type = 'beverage'` into `is_food_item` and `is_drink_item` with `coalesce` to `false` and converts `price` with the `cents_to_dollars` macro; `stg_orders` converts three cent columns with the same macro and truncates `ordered_at` to a day. `products`, `supplies` and `locations` are pass-throughs, and `stg_customers` only renames, so they get no fixture.

The agent writes each test in the model's own YAML, which is where dbt requires them, in the inline `dict` format the existing three use. For `customers`, `given` provides `stg_customers` rows for three people and `orders` rows with two orders for the first, one for the second and none for the third, and `expect` asserts `returning`, `new` and `new` with the matching `count_lifetime_orders`, `lifetime_spend` sums and null dates for the customer with no orders. For `stg_products`, `given` provides `source('ecom', 'raw_products')` rows with a jaffle, a beverage, another type and a null type and a price in cents, and `expect` asserts the two booleans and `product_price` in dollars. For `stg_orders`, `given` provides `raw_orders` rows with cent amounts and a timestamp, and `expect` asserts `subtotal`, `tax_paid`, `order_total` and the day-truncated `ordered_at`. Every `ref` and `source` the model reads is listed as an input, which the documentation says avoids a node-not-found error at compile time.

Then it runs them, which a unit test needs a warehouse for. The repository has no `profiles.yml` (it is gitignored) and its CI runs jobs in dbt Cloud through `.github/workflows/scripts/dbt_cloud_run_job.py` with an API key secret, so the agent writes a `profiles.yml` for a DuckDB target in the sandbox, installs dbt with the DuckDB adapter at a version that satisfies `require-dbt-version`, runs `dbt deps` for `dbt_utils 1.4.1` and `audit_helper` from `packages.yml`, and seeds the sample data with `dbt seed --vars '{"load_source_data": true}'`, the command the README gives. The documentation says the direct parents of a tested model must exist first, so it runs `dbt run` for them, or `dbt run --empty` where seeding is not wanted, then `dbt test --select "test_type:unit"` and pastes the result, including the three pre-existing tests passing beside the new ones.

The `cents_to_dollars` macro dispatches per adapter with `default`, `postgres`, `bigquery` and `fabric` branches, so the agent notes which branch the DuckDB run exercised and that the fixtures assert values, not SQL, so they hold on the warehouse the dbt Cloud jobs use. It runs `sqlfluff` through the repository's `.pre-commit-config.yaml` hooks on the changed YAML and leaves `data-tests/` untouched.

## Start it with one API call

`POST https://api.phoenix.so/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "Add unit tests to this dbt project and open a pull request. Read dbt_project.yml, packages.yml, every model SQL and YAML under models/, the macros, and the existing unit_tests blocks first. Write unit tests only for models that transform data and have none: at least customers (customer_type, lifetime sums, first and last order dates, a customer with no orders), stg_products (is_food_item, is_drink_item including a null type, product_price from cents) and stg_orders (the three cents_to_dollars columns and the day-truncated ordered_at). Put each test in the model's own YAML in the inline dict format the existing tests use, and list every ref and source the model reads as an input. Skip pass-through and rename-only models and say which. In the sandbox write a profiles.yml for DuckDB, install dbt with the DuckDB adapter at a version that satisfies require-dbt-version, run dbt deps, seed with load_source_data true, build the parents, run dbt test --select test_type:unit, and paste the output in the pull request. Do not change any model SQL or data test.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
```

1. **Get an API key.** In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
2. **Send the request.** POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
3. **Read what came back.** Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

## What comes back

What comes back is a pull request that adds a `unit_tests:` block to `models/marts/customers.yml`, `models/staging/stg_products.yml` and `models/staging/stg_orders.yml` and changes no SQL. The description lists each test with the behaviour it pins down, names the pass-through models it skipped and why, and shows the `dbt test --select test_type:unit` output from the sandbox with the existing and the new tests together.

The repository's own CI runs dbt Cloud jobs on pull requests to `main` and `staging` through `dbt_cloud_run_job.py`, so the new tests also run there on the warehouse the project is connected to; the sandbox run on DuckDB is the agent's proof, and the Cloud job is the check on the pull request.

A reviewer reads each `expect` row against the model SQL to confirm it asserts the rule and not the fixture, checks that the customer with no orders exercises the left join, and that no fixture row contradicts a data test in the same YAML. Then they merge.

## Run it on every model without a unit test

A team that wants every mart to carry a unit test adds a `schedule` automation on the Project with this prompt and a weekly `cron`, or sends one `POST /o/{org}/sessions` with the repository in `resources` after each model lands. Each run reads the `unit_tests:` blocks that exist and writes only the missing ones, so a project where all three marts are covered opens no pull request and says so.

An automation run has its own `max_runs_per_hour` cap. Give an API key a `webhook_url` for the runs it starts and Rush POSTs `run.completed` when each turn ends, with `session_url` to read the pull request from.

## Why run it on Rush?

A unit test in dbt is small to write and awkward to run: the parents have to exist in a warehouse, the packages have to be installed, and a fixture that references a model it did not list fails at compile time with a message about a missing node. In a Rush sandbox the agent stands up DuckDB, seeds the sample data, builds the parents and runs the tests for real, and the output is kept in the run's record beside the YAML.

The judgement is which models deserve a fixture. The agent reads the SQL, skips `select *` pass-throughs, and spends the rows on `customer_type`, the booleans in `stg_products` and the cent conversions, which is where a change would silently alter numbers. The same request works unchanged on the next dbt project.

## Frequently asked questions

### How is this different from data tests?

A data test such as `not_null` or `dbt_utils.expression_is_true` runs against real rows after the model builds. A unit test gives the model fixed input rows and asserts the output rows, so it checks the SQL's logic on cases you choose, such as a customer with no orders, before any data exists.

### Which models get a test?

Those with logic: `customers`, `stg_products` and `stg_orders` here. `products`, `supplies` and `locations` are `select *` from staging and `stg_customers` only renames columns, so a fixture would assert nothing, and the pull request names them as skipped.

### How does it run without my warehouse?

The sandbox uses a DuckDB profile written for the run, with the seeds loaded by the README's `dbt seed` command. The fixtures assert values rather than SQL, so they pass the same way on the warehouse the project's dbt Cloud jobs use, where the pull request check runs them.

### What dbt version is needed?

The documentation says unit tests are available from dbt 1.8, and this project's `require-dbt-version` is `>=2.0.0`, so the agent installs a version that satisfies that constraint along with the DuckDB adapter and records the exact versions in the pull request.

## More dbt jobs

- [Convert a dbt Mart to an Incremental Model](https://getrush.ai/run/convert-to-incremental-model/dbt/)
- [Document dbt Models and Find Data Quality Gaps](https://getrush.ai/run/dbt-model-docs/dbt/)
- [Publish dbt Docs to GitHub Pages](https://getrush.ai/run/publish-dbt-docs/dbt/)

## More jobs like this: Write the missing tests

- [Add Visual Regression Tests With Playwright](https://getrush.ai/run/add-visual-regression-tests/playwright/)
- [Add Visual Regression Tests to Storybook Stories](https://getrush.ai/run/add-visual-regression-tests/storybook/)
- [Migrate a Cypress Test Suite to Playwright](https://getrush.ai/run/migrate-cypress-to-playwright/cypress/)

## Keep reading

- [How a Rush run works on your repository, from request to pull request](https://getrush.ai/cloud/)
- [Compare cloud agents, agent sandboxes and AI code review](https://getrush.ai/compare/)
- [Rush pricing: Free, Pro and Max](https://getrush.ai/pricing/)
