All jobs

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 RushOpens 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 with this body:

{
  "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.

The use case this job follows, with its automation and trigger: Write the missing tests.

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

More jobs like this: Write the missing tests

Keep reading